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

Top 5 Contributors of the Month
Melody Anderson
Post New Web Links

Calculating Full Year Total

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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!

View Complete Post

More Related Resource Links

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.

Calculating MAT (Moving annual total) in MDX

Hi, I am presently using the following script to calculate the MAT : CREATE MEMBER CURRENTCUBE.[Measures].[LatestMAT] AS SUM([Time].[Time].currentmember.lag(13) : [Time].[Time].currentmember,[Measures].[Volume]) Could someone please tell me if there's any other more efficient way to do this? MAT or moving annual total is calculated as the sum of volume of every time period from the present month to a year back. (in my case, i have 13 periods in a year. so that is 13 periods back)

Calculating total of template textbox inside gridview



I have been asking this for a long time.But the answer what i get is not want i need.I have a  gridview with two template text box (t1 and t2) inside gridview and four textboxes outside the gridview on the form (txt1,txt2,txt3,txt4).All these textboxes allow only integer value to be entered.What i need is to get the total of all these textboxes (ie,template textboxes as well as textboxes outside gridview) in a textbox named Total at the same time we enter values in a textbox.Also if i change the value of a textbox later that too should be reflected in the Total textbox .I know how to calculate the total of the textboxes outside the gridview.But i need the total of all textboxes including template as well as textboxes outside simultaneously

Pls help and thanks in advance

Total Sales and Total Last Year Sales


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],




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



MDX For Full Year Estimate (FYE) calculated measure


I’m trying to come up with an MDX expression/statement that I can use at server level to create a calculated measure. In my organisation we work with totals on YTD, which is not a problem for me to get its MDX. We also work with totals on Full Year Estimate (FYE). Using the following example can someone kindly hep me with an MDX expression/statement that I can use. 


Dimension – Attendance Status

Attribute &n

PDA Keyboard v1.0 Light Edition - Full Screen Keyboard

The default keypad on Windows Mobile is not at all finger friendly, there is no way you can use it with your fingers. I have trouble using the stylus with it too. But there are many freeware option available to make the SIP ( Software Input Panel - check the Glossary ) finger friendly. Some of these are HTC Diamond keyboard, Blimkeys, PocketCM Keyboard and then now we have PDA Keyboard v1.0 Light Edition from pdamedia.biz. When you hit the blue button it goes into fullscreen mode , then you can use both your hands for typing.

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.

Is this load full or matching one only... coz of need to know performance



This my Two Table i have one doubt...

For my first table i have select Top 2 from Employee_info 

and second table itz just selct * from MDEp..

What my doubt is on loading the above report?

Is this load all the record from MDep or just matching one based on Employee_info..?

Editor's Note: A New Year-A Fresh Perspective


The results of our reader survey are in. This month, Howard Dierking shares the takeaways.

Howard Dierking

MSDN Magazine January 2008

Editor's Note: A New Year-A New Vista


Josh Trupin revs the engine to get us all ready for Windows Vista.

Joshua Trupin

MSDN Magazine January 2007

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

Editor's Note: Celebrating Our 20-Year Milestone


The world of software development is all about milestones. At the lowest level, a programmer has to create a certain set of functions in a fixed amount of time, and is then judged on this performance.

Joshua Trupin

MSDN Magazine January 2005

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. 



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