17th September 2016 at 11:28 am #138
Hi, I may be missing an easy technique to do this, but I have a DataSet with several date fields, and I am trying to group the data based on a single date field into groups by month, and then perform some aggregate calculations for each group. I’ve been able to do the grouping either by adding a computed column in the DataSet that uses the month floor (ie first of the month) for the date field of interest, or by using the automatic BIRT Interval grouping by month. What I need to do is to then calculate an aggregation for each month group, but based on the full DataSet and not just the records satisfying the grouping criteria. So say I have a group based on date March 1, 2011, I would like to use this value against the full dataset to calculate how many records contain a date field that is after this date. I can’t just use the computed column that I’ve grouped on because for each record in the calculation, because the value will vary depending on what date field I used to obtain the month floor for grouping. I want to use March 1, 2011 as the date comparison for EACH record, not just those that fall within the March 2011 grouping, and so on for each group within my report. I tried writing some scripts to set a persistent global variable in the onCreate event hander for the group header, something like: reportContext.setPersistentGlobalVariable(“groupDate”,row[‘groupingDateField’]) but I am getting null values when I try to retrieve the variable in my aggregation calculation: reportContext.getPersistentGlobalVariable(“groupDate”) Also, I put some debugging output in my report, and it appears that the ‘groupDate’ global variable doesn’t get set until after the first report detail row is written out if I set it using the method above in the ‘onCreate’ event handler of either the group header or detail row. Reading the documentation, I would expect that it would get set if I put the above ‘setPersistentGlobalVariable’ code in the detail row ‘onCreate’ event handler and then tried to output it in the same row via a data element set with the expression (reportContext.getPersistentGlobalVariable(“groupDate”);). This works, however the first row in the output table has a null value, and subsequent rows have the correct value, so the event order is not what I’d expect. It seems I have an issue with the event handler firing order, and it is difficult to see what order the events are actually firing in. It seems to me that my aggregation function is trying to retrieve the ‘groupDate’ persistent global variable before it is able to be set, and I don’t see which report element ‘onCreate’ event handler to set it in to avoid this. Hopefully this makes sense. Is there an easy way to debug event firing when previewing a report? What should I do to get this working. Or, better yet, is there an easier way to use attributes of a group in aggregation calculations (ie begin and end dates in an automatic monthly interval grouping)? Example data: ID GroupingDate OtherDate A1 March 5, 2011 April 11, 2011 A2 March 25, 2011 March 28, 2011 A3 Feb 6, 2011 April 10, 2011 A4 Feb 19, 2011 Feb 29, 2011 If I group on the GroupingDate field by month, I’d like to count which records ‘OtherDate’ fields occur before the end of the last day of the grouped month. So in the March group, A2 would be counted, A1 would not, etc. Thanks for any assistance!17th September 2016 at 11:28 am #139
So, you’d want the count of “OtherDate” values that happen before April 1st, in this case? So, A2 and A4 would apply? Or only the rows from that particular group? So, only A2? Let me know.17th September 2016 at 11:29 am #140
Thanks for the quick response. Yes, both A2 and A4 would be counted for the March group. For the Feb group, it would just be A4, and so on. What I’m trying to do is to run these calculations over a two year period on a monthly basis. I didn’t see a way to access metadata about the grouping criteria (ie month start, end) in a filter expression for an aggregation when using monthly interval grouping.17th September 2016 at 11:29 am #141
Is there any way you can get me a larger chunk of data to test with? I think I might have a solution. This is just a group by group count, correct? You don’t figure this for each row in the group, just the group, so everything with the second date happening prior to the current month? Let me know.17th September 2016 at 11:30 am #142
I think a more complete dataset would probably help to explain what I’m trying to do. I don’t want to post to the forum however. Is there another way I could get you a sample of the data? Basically what I need to do is to do a calculation on the data for each month over a two year period. I’m learning BIRT at this point, and the easiest way I could see to do this was to set up monthly groups based on one of the date fields in the dataset (ie the computed column for first of the month). The issue though (I think this is your second question) is that I need to do the count on the ‘OtherDate’ field for each row in the data set, not just the rows that are included in the monthly grouping (by ‘GroupingDate’ field). If you include the following record A5, this should show what I mean: ID GroupingDate OtherDate A1 March 5, 2011 April 11, 2011 A2 March 25, 2011 March 28, 2011 A3 Feb 6, 2011 April 10, 2011 A4 Feb 19, 2011 Feb 29, 2011 A5 Jan 10, 2011 Feb 22, 2011 A5 should be included in the monthly total count for the Feb grouping along with A4, even though its GroupingDate field is in January, and it won’t technically be included in the Feb group. Let me know how I could send a larger data sample. There may be a simpler way to do this, I just haven’t figured out how. Thanks!17th September 2016 at 11:31 am #143
I understood that you want to consider all rows within your count. My question was if you wanted just one count per “month” group or if you wanted the count for all dates prior to the date on every detail row of the “month” group. I’m guessing you just need one count per month though17th September 2016 at 11:31 am #144
Ok, I understand your question now. Yeah, you’re right, just the one count per month group. I’ll send you some sample data. Thanks, Micah17th September 2016 at 11:31 am #145
Were you able to find a solution to the count aggregation/grouping issue I was having? I still don’t have a fix after reading all the documentation I could find on report scripting, event handling, and scripting contexts in BIRT. My approach to use reportContext to set a persistentGlobalVariable based on the date field I’m grouping by doesn’t seem to work because it always comes up null in the filter I apply to the aggregation. I’m pretty sure that this has to do with the order of execution for my code that’s setting the variable and the aggregation calculation, but I couldn’t find a workaround to change this. Thanks for your help.17th September 2016 at 11:32 am #146
If you’re using SQL, you might be able to run a second query to count the rows that qualify for your criteria. I’m trying to do the same with csv and filters, but it’s not working out like I planned. Embedded SQL tables using dataSet binding parameters is easier. 🙂17th September 2016 at 11:32 am #147
Can you explain how this would work? I’m not sure I understand what you mean by ’embedded SQL tables using dataSet binding parameters’. How and where would I run the second queries? Right now my report groups the data monthly by the ‘GroupingDate’ field, I guess I can see how if I could execute a SQL statement against my database for each monthly group instead of trying to use a filter in the Aggregation builder, this would work, but how do I do this? I tried setting up a count aggregation and applying a filter against the entire table (to count all rows) using the grouping criteria (ie first of the month date for each group), but this doesn’t seem to work due to event firing issues. Can you explain how to set up the dataSet binding parameter to an embedded SQL query that I can put in a row for each of my monthly groups? Thanks for your patience, I’m just learning how to use BIRT.17th September 2016 at 11:33 am #148
Take a look at this SQL:
SELECT trunc(yt1.your_date_field, ‘MM’),
FROM your_table yt2
WHERE yt2.your_date_field > trunc(yt1.your_date_field, ‘MM’)
FROM your_table yt1
GROUP BY trunc(yt1.your_date_field, ‘MM’)
As you can see, the grouping is done in the SQL, not in BIRT.
The use of trunc may be Oracle specific, it is used here to distract the first day of the month, which is what determines the groups.
Hope this helps17th September 2016 at 11:33 am #149
Thanks !!! That’s kinda what I was talking about above with SQL, only you took care of one of the steps in the SQL with the count. 🙂
You must be logged in to reply to this topic.