If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Multiple measures in a chart

Started by Yunus, 11 Mar 2008 06:26:13 AM

Previous topic - Next topic

Yunus

Hi all.  I am trying to make a chart that compares an individual section to the entire company for employee data.
It sounds simple but really I am trying to report on more than 100% of the numbers.  I am a member of my section but I am also a member of the whole.  Currently I have a working solution using 1 query that lists everyone then in another column lists if they are a member of the section.  Is it possible to have 2 queries display on 1 chart?  I tried this with a Union and the chart would not display.  If anyone has any other suggestions I am open to them.  My solution works but it is not fast.

Thanks.

rockytopmark

Sounds like you have a Dimension... a Hierarchy, with Section being a Level below "All"

If you model your metadata dimensionally, then you can do what you are trying to do using Levels and Members of the levels, from within a dimension.

Otherwise, you might also fashion a report Query that returns the measure, filtered by your section, as well as another measure, that represents the entire company.  Then use multiple series in your chart.

For the section specific measure, your calculation might be:
   IF([namespace].[query].[Section] = ?pSection?)
   THEN ([namespace].[query].[some measure])
   ELSE (0)

For the "All" measure, just return the measure, as-is. 
    [namespace].[query].[some measure]

The chart will do your aggregating for you.

Yunus

Thanks for the reply.

Actually the second way you listed is how I am currently doing it.  I will try to dimensionally model it in FM though, that might make this quicker.  It is dimensionally related data its just currently in relational format.