Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get AVERAGE of column data in Reporting Services matrix. Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
I need to create an AVG based on the cell data in row_group1 and the number of months that have data. I have the sum of row_group1, but cannot get a count on number of months, and just entering an AVG function in the total column does not produce the correct results.

I have two row groups, and a single column group on "Month" that produces row data by "monthname".

I can get the SUM of my CountDistinct(Fields!CustID.Value), so if I can get the count of the number of "Months" produced by my column group I could calculate my own average, but I cannot get the correct count of months using InScope, (returns all 0's):

=Iif(InScope("MTH"),(CountDistinct(Fields!CustID.Value)), 0)

Basic structure of matrix:

Service Feb Mar Apr AVG # of Distinct Customers per Month
Cleaning 3 7 2 4
Post #1442425
Posted Wednesday, April 17, 2013 8:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:56 PM
Points: 393, Visits: 192
Hi,

Caveat, I've never used or done this before but. . .

there is a Matrix.Columns Property available in SSRS, which will return the number of columns in the report. Based on that, you should be able to determine how many months are being used, and thus calculate an average.

I believe that you will have to write some custom code to get to that property (Report, Report Properties, Code) or write you own class/assembly using the language of your choice.

Otherwise, I would consider calculating it ahead of time in your SQL if possible.

Good luck.
Post #1443322
Posted Wednesday, April 17, 2013 9:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
Thank you, I will give that a try, and update you if I can get the results I require.
Post #1443325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse