Using a List Summary Value in a Dynamic Cube List Report??? [SOLVED]

[torre2011]

 Within my Cognos 10.2 list report, I am using a Dynamic Cube as a source. Based on the business requirments I am using a list rather than a crosstab to show specific OLAP data.

My issue is that I need to create a column that uses the value of a cell within the row divided by the total of all such values in that column...for the level in the row.

Ok so here is an example:

Col1	Col2	Col3
Red	40	.44
Blue	50	.55
TOTAL =	90

In the example above, Col1 is a hierarchy and so I am able to drill up/down thus changing the values of the columns to the right of it.

I am attempting to build Col3, which has the formula of Col2 / (Total(Col2)). The challenge is to get the correct Total value based on the edge value of Col1. So while the example shows a total of 90, if I were to drill down on 'Red', then the total would be different..and thus would impact the value of Col3.

I created a List Summary item for Col2 by selecting Col2 and use the Total aggegation...this created a data item in my query. I attempted to use that within a calculated data item in the list withe the formula of Col2 / Total.  But the result was incorrect.  What I found is that if I drag the Total data item to the last column of the list and ran it..the value for the total was not the summarized value for all rows but just for each row...so Col2 and the Total were exactly the same!  Not sure why, but it has to have something to do with the hierarchy on the left edge (Col1).

If i use the calculation of Col2 / total(Col2 for report) I get exaggerated total values that are wrong.   I am in need of a way to create a constant total value that will change as I move up and down the hierarchy.  Then I will need to use that value in the formula mentioned above.

Does anyone have any suggestions?

THanks!

[MFGF]

Within my Cognos 10.2 list report, I am using a Dynamic Cube as a source. Based on the business requirments I am using a list rather than a crosstab to show specific OLAP data.

My issue is that I need to create a column that uses the value of a cell within the row divided by the total of all such values in that column...for the level in the row.

Ok so here is an example:

Col1	Col2	Col3
Red	40	.44
Blue	50	.55
TOTAL =	90

In the example above, Col1 is a hierarchy and so I am able to drill up/down thus changing the values of the columns to the right of it.

I am attempting to build Col3, which has the formula of Col2 / (Total(Col2)). The challenge is to get the correct Total value based on the edge value of Col1. So while the example shows a total of 90, if I were to drill down on 'Red', then the total would be different..and thus would impact the value of Col3.

I created a List Summary item for Col2 by selecting Col2 and use the Total aggegation...this created a data item in my query. I attempted to use that within a calculated data item in the list withe the formula of Col2 / Total.  But the result was incorrect.  What I found is that if I drag the Total data item to the last column of the list and ran it..the value for the total was not the summarized value for all rows but just for each row...so Col2 and the Total were exactly the same!  Not sure why, but it has to have something to do with the hierarchy on the left edge (Col1).

If i use the calculation of Col2 / total(Col2 for report) I get exaggerated total values that are wrong.   I am in need of a way to create a constant total value that will change as I move up and down the hierarchy.  Then I will need to use that value in the formula mentioned above.

Does anyone have any suggestions?

THanks!

Firstly, I have to get this off my chest... List reports against dynamic sources are something to be avoided at (virtually) all costs. Lists are geared up for flat relational sources, not OLAP cubes. Anyhow. I will step down off my soapbox now and resule mormal service :)

Try coding your expression for Col3 as

[Col2] / aggregate([Col2] within set [Col3])

This ought to do the trick

MF.

[torre2011]

MFGF,

I am right there on the soapbox next to you!  ;D

For reasons that would be too long for this forum, I decided to try this solution...but I am in full agreement with your reservations.

Anyway, it was the set statement that I was missing...so THANK YOU!

[CognosPaul]

Just a quick clarification on what MF said.

There are two reasons you need to avoid using lists against OLAP models.

1. Lists don't maintain context. When using a crosstab, you always know that nodes to the right are in the context of nodes to the left, and not the reverse. With lists everything is flat, so functions like currentMember() will not work as expected.
2. Cognos needs to pivot data locally. Think of it like this, when you're using a crosstab against a relational source, Cognos will need to pivot rows to columns. Relational sources are naturally in lists - the data is processed locally and it's an expected and unavoidable operation. OLAP sources are naturally crosstabs. Cognos has to effectively unpivot the data, which is also done locally.