SSRS Report Design Layout

Question

I have an SSRS report and not 100% how to get it working. The dataset has the following columns:

Title
DayOrder
Data
Format

The report is going to show all the data for the month (please see attached screen shot)

The values for "Description" are found in column "Title". So I want to display day 1 data (data is found in column "Data") under the row header "1". I want to display day 2 data under row header "2". Day is found in DayOrder column in the dataset.

So my data sort of looks like this (please note, I am only showing for the first three days and hope you guys understand there will be more rows as the month has more than just three days):

Title    DayOrder   Data      Format
Row1     1          1         %
Row1     2          0         %
Row1     3          .94       %     
Row2     1          13        N
Row2     2          NULL      NULL  
Row2     3          3123      N
Row3     1          61        N
Row3     2          89.76     D
Row3     3          44        N

The "Format" column is used for display, so we know if the number is a "Number" / "Percent" / "Dollar".

How would I get this laid out so the data is shown for the screenshot?

Answer 1

I would lay this out with a simple table including a row group on Title and a column group on DayOrder. That will leave you with one row per distinct Title and the column group will push out your values into day buckets or distinct day values.

NOTE : If your rows do not cover all days of the months then you will be left with gaps and you will need to fill them.

The easiest way to fill the gaps is to add a dummy record in your data with all DayOrders for each day in the month and NULL values for Data. Or you can fudge one of your existing records to ensure it has a complete range of days with nullified missing day values.

The formatting will be a piece of cake as you already have the format identifier. You can just apply that to the column text format in the form of Format = First(Fields!Format.Value,"DayOrder").