SSRS - One parameter menu to filter every Fields RRS feed

  • Question

  • Hi SQL experts !

    I am getting used to report developments under SSRS (2012, moving soon to 2016), but I have one insolvable problem.

    To make it quick, my job is to migrate reports from Hyperion Interactive Reporting (know as BRIO) to SSRS. That one functionnality I am in trouble with was existing in BRIO, but I can't manage to do the same with SSRS.

    Here is an example of an old report :

    The user can choose to filter any fields he wants, with an operator of his choice. He can do that on the number of fields he wants, the selected filters are stored and shown at the right (it's in French btw, sorry for that). Then, he asks for execution and the tables will appear, with all the filters selected.

    I was already in trouble making that same kind of filter working only one time, because of the operator. My solution was to create 3 parameters, including one with operators, and write all the possible cases in my SQL Stored Procedure.

    Here is how it actually looks in SSRS for now, and an idea of my code :

    (I have 3 parameters on the left to create that filter, but they only work one time. They can't store a first filter, then a second, etc... and apply all of them when I ask for the report's display)

    (I did cut the request with [...] to show you the IF with @operator, and the @ColName < @ValueToFilter in the end, it's not a mistake)

    This is a very annoying situation, my actual reports are obviously not functionnal but I can't find any idea. Is there a way to do the same with SSRS ? Using multiple times the same parameters, or letting the users filter on the fields they want before of even after execution ? I couldn't find any proper way to do it anywhere on the net.

    Thank you by advance for you help or ideas and greetings from Paris,

    Tuesday, February 11, 2020 9:04 AM

All replies

  • To explain it with more details :

    I am developping reports for other collaborators (in a big insurances company). I have to make reports that all of them can use from the web portal, so the reports have to be very flexible.

    The team I am working for on this report for instance told me that they can potentially want to apply filters on 15 potential fields. Sometimes only one, sometimes 3, 5, and never the same ones... They need to be able to select the column to filter, to choose the operator, and enter the value to filter. For instance for one execution the year of subscription, >, 2015 ; and the name of the customer, Like, "Bond". Then they ask for the report's display with those filters applied. Which means that I can't define those filters myself before, in the Filter option of my dataset for instance, because it would force me to choose an operator, and I don't want to. The user needs to have that choice.

    To say it again in an other way, I have to create a menu in the parameters, that lets the users filter on the fields they want, and I don't know by advance the number of filters to be applied, which fields are going to be filtered, the operator chosen for each field filtered... The problem is there, I have to give a lot of flexibility and choice to the final users of the reports I am developping, but SSRS doesn't seem to be a good solution for that at all...

    Tuesday, February 11, 2020 9:05 AM
  • Could enabling Report Builder for the users provide a solution?  You can build the general dataset and each user can build his/hers own report with the most used filters .  Further filtering can be done by downloading the report to Excel and there add additional filters / sorting orders.
    Tuesday, February 11, 2020 2:56 PM
  • It seems like the only solution to my needs...

    But this is clearly not satisfying, as our end users are not into informatics at all. They really need (and want) to have no efforts to make. They want to click on the report, select filters to apply, and get the report in PDF to work with it.

    Same problem for Excel, I thought about it too but it remains too complicated for most of them, and it takes an additional time that they don't wanna have to take in their daily work.

    Thank you for you ideas anyway !

    Tuesday, February 11, 2020 3:31 PM
  • I advise you to post this requirement in this website https://feedback.azure.com/forums/908035-sql-server  Your feedback enables Microsoft to offer the best software and deliver superior services.

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, February 15, 2020 6:33 AM