Hi Gurus,
I need some help for YTD calculation(Total or Average) depending on the metrics. But it should show under one column(YTD) in the crosstab.
Suppose my crosstab looks like :
Jan 2011 Feb 2011 Mar 2011 YTD
Metric A 1 1 1 3 (total)
Metric B 2 2 2 2 (average)
Metric C 3 1 2 6 (total)
.
.
.
Metric N 3 5 1 3(average)
So, under YTD column it should show either TOTAL or AVERAGE(for time) depending on the metrics. For few metrics it should be total and for few of them it should be Average.
So, My question is how to show total or average calculation under the same column(YTD) in the crosstab.
Please help me in this regard.
thank you.
Any help on this is appreciated.
Is this relational or dimensional?
Have you tried to manipulate the cognos generated YTD total column? How about writing a case or if/else statement within it?
case
when [Metric] ='Metric A ' then Total([Measure])
when [Metric] ='Metric B ' then average([Measure])
----
-----
end
Does it helps?
This is dimensional(DMR).
Hi Prit,
Can you please explain this in detail. I am not getting how to get [Metric] in the above mentioned CASE statement. Please explain it in detail.
You can control the aggregation displayed with clever use of style variables. First, what logic do you use to determine which metric displays which form of aggregation?
Let's assume you have an member attribute for it. So drag that attribute to the query, and attach it to the properties of your member node. Create a new string variable called "Agg Type". Values: Total, Avg. The variable expression should be something like:
[Query 1].[Agg Type Attribute] or case when [Query 1].[Agg Type Attribute] = 1 then 'Total' else 'Avg' end
Now the clever bit. Drag in both a total and an avg fields to the crosstab.
So it should look something like:
Corner | Measure | Total | AVG
---------+---------+-------+-----
Metric A | 123 | 123 | 123
Metric B | 123 | 123 | 123
Metric C | 123 | 123 | 123
Metric D | 123 | 123 | 123
Right click on the Total Node and select member fact cells. Set the style variable to "Agg Type" and check off Avg. When it's AVG then the box type should be none. Use the same idea for the AVG column.
Set the AVG column header to box type none, and rename Total to something more generic.
That should work now.
Thanks. I will try this.
I agree with Paul Suggestion.
It works well.
-
Srinivas Kalyanapu