Hi folks,
First, sorry for my English it's not my first language. I have a problem right now and I hope you can help me. I work with dimensional data and measure and I try to find a way to do the following task.
In a crosstab all measure are by default summarized and it's ok, but can I build a calculated measure coming from all the underlaying measure without changing the database. I have to know from 50,000 measure How many are greater then a certain number. How can I do that, I try tuple currentmember and nothing work or it's me ..
Example
rap amount grater then 500
1 12 0
2 1000 1
-------
on the report I like to see those number
report amount greterthen
2 1012 1
Thanks for your help
Hello Jive,
In your report, create a data item to use as a flag for all measures greater than the certain value . In this Data Item, use a Case statement to put either 1 if the value is greater or 0 if it is not. The Case statement should be like :
Case
When MyMeasure > 500
Then 1
Else 0
End
Now you can use this Data Item in your report. For example if you would like to gent the number of measures greater that 500 you can Count or Aggregate this Data Item.
Hope this can help you.
Thanks ..
thanks for your help,
One last question, for the data item I build with the case, It will work with dimensional model, I mean in matter of drill up or drill down did the number will consistent.
Thanks
Hello Jive. I think yes it will does. Because if you use a function like Count or Aggregate to sum the number of measures, the function will always work in the Crosstab context. So, in cases of drill up and down, the Data Item will be affected also.
You will need to try it and let me know if you still have any problems.