CODEWITHSUNDEEP
cognoscognos-10
kannan Vinod
kannan Vinod

Reputation: 21

Cognos Report Studio 10.2.1: how to split query calculation formula and display as stacked bar chart

I am creating a sales incentive model report. I am having base salary and commission rate as a parameter . I need to display base salary and commission as a stacked bar chart which is nothing but compensation(sales*commission rate/100+base salary).The graph has to change based on the above two parameters. I am getting compensation as a single graph but I want to achieve like below link base salary mirrored with commission.

For the reference please check the below link.In the link please scroll down and go to "what if" analysis" tab.Check the second graph as I exactly want to achieve.

http://www.tableau.com/solutions/sales-reporting-and-analytics#

Kindly help me in creating this report

Thank You

Upvotes: 1

Views: 421

Answers (1)

Damienknight
Damienknight

Reputation: 1896

For a chart in Cognos to work stacked, you have to have a single measure. To achieve this in a cube, setup a 'Salary' with a Dimension 'Salary Type' with two types, Base and Commission. When setting up your chart, select stacked bar chart, and set up the Salary Type with the Salary below it:

Salary Type
    Salary

To achive this via queries (not using a cube) you need to duplicate your query. Create a calculated Measure 'Salary' and another measure 'Salary Type'. In one query set Salary Type to '"Base Salary"' and set the Salary = [Base Salary]. In the second query set Salary Type = 'Commission' and set the Salary = [Commission].

Now union the two queries together into a final third query, which you plug into your chart.

Upvotes: 1

Related Questions