Search code examples
sqlsql-serverreporting-servicesssrs-2008

SSRS 2008 not passing multiple values to report run from button click

I have a simple query that I am running within a report on SSRS 2008, this query simply takes a date from the user of the report and a user code which is a simple md5 hashed code based on their local user id.

and this filters down the report to a subset of data relative to the period and the user selected. This report takes the below parameters:

 select * from 
 ops
 where [Late Flag] = 'Late Start'
 and [Planned Session Start Time] >= @start
 and [Planned Session Start Time] < @end
 and [Hashed Surgeon 1 Code] IN (@UserCode)

The problem starts to appear when this data-set is filtered down using these parameters and then the user wishes to see the charts, which again is a simple button click, the action of which runs another report showing the charts for the same period.

I am passing the session ID's of all of the sessions relating to the user as a parameter to the charts report. The charts report is only based on the below:

 select * from 
 ops
 where  [Session ID] in (@SessionID)

I expect to see multiple sessions here if a user has more than one in the period. but the report is only displaying one of these even though I have allow multiple values checked and I am using an IN as can be seen above.

Question is, has anyone had this problem before if so what can I do about it, I need to see all sessions in the same period selected for that particular user. It is very frustrating I have thought of everything I can am I missing something?

Solution

  • After reviewing the suggested approaches kindly provided by @Lukasz Szozda I realised that the best approach for me in this situation would be to create a max and min of each session date that could be passed to the charts report.

    This removed the need to parameterise the IN statement. Obviously not a solution for everyone where passing multi values to multi values is required but worked nicely for me.

    I must also state that this will only work for passing a single userCode in my setup, if I wanted to see more than one UserCode activity for the same period then I would need to follow the suggested approach and commit to parameterising the IN statement.