SSRS 2016 - Customise Output based on values in data

Question

I have been tasked with creating a report that needs to display in chronological order, but within that report there is data from 2 sources that covers 2 different areas of business.

What I have been asked for, is when the type of business changes, the header row is repeated with the correct column headings for that type - i.e. :

Type 1 :

Order Ref / Sales Exec / Contract Term / Despatch Date / Agreement Type / Hardware ID

Type 2 :

Order Ref / Sale Type / Contract Term / Despatch Date / Start Date / Hardware ID

Also each header row should have a different background color by the type also (although I think I can do this already). Within the report the header row could be needed multiple times if the types flip from one to the other as the report traverses through the orders in date order.

I was first looking at adding a grouping by type, but that will interrupt the chronological flow of course.

Just to complicate matters there are different numbers of item columns for each type in reality, but I might have to just leave blanks where we have less columns for one type.

Or is there some way of having sub reports that are reporting for an "outer loop" of the actual order references?

It's almost like I need to construct the report in some sort of looped code.

How can I achieve this?

Answer 1

You can do this using table grouping. The outermost group would be by day and then within that you would group by type. Within that you can add a group that isn't grouped by anything so that it will list all the details depending on the number of records in the dataset. So the design would end up looking something like this:

This is how it looks when you run it.

Of course you can play around with how everything is organized, but the general structure should work for you. Or at least point you in the right direction.