Microsoft Dynamics 365 | Integration, Dataverse...

SSRS Matrix - Row columns level grouping

(0) Share

Report

Posted on by EBMRay

899

Hello,

We all know that when we export data from Dataverse through Excel, it looks exactly like the screenshot below (each record is in a row).

I am looking to format an SSRS report so that when a user exports the data, it should look like the screenshot below:

Based on the above screenshot, the records are grouped by Employee ID, and the payroll control column for each record from the first screenshot is shown as columns instead of rows, and their values are mapped to the same record grouped by the employee.

Could you please advise how this can be done through an SSRS report to achieve it?

I would greatly appreciate it if you could provide an example.

Best regards,

EBMRay

All responses (2)

Answers (0)

Suggested answer

Haig Liu Microsoft Employee on at

Like (0)

Report

RE: SSRS Matrix - Row columns level grouping

Hi EBMRay,

It is recommended that you perform data processing after exporting the data.

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/reporting-services-basics-adding-groups-to-reports/

https://www.tutorialgateway.org/grouping-in-ssrs-matrix-reports/
Suggested answer

DAnny3211 9,280 Moderator on at

Like (0)

Report

RE: SSRS Matrix - Row columns level grouping

hi

to achieve the desired grouping and formatting in an SSRS report, you can follow these steps:

Create a new report in Visual Studio with a dataset that retrieves the required data from Dataverse.

In the report design, add a table to the report body and set the dataset as the data source for the table.

Add the required columns from the dataset to the table, including the Employee ID, Payroll Control, and other relevant columns.

Right-click on the table and select "Tablix Properties". In the "Row Groups" tab, click on "Add Group" and select "Parent Group". Choose the "Employee ID" column as the group by field and click "OK".

In the table header row, merge the cells in the first two columns to create a single cell for the Employee ID column. This will display the Employee ID value only once for each group.

In the table detail row, add a column group for the Payroll Control column. Right-click on the column group header and select "Group Properties". Choose the "Payroll Control" column as the group by field and click "OK".

Add a new column to the right of the Payroll Control column group and merge its cells with the column to the left. This will create a single cell for each Payroll Control value, and the data for each record will be displayed in the relevant cell.

Format the table and the cells as required to match the desired output format.

DAniele