THE BPD Blog

Introduction

Navigating the world of Work Order (WO) management can be simplified with an intuitive calendar view. Displaying scheduled WOs in a monthly calendar format provides clarity and streamlines operational efforts. In this guide, we will explore how to achieve this using BIRT (Business Intelligence and Reporting Tools) based on specific date ranges. Whether you're a seasoned developer or new to BIRT, this guide will walk you through the steps to get started.

Setting up a Calendar View of Scheduled Work Orders using BIRT

Installation & Setup: Before diving in, ensure you have BIRT Eclipse set up on your system. If not, follow this guide from IBM for a seamless setup.

Report Design: Begin by creating a new BIRT report design file named CalendarviewWO.rptdesign.

Dataset Creation: Establish a new dataset named dataset_workorder. Add necessary fields to the Output column for display.

SQL Script: Visit the Open Script and incorporate the SQL Script. This script is responsible for fetching data based on the given date range from the Workorder table. Here’s a sample db2 database query for your reference:

Note: Customize the query according to your database.

With DateSequence(Date1) AS (

   SELECT date(startdate) - (DAYOFWEEK(date(startdate) ) - 1) DAYS

   FROM sysibm.sysdummy1

   UNION ALL

   SELECT Date1 + 1 DAY

   FROM DateSequence

WHERE date(Date1) <= date( enddate ) + (7 - DAYOFWEEK( enddate )) DAYS AND date(Date1) >= date(startdate) - (DAYOFWEEK(date(startdate) ) - 1) DAYS

)

SELECT Date1

FROM DateSequence

WHERE date(Date1) <= date( enddate ) + (7 - DAYOFWEEK( enddate )) DAYS AND date(Date1) >= date(startdate) - (DAYOFWEEK(date(startdate) ) - 1) DAYS

ORDER BY Date1

Data Integration: The provided query can be integrated with other tables (like Workorder) to extract data to display in calendar format. Here’s an extended example to guide you:

SELECT Date1 , ss.wonum, week(Date1) AS weeknum1,dayname(Date1) daynameofmonth,ss.targstartdate FROM (

   With DateSequence(Date1) AS (

   SELECT date(startdate) - (DAYOFWEEK(date(startdate) ) - 1) DAYS

   FROM sysibm.sysdummy1

   UNION ALL

   SELECT Date1 + 1 DAY

   FROM DateSequence

WHERE date(Date1) <= date( enddate ) + (7 - DAYOFWEEK( enddate )) DAYS AND date(Date1) >= date(startdate) - (DAYOFWEEK(date(startdate) ) - 1) DAYS

)

SELECT Date1

FROM DateSequence

WHERE date(Date1) <= date( enddate ) + (7 - DAYOFWEEK( enddate )) DAYS AND date(Date1) >= date(startdate) - (DAYOFWEEK(date(startdate) ) - 1) DAYS

ORDER BY Date1) LEFT OUTER JOIN (select week(workorder.TARGSTARTDATE) AS weeknum,dayname(workorder.TARGSTARTDATE) daynameofmonth,date(workorder.TARGSTARTDATE) AS TARGSTARTDATE ,

workorder.PMNUM ,workorder.wonum from workorder

where (workorder.pmnum is not null and workorder.status='WSCH' and

date(workorder.TARGSTARTDATE) >= date(startdate)

and date(workorder.TARGSTARTDATE) <= date(enddate))

GROUP BY workorder.TARGSTARTDATE,workorder.PMNUM ,workorder.wonum

ORDER BY date(workorder.TARGSTARTDATE )) ss ON date(Date1)=date(ss.TARGSTARTDATE) ORDER BY Date1

Data Configuration: After the data extraction, configure the display values in the Fetch Script. Ensure to include:

Date1: Display all dates within the date range.
Weeknum: Indicate the week of the year.
Dayname: Showcase the specific day of the week.

Layout Design: Your report layout determines the user experience. Here’s a breakdown:

1. Initiate with a table for dataset_Workorder and integrate all columns.
   
   
2. Access the Properties Editor → Table and incorporate Groups.
   
   
3. Organize groups based on weeknum.
   
   
4. Update header columns with hardcoded Week Days Names for a Calendar Week view.
   
   
5. Within the Table Details Row, each day should have an embedded table (1 column, 2 rows). This table will display the Date1 as a header and wonum (workorder number) in the detail row.
   
   
6. Apply specific filters in the Properties Editor → Table for the subtable, ensuring only Workorders for specific days are displayed.
   
   
7. Replicate this process for all the days of the week. Debug, and run your report. 
   Note: The outcome should resemble the structure provided below.
   
   

Conclusion 

Congratulations on setting up your calendar view for scheduled Work Orders using BIRT! This visualization method is not only user-friendly but also crucial for efficient management. If you found this guide helpful and would like to stay updated on similar technical walkthroughs, feel free to contact us. Together, we'll explore more efficient ways to manage your technical operations.