akalisa
asked on
Draw stacked bar chart (reporting services, BIDS)
I'm trying to draw a bar chart that shows:
+ Number of contracts due to renew in a month, Total number of revenue for those contracts.
+ Number of actual contracts renewed, Total number of revenue of those renewed contracts.
These figures are separated into two group Resident and Business.
Currently, I have to put Due to Renew data in a bar chart, and Actual Renewed data in another separated one.
Because the number of renewed contracts are a percentage of number of contracts due to renew. It would be nice if they can be merged into some sort of stacked bar chart but I'm unable to figure out how to do this. Does anybody have any recommendation?
+ Number of contracts due to renew in a month, Total number of revenue for those contracts.
+ Number of actual contracts renewed, Total number of revenue of those renewed contracts.
These figures are separated into two group Resident and Business.
Currently, I have to put Due to Renew data in a bar chart, and Actual Renewed data in another separated one.
Because the number of renewed contracts are a percentage of number of contracts due to renew. It would be nice if they can be merged into some sort of stacked bar chart but I'm unable to figure out how to do this. Does anybody have any recommendation?
ASKER
Sorry for not explain it clearly.
I drew this picture as an example of what I'm trying to build. I'm just thinking it would be nice to have, but not sure if it can be built using BIDS
FYI:
Currently the stored procedure returned recordset in form of detailed rows:
ContractNo | Volume | CustomerType | RenewedFlag
A001 | 100 | Resident | No
A002 | 80 | Resident | Yes
B001 | 1500 | Company | No
B002 | 1800 | Company | Yes
B004 | 900 | Company | Yes
However, I can modify it to whatever form of record set I want either detailed or summarized, so this is not an issue.
I'm using SQL server 2005 for DB, Reporting servcies 2005
sample-chart.JPG
I drew this picture as an example of what I'm trying to build. I'm just thinking it would be nice to have, but not sure if it can be built using BIDS
FYI:
Currently the stored procedure returned recordset in form of detailed rows:
ContractNo | Volume | CustomerType | RenewedFlag
A001 | 100 | Resident | No
A002 | 80 | Resident | Yes
B001 | 1500 | Company | No
B002 | 1800 | Company | Yes
B004 | 900 | Company | Yes
However, I can modify it to whatever form of record set I want either detailed or summarized, so this is not an issue.
I'm using SQL server 2005 for DB, Reporting servcies 2005
sample-chart.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's seems that we can't do that in BIDS so I accept this response to close the topic. But if anyone have any better solution, it will be greatly appreciated.
1) create your graph on your report. For the chart type, select 'Column'. For the chart subtype, select '100% stacked column chart' (you can do the same for 'Bar' chart type)
2) Add your category fields (in your case, it will be resident/business)
3) Add the values fields for your graph. This is the key part and depends on the format of the data coming from your SQL. Is it aggregated data? ie showing totals only or is it detailed data that is coming out? If its just totals then add Add 2 value fields to this section. Call one 'Renewals Due' and one 'Actual Renewed'
For the value of 'Actual Renewed', set the formula so that it is the the field that represents the numbers renewed. Click on the Appearance tab and click on the series style button and set the fill color that will represent this portion of the data.
For the value of 'Renewals Due', set the formula in the value field so that it says something like 'total renewals due minus numbers renewed'.
ie =fields!totalrenewalsDue.v
Basically what we are doing is taking number of renewals due as 100% and then subtracting the numbers already renewed to give us the remaining percentage. set the color that will represent this part of the data.
If the data is being extracted as detailed data then you will need to use the SUM fuction to sum up the values for each field.
please note that even though the graph will represent a 100% stacked chart, the actual values for the Y axis will display the actual number and not percentages. you will have to convert the numbers to percentages if you want the labels to show percentages.
Hope this helps.
Rob