Hi all,
I have a requirement in which i have to pick up the initial value of each row in a crosstab..
My crosstab looks like this
value 1960 1970 2010 2011
aus 10 5 11 6
eng 5 2
bra 11 4
ind 8 11
i have to add another column which picks up the initial value for every row based on the year..
so the result should look like this.
value 1960 1970 2010 2011 initialValue
aus 10 5 11 6 10
eng 5 2 5
bra 11 4 11
ind 8 11 8
i don't have a working cognos environment to test so this is of the top of my head:
createa a new data item as:
item(filter(members([year level]), tuple([Value], currentMember([time hierarchy]) is not null), 0)
You don't mention if your source is relational or dimensional.
yes, good question Lynn :)
i just assumed dimensional when i gave my answer
ya it is dimensional report...
Quote from: kishor2010 on 12 May 2014 08:20:05 AM
ya it is dimensional report...
In that case the solution adik proposed is almost correct - I tweaked it a little to get the result you are looking for:
tuple([Your measure],item(filter([Year level],tuple(currentMember([Time hierarchy]),[Your measure]) is not null),0))
Cheers!
MF.
Thanks for the reply..
But the problem is it is a relational database
Quote from: kishor2010 on 12 May 2014 08:20:05 AM
ya it is dimensional report...
Quote from: kishor2010 on 13 May 2014 06:27:36 AM
But the problem is it is a relational database
Hmmm. We have two conflicting answers from you here. Which is it? Is your package relational (ie query subjects and query items) or dimensional (ie dimensions, hierarchies, levels, members, measures etc)?
The answer is important because it has a big impact on the approach any solution needs to take.
MF.
I am really sorry for the conflict..
My package is relational (ie query subjects and query items)
Thanks in advance
I am waiting for the solution.. please reply
Try to add a query calculation for initial value and typed this code on the expression:
case when [Year] = minimum([Year])
then
total([Presentation View].[Financial Consolidated].[CY Actual] for [Group Level1],[Year])
end
Replace the columns:
[Year] with your year column, [Level1].[Level2].[Amount] with your column that contains aus, eng, bra, ind. And, [Val] with your column that contains the value or the amount (your measure). I used a crosstab to try this. If you have any question feel free to ask. Hope it helps. :)
By the way, [Level1].[Level2].[Amount] is your measure. I guess it's [Value] on your case. It is the measure that you dragged on the crosstab intersection if you are using a crosstab.
Quote from: kishor2010 on 13 May 2014 11:17:25 PM
I am waiting for the solution.. please reply
We are waiting for your response to the helpful message that was posted... If it worked, you should thank Tori. Please reply...
Sorry Tori and MFGF, i had parked that issue aside, now i will check and update..
Thanks for the reply.. :)