[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2663
  • Last Modified:

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?

0
akalisa
Asked:
akalisa
  • 2
  • 2
1 Solution
 
shorakCommented:
Which version of reporting services are your using? I havnt used 2008 so these instructions are based on reporting services 2005 using BIDS.

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.value-fields!renewed.value
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
0
 
akalisaAuthor Commented:
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
0
 
shorakCommented:
Hi.. I dont have much experience when it comes to working with stacked charts in reporting services, especially when you have more than one category on the x-axis. I have done a few stacked charts but they all involved one category.

Initially you said that you have two graphs as per your statement  
"Currently, I have to put Due to Renew data in a bar chart, and Actual Renewed data in another separated one."

does this mean that each graph has a column representing renewals and a column representing revenue?

Looking at your record set. It would be difficult to create a second category that represented the revenue so I dont think you can create a single graph showing everything so you will have to have two graphs, one showing renewals and one showing revenue.  Also, I dont think you can have a total bar but since I dont use graphs much, I might be wrong.

To create your charts, follow my instructions and use the following expressions in each chart value item:

For the Renewals chart:
for the value item representing 'No of renewed contracts'
=SUM(IIF(Fields!RenewedFlag.value="Yes",1,0))

for the value item representing 'outstanding contracts to be renewed'
=Count(Fields!ContractNo.value) -SUM(IIF(Fields!RenewedFlag.value="Yes",1,0))


For the Revenues Chart
for the value item representing 'Actual renewed revenue'
=SUM(IIF(Fields!RenewedFlag.value="Yes",Fields!volume.value,0))

for the value item representing 'revenue due from outstanding contracts to be renewed'
=SUM(Fields!volume.value)-SUM(IIF(Fields!RenewedFlag.value="Yes",Fields!volume.value,0))

Make sure you add customer type as the category.

My solution involves 2 graphs. I would look around the net and see if anyone has managed to create what your trying to do in reporting services 2005. It might be possible to do this using 3rd party add-ons such as Dundas charts but I havent used it so I cant really say.

Rob





0
 
akalisaAuthor Commented:
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.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now