| Groups | Blog | Home |
sql server reporting services : Why is reporting services designer modifying my sql statement???
This is a duplicate of my posting over on MSDN SSRS forum. Perhaps someone here has seen this. I hope that Microsoft can weigh in on this one: I have a sql statement as part of a report that I'm developing using VS2005 and SSRS. The WHERE clause (part of a much larger SELECT / UNION initially is: WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers)) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) I'm adding "All" support so I'm modifying it to: WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers) OR 'All' IN (@Customers)) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs) OR 'All' IN (@Customers)) On pressing the "!" button to test the query the designer is changing my sql to: WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers)) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) OR (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) AND ('All' IN (@Customers)) OR (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers)) AND ('All' IN (@Customers)) OR (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND ('All' IN (@Customers)) AND ('All' IN (@Customers)) Note the removal of my parentheses, the repetition of logic and placement of the ORs. I've verified that both versions produce the same results but why is it being changed? Perhaps the designer thinks that its version is optimized but I (or others here) have to maintain it and my version (at least in my humble opinion) is more readable as to the intent. Please Microsoft share your thoughts on this "feature"??? Is there any way to prevent it? Thanks! Glenn L
Thanks for the reply Bruce.
I was using the non-graphical one...I was entering pure sql. Glenn L [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> There are two designers. Graphical and non-graphical. I always use the > non-graphical because I typically am both faster and also write more complex > queries that I don't want munged up. The button to switch the type of query > tool to be using is to the right of the ... > > Just hover over the buttons until you find the right one. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "glennl" <glennl@discussions.microsoft.com> wrote in message > news:DCFB5583-4566-4A4F-8750-8CAB319901AC@microsoft.com... > > Hi! > > > > This is a duplicate of my posting over on MSDN SSRS forum. Perhaps > > someone > > here has seen this. > > > > I hope that Microsoft can weigh in on this one: > > > > I have a sql statement as part of a report that I'm developing using > > VS2005 > > and SSRS. The WHERE clause (part of a much larger SELECT / UNION > > initially > > is: > > > > WHERE (s.cnt = 51) > > AND (fi.FI_Status = 0) > > AND (fe.FE_Status = 0) > > AND (fi.FI_Customer IN (@Customers)) > > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) > > > > I'm adding "All" support so I'm modifying it to: > > > > WHERE (s.cnt = 51) > > AND (fi.FI_Status = 0) > > AND (fe.FE_Status = 0) > > AND (fi.FI_Customer IN (@Customers) OR 'All' IN (@Customers)) > > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs) OR 'All' IN (@Customers)) > > > > > > On pressing the "!" button to test the query the designer is changing my > > sql > > to: > > > > WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND > > (fi.FI_Customer IN (@Customers)) AND > > (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) OR > > (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status > > = 0) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) AND ('All' IN > > (@Customers)) OR > > (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status > > = 0) AND (fi.FI_Customer IN (@Customers)) AND ('All' IN (@Customers)) OR > > (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status > > = 0) AND ('All' IN (@Customers)) AND ('All' IN (@Customers)) > > > > > > Note the removal of my parentheses, the repetition of logic and placement > > of > > the ORs. > > > > I've verified that both versions produce the same results but why is it > > being changed? Perhaps the designer thinks that its version is optimized > > but > > I (or others here) have to maintain it and my version (at least in my > > humble > > opinion) is more readable as to the intent. > > > > Please Microsoft share your thoughts on this "feature"??? Is there any > > way > > to prevent it? > > > > Thanks! > > > > Glenn L > > > > > >
You're right...the problem did turn out to be the "non-generic" query
designer...I was entering the sql into the sql pane of what turned out to the the "non-generic"one although it wasn't obvious (to me anyway). I found the toolbar button that took me to the generic one and it didn't modify the sql. Very subtle. Thanks for the help! GlennL [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> I don't think you are in the non-graphical. I live in it designer, it never > ever touches your SQL. The generic query designer (what they call the > non-graphical) is there exactly for this reason. Try toggling back and > forth. > > One other point, are you using the Report Designer or are you using the > tools that come with VS and creating a local report? > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "glennl" <glennl@discussions.microsoft.com> wrote in message > news:F2974D3F-179A-4009-9C03-37A0AE82DB71@microsoft.com... > > Thanks for the reply Bruce. > > > > I was using the non-graphical one...I was entering pure sql. > > > > Glenn L > > > > "Bruce L-C [MVP]" wrote: > > > >> There are two designers. Graphical and non-graphical. I always use the > >> non-graphical because I typically am both faster and also write more > >> complex > >> queries that I don't want munged up. The button to switch the type of > >> query > >> tool to be using is to the right of the ... > >> > >> Just hover over the buttons until you find the right one. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "glennl" <glennl@discussions.microsoft.com> wrote in message > >> news:DCFB5583-4566-4A4F-8750-8CAB319901AC@microsoft.com... > >> > Hi! > >> > > >> > This is a duplicate of my posting over on MSDN SSRS forum. Perhaps > >> > someone > >> > here has seen this. > >> > > >> > I hope that Microsoft can weigh in on this one: > >> > > >> > I have a sql statement as part of a report that I'm developing using > >> > VS2005 > >> > and SSRS. The WHERE clause (part of a much larger SELECT / UNION > >> > initially > >> > is: > >> > > >> > WHERE (s.cnt = 51) > >> > AND (fi.FI_Status = 0) > >> > AND (fe.FE_Status = 0) > >> > AND (fi.FI_Customer IN (@Customers)) > >> > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) > >> > > >> > I'm adding "All" support so I'm modifying it to: > >> > > >> > WHERE (s.cnt = 51) > >> > AND (fi.FI_Status = 0) > >> > AND (fe.FE_Status = 0) > >> > AND (fi.FI_Customer IN (@Customers) OR 'All' IN (@Customers)) > >> > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs) OR 'All' IN > >> > (@Customers)) > >> > > >> > > >> > On pressing the "!" button to test the query the designer is changing > >> > my > >> > sql > >> > to: > >> > > >> > WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) > >> > AND > >> > (fi.FI_Customer IN (@Customers)) AND > >> > (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) OR > >> > (s.cnt = 51) AND (fi.FI_Status = 0) AND > >> > (fe.FE_Status > >> > = 0) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) AND ('All' IN > >> > (@Customers)) OR > >> > (s.cnt = 51) AND (fi.FI_Status = 0) AND > >> > (fe.FE_Status > >> > = 0) AND (fi.FI_Customer IN (@Customers)) AND ('All' IN (@Customers)) > >> > OR > >> > (s.cnt = 51) AND (fi.FI_Status = 0) AND > >> > (fe.FE_Status > >> > = 0) AND ('All' IN (@Customers)) AND ('All' IN (@Customers)) > >> > > >> > > >> > Note the removal of my parentheses, the repetition of logic and > >> > placement > >> > of > >> > the ORs. > >> > > >> > I've verified that both versions produce the same results but why is it > >> > being changed? Perhaps the designer thinks that its version is > >> > optimized > >> > but > >> > I (or others here) have to maintain it and my version (at least in my > >> > humble > >> > opinion) is more readable as to the intent. > >> > > >> > Please Microsoft share your thoughts on this "feature"??? Is there any > >> > way > >> > to prevent it? > >> > > >> > Thanks! > >> > > >> > Glenn L > >> > > >> > > >> > >> > >> > >
There are two designers. Graphical and non-graphical. I always use the
non-graphical because I typically am both faster and also write more complex queries that I don't want munged up. The button to switch the type of query tool to be using is to the right of the ... Just hover over the buttons until you find the right one. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "glennl" <glennl@discussions.microsoft.com> wrote in message news:DCFB5583-4566-4A4F-8750-8CAB319901AC@microsoft.com... > Hi! > > This is a duplicate of my posting over on MSDN SSRS forum. Perhaps > someone > here has seen this. > > I hope that Microsoft can weigh in on this one: > > I have a sql statement as part of a report that I'm developing using > VS2005 > and SSRS. The WHERE clause (part of a much larger SELECT / UNION > initially > is: > > WHERE (s.cnt = 51) > AND (fi.FI_Status = 0) > AND (fe.FE_Status = 0) > AND (fi.FI_Customer IN (@Customers)) > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) > > I'm adding "All" support so I'm modifying it to: > > WHERE (s.cnt = 51) > AND (fi.FI_Status = 0) > AND (fe.FE_Status = 0) > AND (fi.FI_Customer IN (@Customers) OR 'All' IN (@Customers)) > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs) OR 'All' IN (@Customers)) > > > On pressing the "!" button to test the query the designer is changing my > sql > to: > > WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND > (fi.FI_Customer IN (@Customers)) AND > (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) OR > (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status > = 0) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) AND ('All' IN > (@Customers)) OR > (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status > = 0) AND (fi.FI_Customer IN (@Customers)) AND ('All' IN (@Customers)) OR > (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status > = 0) AND ('All' IN (@Customers)) AND ('All' IN (@Customers)) > > > Note the removal of my parentheses, the repetition of logic and placement > of > the ORs. > > I've verified that both versions produce the same results but why is it > being changed? Perhaps the designer thinks that its version is optimized > but > I (or others here) have to maintain it and my version (at least in my > humble > opinion) is more readable as to the intent. > > Please Microsoft share your thoughts on this "feature"??? Is there any > way > to prevent it? > > Thanks! > > Glenn L > >
I don't think you are in the non-graphical. I live in it designer, it never
ever touches your SQL. The generic query designer (what they call the non-graphical) is there exactly for this reason. Try toggling back and forth. One other point, are you using the Report Designer or are you using the tools that come with VS and creating a local report? -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "glennl" <glennl@discussions.microsoft.com> wrote in message news:F2974D3F-179A-4009-9C03-37A0AE82DB71@microsoft.com... > Thanks for the reply Bruce. > > I was using the non-graphical one...I was entering pure sql. > > Glenn L > > "Bruce L-C [MVP]" wrote: > >> There are two designers. Graphical and non-graphical. I always use the >> non-graphical because I typically am both faster and also write more >> complex >> queries that I don't want munged up. The button to switch the type of >> query >> tool to be using is to the right of the ... >> >> Just hover over the buttons until you find the right one. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "glennl" <glennl@discussions.microsoft.com> wrote in message >> news:DCFB5583-4566-4A4F-8750-8CAB319901AC@microsoft.com... >> > Hi! >> > >> > This is a duplicate of my posting over on MSDN SSRS forum. Perhaps >> > someone >> > here has seen this. >> > >> > I hope that Microsoft can weigh in on this one: >> > >> > I have a sql statement as part of a report that I'm developing using >> > VS2005 >> > and SSRS. The WHERE clause (part of a much larger SELECT / UNION >> > initially >> > is: >> > >> > WHERE (s.cnt = 51) >> > AND (fi.FI_Status = 0) >> > AND (fe.FE_Status = 0) >> > AND (fi.FI_Customer IN (@Customers)) >> > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) >> > >> > I'm adding "All" support so I'm modifying it to: >> > >> > WHERE (s.cnt = 51) >> > AND (fi.FI_Status = 0) >> > AND (fe.FE_Status = 0) >> > AND (fi.FI_Customer IN (@Customers) OR 'All' IN (@Customers)) >> > AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs) OR 'All' IN >> > (@Customers)) >> > >> > >> > On pressing the "!" button to test the query the designer is changing >> > my >> > sql >> > to: >> > >> > WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) >> > AND >> > (fi.FI_Customer IN (@Customers)) AND >> > (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) OR >> > (s.cnt = 51) AND (fi.FI_Status = 0) AND >> > (fe.FE_Status >> > = 0) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) AND ('All' IN >> > (@Customers)) OR >> > (s.cnt = 51) AND (fi.FI_Status = 0) AND >> > (fe.FE_Status >> > = 0) AND (fi.FI_Customer IN (@Customers)) AND ('All' IN (@Customers)) >> > OR >> > (s.cnt = 51) AND (fi.FI_Status = 0) AND >> > (fe.FE_Status >> > = 0) AND ('All' IN (@Customers)) AND ('All' IN (@Customers)) >> > >> > >> > Note the removal of my parentheses, the repetition of logic and >> > placement >> > of >> > the ORs. >> > >> > I've verified that both versions produce the same results but why is it >> > being changed? Perhaps the designer thinks that its version is >> > optimized >> > but >> > I (or others here) have to maintain it and my version (at least in my >> > humble >> > opinion) is more readable as to the intent. >> > >> > Please Microsoft share your thoughts on this "feature"??? Is there any >> > way >> > to prevent it? >> > >> > Thanks! >> > >> > Glenn L >> > >> > >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |