Combining Data From Multiple Datasets In Single Table?

[MonteBob]

Hello,

I am trying to do something I thought would be quite simple. I am using RS 2005. I have 2 datasets in a report, 1 is a SQL Server query and the other is a spreadsheet. Both datasets contain the same 3 fields with the same data types.

Now, where I am struggling is trying to create a single table which shows all the data from both datasets. E.g.:

Dataset 1:
Name, Index, Value
Jim, 1, 30
Bob, 3, 50

Dataset 2:
Name, Index, Value
Fred, 2, 40
Mike, 4, 60

And I want:
Name, Index, Value
Jim, 1, 30
Fred, 2, 40
Bob, 3, 50
Mike, 4, 60
Total 180

The reason for combining them in a single table is to allow them to be sorted by a common field (Index) and subtotaled (Value)

These 2 sets of data come from completely separate systems and cannot be combined before getting to the report. Is there any way of achieving this please?

Thanks,
Bob.

 

[jymm]

I do something similar with SQL using a UNION ALL and using fully quailified info on where the info is coming from (ex server.db.dbo.table)

so similar to

select Name, Index, Value
from server1.mydb.dbo.table
union all
select Name, Index, Value
from server2.mydb.dbo.table

this help or hinder?

 

[rose4567]

Just a quick note about the difference between UNION and UNION ALL.

UNION ALL will keep duplicate values in the list if any exist.

UNION will only display one value of each in your list.

So if "Jim" happens to be in both datasets and you only want to see ONE listing for Jim, use UNION.

Cheers.

 

[MonteBob]

Jymm/Rose,

Thanks for the suggestions. I have used cross-server joins before where both datasources are SQL-Server based, but in this case one of the data sources is an Excel spreadsheet. If it is possible to join to an Excel sheet from a SQL query, can you please advise me on the syntax? I have tried [DSN Name].[Sheet name].[Column name]

Cheers,
Bob.

 

[jymm]

I prefer Union All - I need the control which 'dupes' we see and which we do not - sorry if there was any confusion.

I have done this before, but using Excel like this you have to enable some features in SQL that tend to lend themselves to injection attacks and such. If this is not a concern... well - follow these instructions - I know they got me a long way before.

http://www.experts-exchange.com/Database/Reporting_/Q_22103299.html

if you can not get into experts-exchange - here is the answer Chris gave (got me a fair distance when I did similar).
----
This can be achieved entirely using T-SQL. I'm assuming you have SQL 2005.
Below is the SQL code to get the data from a spreadsheet. Note the name in square brackets is the sheetname in Excel. It could also be a named range or a cell range, e.g. [Customers$A2:X100].
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

You can also refer to individual column names if you have headings in row 1. In the second OPENROWSET argument, add 'HDR=Yes;'
SELECT Code, Name, City FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;Database=C:\test\xltest.xls', [Customers$])

NB: Ad-hoc remote queries must be enabled for this to work. Use the 'Surface Area Configuration (SAC) Tool' - it's in 'All Programs | SQL Server 2005' menu.

We can treat this OPENROWSET as though it were a SQL table and you can join on it.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$]) As XLCust
JOIN CustomerTableFromSQL SQLCust On XLCust.Code = SQLCust.Code

Now this assumes the spreadsheet name is always the same. You cannot pass variables into the OPENROWSET command. If you need to alter it you'll have to do it in dnyamic SQL. I.e. Build the whole query up in a variable and pass it through to SQL, like so;
Declare @SQL VarChar(1000)

Set @SQL = 'SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @SpreadsheetName + ''', [Customers$])'

Exec (@SQL)

This approach has potentially a lot of security issues, for instance the SQL Server Service user needs access to the Excel file. The last example opens up the possibilty for SQL injection attacks (Google it if you've never heard of it). That's not to put you off, just so you're aware and can take appropriate precautions. Post another question here if you want more info on how to prevent SQL injection attacks from Reporting Services.

This should be enough to get you going. But depending on your exact scenario things can be done differently if there are specific issues.

Cheers
Chris
---

 

[MonteBob]

Thanks Jymm. I should be able to work with that.