Jump to content

BIRT Developer Center Download


Photo
- - - - -

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

Chart Labels

  • Please log in to reply
12 replies to this topic

#1 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 09 February 2017 - 07:16 AM

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.

 

Attached File  Chart.jpg   27.46KB   0 downloads

 

Any thoughts on how to accomplish this?  I did see this post from a few years back, and I tried the script, but it broke the chart.

 

Thanks in advance for any thoughts / ideas!

 

Scott


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#2 Clement Wong

Clement Wong

    Senior Member

  • BIRT Experts
  • 1163 posts

Posted 09 February 2017 - 05:18 PM

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?


Clement Wong
Principal Analytics Architect

OpenText Corporation
http://www.actuate.com | http://www.opentext.com


#3 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 10 February 2017 - 05:50 AM

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


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#4 Clement Wong

Clement Wong

    Senior Member

  • BIRT Experts
  • 1163 posts

Posted 10 February 2017 - 04:42 PM

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.

salesDataSetLastDate = null;

2.  In the original data set in the onFetch event, compare the date value and save the maximum date.

if ( BirtComp.equalTo(salesDataSetLastDate, null) )  salesDataSetLastDate = row["Date"];
else if ( BirtComp.greaterThan(row["Date"], salesDataSetLastDate) )  salesDataSetLastDate = row["Date"];

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.

ctr = 1; 
lastDayOfMonthCtr = BirtDateTime.day(BirtDateTime.addDay(BirtDateTime.addMonth(BirtDateTime.firstDayOfMonth( salesDataSetLastDate ),1), -1)) - BirtDateTime.day(salesDataSetLastDate) + 1;

5.   In the fetch event of the scripted data set, write the values to the current row.

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

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.


Clement Wong
Principal Analytics Architect

OpenText Corporation
http://www.actuate.com | http://www.opentext.com


#5 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 13 February 2017 - 03:47 AM

Clement,

Thanks so much for the help!  I will modify the report using the provided code.

 

Best Regards,

Scott


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#6 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 13 February 2017 - 04:45 AM

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


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#7 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 15 March 2017 - 07:37 AM

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


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#8 Clement Wong

Clement Wong

    Senior Member

  • BIRT Experts
  • 1163 posts

Posted 15 March 2017 - 08:12 AM

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


Clement Wong
Principal Analytics Architect

OpenText Corporation
http://www.actuate.com | http://www.opentext.com


#9 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 15 March 2017 - 08:16 AM

The commercial BIRT using HTML 5 Chart script.


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#10 Clement Wong

Clement Wong

    Senior Member

  • BIRT Experts
  • 1163 posts

Posted 16 March 2017 - 11:59 AM

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.

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

And in the fetch of the scripted data set.

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

Then, I changed the chart's grouping to months, and the X-Axis label format to "MMM yy".


Clement Wong
Principal Analytics Architect

OpenText Corporation
http://www.actuate.com | http://www.opentext.com


#11 CodeRookie

CodeRookie

    Senior Member

  • Members
  • 138 posts

Posted 17 March 2017 - 04:00 AM

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:

 

Attached File  Error.jpg   26.09KB   0 downloads

 

It's probably a simple fix, but I'm still working on my first cup of coffee, and nothing is jumping out at me.  I'll attach a copy of the report design I'm making these modifications to just so you can see what I'm working with.

 

 

Thanks again for your help!

 

Best Regards,

Scott

 

 

 

 


Scott Stanley

Business Analyst

North Star BlueScope Steel, LLC

 


#12 Clement Wong

Clement Wong

    Senior Member

  • BIRT Experts
  • 1163 posts

Posted 17 March 2017 - 09:36 AM

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.


Clement Wong
Principal Analytics Architect

OpenText Corporation
http://www.actuate.com | http://www.opentext.com


#13 jar

jar

    Advanced Member

  • Members
  • PipPipPip
  • 55 posts

Posted 18 March 2017 - 05:55 AM

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...







Also tagged with one or more of these keywords: Chart, Labels