Union multiple data sources from different data connections?

[jamesiv]

Ok I have Crystal Reports Developer XI and we are using a Business Objects Enterprise XI R2 environment with Infoview for our reporting needs.

My problem is the majority of our sales and other data is stored in a CRM package Saleslogix which I can easily report out of using Crystal. The problem is two of our products are not tracked in this CRM and are actually stored in an old ERP type system and the data is currently exported to Excel for business users to access. This means my reports currently only report on CRM based data and I am trying to figure out the Best way to add in the other Excel based sales information.

I know the best solution is to build a data warehouse where I can create a centralized database for storage and reporting but that is not the best solution for us right now. I just need a way to UNION the excel data along with my CRM stuff to get one dataset to report on. I have started to play with Business Views but not sure the best way to approach this. Any advice from anyone would be fantastic!

 

[synapsevampire]

A more flexible approach would be to create an Access database and LINK (not import) all of the data sources int here, then buildout a UNION ALL query in Access as the source for the report.

A quick and dirty datamart.

-k

 

[jamesiv]

Yeah ok I have never performed a Link in Access but I'm sure that is easy enough to figure out. I guess I would have to make sure my data types match from data source A and B since that is required for a union to work.

I thought that the Business View or other BOXI type tools might allow this but I guess I have a new option if need be going the Access route.

I was also going to maybe try and use a DTS to import the Excel data into SQL and then just do a union in the Command area within the report. Just didn't know if I could cross databases or not.

 

[synapsevampire]

Yeah, using DTS to import it would be optimal.

But if you've done that, why not just create a View in SQL Server to do the UNION?

-k

 

[jamesiv]

Well these databases would be on different servers. I cannot remember if I can do a union in a view or whatever that spans over different servers or not?

Example:

Server A:
Database SLX

Server B:
Database EXCEL_IMPORT_DATA

 

[synapsevampire]

Checkout:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38531

-k

 

[jamesiv]

Thanks for your help!!

 

[jamesiv]

Just thought of another Idea.

What if I create a Temp Table using a SPROC and load it with the select from database 1 and then just insert records to this temptable from select 2 which would act like a union.

I have done this before after selecting my first data set into the temp table from Database and Server 1 I can change the server and DB connection in the SPROC then insert data set two into the temptable. In the end I just select all from the temptable and I should have a dataset that has acted like a UNION.

 

[synapsevampire]

Yep, same net effect.

Not sure why you'd prefer a temp table to a UNION within the SP though,I'dthink the performance similar, in fact I'd think the UNION ALL to prove faster.

-k