Home Analytics Designing reports
The community of Analytics developers are invited to follow and participate in the developer network.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.

Dynamic Grouping of crosstab in BIRT

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


  • wwilliamswwilliams Junior Member

    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.

    Jeff F.
    Sr. Technical Support Specialist
  • 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.


Sign In or Register to comment.