SSRS - Custom Grouping for sum

Question

I am working on an SSRS report that shows the performance of each department for each shop order on each line for each shift.

We have 10 lines (line 1 through 10). One line may get 1 or more shop orders in it. My report is grouped by shift first, because we need to show the shift totals to show performance of each shift. After that, it is grouped by Shop order.

I need to add a grouping on the line now. But the problem is, they are not each a group of its own. Here is how the lines need to be grouped:

Line 1 - Hand line
Line 2, 3, 4, 5 - Machine line
Line 6 - Pacific line
Line 7, 8, 9, 10 - Other line

I need the totals of each of these groups under the last shop order on the last line of the group.

How do I go about doing this?

Here is the current PDF file that is getting sent out. This is designed by an Access report and we are moving to SSRS:

As you can see in the image above, line 1 had 2 shop orders for yesterday.They both get put in the Hand total group and summed up as 1 group.

This is what my current grouping in SSRS looks like: Shift -> ShopOrder

This is what I've tried: I tried adding a Child group to the ShopOrder group and in the GroupBy Expression, wrote something like this:

IIF(
        Fields!Line.Value = 1,
        "1HandTotal",
        IIF(
                Fields!Line.Value > 1 AND Fields!Line.Value < 6
                "2MachineTotal",
                IIF(
                        Fields!Line.Value > 5 AND Fields!Line.Value < 8
                        "3PacificTotal",
                        "4OtherTotal"
                )
        )
)

I then added totals to that group and it does not seem to do the trick. I just get one total in the end of all the shop orders.

This is what my current design look like:

Any help would be appreciated.

Thanks

This doesn't seem like it would be that difficult. Have you tried the overly simple Add Total from one of your line item amounts to see if it works? Sometimes it actually does what you want. Otherwise, a pic of your DESIGN view might help. — Hannover Fist
@HannoverFist added an image of my current design. There are two groups. Shift and Shop order. Which group do I add totals to? The "group" I need totals of does not exist and hence, my confusion. — Crazy Cucumber

Frank Ball Frank Ball · Accepted Answer · 2018-07-24T19:02:20

I would add the field to your query using something along the lines of:

case
when Line in (1) then 'Hand line'
when Line in (2, 3, 4, 5) then 'Machine line'
when Line in (6) then 'Pacific line'
else 'Other line'
end

SSRS - Custom Grouping for sum

1 Answers