SSRS - One filter for every Fields
-
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,
Robin
-
So you want to make some parameters optional?
SomeColumn = COALESCE(@ParameterName, [SomeColumn])
So If @ParameterName is not assigned a value, then the comparison evaluates to
SomeColumn = SomeColumn
… so it's like 1=1 (gets ignored).
-
Actually no, I was thinking about doing something like that with optionnal parameters but it creates too many cases to deal with in my Stored Procedure.
The thing is that the filter I have to create acts like this : the users chooses one field, one operator and enters the value to filter ; this is stored. Then he can choose an other field/operator/filter on the same menu (which is the problem), and create an other filter. He uses the menu to set multiple filters, and then asks to see the report, and all those filters are applied. [screenshot 1]
In SSRS I couldn't find a way to do the same. If you select a value for a parameter (drop down or text area same problem), when you visualize the report it's only this one to be applied. If you change the values of the parameters and press the Show Report button again, only the new value of the parameters will be used...
I find it very tricky...
-
Some days I can't see the forest for the trees. If the filters are all optional, you can do the filtering in SSRS. (So you could have a whole bunch of optional *report* parameters (not to be confused with stored procedure parameters). To do that, you open the report in design mode. Right-click the DataSet the report is based on. One of the last options is Filters. If you add filters there, those are applied after the data is retrieved from the source (like SQL Server), and you can create parameters for those manually. You can make those optional, too.
-
Hi and thank you for your answer,
I already considered this option a long time ago, but the situation is way more complicated. I'll try to explain it better :
I am developping report for other collaborators (big big insurances company). I have to make reports that all of them can use from the web portal, so they 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 beofre, in the Filter option for instance like you mentionned, because I am forced 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...
-
Don't think you can do that very easily with SSRS. the best I think you can do is to make the filters optional... Except you can't build complete filters through the UI...
If I were doing this in Access <yeah, I can hear the groans>, I could basically create a form with dropdowns to choose [fieldname] [comparison operator] [value]… and then build filters with that. Don't think you can do that with SSRS directly.
-
This is exactly what I want to do and what was done with the previous solution, now toxic (or soon).
I am very annoyed, but it seems that SSRS will not be a suitable solution for our reporting needs...
Thank you for your help and answers once again and have a good day !
-
One of the benefits of SSRS is that you can allow the users the ability to access and use Report Builder. For this you would define a data/report model and publish that to the end user - and then the end user can build any reports they need against the published models.
Maybe that could be the replacement solution for this dynamic report that allows the user the ability to query any way they want...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey,
Thank you for your answer, and thank you for making my colleage and I laugh early in the morning ! Our end users are clearly not enough in informatics for that, even printing something is sometimes a challenge ! (;?_?)
But seriously, I take note that this is the best we can do with SSRS, the most advanced interactivity we can give them, before having to let them design things by themselves.
Have a good day and thank you for your time
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply