none
ssrs stack chart

    Question

  • Hi all,

    I Have scenario which attached below as a Image. 

    Ex: I have data like this

    Account  Goal savings  Projected Savings  Shortfall Savings

    x 33  29 21

    What I wanted is goal on x axis and accounts on y axis and drop down button for account selection(multiple accounts as well) and want to shade the projected savings and shortfall savings in the same bar showing that goal savings as 100% and showing the percentages of projected savings and shortall savings in the same bar so that I can explain to team about the reached targets clearly... Help is appreciated.

    In short I want

    1:goal on y axis and accounts on x axis

    2. drop down for accounts

    3.as shown above projected and shortfall in the same bar with color shading or color coding..

    sample rdl in drop box or any sharing link is highly appreciated. Thanks.


    - please mark correct answers

    Sunday, May 18, 2014 8:25 PM

Answers

  • Hello,

    Based on your description, the accumulative value of the "Goal" column in the table is 90 (33+30+27) and you want to display chart with the column hight based on the "Goal" percentage for each account. For example, the account X is 37%(33/90) and Y is 33%(30/90).
    For each account, there are two other saving type: "Projected" and "Shortfall", and you want to display the percentage vlalues in the stake column for each account: Projected %=[projected]/([Projected] + [Shortfall]), Shortfall %=[shortfall]/([Projected] + [Shortfall])

    If I understanding correctly, please refer to the following steps:
    1. Create the table with sample data for test
    create table account
    (account varchar(20) ,goal int, projected int, shortfall int )
    insert into account values ('X',33,29,21),('Y',30,26,18),('Z',27,23,15)

    2. Create report dataset from the table created above with following query:
    select account, goal, saving, qua from
    (select account,goal, projected,shortfall
     from account )p
     unpivot (qua for saving  in (projected, shortfall)) as unpvt

    3.Create a Multiple values parameter "Account" and specify the available values and default values .
    4.Create stacked column chart as follows:
    The value expression for "qua" field in values area:=Sum(Fields!goal.Value)/(sum(fields!goal.value,"DataSetname"))
    In Catefory Group, specify filters to filter account with parameter.
    Open the Series Label Properties dialog box and specify the Label data with following expression:
    =Sum(Fields!qua.Value)/(sum(fields!qua.Value,"Chart_CategoryGroup"))
    5. Please refer to the following screen shot of the report designer and preview:

    Regards,

    Fanny Liu


    Fanny Liu
    TechNet Community Support


    Tuesday, May 20, 2014 9:58 AM
    Moderator