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

Aggregate of an aggregate function in SSRS

Jul 3 2010 5:52PM by Jason Thomas   

I am worse than a child when it comes to colours and no wonder, spend a lot of time on the aesthetics whenever I am preparing an Excel sheet or a PowerPoint presentation. For me, this is rather a way to indulge the child within me than to make it more presentable to the audience. The joy that you get when you arrive on that perfect colour for your headers or that perfect font to suit the mood or that perfect design template for your slides is something that can’t be expressed. Recently, I was preparing the sample data for a report and playing around with the colours in excel as usual when I noticed that there was an unexpected aggregation that would be required. Beads of sweat broke from my forehead as I contemplated going back to the user and saying that I might need some time to think whether this is possible.

Let me give you an overview of the requirement. The measure (Order Count) comes in at a sub category level and the client wanted to see the average of the subcategories within a category, as well as an average of average as the grand total (refer below image for calculations)

Aggregate CustomCode 

I straightaway opened BIDS and decided to try it out for myself. I quickly made the dataset and designed the layout as shown below.

Design mode - layout

On previewing the report, I got the following output

Preview

I was almost giving that smug grin of mine thinking everything is fine, when I spotted that the Grand Total doesn’t match with the requirements. The totals were coming properly at the category level (which were just an average of the sub categories) but at the Grand Total level, instead of taking an average of the Categories [ (7585 + 7857 + 4082) / 3 ], it was still taking an average of the sub-categories [ (5212 + 7512 + 10030 + 6185 + 9528 + 3382 + 4781) / 7 ]. That is when I realized that what we need here is an average of an average at the grand total level. But SSRS 2008 and below versions doesn’t allow us to define an aggregate of an aggregate function. This was confirmed when I got the following error on replacing Avg(Fields!Order_Count.Value) by Avg(Avg(Fields!Order_Count.Value))

[rsAggregateofAggregate] The Value expression for the textrun 'Order_Count9.Paragraphs[0].TextRuns[0]' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

My next approach was to try using the Avg function on a textbox which would already have the value Avg(Fields!Order_Count.Value), something like Avg (ReportItems!Textbox21.Value). The result was again an error, not much different from the previous error

[rsAggregateReportItemInBody] The Value expression for the textrun 'Order_Count9.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

That is when I decided to dig into the treasure troves of the MSDN forum, and unearthed this post by Jin Chen (MSDN Moderator) where he gives a sample custom code to achieve the functionality. Armed with a minorly tweaked version of this custom code, I followed the steps below to achieve my requirement:-

1) Go to design mode and right click on the area outside the report body. Select Report Properties

Report Properties

2) Select the Code tab and paste the following code there

Dim public totalBalance as Integer
Dim public Cnt as Integer
Public Function AddTotal(ByVal balance AS Integer ) AS Integer
                totalBalance = totalBalance + balance
Cnt=Cnt+1
                return balance
 
End Function
 
Public Function GetTotal()
                return totalBalance/Cnt
End Function

 

The AddTotal function basically adds all the values of a textbox and sets it to totalBalance variable. This function also counts the number of values. The GetTotal function returns the sum of the values by the count of the values which is basically the average of the textbox values. Once you have pasted the code, you can click OK.

Custom code window

3) Replace the expression at the Total level from

=Avg(Fields!Order_Count.Value)

to

=Code.AddTotal(Avg(Fields!Order_Count.Value))

This is done so that when the average is being calculated at this textbox during report rendering, at the same time the sum and count of these averages will also be calculated.

4) Replace the expression at the Grand Total level from

=Avg(Fields!Order_Count.Value)

to

=Code.GetTotal()

This function will return the computed value, which would be the average of the average as we required.

5) Preview the report to confirm that the output matches with the requirement.

Preview result

Guess who is grinning smugly now? :D

Tags: SSRS, BRH, MSBI, SQL Server, #BI, #SSRS,


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



Submit

5  Comments  

  • I have tried this... but it isn't working for me.. i have details in one column and a budget in another. The budgets are done at the group 4 level... i need to rollup the budget numbers at the group 3, group 2 level and group 1 levels... however, the actual budget numbers appear (though i hide them) at each detail line... so when i sum them at the group 3 level the numbers are huge... does that make any sense? have you any suggestions?

    kinda desperate...

    commented on Jan 25 2012 4:55PM
    2929
    2749 · 0% · 4
  • This worked for me with one exception. When the report paginates the variables in the code seem to reset so I only get the grand total from what is displayed on the last page. Is there a fix for this?

    commented on Mar 12 2012 5:34PM
    tdrepass
    2367 · 0% · 5
  • Declare the variable as shared, should solve your issue

    commented on Mar 12 2012 5:49PM
    Jason Thomas
    19 · 9% · 2997
  • Declaring the variable as shared did let it total across pages, but the code keeps getting call as you navigate the pages. I set the table o keep together on one page. That will work for now.

    commented on Mar 13 2012 5:15PM
    tdrepass
    2367 · 0% · 5
  • Hello Jason,

    Your solution works, but now I'm trying to use =Code.GetTotal() in an other tablix to show only the totals and I get zero's. =(ReportItems!TextboxXX.Value) also doesn't give a result. I also replaced the word public by shared. but also not with the desired result.

    commented on Jul 17 2012 4:20AM
    JWMW
    3050 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Aggregate of an aggregate function in SSRS" rated 5 out of 5 by 3 readers
Aggregate of an aggregate function in SSRS , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]