How to calculate an Average Value in SSRS Chart

1
votes

I am building a report in Report Builder for SSRS. My Data looks like:

Raw Data

My Column Chart is grouped by Organization, with a series group of Category and each of the 6 categories will apply to each Organization.

I'd like to add an additional Line Series to show an overall average of all categories for each Organization.

Can anyone suggest the way to achieve this? Do I need a new calculated field in SSRS, should I enhance my SQL query to get the calculation done there?

My SQL Query is:

select OrganizationUnitID,Category,AVG(NumericValue) as average
, case 
 when AVG(NumericValue) <= 1 then 0
 when AVG(NumericValue) > 1 and AVG(NumericValue) <= 2 then 50
 when AVG(NumericValue) > 2 and AVG(NumericValue) <= 3 then 75
 when AVG(NumericValue) > 3 then 100 end
 as percentage
from SurveyMetricView
group by OrganizationUnitID,Category
order by OrganizationUnitID

The desired result is this chart:

End Result Chart

Thanks in advance for any advice.

2
sqlsql-serverreporting-serviceschartsssrs-2012
I think for additional Line Series,you need different datasource,different sql query,if existing sql query do not satisfy.So for different sql,you need to group by on ids like group by group by OrganizationUnitID,CategoryID. I – KumarHarsh
Thank you for the suggestions! What I'm after though is a line that charts the total percentage for each Organization, for example Accounting has 6 values, one for each category which gives a total of 95%.833, Similar for consulting Services so I'd like the line series to map that out. I guess I just don't know how to do the calculated field to achieve it. Any ideas? Is it a calculated field on the existing dataset or a new dataset and the calculation is done as part of the SQL query? Thanks. – Ross

2 Answers

1
votes

This is a possible solution for the requeriment you posted.

Add textbox to your surface, just put it in any place in your report.

enter image description here

Set the following expression to the textbox.

=Avg(Fields!percentage.Value, "DataSetName")

Add the below expression to the series. Note in the expression that I am referencing the Textbox49 since it's the name of the textbox that I added to the report before and It contains the average of all values.

=ReportItems!Textbox49.Value

enter image description here

Change the chart type for the series you've added to Line Chart.

enter image description here

It will preview this:

enter image description here

You can set the Visibility property of the textbox to hidden = true if you don't want it appears in your report.

Let me know if this was helpful

0
votes

Thanks for the pointers.

In the end what I did was to create a new dataset with the following query to calculate the overall average for each Organization, essentially selecting distinct organization:

 declare @st_date datetime;
declare @en_date datetime;

set @st_date = (@st_datein);
set @en_date = (@en_datein);

Select Distinct
  SurveyMetricView.OrganizationUnitID,
  Avg(SurveyMetricView.NumericValue) As average,
  Case When Avg(SurveyMetricView.NumericValue) <= 1 Then 0
    When Avg(SurveyMetricView.NumericValue) > 1 And
    Avg(SurveyMetricView.NumericValue) <= 2 Then 50
    When Avg(SurveyMetricView.NumericValue) > 2 And
    Avg(SurveyMetricView.NumericValue) <= 3 Then 75
    When Avg(SurveyMetricView.NumericValue) > 3 Then 100 End As percentage
From
  SurveyMetricView 
where 
  OrganizationUnitID in (@OrganizationUnit)
and ClosedDateTime >= @st_date
        and ClosedDateTime <= @en_date
Group By
  SurveyMetricView.OrganizationUnitID
Order By
  SurveyMetricView.OrganizationUnitID