Change format of calculated item in a pivot table
Hi community,
We have one measure called "measure". "income" and "gallons" are two accounts of the account dimension that gives the column header in the pivot table. The rows are given by the company dimension. The calculated item I did in the selection steps divides "income" by "gallon" income has a $ format while gallon doesn't. I'd like to give the income per gal calculated item the $ format too and will include other cacluated items in the table so being able to modify their formats would be very helpful.
Thanks!
Answers
-
If you want to do operations like that it's probably better to model your account dimension members as RPD measures rather than just dimension member which you cross using the "Measure" measure (no better way of putting this comes to mind).
Otherwise you'll always end up doing whacky things or writing MDX in EVALUATE wrappers...
0 -
I'd kind of agree with you but these are all financial essbase BSO cubes. We can flatten the cubes and this gives us 1000's of measures which is not a best practice from what I've read for bringing into the analysis layer and makes the analysis slow. I'm not sure how to do a much smaller segment of measures based off of the measure with the bso cubes. (Basically in the presentation layer we see a bunch of hierarchies and one measure in the modeling layer we see tables of the dimensions, hierarchies, and the one measure in the measure table. Would would you recommend, just flatten or only calculate a few (we only have requests for dashboards for a much smaller subset of these) and do you have a good source for created additional measures with BSO cube data? Thanks!
0 -
Well to that I can only say: It depends.
Sorry but it's really that. Any of those different approaches is valid for certain use cases and you'll need to test the one against the other with regards to usability, perfromance and user requirements.
Normally it's not a "one size fits all" anyways with a case like yours. For some cases flattening the accounts dimension is the way to go whereas in other cases you'll have loads of UNION requests pulling together in several data streams and then let the pivot condense things and in some cases you'll probably not get around writing MDX in an EVALUATE which then will obviously severely restrict the front-end manipulation capabilities.
It's always the case when you attack financial cubes with an analytical tool that changes viewpoints all the time as is super dynamic. It's just two different approaches to doing things that you will never be able to have working together in a total seamless and obvious fashion.
0
