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.
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]
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:
5. Please refer to the following screen shot of the report designer and preview:
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.