CODEWITHSUNDEEP
reporting-servicesssrs-2016
tomorrowgrl
tomorrowgrl

Reputation: 3

SSRS (Report Builder / MS SQL 2016) - SSRS expression to combine results of two rows that are part of a group

Summary of report:

I have created an SSRS report where the user selects a range of dates, and the report calculates Visitor Count and Visitor Over Capacity Counts for different Locations for each day in a range selected by users (each day displays in a separate column). For instance on Nov 1, I have 30 visitors for Location B, but Location B's capacity is 16, so the Over Capacity Count is 14 for that day (30-16 = 14).

My report is grouped by date (for daily results) and location. Date is a column group. Location is a row group.

Here's my problem:

In addition to the 5 locations that exist in the database, I also need to combine the results of two locations into a single row that behaves like the grouped locations (ie. results per day). Since this combined location doesn't exist in the database as its own location, I had hoped to just add a row after the Location row group, but it comes up with 0 for results and doesn't display results per day like the groupings do.

If this worked as hoped, for October 31st, the "Combo Loc 3-4" Row would have DailyVisitorCount = 13, DailyOverCapacity Count = 6 (overcapacity is a count > 7)

Report Layout

The results are grouped by [date] (column grouping) and [location] (row grouping). The COMBO LOC 4-5 in the example below is outside the row grouping.

enter image description here

Current Results (for selected period of Oct 31 to Nov 02):

NOTE: for the Combo Loc 3-4 I'm focusing on getting the daily visitor counts correct first (that's why the overcapacity count fields are empty right now).

enter image description here

Desired Results

enter image description here

Expressions Used Successfully with Grouped Locations

DailyVisitorCount (used for both DailyVisitorCount and TotalVisitorCount).

=Sum(Fields!VisitsCount.Value)

DailyOverCapacityCount (used for both DailyOverCapacityCount and TotalOverCapacityCount):


=SWITCH(
Fields!Location.Value = "LOC1" AND Fields!VisitsCount.Value > 24, SUM(Fields!VisitsCount.Value - 24), 
Fields!Location.Value = "LOC2" AND Fields!VisitsCount.Value > 16, SUM(Fields!VisitsCount.Value - 16), 
Fields!Location.Value = "LOC3" AND Fields!VisitsCount.Value > 7, SUM(Fields!VisitsCount.Value - 7), 
Fields!Location.Value = "LOC4" AND Fields!VisitsCount.Value > 7, SUM(Fields!VisitsCount.Value - 7),
Fields!Location.Value = "LOC5" AND Fields!VisitsCount.Value > 11, SUM(Fields!VisitsCount.Value - 11),  
   True, 0)

Averages were calculated by using the above expressions but adding to the end:

/CountDistinct(Fields!date.Value)

Expression Used Unsuccessfully for combined location (outside grouped location row)

=IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Sum(Fields!VisitsCount.Value), 0)

Upvotes: 0

Views: 260

Answers (1)

tomorrowgrl
tomorrowgrl

Reputation: 3

This is what worked to get the daily totals (still haven't been able to get the over capacity to work):

=SUM(IIF(Fields!Location.Value = "LOCATION3" OR Fields!Location.Value = "LOCATION4",Fields!VisitsCount.Value,0))

Upvotes: 0

Related Questions