Customizing Business Intelligence reports

After you have completed the configuration and extraction, and the data is replicated into WCA.OREDERITEMGIFT, in the WebSphere Commerce Analyzer, you will create a new WebSphere Commerce Analyzer report. In this scenario, the report that will be created shows what kinds of gifts were bought by the customers and the revenue and profit level. The reporting framework generates reports using data contained in the production database, and displays the reports in real time. You can customize the framework by adding valid SQL queries, and defining JSP files used to request and display the generated reports.

To create a new business intelligence report, do the following:

To modify an existing report, navigate to Dynamic Web Projects/Stores/WebContent/tools/bi, create a custom folder, copy, and rename the target files that require modification.

  1. Create the JSP file from which the report is requested. This JSP file is used to collect data from the customer. The report is then generated based on this data. The filename for this input JSP file is biSalesByGiftRegistryReportInputView.jsp file. This code sample shows an example of an Input JSP file for a report. 
    <%@include file="/tools/common/common.jsp" %>
<%@include file="/tools/reporting/ReportStartDateEndDateHelper.jspf" %>
<%@include file="/tools/reporting/ReportFrameworkHelper.jsp" %>
 
<HTML> 
 <HEAD>
 .......
 .......
 <SCRIPT>
 function initializeValues()
       {
  ........
  ........
 // Initialize to load the input page by considering the previous launch.
       }
 function savePanelData()
       {
  .......
  .......
 // Save the selected options on input page with entered values.
}
</SCRIPT>
</HEAD>
<BODY>
.......
.......
//Create input functionalities for customers to choose input options

</HTML>

  2. Create the JSP file to display the report, unless the generic JSP is used. This JSP file is used to visualize the report to the customer. The filename for this output JSP file is biSalesByGiftRegistryReportOutputView.jsp. This code sample shows an example of an Output JSP file. 
    <%@include file="/tools/reporting/ReportHeaderSummaryHelper.jsp"%>
<%
........
// Declare reportPrefix, resource bundle and locale
........
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN">
<html>
   <head>
.........
.........
//Creating header for report on result page
<%=generateHeaderInformation(reportPrefix, biNLS, request)%>
   </head>
   body class="content" onload="javascript:parent.setContentFrameLoaded(true)">

     //Writing parameters which has been selected from input page
<%=generateOutputHeading(reportPrefix, biNLS)%>

//Writing specific/extra parameters which has been chosen from input page
//This functionality is needed only for specific requirement apart from selecting time periods on input page. For example: providing extra input option on input page

<%=generateSpecificOutputInputCriteria(reportPrefix, biNLS, biCommandContext.getLocale())%>

//Writing result output for the report
     <%=generateOutputTable(reportPrefix, biNLS, biCommandContext.getLocale())%>

 </body>
</html>

  3. You must register your newly created views in the Struts configuration files for the biSalesByGiftRegistryReportInputView.jsp and biSalesByGiftRegistryReportOutputView.jsp. To register the new views...

    1. Navigate to WC_installdir\WebSphere\AppServer\profiles\instance\installedApps\WC_instance _cell\WC_instance.ear\CommerceAccelerator.war\WEB-INF\struts_config.xml. In case of WebSphere Commerce development environment, navigate to Web Content\WEB-INF\struts_config.xml

    2. Add the following code:

      Under Forward Tag: 

      <forward className="com.ibm.commerce.struts.ECActionForward"
name="biSalesByGiftRegistryReportInputView" path="/tools/bi/biSalesByGiftRegistryReportInputView.jsp">
<set-property property="resourceClassName" value="com.ibm.commerce.tools.command.ToolsForwardViewCommandImpl"/>
 </forward>
 <forward className="com.ibm.commerce.struts.ECActionForward"
     name="biSalesByGiftRegistryReportOutputView" path="/tools/bi/biSalesByGiftRegistryReportOutputView.jsp">
<set-property property="resourceClassName" value="com.ibm.commerce.tools.command.ToolsForwardViewCommandImpl"/>
        </forward>

      Under Action Tag: 

      <action path="/biSalesByGiftRegistryReportInputView" type="com.ibm.commerce.struts.BaseAction">
<set-property property="https" value="0:1"/>
</action>
<action path="/biSalesByGiftRegistryReportOutputView" type="com.ibm.commerce.struts.BaseAction">
<set-property property="https" value="0:1"/>
</action>

  4. Update the business intelligence reports property file which is located in WC_installdir\WebSphere\AppServer\profiles\instance \installedApps\WC_instance_cell\WC_instance.ear\properties\com\ibm\commerce\tools\bi\properties\BINLS_en_US.properties

    Make sure that you take a backup of the modified file. This file contains the text that displays on the input JSP page. For example, orderAmount=Order Amount salesbyregistry=Sales By Registry

  5. Define the report in three XML files. Reports require three XML files. These files must be located in the WC_installdir\WebSphere\AppServer\profiles\instance\installedApps\WC_instance _cell\WC_ instance.ear\xml\tools\bi directory. This directory also contains XML files for default reports that come with WebSphere Commerce. Use these files as examples for your new report's XML files. The three required XML files are:

    • biSalesByGiftRegistryReport.xml for the SQL used to retrieve the data for the report. Ensure the SQL statement runs successfully before continuing to create your report. The sample of this report xml file is as follows: 
      <Reporting>
<Report reportName="biSalesByGiftRegistryReport" online="true" dataSourceName="WCA DataSource">
<comment>
........
//Write any report related comment here if required
</comment>
<SQLvalue>
................
//Report sequel script which will result the report data
//This script is written by assuming that marketing manager would like to see the revenue profit because of gifts associated with orders placed within selected time periods.
................
SELECT
USRG.LOGONID AS CSRID,
CONCAT(COALESCE(M.FIRST_NAME,''),CONCAT(CONCAT(' ',COALESCE(M.MIDDLE_NAME,'')),CONCAT(' ',COALESCE(M.LAST_NAME,'')))) AS NAME,
ORDGFT.GIFTMESSAGE AS GIFTMSG,
SUM(FS.RPT_REVENUE) AS REVENUE,
(CASE
WHEN WCA.IS_COST_AVAIL(FS.SUBMITTER_ID, DATE($DB_TIMESTAMP_FUNC({StartDate})$), DATE($DB_TIMESTAMP_FUNC({EndDate})$)) = 1
THEN SUM(FS.RPT_PROFIT)
ELSE NULL
END) AS PROFIT
FROM
WCA.FACT_SALES FS,
WCA.USERREG USRG,
WCA.MEMBER M,
WCA.MBRGRPREL MGR,
WCA.ORDERITEMGIFT ORDGFT
WHERE
MGR.MEMBER_ID = USRG.USERS_ID AND
USRG.USERS_ID = FS.SUBMITTER_ID AND
M.MEMBER_ID = FS.SUBMITTER_ID AND
FS.STORE_ID IN ({storeent_id}) AND
FS.ORDERITEMS_ID = ORDGFT.ORDERITEMS_ID AND
MGR.MBRGRPTYPE = 'ServiceRepGroup' AND
WCA.PARAM_VALUE_IN(CHAR(FS.ORDER_STATUS_ID), 'ORDER_STATUS_SHIPPED') = 1 AND
WCA.TIMEPERIOD_IN(DATE($DB_TIMESTAMP_FUNC({StartDate})$), DATE($DB_TIMESTAMP_FUNC({EndDate})$), FS.PER_ID) = 1
GROUP BY
FS.SUBMITTER_ID,
USRG.LOGONID,
M.FIRST_NAME,
M.MIDDLE_NAME,
M.LAST_NAME,
ORDGFT.GIFTMESSAGE
ORDER BY {Sort} {Order} 
</SQLvalue>
<display>
<standardInfo>
<resourceBundle>
//Resource Bundle Name for text keys like bi.biNLS
//For example: <resourceBundle>bi.biNLS</resourceBundle>
</resourceBundle>
</standardInfo>
<userDefinedParameters>
..........
//Here report specific layout can be defined like ...

<THStyle>TH {font-family: Arial, Helvetica, Sans-serif; font-size: 9pt; line-height: 9pt; color: white; background-color: darkblue; font-weight: bold ;} </THStyle>
<TDStyle>TD {font-family: Arial, Helvetica, Sans-serif; font-size : 9pt; line-height: 9pt; color : Black; }</TDStyle>

<spaceBetweenColumns>20</spaceBetweenColumns>
<columnDefaultAttributes>
<displayInReport>true</displayInReport>
<displayInHeader>false</displayInHeader>
<displayInSummary>false</displayInSummary>
<displayTotal>false</displayTotal>
<columnWidth>0</columnWidth>
<maxEntryLength>999</maxEntryLength>
<columnType>string</columnType>
<columnOptions>ALIGN=LEFT HEIGHT=20 NOWRAP</columnOptions>
</columnDefaultAttributes>

<column>
...............
...............
//Define here column name key and the same in properties file
//Define column data type and customized tags which will display the column data as per tag attributes value.
//For example:
<columnKey>C0</columnKey> <columnName>userLogonID</columnName>
<columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>

<columnKey>C1</columnKey> <columnName>userName</columnName>
<columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>

<columnKey>C2</columnKey> <columnName>giftMsg</columnName>
<columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>

<columnKey>C3</columnKey> <columnName>orderAmount</columnName>
<columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>
<columnType>decimal</columnType>
<setMaximumFractionDigits>2</setMaximumFractionDigits>
<setMinimumFractionDigits>2</setMinimumFractionDigits> 
<columnKey>C4</columnKey> <columnName>profit</columnName>
<columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>
<columnType>decimal</columnType>
<setMaximumFractionDigits>2</setMaximumFractionDigits>
<setMinimumFractionDigits>2</setMinimumFractionDigits> 

</column>
</userDefinedParameters>
</display>
</Report>
</Reporting>

    • biSalesByGiftRegistryReportInputDialog.xml for display text for the input JSP file. The sample of this report xml file is as follows: 
      <?xml version="1.0"?>
<!DOCTYPE dialog SYSTEM "../common/DialogPanels.dtd">

<dialog resourceBundle="bi.biNLS"
windowTitle="salesbyregistry"
finishURL="GenericReportController" >

<panel name="report"
url="biSalesByGiftRegistryReportInputView"
hasFinish="NO"
hasCancel="YES"
helpKey="MC.bi.SalesByGiftRegistry.Help" />
<button name="viewReport" action="finish();"/>

</dialog>

    • biSalesByGiftRegistryReportOutputDialog.xml for display text for the output JSP file. 
      <?xml version="1.0"?>
<!DOCTYPE dialog SYSTEM "../common/DialogPanels.dtd">

<dialog resourceBundle="bi.biNLS"
windowTitle="salesbyregistry"
finishURL="" >

<panel name="report"
url="biSalesByGiftRegistryReportOutputView"
passAllParameters="true"
hasFinish="NO"
hasCancel="NO"
helpKey="MC.bi.SalesByGiftRegistry.Help" />
//Help html files should have been created before writing this command.

<button name="ReportOutputViewPrintTitle"
action="CONTENTS.printButton()" />

<button name="ReportOutputViewOkTitle"
action="CONTENTS.okButton()" />

</dialog>

"
finishURL="GenericReportController" >

  6. Create a new category in the business intelligence report list. For this, create a new xml file with the filename biRptSalesByGiftRegistryContextList.xml. This XML file represents a category of defined BI reports and is referenced in the biContext.xml file. In this file, you can mention the maximum number of reports that you want to create under this category.

    You do not need this file if you develop a new report under an existing category. For example: 

    <?xml version = "1.0"?>

<action resourceBundle = "bi.biNLS"
formName = "ContextListFORM"
helpKey="MC.bi.ordergiftregistryContextReport.Help" >
<parameter
listsize = "15"
startindex = "0"
resultsize = "0"
context = ""
contextConfigXML = "bi.biContext" />
<scrollcontrol
title = "contextList"
display = "true">
</scrollcontrol>
<controlpanel
display="true">
</controlpanel>

</action>

  7. Create a category in the business intelligence reports root xml file, biContext.xml that resides in the same directory. Insert a new context tag for the new report category and then create a new sub-context for the reports coming under this category.
    For example, the lines of code are as follows:

    Create a new category under the store context: 

    <contextList resourceBundle="bi.biNLS" version="1.0">
<!--======== Store Context========== -->
<context name = "store" displayKey = "storeContext" autoLaunch="true">
<!-- ========== Order Gift Registry ========= -->
<entry nameKey="ordergiftregistry"
descriptionKey="ordergiftregistryDescription" 
breadCrumbTrailTextKey="ordergiftregistryContext" 
toolsComponent="CommerceAnalyzer">
<roles>
<role>siteOwner</role>
...............
</roles>
<stores>
<store>B2C</store>
...............
</stores>
<command name = "ShowContextList">
<parameter name="context" 
value="ordergiftregistry" />
<parameter name="contextConfigXML"
value="bi.biContext" />
<parameter name="ActionXMLFile" 
value="bi.biRptSalesByGiftRegistryContextList" />
</command>
</entry>
</context>

    Create a report under the new category: 

    <! -- ===== Order Gift Registry Reports===== -->
<context name = "ordergiftregistry" displayKey = "ordergiftregistryContext" autoLaunch="false">

<! -- ========= Sales by Registry Report=========== -->
<entry nameKey="salesbyregistry" descriptionKey="salesbyregistryReportDescription" breadCrumbTrailTextKey="salesbyregistry" toolsComponent="CommerceAnalyzer">
<roles>
<role>siteOwner</role>
.............. 
</roles>
<command name = "DialogView">
<parameter name="XMLFile" value="bi.biSalesByGiftRegistryReportInputDialog" />
</command>
</entry> 
</context>

  8. Update resources.xml.
    Update WC_installdir/xml/tools/reporting/resources.xml with information about the three XML files created in the previous step. Add lines similar to the following: 
    <resourceXML name="biRptSalesByGiftRegistryContextList"
file="bi/biRptSalesByGiftRegistryContextList.xml" />

<!-- Order Gift Registry -->
<resourceXML name="biSalesByGiftRegistryReportInputDialog"
file="bi/biSalesByGiftRegistryReportInputDialog.xml" />

<resourceXML name="biSalesByGiftRegistryReportOutputDialog"
file="bi/biSalesByGiftRegistryReportOutputDialog.xml" />

<resourceXML name="biSalesByGiftRegistryReport" 
file="bi/biSalesByGiftRegistryReport.xml" />

  9. Set access control for the report. You can give access to users like Marketing Manager or Category Manager to see the reports. For this do the following:

    1. Navigate to WC_installdir\xml\policies\xml directory.

    2. Edit the defaultAccessControlPolicies.xml.

      1. Create two entries in the xml file within
        <!DOCTYPE Policies SYSTEM "../dtd/accesscontrolpolicies.dtd"> <Policies> </Policies> 
        <Action Name="biSalesByGiftRegistryReportInputView"
CommandName="biSalesByGiftRegistryReportInputView">
</Action>

<Action Name="biSalesByGiftRegistryReportOutputView"
CommandName="biSalesByGiftRegistryReportOutputView">
</Action>

      2. Create the action group within <!DOCTYPE Policies SYSTEM "../dtd/accesscontrolpolicies.dtd"> <Policies> <ActionGroup Name="MarketingManagersViews" OwnerID="RootOrganization"> <ActionGroupAction Name=" biSalesByGiftRegistryReportInputView"/> <ActionGroupAction Name=" biSalesByGiftRegistryReportOutputView"/> </AcionGroup> </Policies> For example, this is for giving access to the Marketing Manager. 
        <ActionGroup Name=" biSalesByGiftRegistryReportInputView "
OwnerID="RootOrganization">
<ActionGroup Name=" biSalesByGiftRegistryReportOutputView "
OwnerID="RootOrganization">

        After you have created the entries and the action group, the defaultAccessControlPolicies.xml file will be as follows: 

        <Policies>
<!DOCTYPE Policies SYSTEM "../dtd/accesscontrolpolicies.dtd">

<Action Name="biSalesByGiftRegistryReportInputView"
CommandName="biSalesByGiftRegistryReportInputView">
</Action>

<Action Name="biSalesByGiftRegistryReportOutputView"
CommandName="biSalesByGiftRegistryReportOutputView">
</Action>


<ActionGroup Name="MarketingManagersViews" OwnerID="RootOrganization">

<ActionGroupAction
Name=" biSalesByGiftRegistryReportInputView"/>

<ActionGroupAction
Name=" biSalesByGiftRegistryReportOutputView"/>

</AcionGroup>
</Policies>

      3. Navigate to WC_installdir\bin and run acpload.bat file with this defaultAccessControlPolicies.xml file. This will load the data to the appropriate table in the database. For example: WC_installdir\bin\acpload.bat defaultAccessControlPolicies.xml

    3. Edit the defaultAccessControlPolicies_en_US.xml do the following:

      1. Create two entries for two new jsp files under Business Intelligence section. For example: 
        <! DOCTYPE PoliciesNLS SYSTEM "../dtd/accesscontrolpoliciesnls.dtd">

<PoliciesNLS LanguageID="en_US">

<Action_nls ActionName="biSalesByGiftRegistryReportInputView"
DisplayName_nls="$$$_Business Intelligence Gift Registry Report Input View"
Description_nls="$$$_Specifies the criteria to create Business Intelligence Gift Registry Report"
/>

<Action_nls ActionName="biSalesByGiftRegistryReportOutputView"
DisplayName_nls="$$$_Business Intelligence Gift Registry Report Output View"
Description_nls="$$$_Displays the Business Intelligence Gift Registry Report"
/>

</PoliciesNLS >

      2. Navigate to WC_installdir\bin and run acpnlsload.bat file with this defaultAccessControlPolicies_en_US.xml file. This will load the description data to the appropriate table in the database. For example, WC_installdir\bin\acpload.bat acpnlsload.bat

  10. Stop and start your WebSphere Commerce instance for the changes to take effect. The report is located on the WebSphere Commerce business intelligence report list page (menu). Be sure to login with a user ID that has been assigned one of the roles that may view the report.

Related tasks