Dynamically Writing & Rendering Report

I have a report which will change almost everytime it is requested. I am
building the query and writing the RDL on the fly. I don't want to save the
RDL to the report server as it will be of no future use. Is there a way to
pass the RDL as perhaps a string or something similar and ask the
ReportServer to render that instead of a saved report.
Many thanks in advance for any help.
SimonThis functionality is not supported in the current release but is on wish
list for a future release. For now, you'll need to publish the report in
order to render it, i.e., call CreateReport() and then call Render(). You
can always delete it once you're done.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:%23VX8qckbEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I have a report which will change almost everytime it is requested. I am
> building the query and writing the RDL on the fly. I don't want to save
the
> RDL to the report server as it will be of no future use. Is there a way to
> pass the RDL as perhaps a string or something similar and ask the
> ReportServer to render that instead of a saved report.
> Many thanks in advance for any help.
> Simon
>|||Thanks Ravi,
Thats a bit of pain as I could have 100's if not 1000's of stray reports on
the server that would only ever be used once. I now need to write a cleanup
routine to peridoically remove the unused reports from the server. A majorly
inefficient process but looks like the only option.
Do you have any idea when the next iteration of SQL Reporting is planned for
that will include this functionality?
Simon
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> This functionality is not supported in the current release but is on wish
> list for a future release. For now, you'll need to publish the report in
> order to render it, i.e., call CreateReport() and then call Render(). You
> can always delete it once you're done.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services|||The next release of reporting services is in SQL Server 2005. This feature
is on wish list for that release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:ee0IXHxbEHA.796@.TK2MSFTNGP09.phx.gbl...
> Thanks Ravi,
> Thats a bit of pain as I could have 100's if not 1000's of stray reports
on
> the server that would only ever be used once. I now need to write a
cleanup
> routine to peridoically remove the unused reports from the server. A
majorly
> inefficient process but looks like the only option.
> Do you have any idea when the next iteration of SQL Reporting is planned
for
> that will include this functionality?
> Simon
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> > This functionality is not supported in the current release but is on
wish
> > list for a future release. For now, you'll need to publish the report in
> > order to render it, i.e., call CreateReport() and then call Render().
You
> > can always delete it once you're done.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
>

Dynamically generating RDL

I would like to point my Report Server to an ASPX page that outputs RDL.
Can this be done? Any examples or tutorials that anyone can point me to?
Thanks!
-mdbNot very easily. RS is a server based product. You have to deploy the RDL to
the server. BUT, with VS 2005 you have two new controls that have a local
mode, no server required. You can give the control your RDL (for these
controls it will be a RDLC file). It has to be RS 2005 format. These
controls come with VS, not SQL Server.
I really recommend checking out the controls. If generating RDL these
controls will make it much much much easier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9746A0025EBA5mbrayctiusacom@.207.46.248.16...
>I would like to point my Report Server to an ASPX page that outputs RDL.
> Can this be done? Any examples or tutorials that anyone can point me to?
> Thanks!
> -mdb

Dynamically Adding Subreports

I would like to Create a report that will print as one document, but is
basically made up of multiple reports (rdl files), however, I need to be able
to determine which reports (rdl files) should be added and pass parameters to
each rdl file dynamically. Can this be done with SQL Reporting Services?You could do that in VS 2005 using the RS Winforms/Webforms controls in
"local" processing mode and registering a subreport callback which
dynamically provides different RDLs with different sets of parameters.
I don't really have a good suggestion for RS 2000 - you could probably
achieve some of your requirements by dynamically generating RDLs, publishing
and rendering them by writing an application that uses the SOAP-API.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amie Fedric" <AmieFedric@.discussions.microsoft.com> wrote in message
news:6194746B-7D5B-49CE-8498-50AD8EF3B561@.microsoft.com...
>I would like to Create a report that will print as one document, but is
> basically made up of multiple reports (rdl files), however, I need to be
> able
> to determine which reports (rdl files) should be added and pass parameters
> to
> each rdl file dynamically. Can this be done with SQL Reporting Services?

Dynamic SQL queries

Hi,
I am trying to use dynamic queries in RDL from the data tab in the report
designer. The columns to be selected and the name of the table from which to
select are supplied as report parameters. Can someone give me an example of
how to do this without using stored procedures or UDFs? Is it possible at
all? I am using the reporting services from MS SQL 2000.
Thanks in advance,
PavanI've done this a few times, but then I've used stored procedures or UDF. But
you should be able to do something similar without stored procedures. I've
done something like this with MDX, should work out with SQL queries as well.
I'd try something like this:
4 Parameters - param1, param2, param3 and param4, paramTable
Query:
="Select " & Parameters!param1.Value & " as P1, " & Parameters!param2.Value
& " as P2, " & Parameters!param3.Value & " as P3, " &
Parameters!param4.Value & " as P4 from " & Parameters!paramTable.Value& "
where 1=1"
(Everything needs to be on the "same" line, no line breaks. Test in NotePad
without wordwrap to see that everything is on the same line, even if the
lines break in the editor.)
This should give you a dataset with fields P1, P2, P3 and P4, which you now
can use in your report.
You will need to be sure that all the options are available in all tables.
Or you could do cascading parameters where you choose the table first, and
then the drop down list of all parameters gets filled with valid choices.
Kaisa M. Lindahl Lervik
"Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am trying to use dynamic queries in RDL from the data tab in the report
> designer. The columns to be selected and the name of the table from which
> to select are supplied as report parameters. Can someone give me an
> example of how to do this without using stored procedures or UDFs? Is it
> possible at all? I am using the reporting services from MS SQL 2000.
> Thanks in advance,
> Pavan
>|||One additional point. First have regular SQL so you get your field list.
Then change it as below. Once it is an expression the field list will not
update.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> I've done this a few times, but then I've used stored procedures or UDF.
> But you should be able to do something similar without stored procedures.
> I've done something like this with MDX, should work out with SQL queries
> as well.
> I'd try something like this:
> 4 Parameters - param1, param2, param3 and param4, paramTable
> Query:
> ="Select " & Parameters!param1.Value & " as P1, " &
> Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> " & Parameters!param4.Value & " as P4 from " &
> Parameters!paramTable.Value& " where 1=1"
> (Everything needs to be on the "same" line, no line breaks. Test in
> NotePad without wordwrap to see that everything is on the same line, even
> if the lines break in the editor.)
> This should give you a dataset with fields P1, P2, P3 and P4, which you
> now can use in your report.
> You will need to be sure that all the options are available in all tables.
> Or you could do cascading parameters where you choose the table first, and
> then the drop down list of all parameters gets filled with valid choices.
> Kaisa M. Lindahl Lervik
> "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am trying to use dynamic queries in RDL from the data tab in the report
>> designer. The columns to be selected and the name of the table from which
>> to select are supplied as report parameters. Can someone give me an
>> example of how to do this without using stored procedures or UDFs? Is it
>> possible at all? I am using the reporting services from MS SQL 2000.
>> Thanks in advance,
>> Pavan
>|||Bruce,
I am trying to follow the BOL tutorial on dynamic queries. The expression
that they have will not fit as a one-liner in the generic query designer? Do
you have a work-around so that i can preview the result? Expression below:
="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN
Person.Contact C " &"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
Parameters!Department.Value) & "ORDER BY C.LastName"
Thanks!
"Bruce L-C [MVP]" wrote:
> One additional point. First have regular SQL so you get your field list.
> Then change it as below. Once it is an expression the field list will not
> update.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> > I've done this a few times, but then I've used stored procedures or UDF.
> > But you should be able to do something similar without stored procedures.
> > I've done something like this with MDX, should work out with SQL queries
> > as well.
> >
> > I'd try something like this:
> >
> > 4 Parameters - param1, param2, param3 and param4, paramTable
> >
> > Query:
> > ="Select " & Parameters!param1.Value & " as P1, " &
> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> > " & Parameters!param4.Value & " as P4 from " &
> > Parameters!paramTable.Value& " where 1=1"
> >
> > (Everything needs to be on the "same" line, no line breaks. Test in
> > NotePad without wordwrap to see that everything is on the same line, even
> > if the lines break in the editor.)
> > This should give you a dataset with fields P1, P2, P3 and P4, which you
> > now can use in your report.
> > You will need to be sure that all the options are available in all tables.
> > Or you could do cascading parameters where you choose the table first, and
> > then the drop down list of all parameters gets filled with valid choices.
> >
> > Kaisa M. Lindahl Lervik
> >
> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> Hi,
> >>
> >> I am trying to use dynamic queries in RDL from the data tab in the report
> >> designer. The columns to be selected and the name of the table from which
> >> to select are supplied as report parameters. Can someone give me an
> >> example of how to do this without using stored procedures or UDFs? Is it
> >> possible at all? I am using the reporting services from MS SQL 2000.
> >>
> >> Thanks in advance,
> >> Pavan
> >>
> >
> >
>
>|||Have a report with nothing but your parameters and a textbox. Set your
textbox to the expression so you can see the results.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:42F27824-479E-4B10-A232-083F860313EA@.microsoft.com...
> Bruce,
> I am trying to follow the BOL tutorial on dynamic queries. The expression
> that they have will not fit as a one-liner in the generic query designer?
> Do
> you have a work-around so that i can preview the result? Expression below:
> ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
> HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
> HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER
> JOIN
> Person.Contact C " &"ON E.ContactID = C.ContactID " &
> Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
> Parameters!Department.Value) & "ORDER BY C.LastName"
> Thanks!
>
> "Bruce L-C [MVP]" wrote:
>> One additional point. First have regular SQL so you get your field list.
>> Then change it as below. Once it is an expression the field list will not
>> update.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
>> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
>> > I've done this a few times, but then I've used stored procedures or
>> > UDF.
>> > But you should be able to do something similar without stored
>> > procedures.
>> > I've done something like this with MDX, should work out with SQL
>> > queries
>> > as well.
>> >
>> > I'd try something like this:
>> >
>> > 4 Parameters - param1, param2, param3 and param4, paramTable
>> >
>> > Query:
>> > ="Select " & Parameters!param1.Value & " as P1, " &
>> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as
>> > P3,
>> > " & Parameters!param4.Value & " as P4 from " &
>> > Parameters!paramTable.Value& " where 1=1"
>> >
>> > (Everything needs to be on the "same" line, no line breaks. Test in
>> > NotePad without wordwrap to see that everything is on the same line,
>> > even
>> > if the lines break in the editor.)
>> > This should give you a dataset with fields P1, P2, P3 and P4, which you
>> > now can use in your report.
>> > You will need to be sure that all the options are available in all
>> > tables.
>> > Or you could do cascading parameters where you choose the table first,
>> > and
>> > then the drop down list of all parameters gets filled with valid
>> > choices.
>> >
>> > Kaisa M. Lindahl Lervik
>> >
>> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
>> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> Hi,
>> >>
>> >> I am trying to use dynamic queries in RDL from the data tab in the
>> >> report
>> >> designer. The columns to be selected and the name of the table from
>> >> which
>> >> to select are supplied as report parameters. Can someone give me an
>> >> example of how to do this without using stored procedures or UDFs? Is
>> >> it
>> >> possible at all? I am using the reporting services from MS SQL 2000.
>> >>
>> >> Thanks in advance,
>> >> Pavan
>> >>
>> >
>> >
>>

Dynamic SQL in a RDL file?

Can I put parameters and change the SQL in a dataset using expressions?
Thanks
ScottYes. It is a little tricky but totally doable. First, make sure you have
your expression creating the appropriate SQL String. I usually create the
report with no dataset, just the parameters and then a textbox set to the
expression. This allows me to see the resultant SQL prior to using it for
the dataset. Then I like to first use a static query so that the field list
gets populated. Then (from the generic query window) set the SQL to the
expression. i.e. in the generic query window put
= iif(blah blah, blah, blah) & "whatever else your expression consists of" &
"etc etc"
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:emp%23Bf65EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Can I put parameters and change the SQL in a dataset using expressions?
> Thanks
> Scott
>