Dynamic Grouping of crosstab in BIRT

Options
Mounica
edited February 11, 2022 in Analytics #1

I have a crosstab which have 3 groups. a)Date b)Code c)count of orders(aggregation -sum) .
I have 2 report parameters 'start_date' and 'end_date'.
Based on my input I need to calculate the difference between the dates
example : var diff = start_date - end_date --------- which gives the no of days.
if(diff <15)
Group the "Date group" to display as dates
if(diff >15)
{
Group the "Date group" to display as weeks
{ if(diff > 30 )
Group the "Date group" to display as months
{ if(diff>365)
Group the "Date group" to display as year
}
}

Example : start_date : 2016-10-10 ------------- Group by week
end_date : 2016-11-05
var diff = 25 days

display :
Code1 Code2 Code3
2016-10-10 : 5 6 8
2016-10-17: 4 10 6
2016-10-23: 8 5 9

Example : start_date : 2016-10-10 ------------- Group by month
end_date : 2016-12-10
var diff = 60 days

display :
Code1 Code2 Code3
2016-10-10 : 5 6 8
2016-11-10: 4 10 6
2016-12-10: 8 5 9

Example : start_date : 2016-10-10 ------------- Group by day
end_date : 2016-10-15
var diff = 5 days

display :
Code1 Code2 Code3
2016-10-10 : 5 6 8
2016-10-11: 4 10 6
2016-10-12: 8 5 9
2016-10-13: 6 7 3

In the above fashion I need to generate my output based on the no of days.(grouping them)
For crosstab I came to know we need to include the script in onCreate(), onPrepare() etc.
Can anyone please guide me through this scripting or if i can directly edit in the report for the crosstab paramaerts else any link which I can refer.
Thanks in Advance

Comments

  • Have you thought about having 3 cross tabs using the same data-set and just hiding two of them based on the parameters entered?

  • Yes finally i implemented in the same way as u mentioned, but I want to have a better solution!
    how can we do it in a single crosstab with grouping enabled!

  • Can you please share how you have solved the same.

  • I wasn't involved with the previous discussion, but I have a suggestion on how to change the crosstab grouping based on the parameter values: Create a computed column in the data set. For its expression, add a JavaScript conditional statement to return the Year, or Month, etc. based on the parameter values. Use the computed column as the group for the crosstab columns instead of the date.

    Warning No formatter is installed for the format ipb
  • The above problem is essentially a problem of data preparation, but the way of code in SQL or scripted data sources is hard to write and heavy workload; the way of hiding columns in reports is neither universal nor straightforward. A better solution, then, is to introduce esProc into the reporting tool, which will make it easy to solve all kinds of problems.

    https://data-preparation-for-birt.blogspot.com/2019/06/source-httpsforums.html