Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
MSBI 49
#BI 39
BRH 39
SSRS 35
#MSBI 29
SQL Server 28
#SSRS 22
SSAS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Display Total on top of Stacked Chart

Jun 3 2010 3:27AM by Jason Thomas   

It is a normal phenomenon that human beings take most of the simple things for granted. Take this case, one of my blog readers sent me a mail asking how to display the total on top of a stacked chart in SSRS. The first time I read it, I was almost going to blurt - just enable some property to display subtotals. But the more I thought about it, the more elusive the property became and that is when I realized there is no such property. So for the benefit of all, I thought about posting the workaround that I devised for this.

Suppose I have a simple stacked column chart with Month on Categories, Product Category on Series and Sales as the measure. The requirement is to display the total of all Product Categories for a month on top of the stacked column chart. The required output is shown in the below image.

Stacked Column Chart - Total

For this, follow the steps below:-

1) The original query was

SELECT  Month,
            Category,
            Sales
FROM    Sales

Modify it as follows

SELECT   Month,
             Category,
             Sales
FROM     Sales
UNION
SELECT   Month,
             'Total' AS Category,
             0.1 * SUM(Sales) AS Sales
FROM     Sales
GROUP BY Month;

This is done to include an extra row called Total for Product Categories. The value for Sales for this row would be 10% of the total Sales of that month.

2) Go to the Design tab, right click on the chart and enable the Show Data Labels option. Then select the series label properties by right clicking on the data labels.

Show Data Labels

3) Edit the Label data and enter the following expression

=iif(Fields!Category.Value="Total",sum(Fields!Sales.Value,"Chart1_CategoryGroup")-sum(Fields!Sales.Value),"")

Label Data Expression

The above expression displays blank if the Product Category is not “Total” and displays the sum of the entire Product Categories for that month (including the value for Total) – sum of the Total field.

P.S. : “Chart1_CategoryGroup” would have to be replaced with the category group name of your stacked column chart. To find this, click on the Month (category) button in the chart and press F4. You will find the Name of the group in the property panel that opens up as shown below.

Find category group name

4) Now we should be getting the following output when we click on the Preview tab.

Statcked column chart with Total

Go back to the Design tab and click on Product Category (series) in the chart and press F4 to open the property panel. Enter the below expression in the Label property

=iif(Fields!Category.Value="Total"," ",Fields!Category.Value)

Edit Label expression for series

This is done so that the Total value will not appear in the legend.

5) Right click on the stacked columns and select series properties.

Series properties

6) Go to the Fill tab and enter the following expression for the color

=iif(Fields!Category.Value="Total","Transparent","Automatic")

Color expression

This is done so that the Total value will be transparent and hence, would look like it is not present.

7) Preview the report and you should be getting the required output

Final result 

This report has been done with SSRS 2008 R2 but the concept should work in SSRS 2008 more or less the same way.

Update

For SSRS 2008 users, it has been noticed that all the bars become black when the above fill expression is used. That is because the Automatic colour can not be used in expressions. So as a workaround, follow the step below instead of the 6th step above

1) In BIDS, press Alt+R to open the Report Tab and select Report Properties. Then go to the code section and paste the following code

Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
  If mapping.ContainsKey(groupingValue) Then
    Return mapping(groupingValue)
  End If
  Dim c As String = colorPalette(count Mod colorPalette.Length)
  count = count + 1
  mapping.Add(groupingValue, c)
  Return c
End Function

Rep properties

Right now, there are only 14 colours in the palette but more colours can be added if you have more than 14 values in the series.

2) Use the following expression for colour in the fill tab of series properties.

=iif(Fields!Category.Value="Total", "Transparent",Code.GetColor(Fields!Category.Value))

This would do the trick :)

Tags: SSRS, BRH, MSBI, #BI,


Jason Thomas
19 · 9% · 2997
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Wonderful, this is really great piece of information.

    commented on Jun 4 2010 3:31AM
    Pragnesh Patel
    111 · 1% · 464
  • This is awesome. I was trying to figure this out and I almost gave up before I stumbled upon this information.

    commented on Oct 24 2011 7:27PM
    bsreekanthreddy
    3057 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Display Total on top of Stacked Chart" rated 5 out of 5 by 2 readers
Display Total on top of Stacked Chart , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]