.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Total Sales and Total Last Year Sales

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server

I have this code below that works fine. But since i have filtered the query to only show 10 specific items. I only get the total yr and ly sales for the specific items. I want to get the Total Sales for all items. Is there a way i can create a set or a measure that would show the Total sales no matter what the item number. Without having to take the filter off the report. Please help!


Also I want to add that I want to display ranking on the sales for the selected items only. So this is why i did the report this way.





MEMBER [Measures].[LY Sales] AS (ParallelPeriod([Time].[Fiscal Period].[Year], 1, [Time].[Fiscal Period].CurrentMember), [Measures].[Vet PL Gross Sales Amt])


NON EMPTY{[Measures].[Vet PL Gross Sales Amt] ,  [Measures].[LY Sales]}


, NON EMPTY { ( (DESCENDANTS([Sell To Customer].[Geography].[RC Division].&[0], 

[Sell To Customer].[Geography].[Customer]) 

* [Sell To Customer].[City].[City].ALLMEMBERS 

* {[Brand].[Brands].[Item].&[230],




View Complete Post

More Related Resource Links

Sales Total, Get Curent Month

The date functions in sql is pretty complex to me. I want to get the sum of Grand Total for the current month. OrderDate is a DateTme Column. SELECT SUM(GrandTotal) FROM CompletedOrdersHistory WHERE OrderStatus='COMPLETED' AND OrderDate=@CurrentMonth;

How to Get Total Sales for Customers that ever bought Product X


Hi,  does someone know ?
A) How to Get Total Sales for Customer(s) that ever bought Product X     ?  (including/excluding Product X)
B) How to Get Total Sales for Customer(s) that never bought Product X   ?

How to do this in MDX and in Excel Pivot ?



Total sales past 12 months



I want to do something as simple as Adding the previous 12 months sales to a row.

My tablix report is suppose to look something like this:

Year Filter: 2010

Months:           Months Sales           Sales Past 12 months
Jan                    12345                        67890
Feb                    23456                        78901
Mar                    34567                        89012
And so on

So ... in the third column, Sales Past 12 months, say for Mar 2010, the Sum should be something like:
Sales Mar 2010 + Sales Feb 2010 + Sales Jan 2010 + ... + Sales Apr 2009 = 89

Calculating Full Year Total

Im creating a sales report in SSRS by pointing it at a cube in SSAS.  Im creating calculated members to aggregate my data in various ways so they can be shown on the report.   I have a FACT table containing sales figures and various dimension tables including a server created time dimension.   Ive managed to create pretty much everthing I need (e.g. Current YTD, Prior year YTD).  What I now need is a full year figure to compare current year sales to.   Below is what Im aiming for i.e. the total of 122712 from 2009 appearing against every month in 2010.   Fiscal Year Fiscal Month Count Full Prior Year sales 2009 Apr-09 9805     May-09 9942     Jun-09 8986     Jul-09 9696     Aug-09 8912     Sep-09 10097     Oct-09 11713     Nov-09 10434     Dec-09 8820     Jan-10 10821     Feb-10 11844     Mar-10 11645     Total 122712   2010 Apr-10 3517 122712   May-10 1669 122712   Jun-10 2518 122712   Jul-10 3306 122712   Ive been bashing my head against a brick wall with this one so any help would be gratefully received!

12 months total from last active period at year level


Hi all,

I need to calculate last 12 months at a year and month level.
Using Adventure works as an example this works fine for when a month is chosen using the below mdx.

SUM( {
OPENINGPERIOD([Date].[Calendar].[Month])}, [MEASURES].[Internet Sales Amount])

select {[Product].[Product].&[486]} on rows,
{MonthLevel} on columns
from [Adventure Works]

However I cant create a member for the year level to go back 12 months e.g. the following mdx at first glance would seem to be correct and brings the same results.

 [MEASURES].[Internet Sales Amount]

select {[Product].[Product].&[486]} on rows,
{YearLevel} on columns
from [Adventure Works]
[Date].[Calendar].[Calendar Year].&[2003]

The issue is when the year is not complete e.g. if we were in the 6 month of 2003 only then YearLevel would be incorrect as it yields a YTD value and not the last 12 months total.

Is there an easy way to sum up 12 months from the last active period when the where clause is at a year level (last active period being the highest period in the year when a sale was made)?

sum the total year amount even if a time dimension filter exists


my need is to get the total of a year amount even if a time filter exists for the same year.


I have [Measures].[Amount] and a [Time] dimension and an hierarchy like [Time].[YearMonthQuarter].

In excel I filter [Time] hierarchy and I only keep the first Quarter.

I need to see a measure that will give me the total of year amount (not filtered).


Thank you in advance



Show grand total running total in GridView Footer

In many scenarios we need to display Running total as well as Grand total in GridView footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution using an example here i use a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and use GridView footer to display Running total & Grand total. Let we have a customer table with id and name column plus an orders table with OrderID,CustomerID,OrderDate & Amount. Now our goal is to show all customers order with amount as well as page wise running total & grand total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

UDP Delivers: Take Total Control Of Your Networking With .NET And UDP


UDP, the lesser-known sibling of TCP, is used for DNS resolution, SNMP network status, Kerberos security, digital media streaming, VoIP, and lots more. Learn how to put UDP to work for you.

Yaniv Pessach

MSDN Magazine February 2006

Total newbie question: Calling jQuery in an UpdatePanel?



Please forgive this ignorant question - I've used ASP.NET AJAX a good deal but I'm new to jQuery.  Specifically, is it possible to call or create jQuery events on the fly via an UpdatePanel?  For example, calling a jQuery modal or animating a Label (or <div> contained Literal), etc.

If anyone has any advice or could point me to some easy tutorials on mixing ASP.NET AJAX and jQuery, I'd greatly appreciate it. 



Calculating GridView total using JavaScript/JQuery


Hello everyone.

One month ago I've had very nice conversation with a asp.net forum member asking about issue regarding calculation of GridView textboxes total using JavaScript. The main thing was to allow user to manually write values in the text boxes (placed inside GridView column) and if the value has changed automatically to perform calculation on client side.

At the end of the conversation, we came up to two solutions. The first solution was using plain JavaScript, but since I thought it was not so robust, and in order to make it more robust we would need to write few more functions. Therefore, I have simply proposed another solution using JQuery which came up much better, more robust with less code.

In this blog post I will show both of the solutions and will provide step by step explanation on how we did that.

Note: I'm using VWD.

Get cumulative total value on x-axis.

I'm currently displaying: January $200 February $100 March $400 I want to display:( i've set the custom to MMM on the axis-properties, but still the months are not getting into below required format)  Jan $200 Feb $100 Mar $400 Total $700 on the x-axis.  And i want the y-axis to be divided in the intervals of 200, but though i've set the interval as 200, it is displaying in divisions of 2000.

Plot Running total charts from SSAS Cube

Environment: I am using SQL Server 2008, I have installed SSIS, SSAS and SSRS. Objective: Create a Effort V/S Defect plot in SSRS 2008 Approach: 1. Created the SSAS cubes with Effort and Defects data by timeline (rollup of data based on Week ending date) 2. Created SSRS project and linked the dataset to SSAS cube. But i am unable to plot a Effort (X-Axis) v/s Defects (Y-Axis). As soon as i drag the measures into the query designer in dataset properties, the effort rollup will break. Following is the data that has been created in SSAS. I need to plot a graph of Hours v/s defects (Running total). Please guide me on how to plot the graph using SSRS 2008 from the already created SSAS cubes Week Of Hours Defects 10/24/2009 8   11/7/2009 63   11/14/2009 68   11/21/2009 80.5   11/28/2009 139   12/5/2009 221.25   12/12/2009 131.75   12/19/2009 124.5   12/26/2009 61.5   1/2/2010 73   1/9/2010 153.5   1/16/2010 149.5   1/23/2010 196   1/30/2010 163   2/6/2010 155.5   2/13/2010 178   2/20/2010 138   2/27/2010 161.5   3/6/2010 189   3/13/2010 191.9   3/20/2010 240.5 9 3/27/2010 260.2 5 4/3/2010 214.5 13 4/10/2010 274.6 24 4/17/2010 200.5 15 4/24/2010 227 9 5/1/2010 237.05 18 5/8/2010 190.5 12 5/15/2010 156.25 6 5/22/2010

Sorting a DVWP by Column Total

I have a survey list in Sharepoint 2007, which from what I understand means I can't add a calculated column. In this survey list I have some number columns that need to be calculated for display. They are: (1) Number of Weeks, (2) Number of Hours per Week, and (3) Number of Hours for Credit. In Sharepoint Designer, I've created a Data View Web Part which displays those columns and two additional "calculated columns." Scare quotes because the calculation is happening in the XSL of the web part, not an actual calculated column. These columns are (4) Number of Hours (Column1*Column2) and (5) Eligible Hours (Column1*Column2-Column3). I have the data view grouped by @Author (Created By) and a column total row for each group. I even figured out how to get column totals for all four columns though the nodeset is grouped by Author, which breaks out of the box. Great, beautiful. The final step is sorting the entire view by the column total of Column5. In other words, I want my groups of @Authors listed in descending order by their Total Eligible Hours. Is this possible?

Excel 2003 pivot table - parent showing grand total than the sum of its children

All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.

Matrix Total Help

I have matrix report which renders as follows:                     Value category x      10 category y      5 categoy z        10 Total             25   How can i exclude category y from the total but still display its value for its row group? In other words i want Total to sum up to 20 in the above case.

Show total items for GridView in List.aspx or GridViewPager

Hi, Is there a solution to show total items in Grid View in List.aspx or GridViewPager Eg. Record: 1 - 20 of 231 

How do I create a calculated member for daily total revenue?

Hi everyone! I'm new to MDX and I can't figure out how to create a working calculated member. Here's business background: There are 2 measures in the cube - a and b. The formula for Total Revenue is a - b - $1000 ( don't ask me why =), 1000 is being subtracted from each day's Total Revenue - a ball park adjustment for something) My calculated member [Measures]. [Total Revenue] looks like this Case     When IsEmpty([Measures].[a]- [Measures].[b])     Then null     Else [Measures].[a]- [Measures].[b]-1000 Unfortunately this calculated member doesn't work correctly, it subtracts $1000 from everywhere and not from the intersection of [Dim Calendar].[HierarchyTime].[Day] and Total Revenue. How do I make it work in accordance with business logic? The objective is to adjust Total Revenue by subtracting $1000 for each day.  Thanks in advance!    
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend