% to Total metric in Universe 1

[MSIsam]

Using BO 5.1.3 (I know we need to upgrade)...

I would like to create a % to Total metric and have it as a reusable object. I would like it to be for units and dynamically give the % to total along the time hierarchy. For example, it the user selects the month dimension, unit metric, and the $ to Total metric, the % to Total would be Units (Foreach Month)/Total units on the report. Using Quarter instead of month would dynamically give the quarterly % to To Total.

Any ideas would be great

 

[sh4kil]

What database is being queried?

In Oracle there are some adanced SQL functions that operate on groups/breaks, i.e. a bit like having a BO report with breaks in it with sub-totals, totals, etc.

If you are not using Oracle then I'm not sure

 

[MSIsam]

Thanks sh4kil for your reply. We are, in fact, using Oracle but I do not believe that any DB can dynamically determine the group by (or partition by) when the selection is determined at run time by the user. I know that MicroStrategy has this functionality and I was wondering if BO can do the same.
FYI... Newer versions of Teradata have the OLAP functions as well.

Thanks,
Sam

 

[skrandel]

The reason that MicroStrategy can do this is simple. It uses multi-pass SQL. It takes the results of queries and writes them to temp tables and then reports off those. BO doesn't do this.

What you are asking is very simple though. You need 2 data providers. 1 with the limitations and the other with no limits. You then do your calculation at the report level.

Steve Krandel
VERITAS Software

 

[MSIsam]

Thanks Steve,
Being a bit rusty with BO, I had not thought of using 2 DP's to accomplish this.

Thanks for the input
Sam