Technote

(FAQ)

Implementing dynamic queries for operational reports based on a user's role

How do you implement dynamic queries for operational reports in the WebSphere Commerce Accelerator based on a user's role?

Answer

There are three possible solutions.

• Solution 1:
  Create new report based on the user that is logged on and update the JSP page for the WebSphere Commerce Accelerator menu to display the appropriate menu item based on the user's role.
  
  Here is the customization guide for adding a new report:
  http://publib.boulder.ibm.com/infocenter/wchelp/v6r0m0/index.jsp?topic=/com.ibm.commerce.customizetools.doc/tasks/trfaddnewreport.htm
  
  

• Solution 2:
  Modify the existing report queries to use CASE-WHEN-THEN-ELSE statements to compose one complex statement to return a different view based on the different roles. This solution may also require you to add a new parameter (such as member_id or role_id) to the report xml. For example, the OrderStatusReport.xml currently has StartDate, EndDate, and Currency as supported parameters in this report.
  
  Here is an example of how to use the CASE-WHEN-THEN-ELSE statement:

<Report  reportName="ReviewCustomerContactPointsReportMonthlyTime" online="true"  >
<comment></comment>
<SQLvalue>
select
CASE WHEN {member_id}=3000 THEN orders.totalproduct
WHEN {member_id}=3001
THEN orders.totalproduct1 ELSE 0.0 END
orders.orders_id,
                CASE WHEN {member_id}=3000 THEN orders.totalshipping
WHEN {member_id}=3001
THEN orders.totalshipping1 ELSE 0.0 END
orders.orders_id,
  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  **Similarly one case statement for every field that you want to fetch **
  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
from orders
where orders.storeent_id = {storeent_id}
and orders.status in ('S', 'D')
and orders.lastupdate &gt;=
$DB_TIMESTAMP_FUNC({StartDate})$
and orders.lastupdate &lt;=
$DB_TIMESTAMP_FUNC({EndDate})$
order by orders.lastupdate
</SQLvalue>

The fields returned, for instance orders.totalproduct and orders.totalproduct1 should be of the same data type. Each field requires a separate case statement.

• Solution 3:
  Add a new report element instead of creating a new report or modifying an existing query. For example, you could create a different report section within one report xml like the following example in OrderStatusReport.xml.
  
  Similar to solution 2, this may also require a new parameter to distinguish the different roles. Then modify current report files (OrderStatusReport.xml, ReportOrderStatHelper.jsp, OrderStatusReportInputView.jsp) to display the different report names with different role.
  
  Here is a snippet of a report element from the OrderStatusReport.xml:
  <Report  reportName="OrderStatusReportShipped" online="true" > 
    <comment></comment> 
    <SQLvalue> 
                 select          orders.orders_id,
                                 $DB_DATE_FUNC
                                 (orders.lastupdate)$,
                                 orders.totalproduct,
                                 orders.totaladjustment,
                                 orders.totaltax,
                                 orders.totalshipping,
                                 orders.totaltaxshipping
                 from orders
                 where orders.storeent_id = {storeent_id} 
                         and orders.status in ('S', 'D') 
                         and orders.lastupdate &gt;=
                         $DB_TIMESTAMP_FUNC({StartDate})$ 
                         and orders.lastupdate &lt;=    
                         $DB_TIMESTAMP_FUNC({EndDate})$ 
                            order by orders.lastupdate 
   </SQLvalue>

 

Document Information

Current web document: http://www.ibm.com/support/docview.wss?uid=swg21292816