Align Monthly Results for the Same Control into One Row

Question

I have 12 session results over the course of a year.

The results all relate to one control but the control is performed monthly. At the month the table displays as follows:

Control Table

I would like the results to show in one row and across monthly columns rather than as above where the results are shown across monthly columns but a new row is taken with each result.

I've tried to use merge/breaks and grouping of the control but this has not worked. I am using the following formula to locate the session result for each month:

January (for example):`    =If([Calendar Month]="January") Then [Query 1].[IC CM SESSION Result]
Calendar Month :     =Month([IC CM SESSION Start Date])

In the table reference the fields are as follows from left to right: Control Name =[IC CM SESSION Subject] Frequency =[Query 1].[IC CM Monitor Frequency] January =If([Calendar Month]="January") Then [Query 1].[IC CM SESSION Result]

Answer 1

Either I am misunderstanding your requirement or you are overthinking this. All you need is a crosstab with month of your start date as the column header.

I created a report with the following free-hand SQL query...

SELECT CONVERT(DATE, '2023-01-15') AS [Start Date], 'Effective' AS [Session Result]
UNION SELECT '2023-01-15', 'Effective'
UNION SELECT '2023-02-15', 'Effective'
UNION SELECT '2023-03-15', 'Effective'
UNION SELECT '2023-04-15', 'Effective'
UNION SELECT '2023-05-15', 'Effective'
UNION SELECT '2023-06-15', 'Effective'
UNION SELECT '2023-07-15', 'Effective'
UNION SELECT '2023-08-15', 'Overdue'
UNION SELECT '2023-09-15', 'Effective'

That gives me the following table...

I created a Month Start Date variable much like your Calendar Month variable...

=Month([Start Date])

I made that the column header in my crosstab. When using a crosstab you usually have have a measure to put in the body. In your case you want Session Result. If you put Session Result in the body as a dimension you will not get your desired result. However, we can create a Max Session Result variable which result result in a measure as follows...

=Max([Session Result])

Put that object in the body of the crosstab and there you have it.

You will need to apply a custom sort order to Month Start Date to get it to display in calendar order. You will also need to add your own row header to the crosstab where I have "Session Result" if you want something there.

Noel