How To Create Advance Reports

Overview

Advanced reports is a feature that allows you to generate reports that need to go beyond the following cases:

  1. Generate a non-tabular report such as an XML for feeding into an external system for consumption.
  2. 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:

Sample_Advanced_Report_Screenshot.png

Procedures

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.

Reports_List.png

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.

New_Report_Wizard_Page_01.png

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.

New_Report_Wizard_Page_02.png

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:

  1. 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.
  2. Oracle SQL methods for hierarchical queries can be used such as START WITH, CONNECT BY, etc.
  3. When creating XML reports, the standard Oracle XML functions (XMLType) can be leverage here.
  4. 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:

Report_Bind_Variable.png

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.

Saved_Advance_Report.png

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.

Sample_Report_Output__Table_.png

Related Topics

Have more questions? Submit a request

Comments

Powered by Zendesk