.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

calculated member grand total e subtotal problem

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


I have a cube where I added a  calculated member to extract the last non null value of a measure. This is the script

Create Member CurrentCube.[Measures].[Trascinamento] as


([Data Rif].[Calendario].prevmember,[Measures].[Trascinamento]),


FORMAT_STRING = "Currency", VISIBLE = 1 ,


when viewing the aggregate data in a hierarchy, calculated totals and subtotals are accurate only if all the children of a member are zero at a certain date, and if either has a value, the original total is not zero, and thus is also used in calculated member.


hierarchy |          importo       | trascinamento

100-----10|     null                  |         10000
      ----20 |     null            &nb

View Complete Post

More Related Resource Links

PeriodsToDate problem with calculated member

Hi I've come across an example where my calculated member will yield wrong results. I've been trying in the last few days to find a solution for this, but so far no luck. I have two measures: [Measures].[Marketing Spending (Actual)]: Aggregation: Sum [Measures].[New Subscriptions (Actual)]: Aggregation: Sum Then I created following calculated member in the schema: [Measures].[CPA (Actual)]=[Measures].[Marketing Spending (Actual)]/[Measures].[New Subscriptions (Actual)] If I run a query like this I get the correct results: SELECT [Measures].[CPA (Actual)] ON 0 from [Global B2C Weekly KPI Cube] WHERE [Date.Weekly Calendar].[2010].[1]:[Date.Weekly Calendar].[2010].[32] I can even add some other dimensions and all looks fine. But, when I create a calculated Member in the MDX query with PeriodsToDate and Aggregate, the results looks like the sum of the CPAs by week. WITH MEMBER [Measures].[x] AS Aggregate(PeriodsToDate([Date.Weekly Calendar].[2010],[Date.Weekly Calendar].[2010].[32]),[Measures].[CPA (Actual)]) SELECT [Measures].[x] ON 0 from [Global B2C Weekly KPI Cube] So my take is that as PeriodsToDate gives back all the members, the calculated measure is calculated for each member and then summed up.  In this case the measures should be summed first for the time period and then the calculation should be performed. I don't want to create extra memb

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!    

disable the sub/grand total column in calculated memebers


Is it possible to disable the sub/grand total column in calculated memebers programmatically. I don't want to display the sub/grand totals in pivot table? I know I can manually disable the sub/ grand totals in pivot table.



Question regarding Calculated Member Grand Totals


I have a Calculated Measure that was inherited. The logic is:



"[Time].[Time Hierarchy].[Month].&["

+ VBA!CStr(year ([ForecastMonth].[Month].

CurrentMember.Properties("Key0")) )

+ "]&["

+ VBA!Cstr(Month([ForecastMonth].[Month].


+ "]"

),[Measures].[Regular Cases])

If the [ForecastMonth].[Forecast Month].[Month] is assigned

Grand Total problem in SSRS export to PDF



I have one report in which i have to show list of patients, department wise.. so i added one group for Department and patient belonging to respected department fall under that group.. but the requirement is each department (group) should start from new page.. till now everything works fine.. now the point is at the end of report i have to print total no. of patients (Grand total) included in all departments.. but this Grand Total is appearing one new page even if previous page completes half of the page, as i have activted "New Page After" for group property.. how to overcome this problem ?


With Regards.

Sudha Thiru

Display grand total without subtotal



Below is my testcase. I want to display grand total without any sub total.

create table report (name varchar(6),type1 varchar(6),amt int)

insert into report values( 'ABC','BILL',200)

insert into report values( 'DEF','BILL',200)

insert into report values( 'ABC','Query',100)

insert into report values( 'DEF','Query',100)

insert into report values( 

Grand total problem using sum(descedant) function


Hi there:

I use the sum descendant function to sum the employees that has a sale using the following MDX

Employee with sale =

,[Measures].[Employee wSales Count1]

The result is like this if I have all job category selected.

Employee Group

Employee Count

Employee with sale

IIF works on calcd member but grand total is wrong


Hi.  I'm working on a conditional piece of mdx that started looking like what you see way below.  The idea is to modify this measure's behavior based on which dimension was chosen without nesting scope statements.  The detail and date hierarchy level numbers look good on Date A but the grand total is 0 when I dice on Date A.  I suspect what was posted at


explains why this happens but the solution given there applies to a different class of problem (I think). 

Can someone help to explain how grand totals etc can be aggregated properly for calc'd measures? I tried multiplying [Whatever Orig] by 1 and even put the calc'd member in a different measure group to no avail.  I also used a raw assignment prior to the calculate that kind of looked like [measures].[Whatever Orig] = IIF(ROOT([Date A]),0,...) ; but that didnt work either.  Eventually there will be more scope statements.


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.

overriding "grand totals" for calculated members

hi all, is there a workaround for overriding "grand totals" for calculated members?  (I cannot use the workaround whereby I create the calculation in the DSV or view directly due to it being a pretty complex Descendant parent/child calculation).   thanks much for any thoughts, Cos

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.

Trouble With Calculated Member

WITH MEMBER [Promotion].[Campaign].[Program].[All Others] AS [Promotion].[Campaign].[Program].&[Acquisition]+[Promotion].[Campaign].[Program].&[Publications] SELECT [Measures].[Revenue] ON 0, [Promotion].[Campaign].[Program].ALLMEMBERS ON 1 FROM MyCube In the MDX above "Promotion" is the dimension.  "Campaign" is a user hierarchy.  "Program" is a level/attribute. I keep getting: Query (2, 2) The member '[Program]' was not found in the cube when the string, [Promotion].[Campaign].[Program].[All Others], was parsed. What am I doing wrong here?

create a calculated member in a cube in SSAS

Hello   I’m trying to create a calculated member in a cube in SSAS and was hoping someone could help.   What I’m looking to do is count the number of occurrences of a value in a field.  I have Status field that contains New, Renewals and Enquiries and another field that contains a count, the problem I have and the reason for this query is the count is set to 0 for Enquiries.   The tSQL equivalent would be something like...   Select Date, Status , count(Status) From aTable Where Status = ‘ENQ’   And to summarise, what I’m looking to do would look something like the below   Date Status Count Commission Apr-10 NEW 1 100 May-10 NEW 1 100 May-10 REN 1 50 Apr-10 ENQ 0 0 May-10 ENQ 0 0 Jun-10 REN 1 50 May-10 NEW 1 100 Just looking at the ENQ by month. Date Count of ENQ     Apr-10 1     May-10 1     Jun-10 0       Any help gratefully received.

Problem when using calculated column as column group in a view

Hi,   I have a list with a calculated column named 'FullName', the formula is: "FirstName & " " &  LastName". I created a view group by 'FullName' column. The follow error appeared on the column:   <!-- #RENDER FAILED -->   Please help

Tablix - Grand Total not match up

I'm having a problem in my grand totals when using tablix. You can notice that grand totals are not totalling up. Name Code Total  Items No of Users Total  Due % Due Total Completed % Completed Company Units # of Units Health System 00 447,041 5,163 35,422 8% 411,597 92% Test A CEU 2218.75 Test B CEU 4305.5 Test C Hours 22121 Test D ct Hours 15.8 Test E CEU 18.75 Hospitals 01 47,952 511 2,099 4% 45,853 96% Test A CEU 34.25 Test B CEU 136 Test C Hours 1823.25 Non-Health 9999 9,643 658 2,064 21% 7,579 79% Test C CEU 11.5 Test D CEU 17.75 Test E Hours 478 Trainings Test 34 9 22 65% 12 35%     0 Grand Total:    2,408,024 29,331 189,621 8% 2,218,293 92%              

Olap parameterized report using a calculated member as filter

I'm in the following situation : olap parameterized report - using an Analysis Services data provider  - with 4 filters and parameters.   One of the filters - Time dimension and one of its hierarchies - includes 2 calculated members called:  Primary and Secondary, which basically describe a time interval of a day based on some business rules. An Excel pivot tabel retrieves data without any problems. But a SSRS 2008 matrix report fails when picking up one of the calculated members , as it does not support it, just like olap browseren in AS 2008 does not support filtering by any calculated member. So it's difficult to find a good explanations to the users ... I'm not quite sure that using the ole db provider for analysis services in stead of analysis services will solve my problem. That's why following question : Are you absolutely sure that using the ole db provider for analysis services will solve my issue ? If not, is it any other work around ? If yes, are there any tips and tricks of using / typing within the ole db provider for analysis services editor when using parameters. ( I tried it once without any succes and found it quite complicated when it comes to handling expressions with parameters). Thank a lot for your answer. Best regards, Mihai   

MDX Date Range calculated member

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.
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