Draw stacked bar chart (reporting services, BIDS)

Posted on 2009-02-22
Last Modified: 2012-05-06
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?

Question by:akalisa
    LVL 11

    Expert Comment

    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.


    Author Comment

    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

    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

    LVL 11

    Accepted Solution

    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'

    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'

    for the value item representing 'revenue due from outstanding contracts to be renewed'

    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.



    Author Closing Comment

    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.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Hello, In my precious Article  ( saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now