Calculates between datasets. Horizontal format

LieT
edited February 11, 2022 in Analytics #1

Hi to all.
I know that this topic has been raised several times already, but for an unintelligible reason, the option available here is not suitable for me.Either I do something wrong.
Description:
There are three datasets with the same column names, but different queries (+ compound columns)
In the end, you get something like this
| device1| device2 | device3 |blank
metrics_1 | metrics_2 | metrics_3 | grand total
grand total - must be the sum of metrics from three data sets (metrics_1 + metrics_2 + metrics_3)
How correctly or using best practices to perform calculations?

Answers

  • I'm not sure I understand your example. It sounds like you want to do a union of the data and then create an aggregated sum of the values. If the data is from different tables in a single database, I recommend doing the union in the query. Otherwise, I have attached two sample reports. One is for the professional version of the Analytics Designer and the other is for open source. In both reports I created 3 data sets. The data is the same in all 3 data sets in these basic examples. In your report, the data will differ between data sets.

    For the professional version, I simply created a Union data set that combines the data from all 3 of the original data sets. The open source version does not provide a Union data set option. There is a Joint data set that can be used to join two data sets. I created a Joint data set to join the first two data sets, then I created a second Joint data set to join the third data set to the previously created Joint data set thus combining all 3. Be sure to use "Full Outer" as the join condition to make sure you get all of the rows from all of the data sets. I think this is easier than writing code.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    I'm not sure I understand your example. It sounds like you want to do a union of the data and then create an aggregated sum of the values. If the data is from different tables in a single database, I recommend doing the union in the query. Otherwise, I have attached two sample reports. One is for the professional version of the Analytics Designer and the other is for open source. In both reports I created 3 data sets. The data is the same in all 3 data sets in these basic examples. In your report, the data will differ between data sets.

    For the professional version, I simply created a Union data set that combines the data from all 3 of the original data sets. The open source version does not provide a Union data set option. There is a Joint data set that can be used to join two data sets. I created a Joint data set to join the first two data sets, then I created a second Joint data set to join the third data set to the previously created Joint data set thus combining all 3. Be sure to use "Full Outer" as the join condition to make sure you get all of the rows from all of the data sets. I think this is easier than writing code.

    Many thanks for the answer.
    1) I use BIRT for the development of the report (unfortunately I do not know the fundamental differences between Analytics Designer and BirT, but I believe that the products are similar. I`m rihgt?)
    2) I also tried this way. in this case there were two problems:
    a) the summation result is not visible or 0 whatever type I use
    b)as a result of using the "Full outer" output table is obtained three-level. and this is reflected by duplicate values in the report. I'll attach the current version below

  • jfranken
    edited June 6, 2018 #4

    This is a bit dated, but it's a good description of the difference between the open source and commercial (professional) versions. OpenText bought Actuate a couple years ago so replace "Actuate" with "OpenText" and the versions listed are not up-to-date:

    https://communities.opentext.com/forums/discussion/54527/difference-between-birt-and-actuate

    I forgot to mention an important step. I created a computed column in my Joint2 data set which equates to your Data Set 1. The computed column combines the data from all of the data sets into one column. Taking a second look, it might be better to simply sum the columns in the aggregation. I use the JavaScript Number() function to make sure all values are numbers and it also converts null to 0.

    Regarding duplicates, try joining on a unique key column. I haven't had a chance to test this.

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    This is a bit dated, but it's a good description of the difference between the open source and commercial (professional) versions. OpenText bought Actuate a couple years ago so replace "Actuate" with "OpenText" and the versions listed are not up-to-date:

    https://communities.opentext.com/forums/discussion/54527/difference-between-birt-and-actuate

    I forgot to mention an important step. I created a computed column in my Joint2 data set which equates to your Data Set 1. The computed column combines the data from all of the data sets into one column. Taking a second look, it might be better to simply sum the columns in the aggregation. I use the JavaScript Number() function to make sure all values are numbers and it also converts null to 0.

    Regarding duplicates, try joining on a unique key column. I haven't had a chance to test this.

    but what if there are 10 datasets? doing full outer for all 10? I`ts crazy for the opensource Birt :smile:

  • jfranken
    edited June 7, 2018 #6

    One great thing about BIRT is that there are many options. If the requirement changes from 3 unions to 10, you can try a different method. Here are a few suggestions:

    1. Do the union in the query. (solution limited to all data being in one database)
    2. Purchase the commercial version of the Designer.
    3. Create a scripted data set and do the unions in code.
    4. Use standard data sets and write code to generate the grand total aggregation.

    I recommend the scripted data set for the case where you have 10 different sources of data. All you would need to do is import the data to a table and create a loop to iterate over the 10 data sources. Then you can do a standard aggregation for the total.

    Warning No formatter is installed for the format ipb
  • 1) "Do the union in the query. (solution limited to all data being in one database)", but UNION ALL implies that there will be several cells in the columns (not one value for each column), but for the formation of the dataset it is necessary to have a separate column for each value (for each OFFICECODE - its own row)
    2) "Purchase the commercial version of the Designer." this option will not work, because the report itself is developed for import into BMC TSCO, which "understands" only the reports created in BiRT, but not in opentext (I checked)
    3) "Create a scripted data set and do the unions in code." maybe you have examples of this?
    "I recommend the scripted data set for the case where you have 10 different sources of data. All you would need to do is import the data to a table and create a loop to iterate over the 10 data sources. Then you can do a standard aggregation for the total." I would very much like to see an example.

    while I created Dataset-Joint-Monster by analogy as was done for 3x. However, I am almost certain that these are not the best practices :smiley:

  • I'm sorry, I'm confused as to what you are trying to do. You've provided long descriptions, but sometimes the terminology can be different in different places. Could you please provide an example of your tables and the results you want? Something like:

    Table 1
    Columns: CustomerID, CustomerName, Qty
    Sample Data: 1, OpenText, 88

    Table 2
    Columns: Date, City, Qty
    Sample Data: 2018-02-02, Toronto, 77

    Table 3
    Columns: ...
    Sample Data: ...

    Union Table
    Columns:
    Sample Data:
    Aggregation: SUM(Qty)

    Warning No formatter is installed for the format ipb
  • @jfranken said:
    I'm sorry, I'm confused as to what you are trying to do. You've provided long descriptions, but sometimes the terminology can be different in different places. Could you please provide an example of your tables and the results you want? Something like:

    Table 1
    Columns: CustomerID, CustomerName, Qty
    Sample Data: 1, OpenText, 88

    Table 2
    Columns: Date, City, Qty
    Sample Data: 2018-02-02, Toronto, 77

    Table 3
    Columns: ...
    Sample Data: ...

    Union Table
    Columns:
    Sample Data:
    Aggregation: SUM(Qty)

    Thanks to you, you already succeeded in implementing the task of combining three datacenters. But right after that, the problem of combining 10 datasets at once appeared, in order to perform various mathematical manipulations with them. each data set consists of 6 columns. The main task is to combine these 10 datasets in the most correct and simple way. Because to use full outer for three datasets this one, but for 10 it is necessary to invent other method. you indicated that you can use the scripts to combine them and I would ask you to give examples.

    below I attach the report itself, for reference.

    6.zip 34.4K
  • I apologize, I don't have time to create a scripting example today. I do have an example of doing the union in the query that I have attached. I'm not sure if it meets your requirements. It combines the data from multiple tables into one data set. You stated:

    "UNION ALL implies that there will be several cells in the columns (not one value for each column), but for the formation of the dataset it is necessary to have a separate column for each value (for each OFFICECODE - its own row)"

    I don't follow what you mean by "several cells in the columns" and "separate column for each value". The query simply combines the data from three tables into one table.

    Warning No formatter is installed for the format ipb