Using code to set a parameter.

  • Been using SSRS2005 for a little over a year and a half now. I have a situation here that I thought would be easy to solve, but it's not. The problem is I am currently executing some datasets that I have using the EXEC key word rather than using a stored procedure. That is, the dataset is generated using EXEC in the following way: EXEC dbo.ProcName 'param1', 'param2' construct. However, I want to change this. Instead of getting the 'param1' and 'param2' values from parameters though I want to get it from code because I don't want to create a lot of parameters and set them to hard coded values to do this.

    For example, I want to write this function:

    Public Function myFunction(i as Integer) as String

    Dim myarray(20) as string

    myarray(0) = "Value1"

    myarray(1) = "Value2"

    myarray(2) = "Value3"

    ...

    myarray(19)= "Value20"

    myFunction = myarray(i)

    End Function

    Now, when I'm in the parameter tab of the DataSet tab I want to do something like this:

    @MyParam | =Code.myFunction(0)

    The @MyParam would then have the value "Value1". However, this doesn't work and I'm concerned I'm going to have to add 20 parameters to do the same thing.

    Any help would be appreciated.

    Thanks,

    Aktikt

  • You should be able to do this. Are you getting an error? If so, what is the error?

  • Jack,

    There's no error, but the dataset simply doesn't populate. The stored procedure I'm using creates a dataset which I am trying to use as a dropdown for the user to select the parameter value they want before running the report. However, with the setup I described it shows a greyed out dropdown box.

    Because I was curious if it was the report I was working, I tried it in another report and it worked!

    While this is hopeful it's also more frustrating.

  • Can you run Profiler when you run the report and see what is being sent to the SQL Server?

    The other thing I like to do is to not tie the Code to anything and just stick it in a textbox on my report so I can see if it is returning what I expect.

  • See my above post, I had edited it instead of creating a new post by mistake.

    I've never used profiler to look at a report, but I could try.

  • Ok, I figured out the problem. The issue was I have more than one dataset created by the same stored procedure using the same parameter name @myParam. The report sees this and makes it a cascading parameter for all datasets after the first dataset. (This is kind of bizarre, but at least I get it. The report sees that the @myparam parameter is being used twice even though this is somewhat hidden by the EXEC statement. ) Once you select the first parameter it works.

    Using the EXEC hid this issue, but it is now coming out as I try to use the proc approach.

    This is not good. The parameters should not "cascade" like this.

    Any ideas on how to get around THIS problem?

    Thanks,

    Aktikt

  • I don't know how you would get around that situation.

  • Jack,

    Thanks for your input. I have gone ahead and created parameters to store my hard coded values. So, I have a lot of parameters, it's not that big a deal.

    Aktikt

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply