Apply Aggregation on Dynamic Text Value in Birt CrossTab

prince_iam
edited February 11, 2022 in Analytics #1

Dear Fellows,

Could you please help me in resolving an issue I am facing since quite a long, I am using a CrossTab to develop a matrix report, I have applied aggregation on column area but the dynamically calculated value has been skipped by Birt Aggregation Builder. Can someone help to apply aggregation on Birt Dynamic Text Field or Data Item.

Comments

  • Could you please post the rptdesign file or a sample report that has the same behavior? I want to try to replicate the issue.

    Also, what is the reason you are using Dynamic Text elements?

    Warning No formatter is installed for the format ipb
  • Dear Jeff,
    I have attached the rptdesign file. Actually, the reason of using dynamic text item to crosstab is that I want to display the number of days machine line remain idle (no production) in a particular month. Since, so suppose for the month of january machine line remain idle thus database has no entry for that particular month it means machine line remain idle for the complete month i.e. 31 days, so I have wrote an script for measure["IdleDays"] if it is null the corresponding column of idle days should display machine remain idle for the number of days in that particular month. at the row area I want to display the total number of days a particular machine line remain idle for the past three months. I am using an aggregation builder which is only applying sum on data items which are directly coming from database and skipping the dynamic text value that has been calculated on the basis of logic I explained before.

  • Dear Jeff,
    For your convenience, I have designed a simple report with the desired output. I just want to display value 31 if the measure has null data and finally want to add the 31 to the total at the bottom row area of crosstab.

  • Continuing support I have attached the query result from the database since you can see that for the month of January line 2 has no any entry in the database thus I cannot calculate production working or idle days at query level whereas I can handle this situation at script/program level at crosstab.

  • I also want Dear @mwilliams to please contribute.

  • Thank you for providing the reports, descriptions, and screenshots. Unfortunately Michael Williams hasn't posted for several years. It would be great to get his answer. I've run several tests and I don't see a way to aggregate on the Dynamic Text element values.

    I found suggestions for older questions related to modifying the source data for the cube (i.e. adding a computed column or doing a union of the data), but that isn't an option in this case because you are creating values where data doesn't exist. The only possibility I came up with along those lines is to add a scripted data set. Retrieve and loop through the existing data, add logic to insert the missing data, and use the scripted data set as the source for the cube.

    Another idea is to add code in the onRender event of the Data element for the cross tab measure to replace the null values with data. I tried:

    if (this.getValue() == null) {
      this.setDisplayValue(31);
    }
    

    It didn't work. I believe the issue is that setDisplayValue() requires an IDataItemInstance object, but it is not instantiated when the cell data is null. It might be possible to create the object if it doesn't exist and then set its value. I haven't had time to try it. Even if this works for the measures, I'm not sure that the aggregations would include the values added in code.

    I will try a few more things and let you know the results. If you get it to work, please post your solution.

    Warning No formatter is installed for the format ipb
  • Dear Jeff,

    Thank you for the reply, actually I have also tried the script you mentioned above but this script only displayValue in that particular cell of crosstab and we cannot apply aggregation over this. I have tried so many ways to deal with this situation but unfortunately couldn't succeed. I have also gone through many online forums and posted this question but no success has resulted so far. I would be grateful if you could please spare some time over this and suggest a workable solution.

  • Dear Jeff,
    Please try using the quoted below solution you were talking about. Since I am very new to Birt and couldn't comprehend.

    "I believe the issue is that setDisplayValue() requires an IDataItemInstance object, but it is not instantiated when the cell data is null. It might be possible to create the object if it doesn't exist and then set its value. I haven't had time to try it. Even if this works for the measures, I'm not sure that the aggregations would include the values added in code."

  • I haven't had a chance to look at wwilliams' example, but I wanted to respond to the question from the previous post about setDisplayValue().

    I got the setDisplayValue() code working by using a Number object as the method argument instead of a primitive value. Unfortunately, the display value that appears in the cell does not get added to the aggregations (row and column totals). However, the aggregations also have a setDisplayValue() method, so you should be able to get the aggregation values, modify them, and display the modified value (i.e. total + 31).

    Warning No formatter is installed for the format ipb
  • Dear Jeff,

    setDisplayValue() method only hardcode the 31 to the total. Whereas, I want to add the values dynamically corresponding to the total number of days in that particular month to the idle days and finally to the total. Could you please provide me a workable solution as I have already attached my report and looking for a workable solution. Consider, I am very new to birt.

  • @wwilliams said:
    Maybe this will help.

    Dear Williams,

    I couldn't get your solution. Could you please elaborate.

  • This should supply the missing dates

  • Dear Willams,
    Could you please go through my posted discussion question. I just want to add the measure dynamically displayed to the total. My requirement is quite simple. Please go through my discussion question. I appreciate your time and efforts.

  • jfranken
    edited May 8, 2019 #16

    Attached is an example showing how to modify the values in code. It turned out to be more complicated than I expected. I couldn't find a way to get the row and column numbers for a cell, so I wrote my own code to generate the values. There is code in the onRender event of each column that is highlighted in a color. The table shows the raw data. Line 1 has no data for Period 3. Line 3 has no data for Period 2. Line 4 has no data for Period 1. The code is filling in those three values.

    In general, the cross tab cell events trigger in left-to-right and top-to-bottom order like reading a book. Most of the code is in the green (measure) cell. The code checks for a null value and replaces it with the number of days in the month. The modifications are saved to the corresponding row and column. In the "total" cells, the saved values are retrieved and a new total is displayed.

    I still haven't had time to look at the example from wwilliams. From the description, it sounds like he is showing how to create the missing data. That might be an easier option than the attached example.

    Warning No formatter is installed for the format ipb
  • Dear Jeff,

    Thank you for providing the solution, but I am still unable to get it running since I am using Eclipese Nexon Birt Report Designer Environment and the solution provided probably has been developed in OpenText Analytics Designer.
    I am getting the following errors when compiling/running the report. Could you please help me to fix the issues considering my development work environment.
    "Errors in Report
    Line 2: The library with the namespace "ThemesReportItems31" is not found.
    Line 2: The theme "Modern" used by Report Design is not found.
    Line 17: The element ODA Data Source("Data Source") has an invalid manifest.
    Line 20: The element ODA Data Set("idle") has an invalid manifest."

  • Dear Jeff,
    I am thankful to you for providing a workable solution, somehow I managed to convert and run the provided report to Eclipse Birt Environment. There are few minor corrections as I want to make this report complete dynamically for any period of time (months-years). When I run the report for the period (January 2019 - March 2019) the report displayed absolutely correct data. Whereas, When I run the report for the period (April 2018 - June 2018) report displayed the correct result until Line#4 after Line#4 results were incorrect. I couldn't understand this behaviour of the report since I want to make it completely dynamic.

    Result for the period (January 2019 - March 2019)

    Result for the period (April 2018 - June 2018)

  • Dear Jeff,

    One more thing to mention here that report displaying the correct result for the same period i.e. (January 2018 - March 2018) absolutely correct. What I can infer from here that if we provide the time period between January & March of any year the report displaying correct result. I think now we are very close to the trivial solution I would need you little more efforts to get things done completely dynamic. It is worth to mention your kind support and generous response. Thank you in anticipation.

    Result for the period (January 2018 - March 2018)

  • Dear Jeff,
    I would be glad if you could also make the year variable dynamic. I can extract a year from the database for a provided period.

  • Dear Jeff,
    I would be glad if you could also make the year variable dynamic. I can extract a year from the database for a provided period.

  • I developed the example in one of the OpenText pro designers. I meant to convert to OS Birt before posting, but I forgot. I apologize and I'm glad you were able to figure out how to run it.

    I found the reason why some months aren't working. There is a bug in the code. When there are multiple null values in a row or column, the code is concatenating the days instead of adding the cell values. To fix it, make the following two changes to the code in the onRender event of the green (measure) element:

    1. Change:
      addToRow += days;

    To:
    addToRow = Number(addToRow) + Number(days);

    1. Change:
      addToCol += days;

    To:
    addToCol = Number(addToCol) + Number(days);

    Regarding your question about making the year dynamic, the screenshot you posted shows a cross tab group named "PROD_MONTH". The dates are formatted as "APR-18" in your screenshot. Save that row value like I did in the onRender event of the blue (Month) element in my example. In the green element's code, get the saved row value and parse the month and the year using standard JavaScript. Then use those values as arguments in:

    new Date(year, month, 0).getDate();

    Warning No formatter is installed for the format ipb
  • Dear Jeff,

    I am really thankful and glad for the kind support you extended. I feel lucky to be a member of OpenText Forum. Following your suggestion, I have made minor changes in code and now results are absolutely correct.
    Taking your last point regarding saving year value, could you please just confirm that I should use the same variable row on render event of Month (blue) group column or I may create another variable as I have done in the code snippet below.

    Declaration in Month Group:
    "
    reportContext.setPersistentGlobalVariable("row", this.getValue().toString());
    reportContext.setPersistentGlobalVariable("yearv", this.getValue().toString());
    "
    Access at Measure(greeen) element:

    "
    var row = reportContext.getPersistentGlobalVariable("row"); // get row
    var yearv = reportContext.getPersistentGlobalVariable("yearv"); // get year
    var month = row;
    var year = yearv;
    "
    Just let me correct if these code snipets are correct to dynamically get month and year value from group dimension. Thank you in anticipation.

  • Thank you for the kind words.

    I think your code for the dynamic year is correct. Basically, the year will be part of the data in the group cell for the row (the category on the left). In your screenshots from previous posts, the PROD-MONTH cell has the year value. The data looks like:

    JAN-18
    FEB-18
    MAR-18

    If the value is saved to a global variable when the group cell is created, you can get the variable value when the measure (green) cell is created. The year is needed in the measure to calculate the number of days in the month.

    If the data is in the form "JAN-18", the year can be parsed using standard JavaScript. For example, in the PROD_MONTH cell onRender event:

    date1 = this.getValue(); // this should get the date formatted as "JAN-18" when the first row is created
    dlen = date1.length;
    year1 = "20" + date1.substr(dlen - 2, dlen); // convert "JAN-18" to "2018"
    reportContext.getPersistentGlobalVariable("yearv", year1);

    The bottom line is that the year needs to be accessible in the measure cell in order to calculate the days in the month. You can use any method to get the year that works.

    Warning No formatter is installed for the format ipb
  • Dear Jeff,

    Thanks for the continuous support you rendered. I am really impressed with the way you entertain discussion questions on this forum.