SSRS - Filter property on dataset: how can I make it conditional or combine with an OR clause?

Question

Ok, this is a bit confusing. Our operational/reporting data is in an Oracle database. We have users who want to do searches in SSRS reports on lists of student. Some of these users have lists of students that are over a 1000 IDs long. We have created a process for them to create custom lists that store these values, but they are housed in a SQL Server DB. In SSRS, we use one data set to get the list of student IDs from the SQL Server DB, then pass those values in a "Main" query to get the data from Oracle. With the 1000+ parameter value limit against Oracle, the users get errors when they try to use their custom lists with over 1000 ids. Not ideal. I figured out how to get around that in the SQL by removing the

WHERE PERSON_UID IN(:AllPIDMS)

from the SQL Query and moving it to the actual filter property in the dataset properties. It works! I have successfully had my report return 3k+ students from the Oracle tables.

Great!

However, the report doesn't require the user to enter any students at all, if they would rather filter by other parameters (college, student level, etc). So I need to be able to disable the filter if the user hasn't entered any student IDs to filter on. So in essence something like:

PERSON_UID IN [@AllPIDMS] AND [@UsePIDMS] = 'Y'

OR

[@UsePIDMS] = 'N'    --in which case, don't filter on person_uid at all

Does that make sense? Has anyone found a better way to get around the 1000 value limit? Has anyone found a way to make filter properties conditional?

I don't have permissions to create a temp table in the Oracle DB to store the IDs, and trying to tuple 1000+ values (the person in question for this particular report has a list of 8k students) seems like a nightmare. As does breaking up the list into multiple 999 value chunks. Any help would be appreciated!!

Answer 1

I figured it out! So I had already fixed the 1000 value limit by moving the filter from the Query code to the Filter properties. I was able to send lists of 1000+ students. The issue was to make that new, SSRS-structured filter handle NOT sending any student IDs. It came down to defining the "set" of studentids when no ids were entered. In my case, if no students are entered, the student list = 0 (these are all hidden parameters, made visible so you can see the progression):

so I added this code to the Expression in the Filter property:

So if students are entered (AllPIDMS <> 0) this is the "code result"

WHERE PERSON_UID IN(:AllPIDMS)

but if no studentids are entered (AllPIDMS = 0) this is the code result:

WHERE 0 in(0)

which evaluates to true.

This doesn't run really fast, but it easier/faster/more elegant that the other ways of getting around the the 1000value limit.

I hope this helps other people!