nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Aggregation calculation in Groups, using a grouped-on value for a table aggregation

mwengren

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!

Find more posts tagged with

Comments

mwilliams

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.

mwengren

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="76321" data-time="1303922648" data-date="27 April 2011 - 09:44 AM">
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. </blockquote>
 
 
Hi Michael, 
 
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. 
 
Thanks again for your help, 
 
Micah

mwilliams

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.

mwengren

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!

Micah

mwilliams

Micah,

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 though. You can send me data to mwilliams@actuate.com.

mwengren

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,
Micah

mwengren

Michael,

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,

Micah

mwilliams

Micah,

Did you send me the data? I didn't see it come through. If it came through junk mail, it's probably long gone by now...can you send it again?

mwengren

Michael,

Yeah, I did... your junk mail filter may have picked it up due to the attachment with .sql extension. I'll resend again in a minute.

Thanks!

mwilliams

I haven't gotten it yet if you sent it. Let me know.

mwengren

Michael,

I sent you an email to mwilliams@actuate.com with a link to download the data..... did you receive it? I posted the data to our servers to avoid spam filter issues. I sent the email at 2:47 today. Something must be up, hmmmm.... I'll send it again.

Thanks,
Micah

mwilliams

Micah,

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.

mwengren

Hi Michael,

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.

Micah

Hans_vd

Hi Mwengren, 
 
Take a look at this SQL: 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT trunc(yt1.your_date_field, 'MM'),
(SELECT COUNT(*)
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')</pre>
 
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 helps 
 
Hans

mwilliams

Thanks Hans! 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.

mwengren

Hi Hans, 
 
Thanks for the SQL suggestion. We are using SQL Server, but there is an analogous function available there to the trunc function in Oracle that I was able to use - DATEADD(Month, DATEDIFF(Month, 0, date_value)). The grouping via SQL works for our circumstance just fine. The issue that I am having with the calculations we need to do has to do with using the resulting grouped date values (ie each month in the group list) in a count(*) subquery such as the example you gave. Just using a regular count query and listing the results in the grouped select list gives the same number for each group, where we need a different count for each month group. Maybe I'm missing a way to do this in SQL with the secondary count(*) query? 
 
I also tried mwilliams nested subqueries with dataset binding parameters idea, which worked correctly in terms of passing the grouped month parameter to the nested count subqueries, however we weren't able to chart the results due to issues with charting from different datasets in the same BIRT chart (apparently... still trying to figure out what exactly the issue was there). We weren't able to reference the rows in our BIRT table in a chart due to the fact that our Inventory and Backlog columns were being calculated via nested subquery, and they didn't belong to the outer dataset bound to the table itself. Is there a way to do this I'm missing? The nested subquery seems like probably the ideal way to do this since it's pretty easy to set up in BIRT, we just need to connect our results and put them in a chart. 
 
Just for reference, this is what I'm getting via Hans' SQL approach: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Months Inventory
2011-05-01 00:00:00.0 1473
2011-04-01 00:00:00.0 1473
2011-03-01 00:00:00.0 1473
2011-02-01 00:00:00.0 1473
2011-01-01 00:00:00.0 1473
2010-12-01 00:00:00.0 1473
</pre>
 
The second column should be varying depending on the month in the first column. 
 
 
And our table of monthly calculations in BIRT: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Month Inventory Productivity
Jan 11 266 16.75
Feb 11 251 17.58
Mar 11 232 17.92
Apr 11 214 18.58
May 11 197 18.42
</pre>
 
Thanks a lot for your help in this... any thoughts much appreciated!!! 
 
Micah

mwilliams

This gets more complicated with my suggestion from earlier, but if you were to use the onFetch of the inner dataSet to fill an array with the values that you could use to make a scripted dataSet for your chart to use? I think this is possible as long as the chart comes after the table. You might give it a try.

Hans_vd

Hi Mwengren, 
 
Are you sure you didn't forget this part: 
<pre class='_prettyXprint _lang-auto _linenums:0'>WHERE yt2.your_date_field > trunc(yt1.your_date_field, 'MM')</pre>
including the aliases? 
For me the query works as you want it.

mwengren

Hans_vd, 
 
Our table structure is a little different and the where clause I need to use is different from your example slightly, not sure if that could be causing it. We have just one table we are working with in this case. Our query is basically like the following: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select dateadd(Month, datediff(Month,0,firstDateField), 0) as monthGroup,
(select count(*) from tbl1 where firstDateField is not null and (secondDateField> dateadd(Month, datediff(Month,0,firstDateField ), 0) or secondDateField is null)
from tbl1
group by monthGroup
order by monthGroup desc;
</pre>
 
What I really need to do is replace the '(secondDateField> dateadd(Month, datediff(Month,0,firstDateField ), 0)' clause in my count query with the value of 'monthGroup' for each grouped row, but the count subquery has no reference to its containing query and therefore the 'monthGroup' alias value. 
 
Thanks for your help! 
 
Micah

Hans_vd

My query also selects from the same table twice, but you have to use aliases, to make sure you compare the secondDateField in the inner query to the monthgroup you defined in the outer query. Like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select dateadd(Month, datediff(Month,0,T_OUTER.firstDateField), 0) as monthGroup,
(select count(*) from tbl1 AS T_INNER where T_INNER.firstDateField is not null and (T_INNER.secondDateField> dateadd(Month, datediff(Month,0,T_OUTER.firstDateField ), 0) or secondDateField is null)
from tbl1 AS T_OUTER
group by dateadd(Month, datediff(Month,0,T_OUTER.firstDateField), 0)
order by dateadd(Month, datediff(Month,0,T_OUTER.firstDateField), 0) desc;
</pre>

mwengren

Hans_vd,

Thanks for your solution, this has worked with most of our initial calculations. Adding the aliases to the queries did the trick. It would be great to be able to do a lot of these calculations directly in BIRT, but for now it is going to be easier to do grouping in SQL as you suggested and try to handle most of the number crunching in our SQL queries with the output being charted in BIRT.

Thanks,
Micah

Sannz

Hello Michale, 
 
 I am facing aggregation count(running count) issue while generating report. Actually i am using aggregation inside table group,so i am getting same value for entire row (eg SL:no :1).and table group is required.Can u please suggest me some solution for the <a data-ipb='nomediaparse' href='http://same.it/'>same,it</a> would be very great full.

Sannz

Hello, can anybody help me out to resolve this issue?