Calculate ratio in crosstab?

ccoutsouve
edited February 11, 2022 in Analytics #1

Hello all

We just started using BIRT reporting and i cannot figure out how to do the following.

We have a simple crosstab of:

Category Totals
A 45
B 15

All we want to do is have it calculate the ratio of A:B under the totals. Is this possible within a crosstab, if yes how do we do it?

Comments

  • jfranken
    edited October 2, 2018 #2

    This seems like a simple request- divide two numbers. However, the crosstab aggregation is not designed for this type of functionality. There can be more than two rows in a crosstab and the calculation you want to do is only defined when there are exactly two rows. Therefore, the standard aggregation functions will not work. You need to write your own code to do the calculation.

    Attached is an example created in Birt 4.8. The code to calculate the ratio is in a computed column in the data set. The ratio is saved to a global variable. To display the ratio, insert a Data element that gets the global variable value. The example shows a table element, but the same procedure can be used for a crosstab. Add a grand total for the rows, then replace the aggregation with the global variable value.

    Warning No formatter is installed for the format ipb
  • My cross tab will always have 2 rows - does that help?

  • That makes the code easier, but you still need to write custom code like in my example because the aggregations are not designed for a fixed, two row scenario. I forgot to mention in my previous message that the example is designed to display 0 for the ratio if the row values are not valid. I did minimal testing, so please test carefully prior to using the code in a production environment.

    Warning No formatter is installed for the format ipb
  • jfranken
    edited October 2, 2018 #5

    Here is a better example for your use-case. It shows how to create a ratio in a crosstab where the crosstab has two categories and two rows of data. (The "A" and "B" values are determined by the Data Set sort order.)

    Warning No formatter is installed for the format ipb