Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
30-60-90 report structure
mmarc442
Need to create a 30-60-90 aging report. Use a StartDate as a parameter and going back 12 months from there I need to display in a stacked bar chart for each month (X-axis) the number of invoices Less than 30 days old (Series 1), 30-60 days (Series 2), 60-90 days (Series 3), Over 90 days old (Series 4). Anyone have a simple report like this I can use as a starting point, or suggestion of how to structure the data set ? Thanks.
Find more posts tagged with
Comments
jakoby84
Hi,
I created a sample report. As I used the sample database, I created an artificial age column as a computed column.
The chart uses a custom expression to create the groups (Optional Y Series grouping) and a count aggregation for the Value (Y) field.
The script in beforeOpen() method of the dataset retrieves the report parameter, calculates the endDate (well it should be called startDate because it's earlier but anyways) and then adds a "where date between ..." statement to the query.
I think it should be a good starting point for you.
mmarc442
Wow ! Thanks for the quick response. I should have also mentioned I need to use the Indigo release of BIRT. I got an error about 'extended element not supported yet'. The chart comes up as 'Current chart is invalid' and I can't open it to investigate.
Can you re-create & send your sample in Indigo ?
jakoby84
<blockquote class='ipsBlockquote' data-author="'mmarc442'" data-cid="114341" data-time="1361283237" data-date="19 February 2013 - 07:13 AM"><p>
Wow ! Thanks for the quick response. I should have also mentioned I need to use the Indigo release of BIRT. I got an error about 'extended element not supported yet'. The chart comes up as 'Current chart is invalid' and I can't open it to investigate. <br />
Can you re-create & send your sample in Indigo ?<br /></p></blockquote>
I don't have Indigo. But here are the steps to recreate the chart:<br />
<br />
Chart Type: Side-By-Side Bar Chart (the default bar chart)<br />
Category (X) Series: row["ORDERDATE"]<br />
Category (X) Series Grouping: type DateTime, Unit Months, Data Sorting: Ascending<br />
Value (Y) Series: row["ORDERDATE"]<br />
Value (Y) Series aggregation function: Count<br />
Optional Y Series Grouping Expression:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>ageGroup = "";
if (row["age"] < 30){
ageGroup = "<30";
} else if (row["age"] < 60){
ageGroup = "30 to <60";
} else if (row["age"] < 90){
ageGroup = "60 to <90";
} else {
ageGroup = "90 and older";
}
ageGroup;</pre>
Furthermore, in Format Chart -> X-Axis -> untick "Is category axis"<br />
Hope this helps to recreate the chart.
mmarc442
Helpful but only displays the age of the invoice as of the report date. I need a history of the ages per month. If I have 2 invoices for Jan 15th and one for Feb 15th and run the report on March 31 I need the bar chart to show
Jan 31 - 2 that are Less than 30
Feb 28 - 1 that is Less than 30 and 2 that are 30-60
Mar 31 - 1 that is 30-60 and 2 that are 60-90
Apr 30 - 1 that is 60-90 and 2 that are Over 90
May 31 - 3 that are Over 90
In other words for each month (bar) on the X-axis I need the count of invoices in each category (Less than 30, 30-60, etc.) FOR THAT MONTH.
jakoby84
<blockquote class='ipsBlockquote' data-author="'mmarc442'" data-cid="114387" data-time="1361367711" data-date="20 February 2013 - 06:41 AM"><p>
In other words for each month (bar) on the X-axis I need the count of invoices in each category (Less than 30, 30-60, etc.) FOR THAT MONTH.<br /></p></blockquote>
<br />
Yes, this mechanism is included in the report I attached to my first post. I think you were able to load the report except the chart element. So in this report you should delete the chart element which is not working, and then add a new chart with the settings described in my second post.<br />
In the report I posted there is a script for the dataset (beforeOpen) which reads the report parameter StartDate, calculates an EndDate (StartDate minus one year) and manipulates the query so that data is retrieved for the year before 'StartDate':<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>StartDateParam = params["StartDate"];
if (StartDateParam != null){
startDate = StartDateParam.value;
endDate = BirtDateTime.addYear(startDate, -1);
this.queryText = this.queryText + " where ORDERDATE BETWEEN '" + BirtDateTime.year(endDate) + "-" + BirtDateTime.month(endDate, 1) + "-" + BirtDateTime.day(endDate) + "' AND '" + startDate+ "'";
}</pre>
Then, when building the chart with the settings from my second post, it should add 1-4 bars for EVERY MONTH.<br />
<br />
I attach a screenshot of the chart how it looks here; the only challenge now is to recreate it in your version of eclipse. I'm sorry that I can't provide the report for your version but I think its a nice practice to rebuild the report and I hope my instructions are clear and complete. If something is still unclear, please let me know.
