Idisis Blog

In Part I I introduced the concept of custom reports and how they are implemented in OSIS 2.6.  In Part II I’ll outline the actual customization (ie., code content) of the report. The sample provided with osis 2.6 generates a Tag Cloud of the count of rooms per division. The larger the division text on your screen the more rooms from that division are in your inventory.

In this installment we will create a custom graph representing the same set of data.

The Final Report

Requirements

• An OSIS 2.6 install – Use Of a Development Environment Strongly Recommended
• Some basic SQL and ColdFusion programming knowledge
• OSIS report authoring experience

The Steps

Step 1 – Create The Report

For this tutorial we will utilize the same report created in Part I. (see New In OSIS 2.6 – Feature Spotlight – Custom Reports Framework – Part I ) You can either create a new report from scratch or copy your existing one (make note of the new report ID).

Step 2 – Create The Report Template

• Download and copy this file into the following directory:
  

  {osis install directory}/cfm/tpl/o_reports/o_sample

• Ensure the report from Step 1 above has a its”Fixed Format Report Name” set to:
  

  /osis/tpl/o_reports/o_sample/o_rpt_002.cfm

Understanding the Template From Step 2

Manipulate Query Data

<cfquery name="SumQueryBldg" dbtype="query" maxrows=5 >
SELECT dv_id, udf_dv_id label , COUNT(rm_id) room_data FROM reportdata_qry GROUP BY dv_id, udf_dv_id ORDER BY room_data DESC
</cfquery>

The query returned via OSIS is returned in the variable reportdata_qry. The field names contained therein are the same as those you have defined in your report. this includes any UDF fields. In this sample we have sorted the query in descending order by the COUNT of room codes and have grouped by the rooms division assignment. As this is a top 5 type report we have limited the number of rows returned to 5 using the cfquery variable maxrows.

Javascript For Drill Down Report Link

<script type="text/javascript">
function openReportWithRestriction(restriction_xml) {
var url = "rpt_adv_main.cfm?cr=1&ml=0&rpt_id=55&pr=1&omod=" + restriction_xml;
rptWin=window.open(url,"win1",'toolbar=0,location=0,directories=0,status=0,menubar=0,scrollbars=1,resizable=1,width=1000,height=650');
rptWin.focus();
};
</script>

This Javascript function provides the method to open a report in a new window based on the chart item (pie slice) clicked on in the report. Don’t forget to replace rpt_id=55 with your report ID: e.g., rpt_id=210.

Generate Standard OSIS Restriction For Report

<cfxml variable="restriction">
<?xml version="1.0" encoding="UTF-8"? >
<osis>
<mod_reporting>
<restrictions>
<restriction condition="=" field="dv_id" field_udf="(SELECT dv.name FROM [[SCHEMAOWNER]]dv dv WHERE dv.dv_id = rm.dv_id)" group=") AND (" table="rm" value="$ITEMLABEL$"/></restrictions>
</mod_reporting>
</osis>
</cfxml>

This block of code generates the XML format restriction used by OSIS reporting. The restriction in this sample will restrict the linked report to the specific division clicked on in the chart. The $ITEMLABEL$ is a placeholder for the label which in this case is the divisions name (from the dv.name field.) As such we have also specified that the restriction is based on a UDF.

Use CFCHART to Generate the Chart

<cfchart title="Top 5 - Room Count By Division" format="flash" xaxistitle="Division: " chartWidth="800" chartHeight="600"
yAxisTitle="Count of Rooms" pieSliceStyle="sliced"
url="javascript:openReportWithRestriction(""#JSStringFormat(restriction)#"");" show3D="yes">
<cfchartseries type="pie" query="SumQueryBldg" itemcolumn="label"
valuecolumn="room_data" paintStyle="light" >
</cfchartseries>
</cfchart>

In this final block of code we use the standard ColdFusion CFCHART tag to generate a pie chart.