December 1, 2011 at 12:45 pm
I'm using an expression for the dataset in a SSRS report so that I can parameterize a variable. It worked for an older report in our repository so i basically just copied the basic code. However, it's erring out on me and I cannot figure out why.
The query itself works just fine with either parameter if I manually enter them into the query (ex. WHERE rss.Provider IN ('XYZ', 'ABC') or WHERE rss.Provider = 'ABC'). But the line for the Iif Parameter.value just isn't working right and errs out with "Incorrect syntax near 'rss'."
One would think a syntax issue is simple to troubleshoot, but can anyone else see what the issue may be?? This is the expression used in the dataset:
="SELECT rss.TripDate, rss.Provider, rss.[Route]" &
", rss.SETime, rs.SETime " &
" FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)" &
" LEFT JOIN [dbo].[RouteScoring] rs (NOLOCK) ON rs.LDate = rss.LDate AND rs.[Route] = rss.[Route] AND rs.Series = 'LIVE'" &
Iif(Parameters!Provider.Value = "KC", "WHERE rss.Provider IN ('XYZ', 'ABC')", "WHERE rss.Provider = (@Provider)") &
" AND rss.LDate = @SLDate" &
" ORDER BY rss.Provider"
Again, it's the line with the Iif statement that is causing the trouble.
Grrrrrr.
December 1, 2011 at 12:55 pm
tacy.highland (12/1/2011)
I'm using an expression for the dataset in a SSRS report so that I can parameterize a variable. It worked for an older report in our repository so i basically just copied the basic code. However, it's erring out on me and I cannot figure out why.The query itself works just fine with either parameter if I manually enter them into the query (ex. WHERE rss.Provider IN ('XYZ', 'ABC') or WHERE rss.Provider = 'ABC'). But the line for the Iif Parameter.value just isn't working right and errs out with "Incorrect syntax near 'rss'."
One would think a syntax issue is simple to troubleshoot, but can anyone else see what the issue may be?? This is the expression used in the dataset:
="SELECT rss.TripDate, rss.Provider, rss.[Route]" &", rss.SETime, rs.SETime " &
" FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)" &
" LEFT JOIN [dbo].[RouteScoring] rs (NOLOCK) ON rs.LDate = rss.LDate AND rs.[Route] = rss.[Route] AND rs.Series = 'LIVE'" &
Iif(Parameters!Provider.Value = "KC", "WHERE rss.Provider IN ('XYZ', 'ABC')", "WHERE rss.Provider = (@Provider)") &
" AND rss.LDate = @SLDate" &
" ORDER BY rss.Provider"
Again, it's the line with the Iif statement that is causing the trouble.
Grrrrrr.
You need a SPACE after the 'LIVE' and before the WHERE
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 1, 2011 at 1:19 pm
thankyouthankyouthankyou.
I so rarely work with expressions in SSRS that I never would've caught that.
And once again, thank you.
😀
December 1, 2011 at 1:34 pm
...so apparently one fix leads to another issue...
It works great now ...if the parameter is KC. Otherwise, the report runs but I get no data if it's something other than KC. (Which is not correct since there is data).
Did I miss something else?
It appears that if i change the code to:
Iif(Parameters!Provider.Value = "KC", " WHERE rss.Provider = @Provider ", " WHERE rss.Provider IN ('XYZ', 'ABC') " ) &
(which is opposite of what i want) it still shows both providers, rather than just one provider. ?? It's like it's not seeing the actual provider that's being selected, or it's ignoring the other part of the Iif statement if a provider is selected other than KC.
December 1, 2011 at 1:42 pm
hmm, I don't see anything else.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 1, 2011 at 1:48 pm
How did you fix the original problem (what is the updated expression)? What type of variable is @Provider?
Dave
December 1, 2011 at 2:14 pm
I added the spaces like Jason mentioned (after the "LIVE" and before the "WHERE"):
="SELECT rss.TripDate, rss.Provider, rss.[Route]" &
", rss.SETime, rs.SETime " &
" FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)" &
" LEFT JOIN [dbo].[RouteScoring] rs (NOLOCK) ON rs.LDate = rss.LDate AND rs.[Route] = rss.[Route] AND rs.Series = 'LIVE' " &
Iif(Parameters!Provider.Value = "KC", " WHERE rss.Provider IN ('XYZ', 'ABC')", " WHERE rss.Provider = (@Provider)") &
" AND rss.LDate = @SLDate" &
" ORDER BY rss.Provider"
The Provider parameter data type is "text" (it's a varchar).
December 1, 2011 at 2:55 pm
Just to check that he two different versions of the query are
SELECT rss.TripDate
,rss.Provider
,rss.[Route]
,rss.SETime
,rs.SETime
FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)
LEFT JOIN
[dbo].[RouteScoring] rs (NOLOCK)
ON rs.LDate = rss.LDate
AND rs.[Route] = rss.[Route]
AND rs.Series = 'LIVE'
WHERE rss.Provider IN ('XYZ', 'ABC')
AND rss.LDate = @SLDate
ORDER BY rss.Provider
SELECT rss.TripDate
,rss.Provider
,rss.[Route]
,rss.SETime
,rs.SETime
FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)
LEFT JOIN
[dbo].[RouteScoring] rs (NOLOCK)
ON rs.LDate = rss.LDate
AND rs.[Route] = rss.[Route]
AND rs.Series = 'LIVE'
WHERE rss.Provider = (@Provider)
AND rss.LDate = @SLDate
ORDER BY rss.Provider
What happens if you run both indepenently? They both look correct from a superficial inspection.
December 1, 2011 at 3:01 pm
I've run them both in SSMS and they both run fine, both producing the expected results.
Something with the Iif parameter isn't right. It's like the "WHERE provider = " part is being ignored entirely. The only way i can get results is if I use the KC parameter which is set to use the "WHERE provider in ('ABC','XYZ')".
Is there a better way to do this? I need to set it up like this so one set of report recipients can receive the full report, while other recipients only receive what's specific to them.
December 1, 2011 at 3:07 pm
tacy.highland (12/1/2011)
Is there a better way to do this? I need to set it up like this so one set of report recipients can receive the full report, while other recipients only receive what's specific to them.
I've been following the conversation. There's a few alternatives, but none that really make that much of a difference.
In troubleshooting syntax problems like this, I've found it handy to leve SSRS tell ME what it thinks it's building. Take the full equation statement and shove it in as the formula to a text box on a copy of the report and crack it open. See what it thinks it's building based on the parameter switching. Sometimes you end up with a headslap, sometimes you have to drag that result over to SSMS and see what it's up to.
EDIT: And no, I can't see what the problem is either, that looks right to me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2011 at 3:25 pm
Everything looks good to me, as Jason suggests.
Is @Provider a single value or is it allowed to contain multiple values?
Dave
December 1, 2011 at 4:35 pm
It's set as a single value. I've never used the "Allow multiple values" option in the Parameter Properties before. Don't know how it even works. (?)
I'm willing to make changes to the original SQL code, if anyone has anything better to suggest. I just need to get something together...
December 2, 2011 at 1:55 am
I thought I had a Eureka moment. My thinking was what would happen if you replaced the iif clause with
Iif(Parameters!Provider.Value = "KC", " WHERE rss.Provider IN ('XYZ', 'ABC')", " WHERE rss.Provider = '" & @Provider & "'")
However folllowing this thinking I would have thought there would be a similar problem with the other parameter @SLDate and no rows would ever be returned. So, I doubt if this is the solution.
What happens if you move the iif so it controls the entire SQL statement and not just the WHERE clause? Something like
iif (Parameters!Provider.Value = "KC",
"SELECT rss.TripDate
,rss.Provider
,rss.[Route]
,rss.SETime
,rs.SETime
FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)
LEFT JOIN
[dbo].[RouteScoring] rs (NOLOCK)
ON rs.LDate = rss.LDate
AND rs.[Route] = rss.[Route]
AND rs.Series = 'LIVE'
WHERE rss.Provider IN ('XYZ', 'ABC')
AND rss.LDate = @SLDate
ORDER BY rss.Provider"
,
"SELECT rss.TripDate
,rss.Provider
,rss.[Route]
,rss.SETime
,rs.SETime
FROM [dbo].[RouteScoring_SnapShot] rss (NOLOCK)
LEFT JOIN
[dbo].[RouteScoring] rs (NOLOCK)
ON rs.LDate = rss.LDate
AND rs.[Route] = rss.[Route]
AND rs.Series = 'LIVE'
WHERE rss.Provider = (@Provider)
AND rss.LDate = @SLDate
ORDER BY rss.Provider"
Dave
December 2, 2011 at 10:11 am
It was worth a shot. But...no luck. Same issue. I'm only able to see data for all providers. I've not once, with this Iif statement, been able to see data for an individual provider.
I don't get it.
If I could run the query with the expression parameter in SSMS, it would be helpful to troubleshoot. But don't know any way to troubleshoot expressions (especially since I'm so unfamiliar with them).
Any other ideas?
December 2, 2011 at 10:37 am
Ok, figured it out. (After much hair-pulling.)
Knew something was just not right with the provider parameter. I was looking at the @Provider parameter itself, all the properties, verifying everything i could think of. Well. You know how inside the dataset itself, there's a section for Parameters? (The section I "saw" every time --of the 5,000 times-- I opened up the dataset, ...but didn't really see?) The ParameterValue inside that particular section didn't have the brackets around it.
(Really??)
So. I will apologize for wasting time (and slink away with my tail between my legs), but I do appreciated the effort put forth.
Thank you
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply