April 24, 2008 at 4:31 pm
I am trying to model a SSRS report after a Crystal report. The Crystal report uses a data linking expert wizard that allows for multiple data sources to be included into the report. From this wizard, one can also define relaitionships.
The scenario is as follows: This Crystal report has data sources from two different databases from two different RDBMS' (two physical database servers). I want to mirror this functionality in a SSRS report. The only way to come close to doing so is to create a SSRS Report Model Project.
From within a report model project, multiple data sources can be defined. From here, one creates a data source view that includes said data sources. One can go even further by defining realtionships - similar to Crystal. The last step is to produce a Report Model which throws an exception. It can't be done because I am going against multiple datasources.
ARGH!!!! HOW CAN ONE CREATE A SOURCE OF DATA THAT SPANS MULTIPLE RDBMS'?
What I have tryed and it works is the following:
1) Create a linked server to another RDBMS and query directly against the tables contained within
2) using a the linked server, create a staging table in the local DB and query against it. In effect, I would be querying against one DB.
I have neeeeeever been so frustrated before. Can someone please shed light on this issue?
-Mike DiRenzo
May 9, 2008 at 6:43 am
Mike,
I'm trying to do the same thing. Were you able to figure out how to get this to work?
Thanks.
May 9, 2008 at 8:56 am
You could use a SSIS package as your datasourcetype.
May 9, 2008 at 5:18 pm
To answer the one reader, no I was not able to get it to work - datasrouces from across two different RDBMS'. My solution is not a solution but a workaround:
1) Create a link server
2) query the link server from within a view - I used a CTE and joined to the CTE against the local tables relative to where the view is fired from.
The other responder said to use an SSIS pkg as the data source. PLEASE EXPLAIN IN NO UNCERTAIN TERMS HOW TO DO THIS PLEEEAAASSSE.
-Mike
May 12, 2008 at 7:35 am
Have a look at the following article:
May 15, 2008 at 10:06 am
No doubt the solution posted by xanthos (Posted 5/12/2008 2:35 PM) will be very useful to some. I cannot use this solution because of the potential security risks. I have provided this excerpt from the SQL Server 2005 Books Online(September 2007):
"Configuring Reporting Services to Use SSIS Package Data
Reporting Services includes a data processing extension that retrieves data from a SQL Server Integration Services (SSIS) package. The SSIS data processing extension is an experimental feature that is off by default. Using the SSIS data processing extension on a production server is not recommended at this time."
Click this URL to view the page containing the excerpt from above:
"Before you can select SSIS as a data source type, you must configure the unattended execution account and the SSIS data processing extension. For more information, see Configuring an Account for Unattended Report Processing and Configuring Reporting Services to Use SSIS Package Data. "
Here are all of the links...
Configuring Reporting Services to Use SSIS Package Data
http://msdn.microsoft.com/en-us/library/ms345250.aspx
Configuring an Account for Unattended Report Processing
http://msdn.microsoft.com/en-us/library/ms156302.aspx
Defining Report Datasets for Package Data from SQL Server Integration Services
http://msdn.microsoft.com/en-us/library/ms159215.aspx
Good luck to all who find this information.
-Mike DiRenzo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply