Hi guys
I am working with a DMR data source and using a crosstab to create a simple Profit and Loss report. I have a data item that is defined as Children([Revenue]) - when the report is run this displays the children of the Revenue member and looks something like this:
Actual Budget Variance
Transaction Fees 1000 1200 200
Advisory Fees 750 700 -50
Total Revenue 1750 1900 150
Salaries & Wages 500 520 20
Other Costs 100 120 20
"Actual", "Budget" are measures. The "Variance" is supposed to be using one of two measures - for any revenue items I want to use "Current Period vs Budget" and for all other items I want to use "Current Period vs Budget (Reversed)". I thought this would be straightforward but appears not - I have tried this expression in the "Variance" data item:
if (caption(ancestor( currentMember ([Business View].[Alt Account Dimension].[Alt Account Trees]),1)) in ('Revenue')) then
( [Business View].[Fact Profit Loss Summary].[Current Period vs Budget] ) else
([Business View].[Fact Profit Loss Summary].[Current Period vs Budget (Reversed)])
And I have also tried this:
if (caption(parent( currentMember ([Business View].[Alt Account Dimension].[Alt Account Trees]))) in ('Revenue')) then
( [Business View].[Fact Profit Loss Summary].[Current Period vs Budget] ) else
([Business View].[Fact Profit Loss Summary].[Current Period vs Budget (Reversed)])
The expression always returns the reversed measure. If I modify the expression to refer directly to the currentmember it works as expected e.g.
if (caption(currentMember ([Business View].[Alt Account Dimension].[Alt Account Trees])) in ('Transaction Fees')) then
( [Business View].[Fact Profit Loss Summary].[Current Period vs Budget] ) else
else
([Business View].[Fact Profit Loss Summary].[Current Period vs Budget (Reversed)])
Does anyone know why i can't simply refer to the caption of the parent of the currentmember? I have spent the entire day trying to get this to work so will happily buy a beer for anyone who can help me with this mystery :)
Thanks
dax
have you tried using case when statement instead of if? and instead of using caption why don't just use the MUN of the parent? like case when parent(currentMember(hierarchy)) = [hierarchy].[level].[level] then [measure] else ... end
i don't have a working cognos testing environment right now so i can't tell if this is the solution but give it a try
Thanks for your reply Adik - I did try to use Case When but I got an error when I tried to validate the expression. However, I have just managed to solve my problem another way. What concerns me is that no one else has replied and I didn't think what I was doing was that unusual - what this is leading me to conclude is that I have a fundamental misunderstanding of something, but I do not know what that is. I also tried to get something similar to work using the sample Great Outdoors packages but same result, so I don't think it's because of the data source I'm using.
Anyway, thanks again for your response.
Dax
so what was the sollution you applied? can you please share?
Hi Adik
I didn't solve the problem that was in my OP - I completely changed the report so that I could solve it in another way. If I thought it would add value to the thread then I would explain what I did but I don't think it would be of much use to anyone - I could not find any way to get a reference to the parent of the currentmember.
Regards
Dax
A bit late to the party.
Unfortunately these typese of calculations are a bit difficult in MDX. In an OLAP environment like SSAS you have scope functionality - you could change the calculation based on the current tuple. It's slow, but it would work here. I think the easiest solution would be to have two separate measure nodes and use a style variable to hide specific intersections.
Thanks for your reply Paul. Like I mentioned, I did manage to solve my original problem by changing my overall approach to the report. The main driver for me re-creating the report was due to poor performance so when you mentioned the current tuple method being slow that rings alarm bells.
I still don't "get" some of this stuff. What would be a good way to understand MDX? I have been looking into getting SQL Server Developer, where i could create a database and cubes from scratch and then build some MDX queries from there. Do you think that would help get a better understanding?
Thanks
Daniel
Hi Daniel,
Diving head first into olap reports is how I learned. When I tell people not to use detail filters it's because of bitter, bitter experience. Generally I find MDX significantly easier to work with than SQL. The beginning is a bit rough, especially if you try to compare SQL queries to MDX.
It's the scope functionality of SSAS that is slow. I had a project that was stalled because they had a dim with 40 different members, and each one had a scope calculation. The ones that calculated growth simply killed the performance. A quick redesign and everything flew again.