Setting Parameters from another Parameter In SSRS

Question

I was able to get this working is SSRS 2008, but do to the fact that my company only has 2005 servers I need to downgrade the report to 2005.

The idea is for a given person name there are two key fields EntityType and EntityId

So I have a parameter from a dataset where the Label is the Name and the value is EntityType_EntityId

I use the split function to take the left and right sides of from _

In 2008, I set the query parameters of the dataset to the split function and it works

In 2005, I set the Default Value of each Report Parameters

Now when I run the report and put textboxes showing the value of the parameters, the values are shown correctly but the query does not run. I am guessing that this is a lifecycle issue being

Get Name Parameter
Run Report
THEN Set Parameters = Split of Name

But the problem with that is the second time I run the report I should get result and I do not. Does anyone know what I am doing wrong.

I guess I can pass in the underscore delimted string to the stored procedure and parse it there, but my question is can this be done in the report? Reason being other callers will pass in the parameters as two seperate values.

Answer 1

I already found a suitable answer

Set the query source as text

DECLARE @EntityType INT
DECLARE @EntityId INT

IF @Name = '0_0'
 BEGIN
      SET @EntityType = NULL
      SET @EntityId = NULL
 END
ELSE
BEGIN
    SET @EntityType = LEFT(@Name,CHARINDEX('_',@Name)-1)
    SET @EntityId = RIGHT(@Name,LEN(@Name) - CHARINDEX('_',@Name))
END


EXEC proc @EntityType, @EntityId