How to calculate from 2 different datasources

[jeroen_10]

Hi All,

I want to create a report which calculates with the data from 2 different datasource:

1. mysql database contains article_id and article_count
2. oracle database contains article_id and article_price

The subreport from the mysql database produces the following:

article A   5 itemsarticle B   3 itemsarticle C   1 items

The subreport from the oracle database gives the following info:

article A   € 2,91 article B   € 1,25article C   € 0,50

How to combine this to something like this:

article A  5x € 2,91  € 14,55article B  3x € 1,25  €  3,75article C  1x € 0,50  €  0,50Total costs           € 18,80

Any help is highly appreciated!

Have a nice day and stay safe!

Jeroen

 

 

 

[jgust]

Table, Crosstab, List, and Subreport all support returning values back to the main report using the "Return Values" button.  That button can be found on the dataset tab of the table, crosstab, or list object.  For the subreport it is located on the subreport tab.

I've only used subreports due to my company's design requirements.  I suspect the other objects would function the same. 

 

Here are my notes:

Pass Variables from Subreport Back to the Main Report (Shared Variables)

Crystal
When you declare your variable as SHARED you are then able to pass that variable to subreports or the main report.  To use it in the main report or subreport you simply need to redeclare it and the value will be available.
SHARED NumberVar shStuCount;

shStuCount := DistinctCount({D_STU.STU_UNQ_ID}, {D_STU.LEA_ID});

Jasper
This ability is accomplished by using the “Edit Return Values” on the subreport.  This allows for the main and subreport to talk to each over through the use of the specified variable.

1. Click the add, edit, delete button to manage the shared variables
2. Add the subreport variable that you want to pass to the main report
3. Add the main report variable that you want to pass the variable to

Add a calculation type that you would like the shared variable to have.  If you are just passing a value back and for the then “No Calculation Function” should be used.  In the example above the vSubRPTVariable/vMainRPTVariable is acting as a running total between the main and subreport.

[yama818]

If you are using the 'Commercial Edition', I think there is another solution.
That is to use 'Virtual Data Sources'.
It has the disadvantage of degrading performance and limiting the SQL you can write, but it allows you to reference different databases at the same time.

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-administrator-guide/v790/virtual-data-sources