Dynamic Query in a Report (Newbie) 1

[tb]

Hi guys,

This has just landed in my lap, so here goes ...

We have 8 different databases - so to define the data between them we have a region field.

The first parameter is my Region. No I want my other parameters to take that into account. I found a topic in the Help file and tried to apply it to my report, but without any luck.

Here is the code that I have in my dataset.
="SELECT RepCode, Region, RepName
FROM MSTRRepCodes
WHERE Region = " & (Parameters!Region.Value) &
"ORDER BY Region,RepName"

BUT it does not seem to recognise this code.

Any help would be much appreciated.
Thnx

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[xlbo]

what application are you writing this SQL query in?

 

[xlbo]

D'OH - wrong forum - of course, it will be Reporting Services

try this:

SELECT RepCode, Region, RepName
FROM MSTRRepCodes
WHERE Region = @Region
ORDER BY Region,RepName

 

[tb]

Visual Studio .Net 2003
Data Tab (dataset)

Hope this makes sense.

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[tb]

Hi and thnx for your response.

I get an error with the supplied code above ...
"An error has occured during report processing. Query execution failed for data set 'Reps'. Must declare the variable '@Region'.

Any ideas?

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[tb]

WAIT ... IT WORKED!!!
I copied it to the Generic Query Designer and Bobs my uncle.

THANK YOU SO MUCH !!!

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!