Calculated Member Code in Business Intelligence Development Studio (BIDS)

All Replies

• Sunday, December 30, 2012 9:55 AM

   

   
  

  1

  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 !

  • Reply
  • Quote

   
• Sunday, December 30, 2012 6:47 PM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Monday, December 31, 2012 7:43 AM

   

   
  

  1

  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

  • Reply
  • Quote

   
• Monday, December 31, 2012 7:59 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Monday, December 31, 2012 5:54 PM

   

   
  

  1
  :) 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
  • Reply
  • Quote

   
• Tuesday, January 01, 2013 2:05 AM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Tuesday, January 01, 2013 12:13 PM

   

   
  

  0

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