Combining Tables 1

[sjdbmc]

Our system for handling jobs has various tables associated with live jobs.

When a job is invoiced it is moved to history and the job is now stored in identically structured tables but with different names.

When reporting I have to write 2 reports to get information i.e current & history.

Is it possible for Crystal to merge tables and for a report to query the result?

 

[Turkbear]

Hi,
You could use an Unlinked subreport in the Report Footer of the Current report.
Or, group on some common field ( Maybe a company or other # ) and insert a linked subreport in the Footer of that Group- That way the Current info for that Group identifier is in the details and any historical data would be in the sub..


Without knowing more about your data, that's as good as I can do..How would the tables be merged?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[davidwendelken]

I would suggest that you create a view for each current/historical table pair if you think you will have a lot of reports.

That way, each report will be easier to build.

If you are using Oracle as a back end, I'll be glad to post a SQL script that can be used to build the views from the data dictionary inside of Oracle.





David Wendelken

http://www.casetech.net

 

[sjdbmc]

Example

Table 1 called order_line
Table 2 called c_order_line

Structures are identical with order_line storing live jobs & c_order_line storing historical jobs.

Can these 2 data sources be merged into 1 data source which can then be reported on. We use subreports at the minute but we would like combined results.

Hope this is clear.

 

[davidwendelken]

Sorry, I wasn't clear. A "view" is a pre-defined query inside the database.

In SQL terms in the database, you need the following:

create view all_order_line as
select column1, column2, column3
from order_line
union all
select column1, column2, column3
from c_order_line
/

Obviously, I don't know the names of your columns in your table, so I made up some names for them.

With a view like this defined in the database, you can just write your report in CR by using all_order_line as the data source. The data in both order_line and c_order_line will be merged into one set of data and returned to CR for use.

Why join the data in the database instead of in CR? Because if you have lots of different reports to write, the overall amount of work you will have to do is less.

What type of database are you connecting to? Oracle, Access, SQL Server?

I'm assuming that you have an order and c_order table as well. Maybe others? I use Oracle for my database and can supply a script that would (with small modifications on your part) build the create view statements for you. It's handy, particularly if you add or remove columns from the order tables.







David Wendelken

http://www.casetech.net

 

[sjdbmc]

SQL Server

What you have described is exactly what we need and I'll speak to our IT guy about getting it set up.

Many Thanks

Stuart

 

[synapsevampire]

You can also do this from within Crystal itself and have it pass to the database, but that depends upon your version of Crystal, something which should accompany every post.

-k

 

[davidwendelken]

I know that the version of CR I'm learning (v10) can certainly handle it.

I suggested database views simply because this tends to be the type of activity/subject that lots of reports get written about, and, if so, it would be cheaper to use database views and thereby code simpler reports.

David Wendelken

http://www.casetech.net

 

[synapsevampire]

Exactly what I suggest too, David.

My reasoning is primarily maintenance and reusability, however some people can't create data objects, hence the alternative.

In CR 8.X you can manually append in the UNION ALL
etc... into the Database->Show SQL Query

Not the best solution, but it does work.

Of course with CR 9 and up you can just use the Add Command.

-k