Need to combine data from two different sources on a single report 1

[TA77]

The report has a total of 7 columns, 6 of the colums are pulling from the same data source . The last column will need to use the ID (primary key) pulled from the 1st query to against a different data mart (data source) to pull another piece of information. (therefore a different database connection info)

How do I accomplish this?

I can't use a sequential report since there are on 2 differnt databases, right?

How do i use MulitipleInput Filter? How do I override the fetch method?

Please provide detailed explanation

Thanks a lot

 

[bcaslow]

No, you CAN use a Sequential Section(s) with different data sources (all the reports I am working on now do this). How many records are you looking at returning; do you have any estimates? Since you only need to essentially 'append' the one column to the records returned from your first query, you may be able to return all the records from your second data source, store them in a memory buffer, and just loop through the buffer to match them up. This will be inefficient if your talking thousands of records, though. Actuate can do it, but performance will be an issue.

Let me know an estimate of number of records, and we can go from there.


Bill

 

[TA77]

The number of records could run into thousands..

Essentially, what am trying to do is this --

The report has majority of the information coming a table that resides in a different database. The information needed on the last column (Z) is located on a seperate database.

One of the records returned in the 1st query is a Group ID number. The value in this field will be used against the second table (in a differnt database) in order to retrieve the field needed -- Channel Name.

Keep in mind, two two fields are named differently but contain the same value. i.e the Group ID is labelled Group ID in first table, but the second table it's called Customer Number. But the values are the same

Thanks

 

[bcaslow]

Ok... is it possible, then, for you to have two queries, each ordered similarly, and then loop through matching up the records?

Or, do you need the results from query 1 in order to run query 2?

Actuate can take one record from a recordset at a time, pass a value to a second query, and hit the database; however, I'm sure you can the enormous overhead here.

If you can't simply have the two queries run, and then match them up in a MIF, I would look into doing it on the database side, either with a Union (if possible), or using cursors. But, if you can bring in both queries to match up and need some help on the logic for that, let me know.

 

[TA77]

Yes, I need the results from query 1 in order to run query 2. Query 2 will be pulling values based on the IDs retrivived in query 1.

Unfortunately, I can't do a union on the database side.

I would need a lot of help on how to make the two queries to match and then use MIF..

When i built the Input filters, each filter has it's own database connection. Is this ok?

Also, can I email you my rol and rod file?

 

[bcaslow]

I'm getting hammered today with work, so it could be awhile before I could look at anything. I'm not sure it would help that much for me to look at the design/library.

Let me ask this -- because, with Actuate, if you can do it on the database side rather than in Actuate, you'll be better of performance-wise:

Are the databases the same (ie, SQL Server)?

Are they in the same datbase server?

For a SQL server to reference other SQL server, you have to create a link. But within the same server, you don't need to.

 

[TA77]

The databases reside on two different servers. But they are both UDB databases..

How do I create a link between the two DBs??

 

[bcaslow]

I had to look this up as i haven't done much of anything with DB2 databases! This comes from a post on devshed.com:

I have worked with both Oracle and DB2 (OS/390). I am assuming that you mean DB2 for OS/390. If not, I will cover that below also.

Let me qualify my answer. This discussion assumes that you are not talking about a DB2 Data Sharing environment.

In DB2, you can have multiple databases in a DB2 subsystem.
Each DB2 subsystem has a DB2 Catalog (equivalent to an Oracle Data Dictionary). So, if you are within one DB2 subsystem, and therefore, using the same DB2 catalog, you only need to access an object by owner.objectname (this assumes you have proper authorization).

This means that no matter what database the object is in within a given DB2 subsystem, you can access it without the equivalent of a database link in Oracle because all objects in the same DB2 subsystem are defined in one DB2 catalog. This also means that owner.objectname must be unique within a DB2 subsystem since there is one DB2 catalog.

Now, if you want to access another DB2 object that resides in a different DB2 subsystem, then you would need to use DDF (Distributed Data Facility) to access this object.

There are some catalog tables that are used by DDF to store info regarding the other DB2 subsystems that can be accessed. Assuming that all required information is defined to DDF and the DB2 subsystems are connected by either SNA or TCP/IP, then you need to access your table by location.owner.objectame. (location name is a value defined to DDF to uniquely define that DB2 subsystem).
Since most people find this cumbersome, you can make this easier for the user by defining a DB2 ALIAS. This is somewhat like a synonym. So, instead of a three-part name, you can shortened this down to a 2-part name.
Here is an example:
For a table named SCOTT.TABLEA that resides in another DB2 subsystem whose location name is LOCATIONX you can define
an ALIAS as follows:

GRANT CREATEALIAS TO USERA;
SET CURRENT SQLID = 'USERA'; (COMMENT: this switches the current user to USERA)

CREATE ALIAS USERA.TABLEA FOR LOCATIONX.SCOTT.TABLEA;

Note that USERA can be anything. It does NOT have to be a user as you have in Oracle. You can use something descriptive such as PRODSYSTEM, so you can have the alias be PRODSYSTEM.TABLEA.

Once the alias is created, the user can say
SELECT * FROM USERA.TABLEA;

Now, if you are referring to DB2 on the Windows, Unix, Linux, OS/2 platforms, then the method is very similar.
You can define what is known as a nickname in the DB2 catalog.
This nickname is equivalent to the alias we just mentioned.
The nickname is an alias for a 3-part name in UDB.
The 3-part name is server.owner.objectname.
Again, there are some catalog tables that need to have information, but once defined, you can access your data using a nickname.

So, for DB2 for OS/390 the equivalent is an alias.
For DB2 UDB for Windows, Unix, Linux, and OS/2 it is the nickname.



Hope this helps.

 

[TA77]

Bcaslow,

Can i use a Subreport to create a single report that pulls data from 2 differnt databases that reside on seperate servers?

If so, how?

 

[bcaslow]

If I understand what you're wanting to do initially, then yes, you'll need a subreport. Your structure should be something like:

Report (root)

- RptSection1
- Query1 (returns your 6 columns)
- DataRow1

- Content: rptSection2
- Query2 (pass in parm to modify your WhereClause)
- DataRow2
- Content: (frame or group section)

You'll need to snag the column from Query1 that you need to use as a parameter in Query2 -- as well as the rest of your columns, since you'll need them for the rest of the final output -- use Static variables, and set them in the OnRow( ).

In Query 2 override the ObtainSelectStatement's WhereClause to plug in the static variable from query1. Create additional variable on DataRow2 that will hold the other static variables populated by Query1.

Each record returned from the outer query will be passed to the inner report which will have to hit the database. That's why there will be alot of overhead: ht the database, get record set; pass one record, pass variables and hit the database; get a record, pass, hit database....

Sorry for lack of details, but I hope this is enough to get you going!


Bill