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; } } }

How to create a report aggregated by week?

brad.white

I'm relatively new to BIRT but making good progress. I'm struggling with getting a report done for my client in which they want totals broken down by week. My strategy was to create 2 sql statements, one that gives me all the Sundays-Saturdays in a given date range, and the other to give me all the results I need to compute the totals. I thought I was on a good roll until I realized I can't bind 2 datasets to one table. I also can't join them logically.
 
My next plan is to try and put the resultset/dataset of the weeks into an array, loop through the array and populate the first column of the table and then perform aggregate counts on the second dataset filtered by the weeks... if that's possible.
 
However if someone has done this before and has an example or a suggestion I'd be most appreciative.
 
Thanks.

Find more posts tagged with

Comments

jfranken

Hi Brad,
 
I'm not certain I understand what you are trying to do. Are the "numbers to be totaled" associated with a date in the database? If so, I attached a sample report based on the ClassicModels database that shows a basic way to do weekly totals. There is one Data Set that selects all of the rows in the form:
 
Date Quantity

1-4-11 60
1-9-11 82
2-4-11 33
...
 
Here are the steps to create the report:
 
Add a table to the layout and populate it with the data from the Data Set. 
 
> When the report is run the result will be a simple list of all of the rows of data.
 
Select the table in the layout and create a new group on the Groups tab.
Group by date and set the Interval to "Week" with Range = 1.
 
> One row will be added to the report at the start of each new week. That row will contain just the date.
 
From the Palette, drag an Aggregation element into the second column of the group header.
Set the aggregate Function to SUM and the Expression to the column you want to total.
Set Aggregate On to "Group".
 
> The weekly total will be added to the group row that appears on the report at the start of a new week.
 
That's it. There are a few optional steps you can do like: 
- Format the date. (done in sample report)
- Delete the detail row. (not done in sample report so that it's easier to see how the grouping was made)
- Format other elements in the report or create a chart based on the data.
 
Note: The sample data isn't the best for this example. If you scroll through the pages of the report you'll see that data for all of the days in each week are being aggregated in the group header. Many weeks only have data for a single day of the week.
 
If this doesn't work, please provide more detail on what you want to do.