Advanced reports is a feature that allows you to generate reports that need to go beyond the following cases:
- Generate a non-tabular report such as an XML for feeding into an external system for consumption.
- Linking totally unrelated entities due to the absence of parent/child LOOKUP-type fields. For example, ACCOUNT and COLLECTOR_FIELDS without having to go through the Acccount -> Activity -> Collector Stage link. Custom entities that are independent but may contain identifiers or patterns in their custom fields that need to be pattern matched via SQL in order to obtain the data.
Take the example report below for instance. Instead of representing the data as a normal table, the data is rendered in XML format:
The following steps can be taken in order to generate an advanced report.
1. Create a new advanced report.
Navigate to Reports > Manage Reports > Reports. This will display the list of existing reports in the org. If only one report exists, it will automatically be opened in view details mode.
Click the New button to open the new report wizard. At this point, enter the desired report name, the reference/base entity and the report location under the Reports menu.
Click Next to start authoring the actual content of the advanced report.
2. Author the SQL statement.
On this screen, select the Advanced radio button for the Report Builder type. This will expose the SQL report entry field and the modified Report Bind Variable table instead of the date and non-date report filter grids.
Use the Query field to author the desired SQL. Standard PL/SQL functions can be used when authoring the report. Use the Report Bind Variables table to add user inputs that will later be used in the SQL. If an XML report is needed, tick the XML Format checkbox. A few notes and tips when authoring the advanced report:
- Use of joins are allowed. This is particularly useful when dealing with entities that do not have parent-child relationships that are normally done via LOOKUP-type fields.
- Oracle SQL methods for hierarchical queries can be used such as START WITH, CONNECT BY, etc.
- When creating XML reports, the standard Oracle XML functions (XMLType) can be leverage here.
- The Report Bind Variables grid allows you to create filter variables that can be used in the where clause(s) in the SQL that you are authoring. The data entered by the user can be overridden and are not necessarily linked to the Field Name value used. The Field Name can just be used as a source field to get a certain type of data entry. For example, the AccountObj.AllowPricingInDifferentCurrency can be used as a bind variable to get a checkbox-type field on the report's filter during execution but can be used to check against a different field in the SQL.
Take the example Report Bind Variables below. It aims to obtain two date values and a product name from the user before executing the report:
Then, the SQL is authored in the following manner:
with source_acc_product_cnt as ( select p2.Id as productId, count(ap2.Id) as cnt,p2.Name as PrName, dates2.dt from Account_Product ap2 join Rating_Method rm2 on rm2.id = ap2.RatingMethodId join Product p2 on p2.Id = ap2.ProductId cross join ( select add_months(to_Date(:StartDate,'mm/dd/yyyy'),level-1) as "dt" from dual connect by level <= months_between(to_Date(:EndDate,'mm/dd/yyyy'),to_date(:StartDate,'mm/dd/yyyy')) + 1 ) dates2 where to_date(:StartDate,'mm/dd/yyyy') between trunc(ap2.StartDate) and trunc(ap2.EndDate) and upper(p2.Name) like '%' ||upper(:ProductName) || '%' and rm2.RatingMethodType = 'Subscription' group by p2.id, p2.Name, dates2.dt ) select src.PrName as "ProductName", to_char(nvl(result_dt,src.dt),'mm-yyyy') as "Date", src.cnt - nvl(sum(nvl(CntDeactivates,0)) over (partition by src.PrName order by nvl(result_dt,src.dt)),0) as "Cnt Active Subscriptions", nvl(CntDeactivates,0) as "Deactivated", sum(nvl(CntDeactivates,0)) over (partition by src.PrName order by nvl(result_dt,src.dt)) as "Deactivation Cumulative", round(nvl(CntDeactivates,0) / src.cnt * 100,2) || '%' as "Chum Rate", round(sum(nvl(CntDeactivates,0)) over (partition by src.PrName order by nvl(result_dt,src.dt)) / src.cnt * 100,2) || '%' as "Chum Rate Cumulative" from source_acc_product_cnt src left join ( select dates.dt as "result_dt", p.id as "ProdId", p.name as "ProdName", count(ap.Id) as "CntDeactivates" from ( select add_months(to_Date(:StartDate,'mm/dd/yyyy'),level-1) as "dt" from dual connect by level <= months_between(to_Date(:EndDate,'mm/dd/yyyy'),to_date(:StartDate,'mm/dd/yyyy')) + 1 ) dates left join Account_Product ap on dates.dt = trunc(ap.EndDate,'mon') left join Rating_Method rm on rm.id = ap.RatingMethodId left join Product p on p.Id = ap.ProductId and upper(p.Name) like '%' || upper(:ProductName) || '%' where 1=1 and nvl(rm.RatingMethodType,'-1') = case when ap.Id is not null then 'Subscription' else '-1' end group by dates.dt, p.id, p.name ) result on src.productId = result.prodId and src.dt = result_dt where 1=1 order by src.PrName, src.dt
Notice that the bind variables are manipulated and compared against different fields that are sourced in SQL statement. This allows for added flexibility with performing pattern matching, etc. to filter the records needed for the report.
3. Save the report and check the output.
Click Next to get to the next page of the wizard where you can enter the permissions of the various roles in the system and how they can use the new report. Finally, click Save to finalize the report.
At this point, you can check the execution of the report by navigating to the location that you specified on the first page of the new report wizard, fill in the filter conditions and then click the Run Report button. This will then generate the report data.