Filter multiple times with the same parameters

SSAS and SSRS
1

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 :

832×321 83 KB

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.

For now in my reports I have 3 parameters to create that filter : Column name, operator, and value to filter (text area). 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.

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,

Robin

2

Salut! Maybe you can leverage an xml parameter or a table type parameter.

Now building that in ssrs would be interesting. Let me think about that

3

Also, I'll try to explain it better :

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...

4

The SSRS portal will not be a good solution - the only way to do something like this with SSRS that I am aware of would be a custom application that builds the dataset for the report and then ties that dataset to an RDLC report file and executes the report using the SSRS report viewer.

That will involve quite a bit of development effort...

5

yeah SSRS is not the tool for this solution. maybe just a web portal that like what you used to have

6

Thank you for your answers, I take good notes of that and it confirms my doubts...

I will check out the solution you suggested, even if it's kinda complicated. We already thought about a custom web portal too, but I wanted to be sure before getting into something that long to develop...