Getting a chart to show Category (X) Series labels when there is no actual data to chart

Good morning, all!
 
Here is my dilemma...... I'm building a bar chart that is based on the day of the month, and a corresponding piece of data for that day (see pic). It works fine, but what the end user would like to see is all days for the month plotted even if there is no corresponding data value.
 
So, for example, the month of February would have actual data from the 1st to the 9th. I would still like to show labels out to the 28th.

Find more posts tagged with

Comments

Clement Wong

It looks like commercial BIRT you're using, and HTML5 charts.
 
With HTML5 charts, there are robust scripting capabilities, and you'll need to use that to fill those missing days with 0s.
 
I'll send you a script and sample report design. However, I would can you please tell me how you've defined the X-Axis? It's a Date data type field? And in the Edit Chart > Format Chart > Chart Area > Axis > X-Axis, is the checkbox for "Is Category Axis" checked?

CodeRookie

Clement,
Good morning, and thanks for the response! To answer your questions:
 
It is a date data type field.
 
The checkbox for "Is Category Axis" is checked.
 
Thanks,
Scott

Clement Wong

You can fill the rest of months via the following methods (lots of options):
 
1] Have your data source / data set return the 0s for the rest of days of month
 
2] If you're not able to use your data set to return those values in option 1, you can use a scripted data source / data set to return the rest of the days of the month, and then union that set with the original data set.
 
3] If you're not able to use your data set to return those values in option 1, you can use a scripted data source / data set to return ALL of the days of the month, then do a right outer join with the original data set.
 
4] In the HTML5 charts, script the days for the rest of the month and insert 0s up until the last day of the month.
 
In the attached example, we explore the second option (which applies commercial BIRT because it's using a Union joined data set). For OS BIRT, use option 3. EDIT: I've included an OS BIRT example (created in BIRT 4.6.0) for missing hours using a scripted data set and a joined (right outer join) data set -- see attachment.
 
1. In the original data set in the beforeOpen event, create a variable to store the maximum date.
<pre class="_prettyXprint">
salesDataSetLastDate = null;</pre>
2. In the original data set in the onFetch event, compare the date value and save the maximum date.
<pre class="_prettyXprint _lang-">
if ( BirtComp.equalTo(salesDataSetLastDate, null) ) salesDataSetLastDate = row["Date"];
else if ( BirtComp.greaterThan(row["Date"], salesDataSetLastDate) ) salesDataSetLastDate = row["Date"];
</pre>
3. Create a scripted data source / data set "Scripted Data Source" and its "Missing Days Data Set". Create two columns in the data set to match your original data set. In the examples, we created "Sales" (float), and "Date" (date).
 
4. In the open event of the scripted data set, initialize a counter, and get the # of days missing.
<pre class="_prettyXprint _lang-">
ctr = 1;
lastDayOfMonthCtr = BirtDateTime.day(BirtDateTime.addDay(BirtDateTime.addMonth(BirtDateTime.firstDayOfMonth( salesDataSetLastDate ),1), -1)) - BirtDateTime.day(salesDataSetLastDate) + 1;
</pre>
5. In the fetch event of the scripted data set, write the values to the current row.
<pre class="_prettyXprint _lang-">
if (ctr >= lastDayOfMonthCtr){
return false;
}
else {
row ["Sales"] = 0;
row ["Date"] = BirtDateTime.addDay(salesDataSetLastDate, ctr);
ctr++;

return true;
}
</pre>
6. Create a new union data set, using the values from the original data set, and the new scripted data set from Step 3.
 
7. Change the chart's data set from the original data set to the union data set.
 
 
Now you can run the report, and the days for the rest of the month will be displayed.

CodeRookie

Clement,
Thanks so much for the help! I will modify the report using the provided code.
 
Best Regards,
Scott

CodeRookie

Clement,
Worked like a charm! I just have to mess a bit with the date format for the dates that have a '0' value. Dates with a value have a '02/01/2017' format, while the others have a 'Feb 14, 2017 12:00 AM' format. Should be easy enough to fix.
 
Thanks again for your help!
 
Scott

CodeRookie

Clement,
Don't know if you're still following this thread, or not, but I need a bit of guidance on a similar issue. This time, instead of going by individual days of the month, I've been asked to chart for a full year using the 12 months as divisions. So, as of today, I would only have data for Jan, Feb, March of 2017. I would like to show the other months as having a place holder value of '0' on the chart as we did above with the 'blank' days. I tried to modify the code you originally gave me, but I failed heinously. If you could let me know how to modify, I would be most appreciative.
 
Best Regards,
Scott

Clement Wong

Which method are you using? The commercial BIRT using HTML5 Chart script, or OS BIRT with a scripted data source/set?

CodeRookie

The commercial BIRT using HTML 5 Chart script.

Clement Wong

I downloaded the previous example, and changed filling the empty days to months.
 
The code I changed was in the open of the scripted data set.
<pre class="_prettyXprint _lang-">
ctr = 1;

// This was from the previous example
//lastDayOfMonthCtr = BirtDateTime.day(BirtDateTime.addDay(BirtDateTime.addMonth(BirtDateTime.firstDayOfMonth( salesDataSetLastDate ),1), -1)) - BirtDateTime.day(salesDataSetLastDate) + 1;

monthsToFillCtr = 12 - BirtDateTime.month(salesDataSetLastDate);

</pre>
And in the fetch of the scripted data set.
<pre class="_prettyXprint _lang-">
//var logger = java.util.logging.Logger.getLogger("birt.report.logger");

if (ctr > monthsToFillCtr){
return false;
}
else {
row ["Sales"] = 0;
row ["Date"] = BirtDateTime.addMonth(salesDataSetLastDate, ctr);
ctr++;

return true;
}
</pre>
Then, I changed the chart's grouping to months, and the X-Axis label format to "MMM yy".

CodeRookie

Clement,
Thanks so much for your help on this request. I've run into a bit of an issue on this one. I made the additions of the new code to the respective places, and made the adjustments for my specific row names, etc. Unfortunately, I get the following errors:

Clement Wong

Scott,
 
One thing I noticed in your Scripted Data Set, in the open event, you have "open" as the first line in there. Can you try removing it? 
 
I'm not able to run your report to see what else might be the issue. If you want a deeper dive with potentially a remote session, please contact your local Support center and open a ticket so that a Support Engineer can assist you.
 
FYI, I removed your design, just in case you had any sensitive credential info to your data source. In the future, you may just want to strip out the IP and u/p before attaching.

jar

I solved this by making a dataset with day field filled with 1 to endnumber for that month ...
Then use a join dataset with a outer join on that first dataset and my 'data' dataset and use the day field as the x-as value. 
 
Works for me...