Creating Report using same tables and fields from different databases.

[babcockc]

We have 11 facilities. Each facility has its own database (on same server) and each database is exactly the same in terms of tables, fields, etc. Only the data is different.

I am trying to create one single report to pull data from the same table in each database.

For example, each database has physician information such as name, physician code, address, etc. I want to be able to run one report, pull all this information from each database and then sort it by facility.

The user does not want individual reports for each facility, thus I don't think using Subreports will work...she wants to see it all on one report.

Any suggestions?

I am very new to Crystal, so please bear with me.

Thanks.

 

[CoSpringsGuy]

What version of Crystal are you using?

 

[CoSpringsGuy]

The reason I ask is because I am only familiar with XI and occasionally I see posts on this forum that differ slightly from version to version:

It shouldnt be a problem at all. create the connections to each database in the database expert and add the corresponding tables from each database. Be sure you read any of the message boxes that pop up to ensure those issues displayed wont cause you problems.

Couple things I experienced:

You will notice when you add the new tables that they will have the same name as the first but appended with a _1, _2 and so on. For ease of report creation, rename those tables to something that will key you to which database it is coming from. Otherwise things get confusing.

Pay attention to your linked fields in the database expert. Probably wont cause an issue but depending on what you are trying to do you may have to change the defaults. For example I have two exact databases I pull from but one of the indexed fileds is user_id. Most of the user_ids are unique to each database but the database expert tries to automatically link those. The result of course is only records from the database that the sames users exist in. May be a good idea to turn off the auto link function and map those links yourself.

The other thing to be aware of is performance. Now I am not even close to an expert regarding server side grouping and SQL processing but I believe that when you use this scenario, you get much less server side performance than when working with just one database. Someone else with more knowledge can chime in on this. If I am right about that then if your databases are very large, or your report formulas and formattiing is substantial, you may run into some significant performance issues.

 

[lbass]

Please identify your version. A solution would be to use a command as your datasource and use a union all to combine the multiple databases into one query.

-LB

 

[babcockc]

I'm using XI Developer...for those asking.

BTW, I was able to play with this a bit and seem to have found a solution to my problem.

I am ending up using subreports, one for each facility, and just hardcoding the facility name into each report. Basically I created a "facility" column and placed the name in the Details. This way, for each subreport, there is a column for the facility with the facility name.

Now, when the user runs the report and exports it to Excel, they are getting all records from all 11 databases and can then sort by Facility Name.

I think this will work for the end user.

If not, I will be sure to post a follow up question.

Thanks to all those that replied.

 

[lbass]

I think a command with a union all would give you a speedier report, with more flexibility, so let me know if you want to give that a try.

-LB

 

[babcockc]

lbass,

If you think that will be better, I'm all ears. As I mentioned before, I'm a newbie so I'm not quite sure what is involved in that or how you can help me posting here, but I'm all for trying.

FWIW, I am very technical...just new to Crystal so I should be able to understand what you are saying...LOL.

 

[Madawc]

Use the SEARCH option at the top of the thread to get information about UNION.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

The easiest approach would be to set up a report with the tables you want, and place the desired fields on the report canvas. Then go to database->show SQL query and copy the query into a new report then add "union all" and then paste the query again and keep repeating until you have 11 queries linked by "union all". Then go into each query segment and change the owner (I'm assuming that's how you are distinguishing each set). You also can create a field (shown first below) that will allow you to distinguish the origin of each database. So it looks something like this (using Oracle punctuation, etc.):

select 'data1' "type", "table1"."field1", "table2"."field1"
from "owner1"."table1" "table1" inner join "owner1"."table2" "table2" on
"table1"."field1" = "table2"."field1"
union all
select 'data2' "type", "table1"."field1", "table2"."field1"
from "owner2"."table1" "table1" inner join "owner2"."table2" "table2" on
"table1"."field1" = "table2"."field1"
union all
select 'data3' "type", "table1"."field1", "table2"."field1"
from "owner3"."table1" "table1" inner join "owner3"."table2" "table2" on
"table1"."field1" = "table2"."field1"

//etc.

Use the syntax/punctuation that is used in the show SQL query.

Once you have the query working, you could group on {command.type} to organize the results by facility, etc., but you will also be able to do calculations easily across facilities.

I now see that there are two different posters in this thread--are you the same person?

-LB