Search code examples
sql-serverreporting-servicesssrs-2012dynamic-pivot

SSRS Dynamic Reports for Key Value Pairs

I need to use SSRS to create many different reports, and I have been trying to find the best way for me to easily create them as need, and for users to navigate them and use them for their needs.

To give you and idea of the two sets of data I am dealing with:

  • EDI file from our customer
  • Raw data output from hardware configuration

Now the EDI data is fairly consistent, so these columns are static. The hardware data is usually a massive list of different configuration. I receive them in different flat files formats and using SSIS or other tools I get the data into Key Value Pairs. Now in a report, I use matrix to keep EDI columns static, it matches with the hardware on serial number, and Hardware data pivots.

So the report does not break, and so I don't give the user too much information, it matches up on another table where I specify what keys I want to be columns.

Here is a small example of one of my reports:

Example

The green columns are EDI, while the orange is the hardware.

My question is, is there a better way for me to be doing this? Some reports can get complicated like needing total for certain hardware (counting hardrive space, ram total etc.) which is difficult to do dynamically.

I have tried creating in reports in this fashion, with these parameters:

Example 2

This way I can create the Key columns per project and user can select what report they want to run. The default is All Data.

Is there a better way for me to create these reports? SSRS really doesn't seem to play well with dynamic pivots.

Is there a better tool that will handle these reports dynamically, or let users pick and choose what they want to see in a report?

Solution

  • I can't visualise your data but if I understand correctly, you could have a dropdown list showing all the unique values that are in the column you are using in the column group. Set this to be multi-value and then simply have the WHERE clause read something like 

    SELECT * FROM myTable WHERE myColumnGroupField IN (@myColumnChoiceParameter)

    This way the user could select whichever columns they would like.

    You could extend this by adding another parameter that has some preset groups of columns (I think you might have one of these already if I understand correctly) that would set the default value of the main @myColumnChoiceParameter parameter.

    If you want something more flexible then you might want to look at Power BI but depending on how you intend to deploy that might not be a simple option.