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 change report datasource based upon parameter.

I currently have one report that I would like to be able to use to report
off of stored procs on multiple servers. And am hoping someone can point me
in the right direction.
Example:
Report A will run off of storedProc1 which exists in every database.
However, the specific server and database to use will depend upon the user
currently logged in.
Currently I am trying to make use of the custom dataset extension (by Teo
Lachev) to report off of an XML string. Unfortunately, I am having fits
trying to get it to work and don't even know if this is the best way.
Any help would be appreciated.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* Use a custom data processing extension (as you currently do)
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
Native support (expression-based connection strings) is available in RS
2005.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:O4%23lPKA9EHA.960@.TK2MSFTNGP11.phx.gbl...
> I currently have one report that I would like to be able to use to report
> off of stored procs on multiple servers. And am hoping someone can point
me
> in the right direction.
> Example:
> Report A will run off of storedProc1 which exists in every database.
> However, the specific server and database to use will depend upon the user
> currently logged in.
> Currently I am trying to make use of the custom dataset extension (by Teo
> Lachev) to report off of an XML string. Unfortunately, I am having fits
> trying to get it to work and don't even know if this is the best way.
> Any help would be appreciated.
>

Dynamic SQL Syntax H_ll Please Help

This forum has helped me get to this point a couple times with this project BUT I just can't seem to get the syntax correct. Either one of these two statements do exactly what I want them to do:
SELECT @.RtnValue = Column0 FROM MyTable WHERE RowIndex = @.RIndex
SELECT @.RtnValue = (SELECT Column0 FROM MyTable WHERE RowIndex = @.RIndex)

@.RtnValue is the value the program will work with.

The only problem is that Column0 and @.RIndex need to be dynamic so I can index through each Column and Row of the table.

This is the code I am trying to use to do this dynamically, naturally it will be in two while loop to index through each Column and Row

DECLARE @.RtnValue smallint
SET @.RtnValue = 0

DECLARE @.RIndex smallint
SET @.RIndex = 20

DECLARE @.ColumnName varchar(10)
SET @.ColumnName = 'Column0'

DECLARE @.MySelectString varchar(200)
SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + @.RIndex + ' )'
--SET @.MySelectString1 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = 1 )'

EXEC( @.MySelectString0 )
--EXEC( @.MySelectString1 )

@.MySelectString0 produces this error:
Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value 'SELECT @.RtnValue = ( SELECT Column0 FROM MyTable WHERE RowIndex = ' to a column of data type smallint.

@.MySelectString1 produces this error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable'@.RtnValue'.

I have tried many different combinations of syntax but can not seem to get the magic combination. Can someone tell me the correct syntax to get this to work.

Thank you in advance.

try something like this :

SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + CONVERT(VARCHAR,@.RIndex) + ' )'

|||

Thank You for your responce I will try your solution. I sure hope it works I have been stuck on this problem to long Thanks again

Dynamic SQL Query Question

I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.

I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @.tblNode AND tblCorp = @.tblCorp AND tblService = @.tblService

The @.tblNode, @.tblCorp, @.tblService are all pulling from 3 separate drop down controls.

Ok easy enough.

However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.

Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?

I guess I am looking to see if a wild card can be used so tblNode = % or something.

The server is MSSQL

Thank you

You can use either of the 2 approaches mentioned in this article and its comments:http://www.emxsoftware.com/Optional+Parameters+in+SQL+Server+Search+Queries

|||

Thank you for your help, while this is what I need... would you happen to know how to set the value of the drop downs I am using = NULL?

I can pull now based on the criteria of the drop downs, but the default values should pull everything. That is really where I am stuck right now.

|||

You leave your Select All listItem in your DropDownList to empty like:

<asp:ListItemValue="">ALL</asp:ListItem>

Your SQl where clause:

Where tblNode =ISNULL(@.tblNode,tblNode) AND tblCorp=ISNULL(@.tblCorp, tblCorp) AND tblService= ISNULL(@.tblService, tblService)

and:

If you use a SqlDataSource, you need to set this property too:CancelSelectOnNullParameter="false"

|||

Thank you both, that actually did it and I am up and working.

I appreciate you both taking the time to explain it for me.