SSRS 2000 Parameters

[dhulbert]

I have a paremeter called contractid set up with a fixed value of R00031A this gets used in a dataset using

WHERE contract id in (@Contractid)

I now need to pass 2 values so R00031A, R00031B to the same dataset

so the equivalent of
Where contractid in ('R00031A', 'R00031B')

everytime I try the report runs but returns no data, any idea where I'm going wrong?

Thanks.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[xlbo]

what is the actual value you are passing to the parameter?

Also, if te parameter is fixed, why not hard code into the query?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dhulbert]

I have a template report which is copied for each contract, so the contrct number parameter is changed in each copy and is usedto return data from 10+ data sets.

So we would be using for instance R00068A

But I have some reports which need to pull from 2 or more contracts so the parameter needs to be set to R00031A pull R00031B.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[xlbo]

please can you confirm then the exact value that is bein entered into the parameter (for the multiple value example)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dhulbert]

Single parament is set to R00068A

Multi Paramenter I have tried
R00031A, R00031B
'R00031A', 'R00031B'
"R00031A", "R00031B"

None of which work.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[xlbo]

you're not really going wrong anywhere - it's not easy in SSRS 2000

As far as I can remember there are 2 ways of approaching this (3 if you want a more esoteric approach)

1: Create a custom code function which splits the paramter string value properly and returns it instead of the parameter into the SQL string

2: Use a stored proc instead of native SQL - again you would pass the parameter as a comma separated string into the sp, do some string manipulation and place it into the SQL statement

3: Upgrade to 2005 / 2008 which support multi-value parameters properly

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dhulbert]

Have to say I like Oprion 3 best, not keen on backdating to 2000 but unfortunately the client won;t be persuaded a the moment to do the upgrade.

I'll have a look at the string manipulaton then, thanks a lot.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[xlbo]

No probs - I would strongly suggest the upgrade option - there are SO many fixes in 2005 (and esp 2008). 2000 really was a beta testing product for SSRS. If they have any 2005/2008 SQL Servers about the place then it does of course come bundled with them at no extra cost and there are a wealth of enhancements in both those releases of SQL Server compared to 2000

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question