Aggregation for Dimension not used in Graph

[Adam_Clone]

Hi all,

I am new to this forum, and to DMR/Dimensional reporting  :-[
My apologies in advance, in case my questions are rudimentary in nature.

I have a simple line graph requirement to chart min, max and median of the sum 2 measures for Time (drill down hierarchy of Year, Quarter and Month) on X-Axis. I need the min, max and median to be based on a certain level of a second dimension, that is not included in the graph. The graph in effect has 3 lines (for min, median and max trends for time)

For eg,
-> Say, the X-Axis is showing years; I need the min, median and max values of sum(Measure1 + Measure2) for each year
-> I have a second dimension with the lowest level having employee names, say there are 5 employees (E1, E2....E5)
-> There are n number of other dimenions that have nothing to do with the report
-> For year, 2012, the min line should show the sum(Measure1 + Measure2) value for the employee having the minimum sum of those measures for that year.
-> Same for the median and max lines

I tried doing this with line graph having
-> Time along X-Axis
-> 3 Measure query items with the same calculation Measure1 + Measure2, with aggregation (and rollup) set as Minimum, Median and Maximum

However, I realized that this approach may not give the min, median and max for the sum for the members of the second dimension.

Please help.

[Adam_Clone]

Meanwhile, this is something I tried, but did not work.

-> Created 3 items with the calculation tuple([Measure1]+[Measure2], currentMember([Hierarchy])
-> Applied aggregation and rollup properies of Min, Max and Median for each

Info: I use Cognos 10.1.1 and my model is a DMR and not a true dimensional model

This did not work, it threw the error: "Invalid parameter '[Legal Paid Amount] + [Indemnity Paid Amount]' provided for 'tuple' at position '1' for 'dataItem="Min"'. This parameter is 'Numeric' type but is expected to be one of the following: 'member'."

[Lynn]

The error message is telling you that you can't use a numeric expression as an argument in the tuple function. It is expecting only members for which the function will find the intersection.

If you use the member function (not the members function) you might be able to get around that problem.

However, going back to you original question, did you try creating the min, max and median expressions referencing "within set" as the employee dimension?

If you have the Cognos sample packagess available and could mock up something similar to illustrate your dilemma it might help improve the assistance people are able to provide.

[Adam_Clone]

Hello!

Thanks for the solution of using within set, which is what I am already using.
However, the performance is really bad when having a very large set of data.

After considerable research and PMRs with IBM folks, turns out this is the only way to do median (unless external rollup is done, but which can be done only for fixed axes and filters).

I'm kind of stuck at Median([Measure] within set [Dimension].[Hierarchy].[Level]) due to the performance issue.
Any suggestion on how the same thing can be made to run faster?

I'm thinking Time-Quarter level partitions, as one of the axes of my graph report is time.