.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

What are my options when I need to create a report with data from multiple sources?

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
Hello all,  What are my options when I want to create a report that will get records from multiple data sources based on data from a parameter? The user will enter the customer ID, I have to check that ID against 5 different data sources. Have you guys done anything similar? Should I leverage SSIS on this request, adding an extra layer of complexity? Should I do this all from SSRS? What are my options?

View Complete Post

More Related Resource Links

Creating a report from multiple data sources

I am working on a report where it requires pulling data from various sources, databases and data exported into excel sheets from different databases. The objective of the report is to create a single report with all the Providers (HPs) that have been terminated. So what I have done is that I have exported all the data on a local server in the pubs database and I am using a TSQL query to return the report. The same providers may exists in the various data sources so I have to make sure that I am only returning the data once...how ever the challenge is that, other than FirstName and LastName, there are no consistent IDs that we can use in all. For example, not all of the records have the SSN, PhoneNumber, or other IDs that uniquly identify the record. I have to create a report that returns the result set and identifiys the matching records and the source system the data is from..for example the output should display as such: Termed System firstname lastname SSN homephone phonenumber Active System AMIE Jeannine Donigan NULL 3522631007 Nurse SL Linde Peggy Vanrensselaer NULL 6077321218 Sbdev Below is the approach I I have taken to return the data and I wanted to know if there is a more efficient and better way of doing this: DECLARE @matchingTerminated Table ( FirstName Varchar(255), LastName Varchar(255), Gender Varchar(255), SSNEncrypted varchar(255), Birt

How do you create an association between two different data sources in BDC web parts?



I have a SQL Server database with the customer details and an authenticated web service providing contact details of the selected customer.  How do i connect them using web part connections to retrieve and display the data.

Or is it possible to create a Primary key-Foreign Key relationship between two tables in different databases with a common column?



How do you create a custom BDC data field that allows for multiple selected values?

I need help creating a custom data field using the BDC column as a base.  We need to allow for multiple selected values instead of just a single one.  I can't find anything on the net which shows how to do this.

Using the single *.rpt file with multiple data sources


I've created a set of CrystalReports (*.rpt files) for an ASP.NET web app on a development server. I call each report using the following code:

protected void BTN_RunReport_Click(object sender, ImageClickEventArgs e)
CrystalReportViewer_ClientLetter.Visible = true;

ConnectionInfo con = new ConnectionInfo();
con.ServerName = Constants.ServerIP;
con.DatabaseName = Constants.DatabaseName;
con.UserID = Constants.UserID;
con.Password = Constants.Password;

CrystalReportViewer_ClientLetter.ReportSource = Server.MapPath(Constants.ClientLetters);
ParameterFields parameter = CrystalReportViewer_ClientLetter.ParameterFieldInfo;
ParameterField batchdate = new ParameterField();
batchdate.Name = "@BatchDate";
ParameterDiscreteValue batchdate_value = new ParameterDiscreteValue();
batchdate_value.Value = Convert.ToDateTime(txtBatchDate.Text);

foreach (TableLogOnInfo tlf in CrystalReportViewer_ClientLetter.LogOnInfo)
tlf.ConnectionInfo = con;

Report Builder Report manage data sources - relink programmatically

As part of a site definition solution several report builder report rdl files are uploaded to a report folder.  After a site is created with the defintion, the reports must be manually re-linked to the data source through the UI context menu ("Manage Data Sources").  I have been trying to figure out how to do the re-linking programmatically without luck.  Any ideas? ( Reporting Services is configured as integrated with sharepoint)

Report with two data sources

Hello, I have two data sources: an Oracle table and a SSAS cube. I need to make a report with some information from the table and some from the cube. Example:   Oracle table ID, Comment SSAS ID, Value, Description Report ID (the join key); Value; Description; Comment.   Is it possible to do that? Many thanks  

model report from two different data sources SQL server and access DB

Can we merge two model reports into one . One model report is from SQL server and another one from Access DB. How is that possible. Can you please give a idea how to do that. I need to display data from sql server as well as access DB using model report.  I need to get data from two different sources like sql and oracle or SQL and access db. So can combine tables from these data sources into the DSV or model. I need to use it in a report   Thanks Madhavi

report generate vwd 2008 - website data sources window empty

I need to create report in asp.net web application, I'm working on vwd 2008 express, installed report add-on i have create dataset file xxx.xsd and use storder procedure for data adapter. when I am trying to add columns into report body I cannot see dataset in website data sources window. when i right click on window it just show refresh option. how can I get my data set into window. and add columns in to report? please help... 

Does Microsoft plan to enhance SSRS to allow access to multiple data sources?

We have the need to join multiple data sources into one table in SSRS.  At present, as I understand it, this is not a part of current delivered functionality.  We get around it by creating links withing the source database to access other databases.  The links themselves have their own set of constraints and performance issues. The other option is to use sub reports.  It would be nice to be able to define connections to disparate databases to join data from multiple data sources.  Does Microsoft have plans to deliver such functionality in an upcoming release of the SSRS product?

Missing options on link data sources wizard



First of all I have 2 lists ListA and ListB, ListB is linked to ListA by its ID column and not by a lookup field.

Now, on SPD2007 I just display the data sources library, and choose the create new linked sources option, but in the 2010 version, can't seem to find this option directly, just when I'm using  or have inserted a data source of some sort.

The problem is that when I get to the wizard ( link data sources wizard) I choose both lists, choose to join them, and... that's it!!!! I don't get to choose the filter columns or anything, the only options are 'Back' and 'Finish', so needless to say that I don't get what I want, 'cause it only appears the word 'row' on the child list in the sources library.

I've been serching the web, and some people have posted this same question with no answers found. then I searched this forum, and couldn't find this topic either, so maybe it's just some especific behavior regarding bad installation or something, or its just a Beta problem. please let me know


Let's Finish This

Can I create multiple report tables from one Dataset?


I'm producing a report in Report Builder 3.0 that needs to show events grouped by time of day, and another table with the same data grouped by day of week. Both tables show the same results, just grouped differently. The query that generates the ungrouped data is expensive, so I don't want to run it twice.

I've created queries in Management Studio that get the ungrouped data into a global temp table, and then I can fetch both grouped data sets from there. Unfortunately I can't get this to work in Report Builder. I have one Dataset that runs the main query, saves it into global temp, and then returns the first result set. The second Dataset returns the results from global temp. This keeps failing, the second result set is blank.

Does Reporting services run the queries in the order they are in the report, or are they run concurrently? If they are run concurrently this would explain it. Is there anything I can do to change this so that the second query doesn't run until the first one has completed?

Is there a better way of achieving the results I'm after?

Cube creation from multiple data sources



I have to design a cube that is using multiple data sources to fetch the data.

Is there a way, to connect to 2 data souces and create the data source view (.dsv) ?

Please suggest!!


please help create report using sharpeoint list as data source


the dilema is I can use standalone report builder 3.0 to create report from sharepoint list data source. But I cannot upload any report(not just sharepoint source) from report builder 3.0(standalone) to report server

I can create report model in sql BIDS for report server and using builting report builder 1.0 to create a report, but the data provider in BIDS only shows SQLclient and Oracleclient

any idea?

Create ID (uniqueid?) from two fields when data is entered


I need to create an ID from two fields when they are entered into the db for the first time.  I thought Uniqueidentifer would do this, but it looks like uniqueidentifier is random and i have no control of the process. 

My user will enter 4 letters into a column called INIT and 4 numbers into a column called NUMB.  What I would like to do is create an id by combining those fields.

How can I do this?



data from multiple row



I have a dataset in my SSRS report where I am getting the data like this. It has 3 rows.

ID Parameter Value

1. Client Name-  XYZ

2. Client Email - xyz@s.com

3. Clinet Phone- 234567

I need to check condition in expression, so If parameter is Client name then It will show in client name textbox of Report. How can write expression for this.

Dynamic WPF: Create Flexible UIs With Flow Documents And Data Binding


Flow documents offer enormous flexibility in arranging text layout and pagination, but they don't support data binding, so you can't dynamically change content. Here we build a component to solve that problem.

Vincent Van Den Berghe

MSDN Magazine April 2009

Data Services: Create Data-Centric Web Applications With Silverlight 2


ADO.NET Data Services provide Web-accessible endpoints that allow you to filter, sort, shape, and page data without having to build that functionality yourself.

Shawn Wildermuth

MSDN Magazine September 2008

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend