Cube Measure Vs Calcuated Measure in the report

[venkiatmaruthi]

Hi,

I have a Measure Success_Rate which is sourced from Cube. That is actaully derived from two base measures Suc_N/Suc_D in the Cube. If I apply an aggregate function in the report stuido, it's not giving the result instead it's giving '--'.

I read in user guide that if we are getting -- in the result page meaning that measure doesn't support aggregate function.


Now I pulled base measures for that Success_Rate directly to report Suc_N and Suc_D and used calcuated measure and did the same calcuation in Report studio. Now the numbers are changing in cross tab.

For example, if I use Success_Rate directly I get 0.62 and if I use calcuation Suc_N/Suc_D I get 0.53.

I am using cross tab. it has only row as hanlder name and column as success_rate. That's it


why the numbers are changing here?

[tjohnson3050]

Try changing the aggregation function in the report to calculated.  That will aggregate Suc_n and Suc_d before performing the Suc_n/Suc_d calculation.

[CognosPaul]

The first question is, which result is correct? Pull Suc_N and Suc_D into the report to check.

My guess is that it's a solve order problem. Set the solve order to 2 (or more) to ensure that the calculation is performed after the other calculations. It

[venkiatmaruthi]

Hi,

Thanks for the reply.

Success_Rate is the right one.

I tried to give solve order. No luck.

Coming to aggregation, where can I change the aggregations. These are from Cube source so they are coming as Measures. I can't change in properies for Suc_N, Suc_D and Success_Rate.

I set dimension for salesrep at report level. If I don't do this override dimesnion info I am getting more than one record in the cross tab. I was suprised why in crosstab we are getting salesrep with out grouping.

I am getting result for my cross tab report as __

Salres rep    Success Rate        Succ Rate
salesrep1     95%                          95%
salesrep1     60%                          60%
salesrep2     80%                          80%
salesrep2     70%                          70%
salesrep3     45%                          45%
salesrep3     55%                          55%

If I did override Dimension info

Salres rep    Success Rate       Succ Rate
salesrep1     95%                         78%
salesrep2     80%                          75%
salesrep3     55%                          50%


why is this so? Here I am not able understand how the SuccessRate is getting calculated. But Succ Rate is calcucated like total(Suc_N)/Total(Suc_D).

In my query, I have
salesrep                    --- level set from cube
Success Rate             --- Measure from cube (it is calculated at cube level as suc_n/suc_d only)
Suc_N                       --- Measure from cube
Suc_D                       --- Measure from cube
Succ Rate                  ---- calcuated measure Suc_N/Suc_D

[venkiatmaruthi]

anyone is having any idea....

I think the reason is, if the measure is calculated and not direct measure from source in the Cube then we can't apply aggregation summary function at report level.

Yes we can apply Sum, avg, ...etc.

Thanks.

[CognosPaul]

Wait, if this is a cube, why are you trying to apply aggregation? The aggregation settings are normally only for relational databases; cubes have the aggregation definitions predefined.

It looks like the problem you're facing is that salesreps appear more than once. If that's the problem, you shouldn't be working on fixing it in the report level. The first thing to do is to determine why they appear more than once. Is the hierarchy based on an SCD? Could the same salesrep be in two different departments? If so, why not split the salesreps into a different dimension or hierarchy to ensure that there is always one member per salesrep.

[venkiatmaruthi]

Thanks PaulM.

This was developed by someone. Now it came to me for adding aggregations.
I  am trying to find the reason why duplicates are coming for sales_rep.

[barrysaab]

So,you mean to say it is relational?Thanks

[venkiatmaruthi]

No it's cube only. It was developed by someone I am not sure. I am now trying to look into the cube design and find out why more than one sales rep coming from cube.

we are using this as a source for report. we are showing the data in cross tab. In the bottom of the cross tab user want the aggregation summary.

[venkiatmaruthi]

Yes....the heirarchy is like this

Manager -> Supervisor -> Salesrep

salesrep can be reassigned to other supervisor for a particular period say May11-Oct11 to supervisor Jason and Nov11-Dec11 to Robert.

this is giving duplicates at report level when you want to see the report for salesrep success rate. that's the reason why report level override the dimension.

[CognosPaul]

The age-old problems with SCDs. The easiest solution would be to create another Salesrep hierarchy, and reference that.