Pick up the initial value for every row of a crosstab

[kishor2010]

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

[adik]

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)

[Lynn]

You don't mention if your source is relational or dimensional.

[adik]

yes, good question Lynn :)
i just assumed dimensional when i gave my answer

[kishor2010]

ya it is dimensional report...

[MFGF]

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.

[kishor2010]

Thanks for the reply..

But the problem is it is a relational database

[MFGF]

ya it is dimensional report...

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.

[kishor2010]

I am really sorry for the conflict..

My package is relational (ie query subjects and query items)
Thanks in advance

[kishor2010]

I am waiting for the solution.. please reply

[ToriBurns]

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.

[MFGF]

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...

[kishor2010]

Sorry Tori and MFGF, i had parked that issue aside, now i will check and update..
Thanks for the reply..  :)