cognos: Pick up the initial value for every row of a crosstab

Question

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

Assuming relational data, I think you'll need a second query to identify the "initial" year. Then you can use that with a third query to get the earliest value for each country (or whatever that is). Then you can join that to your original query on country. — Andrew

Johnsonium Johnsonium · Accepted Answer · 2014-05-30T21:19:00

You should be able to use the minimum() function to determine the lowest value for year and then return the value corresponding to that. The expression for the initialValue data item would be something like:

total(
CASE
WHEN [Year] = minimum([Year] for [Language])
THEN [Value]
ELSE 0 
END
for [Language])

We get the lowest year for the specific language in the data set using the minimum() function using the for clause to define the aggregation level. If the year of the row matches this number, we output the value, otherwise we output 0. We then total everything up for each language which should give us the value for the lowest year.

This solution assumes that the numbers displayed in your crosstab are totals of lower-level row detail. If the aggregate is something different, such as average or count, the wrapping summary function should be changed accordingly.

cognos: Pick up the initial value for every row of a crosstab

1 Answers