Home
Analytics
Subtract two Data Set Fields
fiuman
Hi,
I have in two different data sets, two different computed fields (sum). I put them in a table in two separated lines.
Now, in the third row I want to calculate my stock. How can I do that without using SQL Query?
(with sql query I get wrong result)
Is there any function like in Excel to subtact Field1 and Field2.
=F1-F2
Thank you,
fiuman007
Find more posts tagged with
Comments
mwilliams
Hi fiuman007,
If the values are from 2 separate dataSets, you should be able to store the values in global variables and do your computations. Or, if you have a like key in both dataSets, you could do a joint dataSet on this key and create a computed column within the new dataSet.
Hope this helps.
fiuman
Hi mwilliams,
thanks for your reply.
The problem is, I dont have like key in both DataSet (Tables).
Here is the example of the DataSet I make:
DS1:
select dhl_import.weight As weight
from dhl_import
WHERE dhl_import.NL="Germany"
DS2:
select grcontractorpositionsview.weight as weight,
from grcontractorpositionsview
where grcontractorpositionsview.source="Germany"
Now I want to subtract this two weight fiels to get my stock (goods received-outgoing goods)
The easiest way would be to say in BIRT: subtract field1 und filed2
Thanks,
fiuman007
mwilliams
fiuman007,
So, both of these dataSets only return 1 value each, and you want to subtract one from the other? If so, you should be able to use a report variable either in the onFetch script of the dataSet or in the script of the actual data elements you use in your report layout, to sum up the two values. Then, in a data element below those two, you should be able to access that variable.
What version of BIRT are you using?
fiuman
<blockquote class='ipsBlockquote' data-author="mwilliams"><p>
<br />
So, both of these dataSets only return 1 value each, and you want to subtract one from the other? <br /></p></blockquote>
<br />
That is exactly what I want to do!<br />
<br />
<blockquote class='ipsBlockquote' data-author="mwilliams"><p>
<br />
If so, you should be able to use a report variable either in the onFetch script of the dataSet or in the script of the actual data elements you use in your report layout, to sum up the two values. Then, in a data element below those two, you should be able to access that variable.<br />
<br />
What version of BIRT are you using?</p></blockquote>
<br />
Do you have an example how to do that? <br />
<br />
<blockquote class='ipsBlockquote' data-author="mwilliams"><p>
What version of BIRT are you using?</p></blockquote>
<br />
I´m using BIRT with Eclipse<br />
<br />
Viewer Version : 2.5.1<br />
Engine Version: 2.5.1<br />
JRE version: 1.6.0_12<br />
<br />
Thanx a lot!<br />
<br />
fiuman007
mwilliams
fiuman007,
Take a look at the attached example. It shows the use of the report variable to add two values from two different dataSets. You can see the script to do so in the onCreate of the two data elements from the two dataSets. Let me know if you have questions.
fiuman
<blockquote class='ipsBlockquote' data-author="mwilliams"><p>fiuman007,<br />
<br />
Take a look at the attached example. It shows the use of the report variable to add two values from two different dataSets. You can see the script to do so in the onCreate of the two data elements from the two dataSets. Let me know if you have questions.</p></blockquote>
<br />
Hi mwilliams, <br />
thanks a lot. Ok, I have to create a report variable. But what I don´t understand is, where to place my script (Customercount+Employeecount)?<br />
Where can I find the "onCreate" area? <br />
fiuman007
fiuman
I made it. It works!
THANK YOU!!!!
fiuman
I have one last question: when I publish my report to the server and and generate it in the BIRT Report Viewer (Web), I can not see my calculated fields...Is there anything else I have to select/configure?
Thank you,
fiuman007
mwilliams
fiuman007,
What do you mean by not being able to see your calculated fields? The summation we worked on above doesn't display? Or something else?
fiuman
Hi mwilliams,<br />
<br />
<blockquote class='ipsBlockquote' data-author="mwilliams"><p>fiuman007,<br />
<br />
What do you mean by not being able to see your calculated fields? The summation we worked on above doesn't display? Or something else?</p></blockquote>
<br />
yes. When I run it on my local machine, it works perfect. When I copy it on the webserver and run it in the BIRT web viewer the calculation we made above don´t diplay!<br />
I can see all other fields, but don´t see the subtract calculated fields. Its strange. Maybe because I do the calculation with computed columns?<br />
<br />
Thanks, <br />
fiuman007
mwilliams
fiuman007,
Deploy the TestReport.rptdesign I posted above and see if it works when deployed. Let me know.
fiuman
It works with your report...
mwilliams
Hmm...can you post your report in here so I can open it and look at it?
fiuman
is attached...
it is the 4th line SUMME - BES, display on local machine, don´t display on web viewer.
thanx.
mwilliams
I don't see anything wrong. Can you try downloading the latest version of the web viewer example, version 2.5.2 and see if it does anything different? If not, I'll have you try not using the report variables and use persistentGlobalVariables instead to see if it works.
fiuman
Hi mwilliams,
can you see the calculated fields on the web viewer?
I think there is some problems with the computed columns (my first two fields) ...
I will try to run an update.
Thank you,
fiuman007
mwilliams
fiuman007,
I won't be able to run your report if you don't use a flat file or xml or the sample database. You said you were seeing the one from the sample database that I made. It was very simple though. If you can recreate the issue with the sample database or a flat file, that'd be great. Then, I could try it out.
