How to calculate from 2 different datasources

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 items
article B   3 items
article C   1 items

The subreport from the oracle database gives the following info:

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

How to combine this to something like this:

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

Any help is highly appreciated!

Have a nice day and stay safe!

Jeroen

 
jeroen_10's picture
Joined: Oct 11 2021 - 11:19pm
Last seen: 1 month 3 days ago

2 Answers:

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.

jgust's picture
2138
Joined: Jun 10 2010 - 6:39am
Last seen: 50 min ago

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-serve...

 

yama818's picture
2813
Joined: Aug 17 2018 - 3:48pm
Last seen: 16 min 25 sec ago
Feedback