question

kartikaya.singh avatar image
kartikaya.singh asked

I have a requirement from our client regarding one of the SSRS reports which we are developing for them having multivalue parameter seperated by commas accepting real time parameter entries like below

i have policy no. values like 546546,16464 in the parameter which the user want to search. He can enter more such values to get the recordes as there are around 5lkh records which is not feasible to search for user in the dropdown list. Can anyone provide SSRS solution on this. I dnt have any access to change or make any sort of procedure in the oracle db. I want to do this only in SSRS.. Is this possible????
ssrssearchrecord
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kartikaya.singh avatar image kartikaya.singh commented ·
can anyone help me on this?
0 Likes 0 ·
virtualjosh avatar image
virtualjosh answered
Not without modifying the PROC on the back end. Without modifying the back end, only thing you might get away with is manually modifying the default of the Report Parameter from the Report Server configuration page for that single run of the report. Not practical. You can't do magic - you need access to the back end. What I would suggest for this is using a text parameter (let them input a csv), and on the PROC side, split the varchar(max) string into tokens with a split function. I would also trim each token. On the WHERE clause, you might want to do something like: DECLARE @csv varchar(max) = null -- coming from SSRS ... -- Create your #Tokens table with the SSRS Text parameter -- I won't tell you how to do that, that's outside my scope -- Suggest you create a split function and also trim each token ... WHERE ( @csv IS NULL -- If they selected Null, then show All OR MyField IN (SELECT token FROM #Tokens) ) I personally usually do the below with my Multi-Select parameters. Especially if they are non indexed values anyways. You might not want to do the catch All part on big or indexed tables coz in my experience, a full table scan is performed since the optimizer can not predict what @param will be. This technique also uses the special keywords "(All)" and "(No Value)". This is to account for Null values in the back end. Few developers realize they potentially exclude NULL values when they use Multi-Select report parameters. ( @param IS NULL OR SUBSTRING(@param, 1, 5) = '(All)' -- First option is keyword OR COALESCE(Param, '(No Value)') IN (SELECT * FROM dbo.fn_split(',', @param, 1)) ) The only other thing you might try is get all rows and HIDE any row not matching items in your CSV. You would need complex embeded code in the report to split the Report param value and modify visibility for all rows. Again, highly unpractical and not even sure if this is doable. It is a stupid solution anyways - you don't want to have the entire 51k rows travel from the database to hide 99.9% of them.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

virtualjosh avatar image
virtualjosh answered
Just thought of something else.... if you already have a Multi-Select filter in place, then your DB stored procedure (to which you say you have no access to), is already prepared to handle an incoming CSV string. So, why don't you simply replace the multi-select filter with a text parameter?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.