Percentage calculation between cross tabs

Hello I am working with BIRT since 4 months (I am a business analyst and I do not code) and this forum has been very inspriring and helpful - thank you. However, this time, I did not find the solution for my problem which is the following. I need to create a report giving - per department - the percentage of people within the departement who have followed a traning. To do so, I am working with two dataset presented in cross-tabs:<ul class='bbc'><li>learning history</li><li>total population</li></ul> As the two datasets are different ones, I am calculating the percentage by putting in the script on "on create" the following reportContext.setGlobalVariable("Fvorming",this.getValue()); By doing so, I have only one record coming back as percentage, while I would like to be able to get as many values as there are rows in my cross-tab. Is there a way to do so. Thank you in advance for your help SD

Find more posts tagged with

Comments

kclark

It sounds like you're going to need to put the values in an ArrayList if you want all of them. In the initialize() of you're report you should do something like this.<pre class="_prettyXprint">var array = new java.util.ArrayList();reportContext.setPersistentGlobalVariable("array", array);</pre>Then in the onCreate() of the summary in your crosstab you'd add each value<pre class="_prettyXprint">var array = reportContext.getPersistentGlobalVariable("array")array.add(this.getValue());reportContext.setPersistentGlobalVariable("array", array);</pre>Now you can recall all of the values from that crosstab by using that array. You would use something like<pre class="_prettyXprint">var array = reportContext.getPersistentGlobalVariable("array")this.text = array.get(n);</pre>To get each value. If you can post your rptdesign or one similar to what you're doing with the sample db I can make an example for you.

SDBIRT

Hello KClark, I tried but I have the following error:+ A BIRT exception occurred: Error evaluating Javascript expression. Script engine error: Wrapped java.lang.IndexOutOfBoundsException: Index: 38, Size: 38 (#1)
Script source: , line: 0, text:
var array = reportContext.getPersistentGlobalVariable("Dienst")
this.text = array.get(38);. See next exception for more information.
Error evaluating Javascript expression. Script engine error: Wrapped java.lang.IndexOutOfBoundsException: Index: 38, Size: 38 (#1)
Script source: , line: 0, text:
var array = reportContext.getPersistentGlobalVariable("Dienst")
this.text = array.get(38); How can I say to the array, look for the values that exists and return all of them even if I do not know in advance how many departements I will have in my cross tab about learning history (how can I ask the cross-tab to show all the deaprtments even if employees from this departement did not follow a learning? I put my design in attachment - but have no idea how i can add a sample of the DB.Sophie

kclark

If you want to display all of the values in the array it would look something like this<pre class="_prettyXprint">var array = reportContext.getPersistentGlobalVariable("Dienst")for(i=0;i<array.size();i++) { this.text = this.text + "n + array.get(i);}</pre>What is the value of the employee if they did not take the learning, 0? If you click on your crosstab > property editor > empty rows/columns, at the bottom you can so a value if that cell is empty.

SDBIRT

Hi kclark I managed to have two tables with the same amount of rows in my learning history and my complete population. I tried to used the array above. I added a Grid and in one of the two columns, I added the array on "On create". But I am getting the following error message <div>Data (id = 2147):</div><div>+ Unhandled exception when executing script</div><div> </div><div>What am I doing wrong, how may I display the retrieved data?</div>

SDBIRT

I have also another question - I am using a report parameter (we are working with two entities) and the user can chose to display all results or only the ones linked to his entity. How can I make sure that the learning history table and the total population are always showing all the department names even if the are not linked to the entity chosen by the user (line should be filled in with "0" Thx!

kclark

Can you try array.get(37) and tell me if that works? You should be able to show empty rows in your crosstab from property editor > empty rows/columns. At the bottom you can give it a value to display for empty cells.