Using SSAS MDX Calculation Color Expressions

Who is online?  0 guests and 0 members
Home  »  Blogs  »  DevinKnight  »  Using SSAS MDX Calculation Color Expressions
 
0
/5
Avg: 0/5: (0 votes)

Comments (12)

Anonymous
Anonymous said:
Hi Devin, It is possible to use the Analysis Services formatting in Reporting Services by setting the "ReturnCellProperties" extended propery in the dataset connection string: http://prologika.com/CS/blogs/blog/archive/2007/08/14/how-to-get-extended-properties-with-ssas-ole-db-provider.aspx cheers, Nathan
3/17/2010
 · 
 
by
NathanGriffiths
Hi Devin, It is possible to use the Analysis Services formatting in Reporting Services by setting the "ReturnCellProperties" extended propery in the dataset connection string: http://prologika.com/CS/blogs/blog/archive/2007/08/14/how-to-get-extended-properties-with-ssas-ole-db-provider.aspx cheers, Nathan
3/17/2010
 · 
 
by
DevinKnight
DevinKnight said:
I'll give it a shot and let you know!
3/21/2010
 · 
 
by
dustinryan
dustinryan said:
Does the formatting carry over into Excel?
3/23/2010
 · 
 
by
DevinKnight
DevinKnight said:
Yep sure does. That's what the last screen shot is showing.
3/23/2010
 · 
 
by
dustinryan
dustinryan said:
Oops I guess I just skipped right over that screen shot. Thanks.
3/23/2010
 · 
 
by
Anonymous
Anonymous said:
Question: You mention that this exmaple is not dynamic... do you know how to make it a dynamic?
4/29/2010
 · 
 
by
posqualee
posqualee said:
Devin, you mention "This example is actually not dynamic but here is exactly what it does:"... how would one go about making this dynamic?
4/29/2010
 · 
 
by
DevinKnight
DevinKnight said:
Instead of hardcoding the .40. You could use MDX to calculate what the goal should be.
4/29/2010
 · 
 
by
samikane
samikane said:

Here are this and some other articles on Conditional formatting: http://ssas-wiki.com/w/Articles#Conditional_formatting

3/16/2011
 · 
 
by
Puneet
Puneet said:

Hi Devin,

Please help me on making it dynamic:

 

Rating Dimension:

(ID, MainVariance, MaxVariance, Rating, StartDate, EndDate)

  Rating Dimension values:

SELECT     1 AS ID, 0 AS MinVariance, 0.10 AS MaxVariance, 1 AS Rating, '01/01/1900' AS StartDate, '06/06/2079' AS EndDate FROM         ApplDim

UNION

SELECT     2 AS ID, 0.11 AS MinVariance, 0.30 AS MaxVariance, 2 AS Rating, '01/01/1900' AS StartDate, '06/06/2079' AS EndDate FROM         ApplDim AS ApplDim_5

UNION

SELECT     3 AS ID, 0.31 AS MinVariance, 1 AS MaxVariance, 3 AS Rating, '01/01/1900' AS StartDate, '06/06/2079' AS EndDate

FROM         ApplDim AS ApplDim_4

UNION

SELECT     4 AS ID, 0 AS MinVariance, 0.20 AS MaxVariance, 1 AS Rating, '01/01/2011' AS StartDate, '12/31/2011' AS EndDate

FROM         ApplDim AS ApplDim_3

UNION

SELECT     5 AS ID, 0.21 AS MinVariance, 0.50 AS MaxVariance, 2 AS Rating, '01/01/2011' AS StartDate, '12/31/2011' AS EndDate

FROM         ApplDim AS ApplDim_2

UNION

SELECT     6 AS ID, 0.51 AS MinVariance, 1 AS MaxVariance, 3 AS Rating, '01/01/2011' AS StartDate, '12/31/2011' AS EndDate

 

--------

 Created a set in Cube as "RatingSet" :

 


RatingSet

{

[Rating].[Rating].[Rating] *

[Rating].[End Date].[End Date] *

[Rating].[Max Variance].[Max Variance] *

[Rating].[Min Variance].[Min Variance] *

[Rating].[Start Date].[Start Date]

}

 

 Calculation which needs to be made dynamic:-

 

IIF( ([Measures].[Variance Percent]>=0 and [Measures].[Variance Percent]<= 0.10) –--Variance Percent

                ,1

                ,IIF( ([Measures].[Variance Percent]>0.10 and [Measures].[Variance Percent]<= 0.30)

                        ,2

                        ,3 --Rating

                     )

)

i.e. Variance Percent will be 0, 10, 30, 50  etc… and Rating will be 0, 1, 2 3 etc…

---------

I tried below, but not working:


WITH  DYNAMIC SET Test2 as

IIF( ([Measures].[Ownership Variance Percent]>=[Rating].[Min Variance].[Min Variance].members.Item(0)

and [Measures].[Ownership Variance Percent]<= [Rating].[Max Variance].[Max Variance].members.Item(0))

,[Rating].[Rating].[Rating].members.item(0)

,IIF( ([Measures].[Ownership Variance Percent]>[Rating].[Min Variance].[Min Variance].members.Item(1)


and [Measures].[Ownership Variance Percent]<= [Rating].[Max Variance].[Max Variance].members.Item(1))
,[Rating].[Rating].[Rating].members.item(1)

,[Rating].[Rating].[Rating].members.item(2)


)

)

------


Now, How to make it dynamic as above???????

Any help will be appreciated.

Please suggest asap.

Many Thanks in Advance.

 

 

 

7/22/2011
 · 
 
by
user564124
user564124 said:
Hi, I have a sales(SalesID, GoodsType, GrossSale, AverageSale). I was to write two MDX expression in MS Visual Studio SSAS cube's Calculation tab to calculate Sum of GrossSale and Average of GrossSale where GoodsType is "Food". If someone please write the MDX expression for me? I wrote this expression for sum but it doesn't work! CREATE MEMBER CURRENTCUBE.[Measures].Sales AS SUM([Measures].[Gross Sale], [Measures].[Datatype] = "2"), VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ; ........................ I have tried in this way also CREATE MEMBER CURRENTCUBE.[Measures].Sales AS SUM([Measures].[Datatype].&[6], [Measures].[Gross Sale]), VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ; I should elaborate bit more, please check bellow, I have a data table. Where GrossSale and Datatype are columns. Say in GrossSale I have (100, 200, 400, 130, 350) and in Datatype I have (5,2,6,8,2) respectively. Now I want Sum of GrossSale where I have Datatype 2. Then I have GrossSale = 550, that is 200+350. Because for Datatype 2, I have 200 and 350. If someone can help me please?
10/17/2013
 · 
 
by
Blogs RSS Feed

DevinKnight's latest blog posts

Blogs RSS Feed

Latest community blog posts