Post reply

SSRS average column group value for a row group

  • February 28, 2014 at 5:35 am

    #396348

    Hi All,

    So I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.

    I have the following column groups:

    Year

    Month

    Date

    And the following row groups:

    Region

    Product

    SubType (hidden, data at the date level is summed to Product)

    At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)

    I know it's difficult to explain and understand, but if you need to me to build anything to help you visualise let me know and I'll post it ASAP.

    Any help much appreciated even if it's just things to try!

    Cheers,

    Jim.

    SQL SERVER Central Forum Etiquette[/url]

  • October 8, 2017 at 4:15 pm

    #1962776

    Hi Jim,

    Did you ever solve this problem?

    I had a similar issue with SSRS 2008 R2.

    My scenario was orders from an order table:

    Orders: OrderID, ProductName, Quantity

    Which need to be shown in the report as:

    OrderID Product1Name Product2Name Product3Name ...

    The values in the ProductName columns are the quantities.

    At the bottom of the report I wanted to show the average, over all OrderID values, of the quantity of each ProductName that was ordered.

    You cannot use the SSRS AVG() function for this as it only averages over the non-NULL elements. You cannot even trick it by converting the NULLs to zero in its argument.

    My solution was to put this expression into the last row of the OrderID column in the matrix

    =CountDistinct(Fields!OrderID.Value)

    and name that cell "CountOfOrderID" (you can also hide this cell if you don't want the user to see it)

    Then the expression for the average that goes into ProductName column, which is a column group column, is:

    =Sum(Fields!Quantity.Value)/ReportItems!CountOfOrderID.Value


    Cheers,

    Andrew Partridge

  • October 9, 2017 at 5:52 am

    #1962802

    Thanks Andrew.
    I assume I solved the problem or managed it a different way. I can't even remember what it was I was working on! ha
    Cheers,
    Jim.

    SQL SERVER Central Forum Etiquette[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply