Search code examples
sql-serverreporting-servicesssrs-tablix

Is it possible to create a repeating table in SSRS Report based on data from SQL database?

I have created a Powerapp which is used to audit schools and the data saves to my SQL database. I have designed a report in SSRS to display the findings of the audit. The SQL table, shown below, stores the items in each room that were audited (i.e. desks, pcs, shelves etc) plus the name of the room and whether any actions need to take place. I need my report to display one table per room with the items down the left hand side and the name of the room as a title. This should be repeated for each room. There may be a different number of rooms in each report so this will be varied. I've included a screenshot of what the table needs to look like. When I create the table, I can only get the room names down the left hand side in one table and the items across the top. Please help.

SQL Table

Report Table

Solution

  • Too long for a comment so I'll have to reply here.

    Your data is not a a format that is particularly suited to this. I can't see how you can determine 'Compliant' from the data you have shown in your screen shots although it maybe that you have not shown everything you have available.

    However, I would start by looking into the t-sql UNPIVOT function to get your data into a more normalised format. Using UNPIVOT you could turn your data into something like..

    AuditID | Room      | Item       | Present
------------------------------------------
   3019 | Reception | PC         | True
   3019 | Reception | Desks      | True
   3019 | Class 1   | PC         | False
   3019 | Class 1   | Desks      | True

    You can obviously extend this to include all pertinent data.

    Once you have your data in this format, create tablix with 'item' and 'present' columns only. You will have a 'detail' rowgroup at this point. Right-click the rowgroup and add a parent group, set this group to be grouped by Room.

    This will give you the basic layout, from there you can add some padding or blank rows to the room group or even page breaks.

    If you cannot get past the UNPIVOT function then I suggest you post a new question specifically on that topic then return here once you have the data in the correct format