I am trying to implement the setting of a parameter to the default value of
'-All-' using the IIF function in the WHERE clause.
I received a processing error then backed off and checked a basic 'SELECT
select list FROM tablename WHERE columnname = value' query. It ran in
Preview just fine.
Then I wrapped the query in ' = " query w/o changes" ' When this runs in
Preview I get the following message in a pop-up box .. "Processing Error ..
An error has occurred during report processing. Query execution failed for
data set 'TestDS'. Line 1: Incorrect syntax error'.' "
The only change is the ' = " ..." '
The Datatset Command type is 'Text'
Any idea what is happening here'
--
Thanks in advance
GHHi Vakar,
Based on my knowledge, the Generic Query Designer Execute button will grey
out when the query begins with an '='
This is expected behavior since dynamic query cannot be executed in Generic
Query Designer, which also means you are not able to Perview the data now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Correct the i cannot execute the query from the Query Designer in the Data tab.
I get the error when I go to the the Preview tab.
Thanks
"Michael Cheng [MSFT]" wrote:
> Hi Vakar,
> Based on my knowledge, the Generic Query Designer Execute button will grey
> out when the query begins with an '='
> This is expected behavior since dynamic query cannot be executed in Generic
> Query Designer, which also means you are not able to Perview the data now.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||If you get an error when you go to Preview it is because something is wrong
with your string. If you have to (and I avoid it) use an expression then do
these steps.
1. Create a report with report parameters and a text box (nothing else)
2. Set the textbox to the expression that will end up being the source for
the dataset. Preview and look at what is in the textbox and make sure that
it is valid SQL (copy and paste into query analyzer and validate you have it
correctly).
3. create a query that will have the same fields returned to get you field
list populated
4. change to the expression that you now know (from #2) is correct.
These are the steps I always take.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> Correct the i cannot execute the query from the Query Designer in the Data
tab.
> I get the error when I go to the the Preview tab.
> Thanks
> "Michael Cheng [MSFT]" wrote:
> > Hi Vakar,
> >
> > Based on my knowledge, the Generic Query Designer Execute button will
grey
> > out when the query begins with an '='
> >
> > This is expected behavior since dynamic query cannot be executed in
Generic
> > Query Designer, which also means you are not able to Perview the data
now.
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Great suggustion ... I was going to ask for debugging suggestions ... I
The following testing was just completed.
I created a dataset (query 2) that is a subset of my problem query (query 1)
which is populating another table. The IIF function is the SAME for both
queries (I cut and pasted from Query 2 to Query 1). Query 2 works but Query
1 gets the error. Query 1 runs when commenting out the IIF function and
removing the =" ".
Here are the queries:
Query 1
="SELECT DISTINCT
a.EID
,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
,a.PassageDistrictID
,Region = CASE
WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
WHEN e.PassageRegionFullName = '' THEN 'Unknown'
ELSE e.PassageRegionFullName
END
,a.EventName
,RegionalMgrName = CASE
WHEN b.LastName IS NULL THEN 'N/A'
WHEN b.LastName = '' THEN 'N/A'
ELSE b.LastName
END
,OperationsMgrName = CASE
WHEN d.LastName IS NULL THEN 'N/A'
WHEN d.LastName = '' THEN 'N/A'
ELSE d.LastName
END
,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
FROM dbo.Event a
LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=e.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
Query 2
="SELECT a.EID, a.EventName
FROM dbo.Event a
JOIN GeographicDim_PE b
ON a.PassageDistrictID = b.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
"Bruce L-C [MVP]" wrote:
> If you get an error when you go to Preview it is because something is wrong
> with your string. If you have to (and I avoid it) use an expression then do
> these steps.
> 1. Create a report with report parameters and a text box (nothing else)
> 2. Set the textbox to the expression that will end up being the source for
> the dataset. Preview and look at what is in the textbox and make sure that
> it is valid SQL (copy and paste into query analyzer and validate you have it
> correctly).
> 3. create a query that will have the same fields returned to get you field
> list populated
> 4. change to the expression that you now know (from #2) is correct.
> These are the steps I always take.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > Correct the i cannot execute the query from the Query Designer in the Data
> tab.
> >
> > I get the error when I go to the the Preview tab.
> >
> > Thanks
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> > > Hi Vakar,
> > >
> > > Based on my knowledge, the Generic Query Designer Execute button will
> grey
> > > out when the query begins with an '='
> > >
> > > This is expected behavior since dynamic query cannot be executed in
> Generic
> > > Query Designer, which also means you are not able to Perview the data
> now.
> > >
> > > Thank you for your patience and corporation. If you have any questions
> or
> > > concerns, don't hesitate to let me know. We are always here to be of
> > > assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Michael Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Get Secure! - http://www.microsoft.com/security
> > >
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
>|||I have heard that sometimes there is a problem with put in a hard carriage
return in the expression box. Make sure the iif part is on the same line.
I.e. let any line wrapping in the expression box occur on its own, don't do
a carriage return to put the iif on another line. Just a guess.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> Great suggustion ... I was going to ask for debugging suggestions ... I
> The following testing was just completed.
> I created a dataset (query 2) that is a subset of my problem query (query
1)
> which is populating another table. The IIF function is the SAME for both
> queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
Query
> 1 gets the error. Query 1 runs when commenting out the IIF function and
> removing the =" ".
> Here are the queries:
> Query 1
> ="SELECT DISTINCT
> a.EID
> ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> ,a.PassageDistrictID
> ,Region => CASE
> WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> ELSE e.PassageRegionFullName
> END
> ,a.EventName
> ,RegionalMgrName => CASE
> WHEN b.LastName IS NULL THEN 'N/A'
> WHEN b.LastName = '' THEN 'N/A'
> ELSE b.LastName
> END
> ,OperationsMgrName => CASE
> WHEN d.LastName IS NULL THEN 'N/A'
> WHEN d.LastName = '' THEN 'N/A'
> ELSE d.LastName
> END
> ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> FROM dbo.Event a
> LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> e.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> Query 2
> ="SELECT a.EID, a.EventName
> FROM dbo.Event a
> JOIN GeographicDim_PE b
> ON a.PassageDistrictID = b.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> "Bruce L-C [MVP]" wrote:
> > If you get an error when you go to Preview it is because something is
wrong
> > with your string. If you have to (and I avoid it) use an expression then
do
> > these steps.
> > 1. Create a report with report parameters and a text box (nothing else)
> > 2. Set the textbox to the expression that will end up being the source
for
> > the dataset. Preview and look at what is in the textbox and make sure
that
> > it is valid SQL (copy and paste into query analyzer and validate you
have it
> > correctly).
> > 3. create a query that will have the same fields returned to get you
field
> > list populated
> > 4. change to the expression that you now know (from #2) is correct.
> >
> > These are the steps I always take.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > Correct the i cannot execute the query from the Query Designer in the
Data
> > tab.
> > >
> > > I get the error when I go to the the Preview tab.
> > >
> > > Thanks
> > >
> > > "Michael Cheng [MSFT]" wrote:
> > >
> > > > Hi Vakar,
> > > >
> > > > Based on my knowledge, the Generic Query Designer Execute button
will
> > grey
> > > > out when the query begins with an '='
> > > >
> > > > This is expected behavior since dynamic query cannot be executed in
> > Generic
> > > > Query Designer, which also means you are not able to Perview the
data
> > now.
> > > >
> > > > Thank you for your patience and corporation. If you have any
questions
> > or
> > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > assistance!
> > > >
> > > >
> > > > Sincerely yours,
> > > >
> > > > Michael Cheng
> > > >
> > > > Online Partner Support Specialist
> > > > Partner Support Group
> > > > Microsoft Global Technical Support Center
> > > > ---
> > > > Get Secure! - http://www.microsoft.com/security
> > > >
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > Please reply to newsgroups only, many thanks!
> > > >
> > > >
> >
> >
> >|||I just tried that ...
I removed all cr/lf making on long line of code ... nastly ... but it
worked ...
My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
Thanks for you help!!
"Bruce L-C [MVP]" wrote:
> I have heard that sometimes there is a problem with put in a hard carriage
> return in the expression box. Make sure the iif part is on the same line.
> I.e. let any line wrapping in the expression box occur on its own, don't do
> a carriage return to put the iif on another line. Just a guess.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > Great suggustion ... I was going to ask for debugging suggestions ... I
> >
> > The following testing was just completed.
> >
> > I created a dataset (query 2) that is a subset of my problem query (query
> 1)
> > which is populating another table. The IIF function is the SAME for both
> > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> Query
> > 1 gets the error. Query 1 runs when commenting out the IIF function and
> > removing the =" ".
> >
> > Here are the queries:
> > Query 1
> > ="SELECT DISTINCT
> > a.EID
> > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > ,a.PassageDistrictID
> > ,Region => > CASE
> > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > ELSE e.PassageRegionFullName
> > END
> > ,a.EventName
> > ,RegionalMgrName => > CASE
> > WHEN b.LastName IS NULL THEN 'N/A'
> > WHEN b.LastName = '' THEN 'N/A'
> > ELSE b.LastName
> > END
> > ,OperationsMgrName => > CASE
> > WHEN d.LastName IS NULL THEN 'N/A'
> > WHEN d.LastName = '' THEN 'N/A'
> > ELSE d.LastName
> > END
> > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> > FROM dbo.Event a
> > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > e.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > Query 2
> > ="SELECT a.EID, a.EventName
> > FROM dbo.Event a
> > JOIN GeographicDim_PE b
> > ON a.PassageDistrictID = b.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you get an error when you go to Preview it is because something is
> wrong
> > > with your string. If you have to (and I avoid it) use an expression then
> do
> > > these steps.
> > > 1. Create a report with report parameters and a text box (nothing else)
> > > 2. Set the textbox to the expression that will end up being the source
> for
> > > the dataset. Preview and look at what is in the textbox and make sure
> that
> > > it is valid SQL (copy and paste into query analyzer and validate you
> have it
> > > correctly).
> > > 3. create a query that will have the same fields returned to get you
> field
> > > list populated
> > > 4. change to the expression that you now know (from #2) is correct.
> > >
> > > These are the steps I always take.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "GH" <vakar@.community.nospam> wrote in message
> > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > Correct the i cannot execute the query from the Query Designer in the
> Data
> > > tab.
> > > >
> > > > I get the error when I go to the the Preview tab.
> > > >
> > > > Thanks
> > > >
> > > > "Michael Cheng [MSFT]" wrote:
> > > >
> > > > > Hi Vakar,
> > > > >
> > > > > Based on my knowledge, the Generic Query Designer Execute button
> will
> > > grey
> > > > > out when the query begins with an '='
> > > > >
> > > > > This is expected behavior since dynamic query cannot be executed in
> > > Generic
> > > > > Query Designer, which also means you are not able to Perview the
> data
> > > now.
> > > > >
> > > > > Thank you for your patience and corporation. If you have any
> questions
> > > or
> > > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > > assistance!
> > > > >
> > > > >
> > > > > Sincerely yours,
> > > > >
> > > > > Michael Cheng
> > > > >
> > > > > Online Partner Support Specialist
> > > > > Partner Support Group
> > > > > Microsoft Global Technical Support Center
> > > > > ---
> > > > > Get Secure! - http://www.microsoft.com/security
> > > > >
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > > Please reply to newsgroups only, many thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great. Wasn't sure if it would solve the problem but I couldn't see anything
wrong (having the second query with the iif work but not the first was the
clue for me).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:FF5CFDA6-8144-482A-BA91-DB35B53ECE25@.microsoft.com...
> I just tried that ...
> I removed all cr/lf making on long line of code ... nastly ... but it
> worked ...
> My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
> Thanks for you help!!
> "Bruce L-C [MVP]" wrote:
> > I have heard that sometimes there is a problem with put in a hard
carriage
> > return in the expression box. Make sure the iif part is on the same
line.
> > I.e. let any line wrapping in the expression box occur on its own, don't
do
> > a carriage return to put the iif on another line. Just a guess.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > > Great suggustion ... I was going to ask for debugging suggestions ...
I
> > >
> > > The following testing was just completed.
> > >
> > > I created a dataset (query 2) that is a subset of my problem query
(query
> > 1)
> > > which is populating another table. The IIF function is the SAME for
both
> > > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> > Query
> > > 1 gets the error. Query 1 runs when commenting out the IIF function
and
> > > removing the =" ".
> > >
> > > Here are the queries:
> > > Query 1
> > > ="SELECT DISTINCT
> > > a.EID
> > > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > > ,a.PassageDistrictID
> > > ,Region => > > CASE
> > > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > > ELSE e.PassageRegionFullName
> > > END
> > > ,a.EventName
> > > ,RegionalMgrName => > > CASE
> > > WHEN b.LastName IS NULL THEN 'N/A'
> > > WHEN b.LastName = '' THEN 'N/A'
> > > ELSE b.LastName
> > > END
> > > ,OperationsMgrName => > > CASE
> > > WHEN d.LastName IS NULL THEN 'N/A'
> > > WHEN d.LastName = '' THEN 'N/A'
> > > ELSE d.LastName
> > > END
> > > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS
AssignmentLtrSentDt
> > > FROM dbo.Event a
> > > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > > e.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > Query 2
> > > ="SELECT a.EID, a.EventName
> > > FROM dbo.Event a
> > > JOIN GeographicDim_PE b
> > > ON a.PassageDistrictID = b.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > If you get an error when you go to Preview it is because something
is
> > wrong
> > > > with your string. If you have to (and I avoid it) use an expression
then
> > do
> > > > these steps.
> > > > 1. Create a report with report parameters and a text box (nothing
else)
> > > > 2. Set the textbox to the expression that will end up being the
source
> > for
> > > > the dataset. Preview and look at what is in the textbox and make
sure
> > that
> > > > it is valid SQL (copy and paste into query analyzer and validate you
> > have it
> > > > correctly).
> > > > 3. create a query that will have the same fields returned to get you
> > field
> > > > list populated
> > > > 4. change to the expression that you now know (from #2) is correct.
> > > >
> > > > These are the steps I always take.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "GH" <vakar@.community.nospam> wrote in message
> > > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > > Correct the i cannot execute the query from the Query Designer in
the
> > Data
> > > > tab.
> > > > >
> > > > > I get the error when I go to the the Preview tab.
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Michael Cheng [MSFT]" wrote:
> > > > >
> > > > > > Hi Vakar,
> > > > > >
> > > > > > Based on my knowledge, the Generic Query Designer Execute button
> > will
> > > > grey
> > > > > > out when the query begins with an '='
> > > > > >
> > > > > > This is expected behavior since dynamic query cannot be executed
in
> > > > Generic
> > > > > > Query Designer, which also means you are not able to Perview the
> > data
> > > > now.
> > > > > >
> > > > > > Thank you for your patience and corporation. If you have any
> > questions
> > > > or
> > > > > > concerns, don't hesitate to let me know. We are always here to
be of
> > > > > > assistance!
> > > > > >
> > > > > >
> > > > > > Sincerely yours,
> > > > > >
> > > > > > Michael Cheng
> > > > > >
> > > > > > Online Partner Support Specialist
> > > > > > Partner Support Group
> > > > > > Microsoft Global Technical Support Center
> > > > > > ---
> > > > > > Get Secure! - http://www.microsoft.com/security
> > > > > >
> > > > > > This posting is provided "as is" with no warranties and confers
no
> > > > rights.
> > > > > > Please reply to newsgroups only, many thanks!
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >
