.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

MDX select vs calculated member

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

IF I use the following as a calculated member, it gives me Sales for "Research" department

(( [Company].[Department] .&[Research ),[Measures].[Sales])


However, if I use the same in select statement, I get the same value but "Sales" and "Research" show up above that value in the results pane. My understanding is that a calculated member should result in only one member.


How is this statement working fine as a calculated member? Please advise, thanks in advance.


View Complete Post

More Related Resource Links

why after move the calculated member to the select section, it will fail?

The following MDX works.
 with member dayofweek as [CALENDAR].[Day Of Week].currentmember.member_key
select [CALENDAR].[Day Of Week].[Day of Week] on 0,
 dayofweek on 1 from mycube
But if expand the with clause, it failed.
 select [CALENDAR].[Day Of Week].[Day of Week] on 0,
 [CALENDAR].[Day Of Week].currentmember.member_key on 1 from mycube

error is:
The Axis1 function expects a tuple set expression for the  argument. A string or numeric expression was used.

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

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.

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!    

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.

Named Set And Calculated Member within Analysis Services Cube project


Hi there.

I have got the mdx below (titled as MDX In QUERY SsMS) that works fine in SQL Management studio.

I am battling to include it in my Analysis Services project.

Following the logic in the mdx query, I have created the 3 named set and one calculated member (You will see them below), but with no luck.

Please any guidance will do, I desperatly need to include it in my cube



//Get Weeks with sales
[WeeksWithSales] as '
{[Product].[Product Hierarchy].[SKU DESCR]
,([Measures].[SLS R]) >0)}

//the first week
[FirstWeek] as 'HEAD([WeeksWithSales],1)'

[LastWeek] as
[NumWeeks] as 'count([FirstWeek].item(0).item(1):[LastWeek].item(0))'

[NumWeeks] on 0
 (select [Product].[Product Hierarchy].[SKU DESCR].&[BALL BRIDGESTONE 08 E7 +]&[1041597] on 0
 from (select ([FinancialDate].[Financial Calendar].[FINANCIAL MONTH].&[FY2010_MONTH06].parent.parent) on 0
   from [TPSMerch]))

How to force a calculated member to sum from the leave level


Hi there:

Below is a snap short of my data set and hierarchy

Brand Product category Product Name Units Sale Product With Sale

SSAS Calculated Member not working as expected in a SSRS report


Dear forum guys, I have a simple cube to analyze my customer's sales. I have a Time Dimension, a Product Dimension (with a few hierarchys) and a couple measures (sales and purchase amounts).


I also have defined a few Calculated Members, in particular the average sales and purchase amount over the last 12 months from the selected month.


I need to show the results in a SSRS Report: the user must select a month and the report needs to show the sales and purchase amount of the selected month and the average of the previous 12 months (both for sales and purchases).


Everything works perfectly if I test the cube using the Cube Browser or Excel 2007 as a client.


When I define the SSRS Report I cannot get to work with the calculated members (the average): I get strange numbers which are much higher than the real numbers.


I've tried both Matrix and Table reports.


Can anyone suggest me what I'm doing wrong: I think this should be a very common mistake.


Best regards



calculated member grand total e subtotal problem



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

Calculated Member Aggregate (MDX)


Say I have the following query, below

SELECT [Measures].[Sales Amount] ON 0,
[Product].[Category].[Category] ON 1
FROM [Adventure Works]
WHERE {[Date].[Calendar].[Month].&[2003]&[1] : [Date].[Calendar].[Month].&[2003]&[3]}

How can I add a calculated member that shows the MIN date for each Product Category that Actual had [Sales Amount] value, while keeping the [Sales Amount] total in the 0 Axis for all the dates in the slicer?


Javier Guillen

calculated member-distinct count


I have a product dimension table whose level is category-brand-product id. I have a fact table at lowest level product id and measure sales amt which can have positive and negative values.

I need distinct count  of brands for a particular category whose sales is greater than 0.

fact table data:                                   product table
productid salesamt                              category brand   productid
1             50                                          c1         b1         1
2             100                &nb

What's "a calculated member that intersects all other dimensions"?


Someone posted an interview question here: http://www.ssas-info.com/analysis-services-implementations/69-experience/1352-dave-rodabaughs-analysis-services-interview-questions


“Where would you put a calculated member if you wanted it to intersect all other dimensions?”


What does he mean by "intersect" here? Any examples?

Fan Liu

Calculated member with currentMember

I am trying modifying one of the sample queries from "Practical MDX Queries". 

I would have expected [Measures].currentMember be replaced with [Measures].[Reseller Sales Amounts], the default measure, at runtime. But it didn't and returned all <Null> values. Why??

-- running Sum
with member [Measures].[Running Total]
as sum(periodstodate([Date].[Calendar].[(All)],
[Measures].[Running Total]
on columns,
[Date].[Calendar].[Calendar Year]
on rows
[Adventure Works]

Fan Liu

Calculated Measure for Children of a Specific Dimension Member


I would like to add a calculated measure to my cube which is only scoped for the desendants of a specific dimension member.  For all other members of that dimension it would return a zero or null value.

I believe it is similar to the code below, however, rather than having the dimension reference in the where clasue "WHERE [Product].[Category].[Bikes]", it would be part of the MEMBER. 

Something like:

   MEMBER [Measures].[Special Discount] AS
   iif([Product].[Category].CurrentMember,  [Product].[Category].[Bikes].children, ( [Measures].[Discount Amount] * 1.5),  0)

Example from MSDN:

   MEMBER [Measures].[Special Discount] AS
   [Measures].[Discount Amount] * 1.5
   [Measures].[Special Discount] on COLUMNS,
   NON EMPTY [Product].[Product].MEMBERS  ON Rows
FROM [Adventure Works]
WHERE [Product].[Category].[Bikes]


HELP!!!!! How to create a calculated member like this??




Region and Year are dimensions. Count is a measure value. I want to create a calculated member as Historical.

Region       Year           Month               Count              Historical

AMS           2010            1                      4                        4

AMS           2010            2                      3                        7  (4+3)

AMS           2010     &nb

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