How to get pre-calculated data in the framework displayed in a crosstab?

[alex11343]

Hello,

At the moment, I'm having trouble displaying precalculated data at the Framework level in a crosstab.

I have the following crosstab


The fields <#Somme(Code CC)#> and <#Somme(Code Groupe)#>  are obtained with the aggregate function sum on the fields <#Code CC#> and <#Code Groupe#> from the DATA menu.

Their expression generated by Cognos is:    total(CurrentMeasure within detail [Code ___])

"Récapitulatif" is just basic text.


As the sum results on the fields Tot CC and moreover Ratio CC are not satisfying at all, we chose to pre-calculate them in the Framework and replace them in the corresponding boxes in the crosstab.

hierarchy scheme:
A "groupe" is made of all the "CC" hence <Tot Gpe> and  <Ratio Gpe>
A "Société" is made of all the "groupes" hence  <Tot Soc> and <Ratio Soc>

Their expressions are:

ââ,¬Â¢   <Tot Gpe> = total([Tot CC]  for  [Type] ;[Axe];  [Annee]  ; [Code Société]  ; [Code Groupe])

ââ,¬Â¢   <Ratio Gpe> = if ([Tot Gpe] = 0) then (0) else ([Tot Gpe] / [Tot Gpe par Annee])

ââ,¬Â¢   <Tot Soc> = total([Tot CC]  for [Type] ; [Axe];  [Annee]  ; [Code Société])

ââ,¬Â¢   <Ratio Soc> = if ([Tot Soc] =0) then (0) else ([Tot Soc]  / [Tot Soc par Annee])

with

ââ,¬Â¢   <Tot Gpe par Annee> = total([Tot CC]  for [Type] ;[Annee]  ; [Code Société]  ; [ Code Groupe])

ââ,¬Â¢   <Tot Soc par Annee> = total([Tot CC]  for  [Type] ;[ Annee]  ; [Code Société])

A total and a ratio still need to be calculated on the same scheme for all the "sociétés"...


When I ask Cognos to display the data from the query I get an error message PCA-ERR-0062 " the 'cn' knot should not be a 'value' " *
unless I change <#Somme (Code CC)#> into 'Somme Code CC', it is the same for Code Groupe.

When I try to run the report with the above fields renamed I get  the crosstab but the boxes I filled  (unlock the crosstab boxes and define the content in the property menu) with the specific values <Tot Gpe>, <Ratio Gpe> <Tot Soc> and <Ratio Soc> remain empty.

How to make this work?


Any help is welcome... Any further information will be given if asked.

Thanks a lot


*translated from French...

[MDXpressor]

Hi Alex,
Is this a C8 report?  Is it a dimensional source (i.e. cube, DMR)?

The line:  total( CurrentMeasure within detail [Code____]) leads me to beleive that it is.

If so you are doing too much coding for these calcs.  Further, your solve order may be impacting the delivery of the report.  Let's see if we can replicate using the Great Outdoors sample data.

[alex11343]

Thanks for your answer.

It is indeed a C8 report and a dimensional source (DMR).

I shall check my solve order too
[...]

it is as follows:
<#Somme (Code ___)#> is solved at order 1 whereas the rest of the crosstab is solved at order 0.

But as I wrote in my previous post the total(CurrentMEasure within detail [Code___]) expression needs to be renamed into text for the report to work and the boxes to remain empty.

The goal is just to have the correct values displayed in the crosstab instead of those calculated by Cognos. If we simply could add boxes in the crosstab: a title on the left like the one we get by changing names and the precalculated values in the boxes in the measures space like what we try to get, it would be fine as well

My colleagues and I think that Cognos is not able to fill the boxes with the correct information not knowing what to put where but we haven't managed to have it do it correctly so far.
Still searching...

[MDXpressor]

Actually I think you want the solve order on your rows to remain at 0.  Set the solve order on the Ratio column to 1.  Ratio is dependant on the rows being solved against the TOT CC column, so altering the solve order on Ratio will delay calculating that column until there is data available at all of its dependant nodes.

[alex11343]

Yes, that's exactly what I have been told by one of my colleagues today  ;D

I tried to do so and  I also put the sums' solve oder at 2, 3 and 4...
I also chose to calculate the ratio through Cognos at the Report Studio Level instead of at the Framework Level...

Nevertheless it failed, I didn't get the expected results... yet

(there's still hope :) )