Using code to set a parameter.

Question

Using code to set a parameter.

aktikt

Ten Centuries

Points: 1355
More actions
February 23, 2009 at 5:29 pm

#206912

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

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

aktikt Ten Centuries Points: 1355 More actions · Answer 2

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.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

aktikt Ten Centuries Points: 1355 More actions · Answer 4

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.

aktikt Ten Centuries Points: 1355 More actions · Answer 5

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

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

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

aktikt Ten Centuries Points: 1355 More actions · Answer 7

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