Working with multiple data sources 1

[ProgressiveJL]

Hi all,

I'm using CR XI and creating a report that pulls data from 2 databases; current and archive transactions. Both DBs have the same structure and the only difference is that they're located in different folders.

The report that I've made so far is able to pull data from only 1 DB with date parameters. How do make it so that the reports pulls data from both current and archive DBs based on a inputted date range.

Thanks!
JL

 

[lbass]

If you go to database->database expert->are the tables listed under the same datasource/driver? If they are, you could potentially use "Add command" where you do a union all statement, adding in parameters created with the command to each side of the union all statement. This is how I currently handle active and archived records where the tables are distinguished by different owners only.

-LB

 

[ProgressiveJL]

Thanks for the reply lbass.

The DBs I'm using are xBase and there's no "add command" options, at least I don't see it.

Any other suggestions?

 

[ProgressiveJL]

?

 

[lbass]

You need to use a union statement, so if you don't have "add command" available (it would be at the top of your table list when you go to database expert->your connection), then perhaps you could create a view.

If you don't have the option of creating a view (or stored procedure), then all you can really do is place one datasource in a subreport and link it to the main report on the parameter fields. But you won't be able to treat the records as an integrated whole for sorting, etc.

-LB

 

[ProgressiveJL]

It looks as though I'll have to use the subreport method. Do you think it's possible to somehow pass variables from the subreport to the main report in order to integrate the totals?

 

[lbass]

You can certainly use shared variables to pass subreport results to the main report for calculations with the main report figures. In the subreport, set the summary value to a shared variable, as in:

whileprintingrecords;
shared numbervar subamt := sum({table.amt});

Place this in the subreport footer. Then in the main report, you can do a calculation by referencing the shared variable--but it must be in a section below the one in which the subreport is executing, e.g.,

whileprintingrecords;
shared numbervar subamt;
sum({table.mainamt}) + subamt;

-LB

 

[ProgressiveJL]

So I've made my shared variables and successfully passing data to my main report....

Now how do I tie the groups in my main report to the subreport?

What I currently have is a count for all records with 2 groupings and my shared count variable doesn't reset on the change of the groups in my main report.

 

[lbass]

It is hard to help with so little info. If you have placed the subreport in a group section, it should be linked on the group field--then the shared variable will reflect the group level. If you then need to summarize the shared variable across groups in the main report, you need to add another variable to do it.

For more help you need to explain where the subreport is, how it is linked, and what kind of calculations you are trying to do and in what report section.

-LB

 

[ProgressiveJL]

Ahhh yess... that just proves how good you are LB... worked liked a charm!!

Well, at least my test data worked... lets see how the full implementation goes... =)

Thanks again!

 

[ProgressiveJL]

Ok, so here's the scoop...
I'm trying to summarize data from an archive DB with the current DB. There are two problems that I've noticed with my implementation thus far:
1. The shared variables don't seem to be resetting to 0 on change of GH2: Subtype

Subreport Links
- cardtype, subtype

MAIN REPORT
GH1: Cardtype
- blank
GH2: Subtype
- Location of subreport
GF2
- summary by subtype in main report + respective shared variables
GF1
- summary by cardtype in main report + respective shared variables

SUB REPORT
GH1: Cardtype
- various shared variable
eg.
Shared numberVar transCTCount;
transCTCount := Count ({TranOld01.TTIME}, {TranOld01.CARDTYPE})

GH2: Subtype
- same as GH1 except using subtype for condition

Do I need to create another subreport for Cardtypes?

 

[lbass]

The subreport is in the correct location, but it will not give you results at the group #1 level. You need to do a calculation for group #1 in the main report, by adding the shared variable there. First, you should set up a reset formula in the main report:

//{@resetshared} to be placed in GF#2b (the summaries should be in GF#2a):
whileprintingrecords;
shared numbervar transCTCount := 0;

Also create a reset for the Group #1 total to be placed in GH#1:
whileprintingrecords;
numbervar cardtypetot := 0;

Then place this formula in GF#2a:
//{@accum}:
whileprintingrecords;
shared numbervar transCTCount;
numbervar cardtypetot := cardtypetot + transCTCount;
transCTCount; //make this the last line for display purposes

Then add this formula to the GF#1:
whileprintingrecords;
numbervar cardtypetot;

-LB

 

[ProgressiveJL]

So I got my tables to work using the sample\logic you provided LB.. Many Thanks!

Now, I'm trying to create a chart and depending on which summaries I choose:
- I'm unable to use the summaries as values (don't show up in list)
- Get the message, "A print time formula that modifies variables is used in a chart of map

 

[lbass]

You should always state your ultimate goal in your initial post. Now I think you will need to read up on "charting on print-time formulas" on the Business Objects website.

-LB

 

[ProgressiveJL]

Great! Thanks for pointing me in the right direct LB. I was able to find a whitepaper on passing the print-time formulas to a subreport.

Just out of curiousity, could I have approached this a different way? (ie. Other than passing shared variables to the main report to create the table and then passing variables back to a subreport to create the chart.)

Thanks for all the assistance!

 

[lbass]

Well, I guess not--since it appears that creating a command, view, or stored procedure weren't options.

-LB

 

[ProgressiveJL]

I enlarged my test dataset which has revealed a bug in my report... =(

It looks as though groups are created based on the records from my main report. The problem is that records contained in the subreport that do not fall into one of the groups created in the main report are omitted. (Main report reference a current DB while subreport reference an archive DB)

Can anyone think of a fix or work around for this? =(

 

[synapsevampire]

You never bothered to post your database type.

You decided to go with the subreport, which was a bad idea.

LB had correctly suggested that you use a Command object, but instead left the decision to you for some reason instead of learning the database type and emphasizing that you do the right thing.

So post the database being used in future posts (and now of course), and try using a UNION ALL in a Command Object, or create the data using a VIEW on the database.

-k

-k

 

[lbass]

SV-

That would be because the OP said in an 11/7 post that "Add Command" did not appear as an option (and that the database was XBase).

-LB