Getting caption of parent

[dax]

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

[adik]

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

[dax]

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

[adik]

so what was the sollution you applied? can you please share?

[dax]

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

[CognosPaul]

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.

[dax]

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

[CognosPaul]

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.