Summarising two separe reports in one

[leonbutler]

I am currently using CR9 and need to establish a way to summarise two reports in one main report. The two reports in question are primarily the same - i.e. one reports off a production database and the other off an archive database. The reports are identical but use a different DSN when refreshed. The reports contains performance/sales info and I need to be able to add the details from one to the other and summarise in a third report.

I have looked into performing this report in one sole report and it is possible - but only in CR8.5. Due to the changes in CR9's reporting engine multiple DSN's of this nature have to be used in separate reports (correct me if I'm wrong).

To summarise - report A runs from "Live DB" and produces table of figures. Report B runs against "Archive DB" and products figures in exactly same layout to A. Report C now needs to contain report A's figures added to report B's figures - shown in the same format as A and B.

I would simply import into Excel and use Macro's, but the report needs to be able to run on CE9 with no manipulation from the user.

Your help is much appreciated.

Leon

 

[FVTrainer]

What is your backend database? And are you using ODBC or OLEDB connectivity?

If you are using SQL Server, Oracle, or another db that supports the use of the command object in CR 9, you could create a report based on a command object, rather than a set of tables. The command object uses a sql statement. The follow example shows sql statement pulling from two separate databases, as your scenario describes. Your query would be different; I suspect it would use a UNION query to combine the data into a single table. Also, the syntax below is specific to SQL Server. Here is the sample:

SELECT c.SNAME, c.AcctName, p.FundID, p.Shares
FROM
mftp.dbo.client c
inner join mftp_test.dbo.client_portfolio_data p
on c.SNAME = p.SNAME

Note that the database qualifier is used to determine which db is used for the table.

 

[leonbutler]

Thanks for the reply....

We are currently using Progress 9.1c. To connect to this I use the packaged Merant SQL 92 ODBC drivers.

Command Objects are new to me, so I may need some further help with explaining these.

Leon Butler
Systems Analyst

 

[FVTrainer]

Well, the first thing to do is check to see if your odbc connection supports a command object. In the Visual Linking Expert, select and logon to your odbc datasource. You should see the database name listed and then below that an item called "Add Command". If you have the Add Command option, you should be able to use the command object.

Because the syntax for the command object is specific to the database and driver being used, the easiest way for you to figure how to create the correct syntax is to build a separate report using the tables you want and dropping the fields you need to use onto this sample report. Preview the report and then go to Database | Show SQL Query. This will allow you to highligh and copy the sql statement. Then create a new report and select (double-click) Add Command (under the ODBC Connection) as the databasource and paste the sql statement into the Add Command dialog box.

If you can accomplish the above and you are not familiar with UNION queries, post the initial query copied from the SQL statement and I'll be able to walk you through the rest of the way.

 

[synapsevampire]

Multiple datasources are allowed in CR 9 and suffer the same limitation to join types as did CR 8.5, so understanding JOINS and your requirements is key to the solution.

A View or SP on the databsae might be your best method, secondly would be to build the SQL (equivalent to the View) and use th Add Coomand to paste it in, otherwise a subreport migh be required depending upon how the data needs to be joined.

Example data from the tables and expected output would serve your description better than text descriptions.

-k