Calculated Member Code in Business Intelligence Development Studio (BIDS)

# Calculated Member Code in Business Intelligence Development Studio (BIDS)

• Sunday, December 30, 2012 2:56 AM

Hi

I want to add a Calculated Member field to a BIDS Cube to calculates the Compound Annual Growth Rate (CAGR).

The formula is ((Oldest Revenue amount/Newest Revenue amount)^(1/2.25))-1

I've tried entering the below formula as an 'IIF' statement as below in the 'Expressions' area under the 'Calculations' tab of the Cube but it doesn't return any results;

(((IIF([DATE 2].[Clndr Month Id] = (MAX([DATE 2].[Clndr Month Id])), [Measures].[New Chrgs Amt], 0))/(IIF([DATE 2].[Clndr Month Id] = (MIN([DATE 2].[Clndr Month Id])), [Measures].[New Chrgs Amt], 0)))^(1/2.25))-1

I was then helped by Eshwar with the below code & added it to the same place but I get an error about the 'With' keyword.

with member [Firstmember] as
Sum(OpeningPeriod([Date].[Calendar Time].[Calendar month],Ancestors([Date].[Calendar Time].CurrentMember, 4).Item(0)),[Measures].[Internet Sales-Sales Amount])
member [CAGR] as
((Sum([Date].[Calendar Time].Currentmember.lastchild,[Measures].[Internet Sales-Sales Amount])/
Sum([Date].[Calendar Time].Currentmember,[Measures].[Internet Sales-Sales Amount]))^(1/2.25))-1
Select {[Measures].[Internet Sales-Sales Amount],[firstmember],[CAGR]} on 0,
{[Date].[Calendar Time].[Calendar Month].members} on 1 from [Analysis Services Tutorial]

Can anyone advise me that I am adding the code to the correct place to get this calculation to work or if there's anything else I'm doing wrong, Thanks

Woofybear

### All Replies

• Sunday, December 30, 2012 9:55 AM

it would be more clear if you post the error you are getting so we can know what's wrong with "With". Anyways, I understood that you want to create two calculated measures, to do so:

1. open Calculations tab, and click on "new calculated member" icon on the tool bar.
2. in the "name" text field insert "[Firstmember]".
3. in the Expression text field insert
Sum(OpeningPeriod([Date].[Calendar Time].[Calendar month],Ancestors([Date].[Calendar Time].CurrentMember, 4).Item(0)),[Measures].[Internet Sales-Sales Amount])

do the same for "[CAGR]" and insert
((Sum([Date].[Calendar Time].Currentmember.lastchild,[Measures].[Internet Sales-Sales Amount])/ Sum([Date].[Calendar Time].Currentmember,[Measures].[Internet Sales-Sales Amount]))^(1/2.25))-1

deploy and process the cube, then, in sql server management studio try to run
Select {[Measures].[Internet Sales-Sales Amount],[firstmember],[CAGR]} on 0,
{[Date].[Calendar Time].[Calendar Month].members} on 1
from [Analysis Services Tutorial]

you should get something !

• Sunday, December 30, 2012 6:47 PM

Thanks Butmah,

The error is as below when I added the script into the 'Expression' field under 'New Calculations';

MdxScript(CUBENAME)(8,5)Parser: The syntax for 'with' is incorrect.

So it was clear that 'with' as the first word in the Expression field was the problem.

To clarify my question I want to add one new calculated field to my cube.

The calculation is as follows & I want it to appear in the cube after processing for all records;

Note the data in my cube contains 36 months of Revenue data split by month.

((Oldest months Revenue amount/Newest months Revenue amount)^(1/2.25))-1

I want to add this calculation into the expression field & have the result appear in the cube like any other Calculated Member.

Thanks

Woofybear

• Monday, December 31, 2012 7:43 AM

did you try what i mentioned in the first post ? if you did, you will find [CAGR] and [Firstmember] as any other calculated member.

keep in mind, that i'm note sure about the accurecy of the result, I just copied the calculation from your first post, and what i posted is just how to create a calculated measure

• Monday, December 31, 2012 7:59 AM

Thanks Butmah,

I haven't tried your suggestion just yet because I didn't understand why I need to run code in SSMS to see the result in my Cube.

Could you explain this point & how it works?

The users will access the cube through MS Excel & see the result as an extra calculated field in the cube.

I'm keen to see how your suggestion works but I need to clarify this point, Thanks

Peter

Woofybear

• Monday, December 31, 2012 5:54 PM

:) that query was just for checking ... No need to run it to achieve the desired ... Just follow the steps and connect to the cube through excel and u should find the newly created measures among the old normal measures
• Tuesday, January 01, 2013 2:05 AM

Thanks Butmah,

That explains a lot!

To get my cube to work, I need to understand the syntax for the First & Last months Revenue in my Cube.

From there, I should be able to complete the rest.

I noticed you used 'Currentmember.lastchild' to get the most recent Monthly Revenue figure from the cube.

I've tried a few times to translate my naming conventions to yours but I can't get a result as yet.

I don't have any Hierarchies set up for the date, my only Dimension is simply [DATE 2].[Clndr_Month_id] & the Measure I'm using for the Measure is [CAGR_TRIALS_1_CUBE] as the cube name & 'Total_Billed' as the Revenue field.

So to translate to your syntax to get the first months Revenue from the cube, I tried the below;

Sum([Date 2].[Clndr_Month_Id].Currentmember.lastchild,[Measures].[Total_Billed]

Is this the correct syntax to get the first months Revenue?

Once I get this part, I think I get the rest, Cheers

Peter

Woofybear

• Tuesday, January 01, 2013 12:13 PM

i don't understand your requirement clearly... i really don't understand what do you mean by the "first months". and i'm sorry i can't send you the exact query as i don't know the dimensions and measure groups you have, but I assume the following:

• You have the [Date 2] dimension which is a table of just months, each month has a [Clndr_Month_Id]
• you want to create a new measure that shows the [Total_Billed] of the first month (as i said, i didn't know what are those first monthS)

i hope this is what you want, if so, please do the following:

1. open Calculations tab, and click on "new calculated member" icon on the tool bar.
2. in the "name" text field insert "[First Month Revenue]" or whatever name you want for this new calculated measure.
• in the Expression text field insert
([Date 2].[Clndr_Month_Id].firstsibling,[Measures].[Total_Billed])

﻿﻿

if you had hierarchy in your Date dimension (which suppose to be in any date dimension) , like YEAR-> MONTH -> DAY, the expression could be written in another way :
(OpeningPeriod([Date].[Hierarchy].[MONTH],[Date].[Hierarchy].currentmember),[Measures].[Total_Billed])

• Wednesday, January 02, 2013 5:38 AM

Thanks again Butmah,

I found that using 'Currentmember.firstchild' & 'Currentmember.lastchild' work well to locate the Revenue for the Earliest & Latest months in my Cube.

So I'm using [DATE 2].[Clndr_Month_Id].Currentmember.lastchild, [Measures].[Total_Billed]for exampleto get the latest months Revenue for example.

Next issue is that some months are 'Null' or Blank because they exist in the Cube but no data has been added as yet, eg; Jan 2013, so I need a condition to deal with these 2 cases.

Thanks again, if you can answer this last part, I think you've solved the question.

Just to clarify, I need a statement that has the condition that only selects the First & Last Revenue value that is not 'Null' or Blank.

Peter

Woofybear

• Edited by Wednesday, January 02, 2013 6:45 AM
•
• Wednesday, January 02, 2013 7:31 AM

OK try to translate the following to what you have:

if you have the following query:

SELECT {[Measures].[Normal],[Measures].upNormal } ON 0,
([D Date].[MONTH No].children) ON 1
from [TC Cube]

and results the following:

you want to remove the " (null) ". you just add "nonempty" and against which measure you want to check if its null or not (in this example against "Normal"); so the query will be :

SELECT {[Measures].[Normal],[Measures].upNormal } ON 0,
nonempty ([D Date].[MONTH No].children,[Measures].[Normal]) ON 1
from [TC Cube]

and you will get:

• Thursday, January 03, 2013 5:21 AM

Thanks again,

Can you give me the code that I could enter into the Expression field in BIDS that returns only the First Populated value (In your second table above, it would be '61995') & then another for only the Last 'Normal' Populated value (159010.5)?

I can then use these in the Calculation.

Thanks

Woofybear

• Edited by Thursday, January 03, 2013 5:23 AM
•