Generic Query Designer quotations

I am trying to use the generic query designer so that I can add dynamic
parameters. My problem is that my query contains double quotes already, ie:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS", "524ALPRXD1_BRITE",
"524ALPRXD1_PH", "524ALPRXD1_COND", "524TLPRXD1_TEMP",
"524CLPRXD2_CONS", "524ALPRXD2_BRITE",
"524ALPRXD2_PH", "524ALPRXD2_COND", "524TLPRXD2_TEMP", "524CLPRXD3_CONS",
"524ALPRXD3_BRITE", "524ALPRXD3_PH",
"524ALPRXD3_COND", "524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP",
"615ALKRAFT_BRITE", "565ALPOTWR_CSF",
"565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND", "565ALPRTWR_BRITE",
"524ALTMP11_CSF", "524CLTMP11_CONS",
"524ALTMP11_BRITE", "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP",
"631ALTMP_CSF", "631CLTMP_CONS", "631ALTMP_BRITE"
FROM GP4_LAB
How would I put this into the generic query designer, because when I try to
do it, I get an error "End of statement expected".Try using single quotes unless double quotes are required by your data
provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Double quotes are required. It is an Oracle database, I am using the OLE DB
provider.
"Donovan R. Smith [MS]" wrote:
> Try using single quotes unless double quotes are required by your data
> provider.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||It should work fine. Unless you are building an expression, quotes should
not matter. Have you tried it with the graphical one?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> Double quotes are required. It is an Oracle database, I am using the OLE
> DB
> provider.
> "Donovan R. Smith [MS]" wrote:
>> Try using single quotes unless double quotes are required by your data
>> provider.
>> --
>> Thanks.
>> Donovan R. Smith
>> Software Test Lead
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.|||I need to build the query in the generic designer, because I need to create
dynamic parameters. I don't believe I can do that in the graphical designer?
The actual query works fine in the graphical designer.
"Brian Welcker [MSFT]" wrote:
> It should work fine. Unless you are building an expression, quotes should
> not matter. Have you tried it with the graphical one?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > Double quotes are required. It is an Oracle database, I am using the OLE
> > DB
> > provider.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> >> Try using single quotes unless double quotes are required by your data
> >> provider.
> >>
> >> --
> >> Thanks.
> >>
> >> Donovan R. Smith
> >> Software Test Lead
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
>
>|||If you are building the SQL dynamically then I suggest you back up and make
sure you are creating the appropriate syntax. Set the source of a textbox to
the expression you are currently doing in the generic designer. Then you can
copy and paste that into whatever Oracle calls their tool to execute ad hoc
queries. Then you can quickly find out whether the syntax is correct.
Consider that the generic designer essentially allows passthrough queries.
You need the exact same syntax that you would have if you were going
directly against Oracle.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
>I need to build the query in the generic designer, because I need to create
> dynamic parameters. I don't believe I can do that in the graphical
> designer?
> The actual query works fine in the graphical designer.
> "Brian Welcker [MSFT]" wrote:
>> It should work fine. Unless you are building an expression, quotes should
>> not matter. Have you tried it with the graphical one?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
>> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
>> > Double quotes are required. It is an Oracle database, I am using the
>> > OLE
>> > DB
>> > provider.
>> >
>> > "Donovan R. Smith [MS]" wrote:
>> >
>> >> Try using single quotes unless double quotes are required by your data
>> >> provider.
>> >>
>> >> --
>> >> Thanks.
>> >>
>> >> Donovan R. Smith
>> >> Software Test Lead
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>>|||Yes, I have done that and confirmed that I am using the correct syntax. I
think the problem is that Oracle requires any field that starts with a number
to have a quote around it to designate it as a string. These quotes are what
is causing me problems in the Generic query designer, because I want to add
dynamic parameters to the query.
"Bruce Loehle-Conger" wrote:
> If you are building the SQL dynamically then I suggest you back up and make
> sure you are creating the appropriate syntax. Set the source of a textbox to
> the expression you are currently doing in the generic designer. Then you can
> copy and paste that into whatever Oracle calls their tool to execute ad hoc
> queries. Then you can quickly find out whether the syntax is correct.
> Consider that the generic designer essentially allows passthrough queries.
> You need the exact same syntax that you would have if you were going
> directly against Oracle.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> >I need to build the query in the generic designer, because I need to create
> > dynamic parameters. I don't believe I can do that in the graphical
> > designer?
> > The actual query works fine in the graphical designer.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> It should work fine. Unless you are building an expression, quotes should
> >> not matter. Have you tried it with the graphical one?
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> >> > Double quotes are required. It is an Oracle database, I am using the
> >> > OLE
> >> > DB
> >> > provider.
> >> >
> >> > "Donovan R. Smith [MS]" wrote:
> >> >
> >> >> Try using single quotes unless double quotes are required by your data
> >> >> provider.
> >> >>
> >> >> --
> >> >> Thanks.
> >> >>
> >> >> Donovan R. Smith
> >> >> Software Test Lead
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >>
> >>
> >>
>
>|||dachrist wrote:
> I think the problem is that Oracle requires any field that starts with a number
> to have a quote around it to designate it as a string.
I'm sure there's an Oracle equivalent of SQL Profiler. Would you run
that tool or NetMon and see whether the generic query designer is
passing the appropriate string to Oracle?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Could you post the statement that you have in the Generic query designer?
That might help generate some ideas on what is going on.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> Yes, I have done that and confirmed that I am using the correct syntax. I
> think the problem is that Oracle requires any field that starts with a
number
> to have a quote around it to designate it as a string. These quotes are
what
> is causing me problems in the Generic query designer, because I want to
add
> dynamic parameters to the query.
> "Bruce Loehle-Conger" wrote:
> > If you are building the SQL dynamically then I suggest you back up and
make
> > sure you are creating the appropriate syntax. Set the source of a
textbox to
> > the expression you are currently doing in the generic designer. Then you
can
> > copy and paste that into whatever Oracle calls their tool to execute ad
hoc
> > queries. Then you can quickly find out whether the syntax is correct.
> > Consider that the generic designer essentially allows passthrough
queries.
> > You need the exact same syntax that you would have if you were going
> > directly against Oracle.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > >I need to build the query in the generic designer, because I need to
create
> > > dynamic parameters. I don't believe I can do that in the graphical
> > > designer?
> > > The actual query works fine in the graphical designer.
> > >
> > > "Brian Welcker [MSFT]" wrote:
> > >
> > >> It should work fine. Unless you are building an expression, quotes
should
> > >> not matter. Have you tried it with the graphical one?
> > >>
> > >> --
> > >> Brian Welcker
> > >> Group Program Manager
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > >> > Double quotes are required. It is an Oracle database, I am using
the
> > >> > OLE
> > >> > DB
> > >> > provider.
> > >> >
> > >> > "Donovan R. Smith [MS]" wrote:
> > >> >
> > >> >> Try using single quotes unless double quotes are required by your
data
> > >> >> provider.
> > >> >>
> > >> >> --
> > >> >> Thanks.
> > >> >>
> > >> >> Donovan R. Smith
> > >> >> Software Test Lead
> > >> >>
> > >> >> This posting is provided "AS IS" with no warranties, and confers
no
> > >> >> rights.
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||This is the statement that works:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
"524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
"524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
"524ALPRXD2_COND", "524TLPRXD2_TEMP",
"524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
"524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
"565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
"565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
"524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
"631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
What I want to do is put in a couple of dynamic parameters. But I add the
=" at the beginning of the statement, and end the statement with ", the query
will not run, with the error "The expression for the query â'PRPQISâ' contains
an error: [BC30205] End of statement expected." If I change the double
quotes within the statement to single quotes, I get an Oracle error,
ORA-00936: Missing Expression. If I remove the quotes all together, I don't
get anything returned from my query.
"Bruce Loehle-Conger" wrote:
> Could you post the statement that you have in the Generic query designer?
> That might help generate some ideas on what is going on.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > Yes, I have done that and confirmed that I am using the correct syntax. I
> > think the problem is that Oracle requires any field that starts with a
> number
> > to have a quote around it to designate it as a string. These quotes are
> what
> > is causing me problems in the Generic query designer, because I want to
> add
> > dynamic parameters to the query.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > If you are building the SQL dynamically then I suggest you back up and
> make
> > > sure you are creating the appropriate syntax. Set the source of a
> textbox to
> > > the expression you are currently doing in the generic designer. Then you
> can
> > > copy and paste that into whatever Oracle calls their tool to execute ad
> hoc
> > > queries. Then you can quickly find out whether the syntax is correct.
> > > Consider that the generic designer essentially allows passthrough
> queries.
> > > You need the exact same syntax that you would have if you were going
> > > directly against Oracle.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > >I need to build the query in the generic designer, because I need to
> create
> > > > dynamic parameters. I don't believe I can do that in the graphical
> > > > designer?
> > > > The actual query works fine in the graphical designer.
> > > >
> > > > "Brian Welcker [MSFT]" wrote:
> > > >
> > > >> It should work fine. Unless you are building an expression, quotes
> should
> > > >> not matter. Have you tried it with the graphical one?
> > > >>
> > > >> --
> > > >> Brian Welcker
> > > >> Group Program Manager
> > > >> SQL Server Reporting Services
> > > >>
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > >> > Double quotes are required. It is an Oracle database, I am using
> the
> > > >> > OLE
> > > >> > DB
> > > >> > provider.
> > > >> >
> > > >> > "Donovan R. Smith [MS]" wrote:
> > > >> >
> > > >> >> Try using single quotes unless double quotes are required by your
> data
> > > >> >> provider.
> > > >> >>
> > > >> >> --
> > > >> >> Thanks.
> > > >> >>
> > > >> >> Donovan R. Smith
> > > >> >> Software Test Lead
> > > >> >>
> > > >> >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > >> >> rights.
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>|||It looks to me that you are coming up against the typical problem of
embedding double quotes in a string. As a coincidence today I have just been
dealing with creating a dynamic sql statement in a stored procedure which
had the same issue but with single quotes. As I mentioned before, the best
way to deal with making sure that your expression is correct is to back off
from trying to create the dataset and first make sure the correct string is
being created. You should just assign the expression to a text box (I create
a report with nothing on it but a textbox).
For instance in your case I started off with this expression for a textbox.
="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
i.e I did what you said you did, added an =" and finished off with a ".
Trying to go into preview it fails with an error. Which is what I expected
would happen. You can not put in double quotes in a string without following
the special string rule, do it twice.
I assigned this string to the textbox:
="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
Notice that everywhere you want your resulting string to have double quotes
I put double quotes twice. In preview the textbox has this:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
Just what you want. When testing this your textbox needs to end up with
exactly the string expected by Oracle. Once you have this then you can
assign the expression to the dataset and away you go.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> This is the statement that works:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
"615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
>
> What I want to do is put in a couple of dynamic parameters. But I add the
> =" at the beginning of the statement, and end the statement with ", the
query
> will not run, with the error "The expression for the query 'PRPQIS'
contains
> an error: [BC30205] End of statement expected." If I change the double
> quotes within the statement to single quotes, I get an Oracle error,
> ORA-00936: Missing Expression. If I remove the quotes all together, I
don't
> get anything returned from my query.
>
> "Bruce Loehle-Conger" wrote:
> > Could you post the statement that you have in the Generic query
designer?
> > That might help generate some ideas on what is going on.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > Yes, I have done that and confirmed that I am using the correct
syntax. I
> > > think the problem is that Oracle requires any field that starts with a
> > number
> > > to have a quote around it to designate it as a string. These quotes
are
> > what
> > > is causing me problems in the Generic query designer, because I want
to
> > add
> > > dynamic parameters to the query.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > If you are building the SQL dynamically then I suggest you back up
and
> > make
> > > > sure you are creating the appropriate syntax. Set the source of a
> > textbox to
> > > > the expression you are currently doing in the generic designer. Then
you
> > can
> > > > copy and paste that into whatever Oracle calls their tool to execute
ad
> > hoc
> > > > queries. Then you can quickly find out whether the syntax is
correct.
> > > > Consider that the generic designer essentially allows passthrough
> > queries.
> > > > You need the exact same syntax that you would have if you were going
> > > > directly against Oracle.
> > > >
> > > > Bruce L-C
> > > >
> > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > >I need to build the query in the generic designer, because I need
to
> > create
> > > > > dynamic parameters. I don't believe I can do that in the
graphical
> > > > > designer?
> > > > > The actual query works fine in the graphical designer.
> > > > >
> > > > > "Brian Welcker [MSFT]" wrote:
> > > > >
> > > > >> It should work fine. Unless you are building an expression,
quotes
> > should
> > > > >> not matter. Have you tried it with the graphical one?
> > > > >>
> > > > >> --
> > > > >> Brian Welcker
> > > > >> Group Program Manager
> > > > >> SQL Server Reporting Services
> > > > >>
> > > > >> This posting is provided "AS IS" with no warranties, and confers
no
> > > > >> rights.
> > > > >>
> > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > >> > Double quotes are required. It is an Oracle database, I am
using
> > the
> > > > >> > OLE
> > > > >> > DB
> > > > >> > provider.
> > > > >> >
> > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > >> >
> > > > >> >> Try using single quotes unless double quotes are required by
your
> > data
> > > > >> >> provider.
> > > > >> >>
> > > > >> >> --
> > > > >> >> Thanks.
> > > > >> >>
> > > > >> >> Donovan R. Smith
> > > > >> >> Software Test Lead
> > > > >> >>
> > > > >> >> This posting is provided "AS IS" with no warranties, and
confers
> > no
> > > > >> >> rights.
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks Bruce! That did it.
"Bruce Loehle-Conger" wrote:
> It looks to me that you are coming up against the typical problem of
> embedding double quotes in a string. As a coincidence today I have just been
> dealing with creating a dynamic sql statement in a stored procedure which
> had the same issue but with single quotes. As I mentioned before, the best
> way to deal with making sure that your expression is correct is to back off
> from trying to create the dataset and first make sure the correct string is
> being created. You should just assign the expression to a text box (I create
> a report with nothing on it but a textbox).
> For instance in your case I started off with this expression for a textbox.
> ="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
> i.e I did what you said you did, added an =" and finished off with a ".
> Trying to go into preview it fails with an error. Which is what I expected
> would happen. You can not put in double quotes in a string without following
> the special string rule, do it twice.
> I assigned this string to the textbox:
> ="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
> Notice that everywhere you want your resulting string to have double quotes
> I put double quotes twice. In preview the textbox has this:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> Just what you want. When testing this your textbox needs to end up with
> exactly the string expected by Oracle. Once you have this then you can
> assign the expression to the dataset and away you go.
> Bruce L-C
>
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> > This is the statement that works:
> >
> > SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> > "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> > "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> > "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> > "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> > "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
> "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> > "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> > "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> > "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> > "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
> >
> >
> > What I want to do is put in a couple of dynamic parameters. But I add the
> > =" at the beginning of the statement, and end the statement with ", the
> query
> > will not run, with the error "The expression for the query 'PRPQIS'
> contains
> > an error: [BC30205] End of statement expected." If I change the double
> > quotes within the statement to single quotes, I get an Oracle error,
> > ORA-00936: Missing Expression. If I remove the quotes all together, I
> don't
> > get anything returned from my query.
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Could you post the statement that you have in the Generic query
> designer?
> > > That might help generate some ideas on what is going on.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > > Yes, I have done that and confirmed that I am using the correct
> syntax. I
> > > > think the problem is that Oracle requires any field that starts with a
> > > number
> > > > to have a quote around it to designate it as a string. These quotes
> are
> > > what
> > > > is causing me problems in the Generic query designer, because I want
> to
> > > add
> > > > dynamic parameters to the query.
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > If you are building the SQL dynamically then I suggest you back up
> and
> > > make
> > > > > sure you are creating the appropriate syntax. Set the source of a
> > > textbox to
> > > > > the expression you are currently doing in the generic designer. Then
> you
> > > can
> > > > > copy and paste that into whatever Oracle calls their tool to execute
> ad
> > > hoc
> > > > > queries. Then you can quickly find out whether the syntax is
> correct.
> > > > > Consider that the generic designer essentially allows passthrough
> > > queries.
> > > > > You need the exact same syntax that you would have if you were going
> > > > > directly against Oracle.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > > >I need to build the query in the generic designer, because I need
> to
> > > create
> > > > > > dynamic parameters. I don't believe I can do that in the
> graphical
> > > > > > designer?
> > > > > > The actual query works fine in the graphical designer.
> > > > > >
> > > > > > "Brian Welcker [MSFT]" wrote:
> > > > > >
> > > > > >> It should work fine. Unless you are building an expression,
> quotes
> > > should
> > > > > >> not matter. Have you tried it with the graphical one?
> > > > > >>
> > > > > >> --
> > > > > >> Brian Welcker
> > > > > >> Group Program Manager
> > > > > >> SQL Server Reporting Services
> > > > > >>
> > > > > >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > >> rights.
> > > > > >>
> > > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > > >> > Double quotes are required. It is an Oracle database, I am
> using
> > > the
> > > > > >> > OLE
> > > > > >> > DB
> > > > > >> > provider.
> > > > > >> >
> > > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > > >> >
> > > > > >> >> Try using single quotes unless double quotes are required by
> your
> > > data
> > > > > >> >> provider.
> > > > > >> >>
> > > > > >> >> --
> > > > > >> >> Thanks.
> > > > > >> >>
> > > > > >> >> Donovan R. Smith
> > > > > >> >> Software Test Lead
> > > > > >> >>
> > > > > >> >> This posting is provided "AS IS" with no warranties, and
> confers
> > > no
> > > > > >> >> rights.
> > > > > >> >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Generating parameters from a list

I have a number of fiscal reports that I am writing. Currently, the
parameter for year is hard-coded. Ideally, I would like to be able to
generate the parameter list so that it offers years in descending from the
current year back 5 years or some defined period. In this manner, the
reports would not need to be updated each new year to modify the parameter
list. Is this possible, in v. 2000?One solution could be to create a dataset that pulls the year of a date field
in your report (group on the year) and use that as your query for the
parameter.
"Sarah" wrote:
> I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?|||This query will do it... Setup your parameter and set this query to be the
default value
select datename(yyyy,getdate())
+',' + datename(yyyy,dateadd(yy,-1,getdate()))
+',' + datename(yyyy,dateadd(yy,-2,getdate()))
+',' + datename(yyyy,dateadd(yy,-3,getdate()))
+',' + datename(yyyy,dateadd(yy,-4,getdate()))
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sarah" <Sarah@.discussions.microsoft.com> wrote in message
news:4F379DE9-9B9D-454A-9ABA-41CFB39C41C9@.microsoft.com...
>I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?

Generating Multiple Reports

I have a report that I pass parameters of a clientID. I run the report and export it to a .pdf file. I would like to be able to do this for multiple clients without manually having to enter the ID each time and exporting it.

I have thought about creating a report that calls this report so that I can pass in the clientID one at a time. The problem is trying to export each one.

Does anyone have any ideas? Thanks in advance!

I beleive you can use the reportviewer.render method to export directly to a PDF. I've done a similar app going straight to printer.|||

Let me quote myself from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2048825&SiteID=1

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

and I believe either method will work for you here. If you need a more specific answer, let me know.

Good luck,

Larry Smithmier

Generating complicate WHERE clause via parameters

Hi,
I have a large number of parameters defined which can be null and I have to
generate WHERE clause based on valid parameters, this makes things complicated
becuse I have to add "AND" to the WHERE clause depending on availability of
the parameters, the number of validation I have to do increases gradually
because the 10th parametrs should check for availability of 10 previous
parameters
before it can add "AND" infront of it. Is there any slick ways to handle
this problem?
Thank you.COALESCE will do.
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Thank you for you reply, I still do not see how CAOESECE can solve my problem.
My query looks like this.
SELECT column1, column2 FROM table1
WHERE
column3 = val1
AND
column4 = val2
with out val1 parameter supplied it should be:
SELECT column1, column2 FROM table1
WHERE
column4 = val2
So printing "AND" is the problem and only way to do it for me right now is
to check
if val1 was supplied or not.
can COALESCE help in this situation?
Thank you.
"Bing Bing Yu" wrote:
> COALESCE will do.
>
> "JC" wrote:
> > Hi,
> >
> > I have a large number of parameters defined which can be null and I have to
> > generate WHERE clause based on valid parameters, this makes things complicated
> > becuse I have to add "AND" to the WHERE clause depending on availability of
> > the parameters, the number of validation I have to do increases gradually
> > because the 10th parametrs should check for availability of 10 previous
> > parameters
> > before it can add "AND" infront of it. Is there any slick ways to handle
> > this problem?
> >
> > Thank you.|||WHERE column3 = COALESCE(val1, column3)
AND column4 = COALESCE(val2, column4)
"JC" wrote:
> Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> My query looks like this.
> SELECT column1, column2 FROM table1
> WHERE
> column3 = val1
> AND
> column4 = val2
> with out val1 parameter supplied it should be:
> SELECT column1, column2 FROM table1
> WHERE
> column4 = val2
> So printing "AND" is the problem and only way to do it for me right now is
> to check
> if val1 was supplied or not.
> can COALESCE help in this situation?
> Thank you.
>
> "Bing Bing Yu" wrote:
> >
> > COALESCE will do.
> >
> >
> > "JC" wrote:
> >
> > > Hi,
> > >
> > > I have a large number of parameters defined which can be null and I have to
> > > generate WHERE clause based on valid parameters, this makes things complicated
> > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > the parameters, the number of validation I have to do increases gradually
> > > because the 10th parametrs should check for availability of 10 previous
> > > parameters
> > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > this problem?
> > >
> > > Thank you.|||I eneded up doing it like this (using AdventureWorks) can this be enhanced?
(I have credited you by the way, thank you)
="DECLARE @.ContactID INT, " &
"@.FirstName NVARCHAR(50) " &
Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
" SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
"WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName =COALESCE(@.FirstName, FirstName)"
"Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
>
> WHERE column3 = COALESCE(val1, column3)
> AND column4 = COALESCE(val2, column4)
>
> "JC" wrote:
> >
> > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> >
> > My query looks like this.
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column3 = val1
> > AND
> > column4 = val2
> >
> > with out val1 parameter supplied it should be:
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column4 = val2
> > So printing "AND" is the problem and only way to do it for me right now is
> > to check
> > if val1 was supplied or not.
> >
> > can COALESCE help in this situation?
> >
> > Thank you.
> >
> >
> > "Bing Bing Yu" wrote:
> >
> > >
> > > COALESCE will do.
> > >
> > >
> > > "JC" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a large number of parameters defined which can be null and I have to
> > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > the parameters, the number of validation I have to do increases gradually
> > > > because the 10th parametrs should check for availability of 10 previous
> > > > parameters
> > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > this problem?
> > > >
> > > > Thank you.|||Hi,
I think you need to build the query something like this.
declare @.str as nvarchar(1000)
set @.str = ''
set @.str = @.str + "select * from "...
if @.val is null
set @.str = @.str + "val = .."
exec sp_executesql @.str
you can use if conditions where ever required.
Amarnath
"JC" wrote:
> I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> (I have credited you by the way, thank you)
> ="DECLARE @.ContactID INT, " &
> "@.FirstName NVARCHAR(50) " &
> Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => COALESCE(@.FirstName, FirstName)"
>
> "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> >
> > WHERE column3 = COALESCE(val1, column3)
> > AND column4 = COALESCE(val2, column4)
> >
> >
> > "JC" wrote:
> >
> > >
> > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > >
> > > My query looks like this.
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column3 = val1
> > > AND
> > > column4 = val2
> > >
> > > with out val1 parameter supplied it should be:
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column4 = val2
> > > So printing "AND" is the problem and only way to do it for me right now is
> > > to check
> > > if val1 was supplied or not.
> > >
> > > can COALESCE help in this situation?
> > >
> > > Thank you.
> > >
> > >
> > > "Bing Bing Yu" wrote:
> > >
> > > >
> > > > COALESCE will do.
> > > >
> > > >
> > > > "JC" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a large number of parameters defined which can be null and I have to
> > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > the parameters, the number of validation I have to do increases gradually
> > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > parameters
> > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > this problem?
> > > > >
> > > > > Thank you.|||Well, I'm pretty sure the dynamic query works just fine. One concern would
be since it's dynamic, SQL server won't be able to optimize the query.
"Amarnath" wrote:
> Hi,
> I think you need to build the query something like this.
> declare @.str as nvarchar(1000)
> set @.str = ''
> set @.str = @.str + "select * from "...
> if @.val is null
> set @.str = @.str + "val = .."
> exec sp_executesql @.str
> you can use if conditions where ever required.
> Amarnath
>
> "JC" wrote:
> >
> > I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> > (I have credited you by the way, thank you)
> >
> > ="DECLARE @.ContactID INT, " &
> > "@.FirstName NVARCHAR(50) " &
> > Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> > Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> > Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> > Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> > " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> > "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => > COALESCE(@.FirstName, FirstName)"
> >
> >
> >
> > "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> > >
> > >
> > > WHERE column3 = COALESCE(val1, column3)
> > > AND column4 = COALESCE(val2, column4)
> > >
> > >
> > > "JC" wrote:
> > >
> > > >
> > > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > > >
> > > > My query looks like this.
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column3 = val1
> > > > AND
> > > > column4 = val2
> > > >
> > > > with out val1 parameter supplied it should be:
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column4 = val2
> > > > So printing "AND" is the problem and only way to do it for me right now is
> > > > to check
> > > > if val1 was supplied or not.
> > > >
> > > > can COALESCE help in this situation?
> > > >
> > > > Thank you.
> > > >
> > > >
> > > > "Bing Bing Yu" wrote:
> > > >
> > > > >
> > > > > COALESCE will do.
> > > > >
> > > > >
> > > > > "JC" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a large number of parameters defined which can be null and I have to
> > > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > > the parameters, the number of validation I have to do increases gradually
> > > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > > parameters
> > > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > > this problem?
> > > > > >
> > > > > > Thank you.|||One easy way would be to use 1=1 and
'Select col1 from table 1 where 1=1 and' + @.str_where
@. str_where can be populated from the parameters you accept in
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Although you can do dynamic SQL I find this technique easier:
select somefields from sometable where (somefield = @.Param1 or @.Param1 ='All') and (anotherfield = @.Param2 or @.Param1 = 'All')
Note that if a number field you can check for a number that doesn't exist in
your database whether it is -1 or -99999 or whatever as long as you know it
doesn't exist in real life.
I prefer to have an All selection, however, you can use Null or Empty string
as well.
select somefields from sometable where (somefield = @.Param1 or
isnull(@.Param1)) and (anotherfield = @.Param2 or isnull(@.Param1))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunil" <Sunil@.discussions.microsoft.com> wrote in message
news:2666D2EB-03EA-47CF-A958-52EC722567FC@.microsoft.com...
> One easy way would be to use 1=1 and
> 'Select col1 from table 1 where 1=1 and' + @.str_where
> @. str_where can be populated from the parameters you accept in
>
> "JC" wrote:
>> Hi,
>> I have a large number of parameters defined which can be null and I have
>> to
>> generate WHERE clause based on valid parameters, this makes things
>> complicated
>> becuse I have to add "AND" to the WHERE clause depending on availability
>> of
>> the parameters, the number of validation I have to do increases gradually
>> because the 10th parametrs should check for availability of 10 previous
>> parameters
>> before it can add "AND" infront of it. Is there any slick ways to handle
>> this problem?
>> Thank you.

Generating a querry parameters

Hello,

Need a bit of help here. In one table i have my production data on some wheels. Each time the wheel is touched, it gets smaller. Then in another table I have the primary data of that wheel, ie price vendor, make model and scrap diameter.

What I'd like to figure out is, In one month, what was the wheel size to start off, what is the end size to give me a total size used during that month. If i could get that, then I think I would be able to figure out what the price cost for useage of that wheel was for the month.

Any help?

Thanks Brian

Could you please provide a description (preferrably one that's runnable in QA) of the tables involved, how they are related, and also a few rows of data that demonstrates wheels being 'touched'.

It does seem like what you want is to find start and end of month for a given wheel, and then subtract end-size from start-size, though it's pretty hard to know how to do it without knowledge of your tables or the data in them =;o)

/Kenneth

|||

Lets say this is the wheel table

Wheel_NO New_Diameter Scrap_Diameter Current_Diameter Price Date_Purchased

12345678 15.2384 12.2500 14.3484 $19000.00 11/21/2005

12345677 15.2384 12.2500 15.1024 $18500.00 7/8/2005

12345679 15.2384 12.2500 13.8754 $17250.00 2/14/2005

12345673 15.2384 12.2500 14.9824 $16000.00 1/1/2004

This in the production History I have

Wheel_No Start_DT End_Dt E_Diameter Start_Dia

12345678 11/21/2005 15:34 11/21/2005 15:48 15.2225 15.2384

12345678 11/22/2005 15:34 11/22/2005 15:48 15.1055 15.2225

12345678 11/25/2005 15:34 11/25/2005 15:48 15.0054 15.1055

12345678 10/25/2006 15:34 10/25/2006 15:48 14.9654 15.0054

12345678 11/01/2006 15:34 11/01/2006 15:48 14.9561 14.9654

12345678 11/15/2006 15:34 11/15/2006 15:48 14.5466 14.9561

12345678 11/21/2006 15:36 11/21/2006 15:48 14.3484 14.5466

12345677 10/20/2006 14:36 10/20/2006 15:21 15.1024 15.2001

12345677 10/31/2006 14:36 10/31/2006 15:21 14.9856 15.1024

12345677 10/20/2006 14:36 10/20/2006 15:21 14.9548 14.9856

So from the production data table I want to know What was the Start diameter and the end diameter of each wheel that ground for November 2006

Does this help at all?

|||

Brian:

Please take a look and see if this is on the right track:

set nocount on
declare @.wheel table
( wheel_no varchar (10) not null,
new_diameter numeric (9,4) not null,
scrap_diameter numeric (9,4) not null,
current_diameter numeric (9,4) not null,
price numeric (9,2) not null,
primary key (wheel_no)
)
insert into @.wheel values ('12345678', 15.2384, 12.2500, 14.3484, 19000.00)
insert into @.wheel values ('12345677', 15.2384, 12.2500, 15.1024, 18500.00)
insert into @.wheel values ('12345679', 15.2384, 12.2500, 13.8754, 17250.00)
insert into @.wheel values ('12345673', 15.2384, 12.2500, 14.9824, 16000.00)
--select * from @.wheel

declare @.prodHistory table
( wheel_no varchar (10) not null,
start_dt datetime not null,
end_dt datetime not null,
e_diameter numeric (9,4) not null,
start_dia numeric (9,4) not null,
primary key (wheel_no, start_dt, start_dia)
)
insert into @.prodHistory values ('12345678', '11/21/5 15:34', '11/21/5 15:48', 15.2225, 15.2384)
insert into @.prodHistory values ('12345678', '11/22/5 15:34', '11/22/5 15:48', 15.1055, 15.2225)
insert into @.prodHistory values ('12345678', '11/25/5 15:34', '11/25/5 15:48', 15.0054, 15.1055)
insert into @.prodHistory values ('12345678', '10/25/6 15:34', '10/25/6 15:48', 14.9654, 15.0054)
insert into @.prodHistory values ('12345678', '11/1/6 15:34', '11/1/6 15:48', 14.9561, 14.9654)
insert into @.prodHistory values ('12345678', '11/15/6 15:34', '11/15/6 15:48', 14.5466, 14.9561)
insert into @.prodHistory values ('12345678', '11/21/6 15:34', '11/21/6 15:48', 14.3484, 14.5466)
insert into @.prodHistory values ('12345677', '10/20/6 14:36', '10/20/6 15:21', 15.1024, 15.2001)
insert into @.prodHistory values ('12345677', '10/31/6 14:36', '10/31/6 15:21', 14.9856, 14.9856)
insert into @.prodHistory values ('12345677', '10/20/6 14:36', '10/20/6 15:21', 14.9548, 14.9856)
--select * from @.prodHistory

-- -
-- This is the subquery where most of the work takes place
-- I show the output of this separately so that it is easire
-- to gauge whether or not this query is on track.
-- -
/*
select h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter)
as [Price / Dia],
month (h.start_dt) as month,
max (h.start_dia) as start_diameter,
min (h.e_diameter) as end_diameter,
max (h.start_dia) - min (e_diameter) as diff
from @.prodHistory h
inner join @.wheel w
on h.wheel_no = w.wheel_no
group by h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter),
month (h.start_dt)
*/

-- wheel_no Price / Dia month start_diameter end_diameter diff
-- - -- -- --
-- 12345677 6190.6036675143889 10 15.2001 14.9548 .2453
-- 12345678 6357.9172801499129 10 15.0054 14.9654 .0400
-- 12345678 6357.9172801499129 11 15.2384 14.3484 .8900

select wheel_no,
month,
convert (numeric (9,2), diff * [Price / Dia]) as Cost
from (

select h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter)
as [Price / Dia],
month (h.start_dt) as month,
max (h.start_dia) as start_diameter,
min (h.e_diameter) as end_diameter,
max (h.start_dia) - min (e_diameter) as diff
from @.prodHistory h
inner join @.wheel w
on h.wheel_no = w.wheel_no
group by h.wheel_no,
w.price / (w.new_diameter - w.scrap_diameter),
month (h.start_dt)
) x

-- -- Sample Output:

-- wheel_no month Cost
-- - --
-- 12345677 10 1518.56
-- 12345678 10 254.32
-- 12345678 11 5658.55

Generate report from list of parameters?

I have one report with two datasets, and consequently two queries. The first
one gets a list of ReportID's for a particular day, while the second dataset
uses each ReportID to create a report.
Query 1:
SELECT ReportID
FROM Table1
WHERE Date=@.Date
Query 2:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID = @.ReportID
If I try to Union '(All)' to Query1, Query2 will return info for all
ReportID's in the system, not just what was generated in Query1. I also
tried creating a comma delimited field of all the ReportID's and using WHERE
ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
Query2 while only running Query1 once?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1On Feb 28, 6:28 am, "ana9" <u28955@.uwe> wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
If I'm understanding you correctly, you should be able to do one of a
few things:
Option 1:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID in (SELECT ReportID
FROM Table1
WHERE Date=@.Date )
Option 2:
Create a cursor (or while loop) to loop through Query 1 and execute
Query 2 in the loop for each ReportID (possibly populating a single
dataset or multiple datasets).
Ofcourse Option 1 is far less performance intensive. If I'm not
understanding you correctly, let me know.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||That was very helpful, thank you. Option1 is a variation of what I had tried
before. The only issue (which I failed to mention) is that Query2 is
actually a report that gets populated from about 20 datasets. So each
dataset may not return the exact same number of rows, in which case some of
the information will not be displayed in the correct manor. Also, for some
of those datasets I use SELECT Top # so using Option1 will not allow me to
get the correct amount of results.
Your suggestion for Option2 seems like it would be perfect for this scenario,
however I was under the impression that you can't create while loops in SQL.
Could you point me in the direction of some resources/examples? Thank you.
EMartinez wrote:
>> I have one report with two datasets, and consequently two queries. The first
>> one gets a list of ReportID's for a particular day, while the second dataset
>[quoted text clipped - 18 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>If I'm understanding you correctly, you should be able to do one of a
>few things:
>Option 1:
>SELECT *
>FROM *A whole bunch of tables*
>WHERE ReportID in (SELECT ReportID
>FROM Table1
>WHERE Date=@.Date )
>Option 2:
>Create a cursor (or while loop) to loop through Query 1 and execute
>Query 2 in the loop for each ReportID (possibly populating a single
>dataset or multiple datasets).
>Ofcourse Option 1 is far less performance intensive. If I'm not
>understanding you correctly, let me know.
>Hope this is helpful.
>Regards,
>Enrique Martinez
>Sr. SQL Server Developer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1|||You might also try
SELECT ReportID INTO #Reports
FROM Table1
WHERE Date=@.Date
SELECT *
FROM *A whole bunch of tables*
inner join #Reports
On ReportID = #Reports.ReportID
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ana9" wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>

Generate a report, export to PDF

I want to programmatically pass in some parameters, generate
a report, export this report to PDF, and pop up adobe acrobat reader in a web
browser in one click while naming the PDF file exactly as I like, including
its path. I currently do this easily with Crystal Reports in one click, but
I need to see if its feasible in RS.
In crystal I use an ASP.net page that does all of this for me. I declare the
report, get the parameters, fill them, set up the username/password for the
database, set a name for the file, and set the export options, and then
generate the report.
If I could get some direction as to how to do some of this stuff, it would
be great =) We are thinking that while its not pretty we are going to use the
URL method if possible to do this stuff, because we already have a custom web
app in place, and no need for a shell etc. We just need a web page that can
do this for us upon hitting submit.
Again, any direction you can offer would be great
TIAHi Pinolian:
From ASP.NET you code access the report server with URL access and the
WebClient or WebRequest classes. Once you have the file pulled down to
the ASP.NET application you could stream the file to the client by
setting the content disposition header and using Response.BinaryWrite.
Some sample code would look like this:
byte[] response;
string url = "http://s/reportserver?Report&rs:Format=PDF";
using(WebClient webClient = new WebClient())
{
webClient.Credentials = CredentialCache.DefaultCredentials;
response = webClient.DownloadData(url);
}
Response.ContentType="application/pdf";
Response.AddHeader(
"content-disposition",
"attachment; filename=MyPDF.PDF"
);
Response.BinaryWrite(response);
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 19 Oct 2004 13:59:01 -0700, "Pinolian"
<Pinolian@.discussions.microsoft.com> wrote:
> I want to programmatically pass in some parameters, generate
>a report, export this report to PDF, and pop up adobe acrobat reader in a web
>browser in one click while naming the PDF file exactly as I like, including
>its path. I currently do this easily with Crystal Reports in one click, but
>I need to see if its feasible in RS.
>In crystal I use an ASP.net page that does all of this for me. I declare the
>report, get the parameters, fill them, set up the username/password for the
>database, set a name for the file, and set the export options, and then
>generate the report.
>If I could get some direction as to how to do some of this stuff, it would
>be great =) We are thinking that while its not pretty we are going to use the
>URL method if possible to do this stuff, because we already have a custom web
>app in place, and no need for a shell etc. We just need a web page that can
>do this for us upon hitting submit.
>Again, any direction you can offer would be great
>TIA

Generate A Report Via Login

To all you experts out there, I have one hurdle I need help on.
I've created a SQL report which has 3 parameters asking for a from_date, a
to_date & an employee. The report then returns all projects, tasks,
yadda-yadda, for the employee, within the date range from a time tracking
database. Very usefull come performance review time.
My intention is to make this report available on Sharepoint via a link (or
Report Manager), where whoever logs into sharepoint (or Report Manager) can
access this report for themselves and no one else, meaning they click on the
report and get prompted for the date range parameters but not the employee
parameter. The report should recongize who is accessing the report by virtue
of their login & find the corresponding employee information in the database
& display the report for that employee.
Should this functionality be implemented in the SQL report or should it be
implemented within Sharepoint? Based on that question, how can this
functionality be implemented?
Thank you very much for the help.I assume you want to have the manager get all of his employees. You would
need a table somewhere with this relationship. Then use the global variable
User!UserID. This is the user running the report. Your query parameter does
not have to be mapped to a report parameter. Click on the ... for the
dataset, parameters tab, right side of list select expression which takes
you to the expression builder. Note that User!UserID returns the user with
the domain i.e. domain\userid
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> To all you experts out there, I have one hurdle I need help on.
> I've created a SQL report which has 3 parameters asking for a from_date, a
> to_date & an employee. The report then returns all projects, tasks,
> yadda-yadda, for the employee, within the date range from a time tracking
> database. Very usefull come performance review time.
> My intention is to make this report available on Sharepoint via a link (or
> Report Manager), where whoever logs into sharepoint (or Report Manager)
> can
> access this report for themselves and no one else, meaning they click on
> the
> report and get prompted for the date range parameters but not the employee
> parameter. The report should recongize who is accessing the report by
> virtue
> of their login & find the corresponding employee information in the
> database
> & display the report for that employee.
> Should this functionality be implemented in the SQL report or should it be
> implemented within Sharepoint? Based on that question, how can this
> functionality be implemented?
> Thank you very much for the help.|||A manager can get to his employees (individually) as the report exists (with
a report parameter for the emp_name).
I completely forgot about the userid. Good news is I can match up the userid
to the substring of a field already being returned (emp_name), but I don't
quite follow how to set it up as a query parameter. Do I create a new dataset
or create this query parameter on the dataset for my existing result set
details(where emp_name exists)? Can you give me a hint on what the syntax
would look like?
Thanks
"Bruce L-C [MVP]" wrote:
> I assume you want to have the manager get all of his employees. You would
> need a table somewhere with this relationship. Then use the global variable
> User!UserID. This is the user running the report. Your query parameter does
> not have to be mapped to a report parameter. Click on the ... for the
> dataset, parameters tab, right side of list select expression which takes
> you to the expression builder. Note that User!UserID returns the user with
> the domain i.e. domain\userid
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> > To all you experts out there, I have one hurdle I need help on.
> >
> > I've created a SQL report which has 3 parameters asking for a from_date, a
> > to_date & an employee. The report then returns all projects, tasks,
> > yadda-yadda, for the employee, within the date range from a time tracking
> > database. Very usefull come performance review time.
> >
> > My intention is to make this report available on Sharepoint via a link (or
> > Report Manager), where whoever logs into sharepoint (or Report Manager)
> > can
> > access this report for themselves and no one else, meaning they click on
> > the
> > report and get prompted for the date range parameters but not the employee
> > parameter. The report should recongize who is accessing the report by
> > virtue
> > of their login & find the corresponding employee information in the
> > database
> > & display the report for that employee.
> >
> > Should this functionality be implemented in the SQL report or should it be
> > implemented within Sharepoint? Based on that question, how can this
> > functionality be implemented?
> >
> > Thank you very much for the help.
>
>|||Thanks Bruce...Got It. I created the UserID parameter as @.EmployeeName &
refered to that parameter in the data set as;
WHERE pe_date between @.FromDate AND @.ToDate
AND ('DOMAIN\' + Substring(descr,3,25)) = @.EmployeeName
OR ('DOMAIN\' + Substring(descr,1,1) + Substring(descr,3,25)) = @.EmployeeName
and it works just great. Thanks for shaking the cobwebs loose.
JD
"Bruce L-C [MVP]" wrote:
> I assume you want to have the manager get all of his employees. You would
> need a table somewhere with this relationship. Then use the global variable
> User!UserID. This is the user running the report. Your query parameter does
> not have to be mapped to a report parameter. Click on the ... for the
> dataset, parameters tab, right side of list select expression which takes
> you to the expression builder. Note that User!UserID returns the user with
> the domain i.e. domain\userid
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> > To all you experts out there, I have one hurdle I need help on.
> >
> > I've created a SQL report which has 3 parameters asking for a from_date, a
> > to_date & an employee. The report then returns all projects, tasks,
> > yadda-yadda, for the employee, within the date range from a time tracking
> > database. Very usefull come performance review time.
> >
> > My intention is to make this report available on Sharepoint via a link (or
> > Report Manager), where whoever logs into sharepoint (or Report Manager)
> > can
> > access this report for themselves and no one else, meaning they click on
> > the
> > report and get prompted for the date range parameters but not the employee
> > parameter. The report should recongize who is accessing the report by
> > virtue
> > of their login & find the corresponding employee information in the
> > database
> > & display the report for that employee.
> >
> > Should this functionality be implemented in the SQL report or should it be
> > implemented within Sharepoint? Based on that question, how can this
> > functionality be implemented?
> >
> > Thank you very much for the help.
>
>