1

I'm not finding any other questions exactly like mine, so it's time to ask.

I have an SSRS 2016 report. It's got a regional hierarchy (State, City, Location). And it's got one measure column that is a calculation performed in the stored procedure. Due to the nature of the calculation, the value of the calculation for a city must be performed independently in the stored procedure. It's not a simple aggregation of the Locations in the City, so it cannot simply be aggregated in the report. I need the report to expand and contract on the regional hierarchy columns, and to pull the measure values straight from the dataset with aggregating.

In other words

I have a dataset like this:

State     City     Location     Measure
FL        NULL     NULL         25
FL        Miami    NULL         12
FL        Miami    Walmart      52
FL        Miami    Kmart        3
FL        Orlando  NULL         33
FL        Orlando  Sears        4

I need for the report to have collapsible rowgroups at the State and City levels, with Location being the "detail" level row group. But I need the value of Measure to be 12 for Miami, and not some aggregation of 2 & 3 (Walmart and Kmart).

I figure the approach must be either:

  1. Use traditional row groups and do some kind of programming in the expression of the measure column for the two upper-level row groups, or
  2. Don't put row groups on the tablix and do conditional formatting of the rows and some kind of programming in the toggle properties.

But in both cases, I'm not seeing anything I can do that SSRS will actually allow for the "some kind of programming" bit.

Is there a solution?

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • If you could produce 3 datasets, one at location level, the same as you have now but with no city or state totals, then another two datasets for city and state totals, then you could use `LOOKUP` to get the total values from the other datasets. This would also make the report design simple and normal toggling of rows groups could be implemented. – Alan Schofield Sep 15 '21 at 20:01

2 Answers2

1

If you must do it in the report, I think you could use a table a FILTER out the NULL city and location values. When you need them you could do a Lookup to get the value from the dataset. This will lookup the Measure value for a City where the IIF will act as a filter for the NULL value - if there is a location the City will have an X0 added and won't match the Lookup City.

=Lookup(Fields!City.Value, Fields!City.Value & IIF(ISNOTHINGFields!Location.Value), "", "x0"),  Fields!Measure.Value, "Dataset1")

If you can put your current results in a temp table, a better way would be to add the totals as seperate fields in the query.

SELECT 'FL' AS State, NULL AS City , NULL as Location, 25 as Measure
INTO #TABLE
UNION
SELECT 'FL' AS State, 'Miami' AS City , NULL AS Location, 12 as Measure
UNION
SELECT 'FL' AS State, 'Miami' AS City , 'Walmart' as Location, 52 as Measure
UNION
SELECT 'FL' AS State, 'Miami' AS City , 'Kmart' as Location, 3 as Measure
UNION
SELECT 'FL' AS State, 'Orlando' AS City , null as Location, 33 as Measure
UNION
SELECT 'FL' AS State, 'Orlando' AS City , 'Sears' as Location, 4 as Measure

--DROP TABLE #TABLE




SELECT T.*, T_S.Measure AS STATE_MEASURE, T_C.Measure AS CITY_MEASURE
FROM #TABLE T 
LEFT JOIN #TABLE T_S ON T.State = T_S.State AND T_S.City IS NULL
LEFT JOIN #TABLE T_C ON T.State = T_C.State AND T_C.City = T.City AND T_C.Location IS NULL
WHERE T.City IS NOT NULL AND T.Location IS NOT NULL

This will let you just have the recordsd you need with the additional comlumns for the summary data.

enter image description here

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
1

Group on state and city and don't use SUM() for your Measure column

Your layout could be like the one below

enter image description here

enter image description here

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • SSRS doesn't let me do this in an upper-level row of a rowgroup. I have to use an aggregate function on the measure column. – Tab Alleman Sep 16 '21 at 13:58
  • By default it suggests using sum but you can change the expression – niktrs Sep 16 '21 at 14:18
  • Ah, looking at your screenshot, I see that you only have the details level row-group. If you used higher-level row-groups, as I am doing, it would not accept a non-aggregate expression. – Tab Alleman Sep 17 '21 at 15:49
  • No i have groups on state and city. Notice the 2 group parenthesis on the screenshot – niktrs Sep 17 '21 at 16:12
  • Ok, sorry this is going to take me some time to look into, but I will get back. – Tab Alleman Sep 22 '21 at 18:57