Monday, May 24, 2010

Step 20 – Apex Developer Day - Building and customizing an interactive Report

Interactive Reporting Regions enable end users to customize reports. Users can alter the layout of report data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different charts, and their own computations. Users can create multiple variations of the report and save them as named reports and download to various file formats including comma-delimited file (CSV) format, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF).

Another new feature shown in this tutorial is Application Date Format. You can define a date format that will be used throughout your application. This date format will be used to alter the NLS_DATE_FORMAT database session setting prior to showing or submitting any page within the application. This format is used by all reports showing dates and is also picked up by form items of type 'Date Picker (use Application Date Format)'. This can be set using:

1. To create a new report , click Create Page


2. Select Report for Page Type, make sure Click Next


3. Select Interactive Report and Click Next


4. For Page Name and Region Name enter Interactive External Employees, and click Next


5. Click Next


6. Now we can create the report, click Query Builder, select EMP table and all columns , Click Run to see the results and Click Return


7. Click Next


8. Click Run


9. Now we can run the report, Click Run


10. The report is displayed. In the next section, you examine some of the Interactive Report features.


Manipulating Your Interactive Report

11. Now that the Interactive Report has been created, you can manipulate the report in many ways.

We can search for values for example salesman. Click GO


12. Notice that only rows with the job SALESMAN are displayed


13. To hide a column, you can select the column header link and then click the hide icon column


14. Notice the column no longer appears


15. To sort on a column, click the header you want to sort on and select the Sort Ascending or Sort Descending icon. Select the Hiredate column and click the Sort Ascending icon


16. You can delete the filter you created earlier. Click the red X next to the filter.


17. You can create a control break on a column. Select the Actions menu pulldown and select Control Break.


18. You want to break on the Department ID. Select Deptno from the list of columns and click Apply.


19. The control break was created. Notice that the column was eliminated in the list and now appears before each breakpoint in the report


20. You can also highlight a particular cell or row in the report based on some criteria. Click the Actions menu and select Highlight.


21. You want to highlight the row of an employee if their salary is higher than $1,000. Enter Top Salaries for the Name, make sure Enabled is set to Yes, and Highlight Type is set to Row. Select [yellow] for Background color and [blue] for Text color. Under Highlight Condition, select Sal for the Column, select  > for the Operator and 1500 for the Expression and click Apply


22. Notice that a highlight rule was created at the top of the report and the rows that met the criteria are highlighted


23. You can create a computed column. In this case, you will create a column to show the monthly salary for each employee. Click the Actions menu and then select Compute.


24. Enter Total Salary for the column header and select the down arrow for Format Mask and select the $5,234.10 from the list.


25. From the list of Columns scroll down and select Salary. Notice that the letter corresponding to the column appears in the computation area. This is the column alias. Click + in the keypad and then nvl(G,0).Notice G is the letter for the column Comm que es la commission del empleado. The Function NVL means that in case the commission is null then a value of 0 will be assigned to column, it is because null values cannot be summed to any number. Then click Apply


26. Notice that the computed column now appears in the report.


27. You can also aggregate a particular column. Click the Actions menu and select Aggregate.


28. You want to total the Monthly Salary column. Select Sum for the Function and **Total Salary (the ** indicates that the column is a computed column) for the Column. Then click Apply.


29. You now see a total salary for each department. Note: the aggregated column inherits any formatting from the base column


30. Each End-User who have the privileges of use interactive reports will be able to save all changes made to the report, with the advantage that these changes will be able only for him and nobody else. It means that each end-User can made its own reports base on a predefined base report. You can save all the customization you have made to this report. Click the Actions menu and select Save Report.


31. Enter Salary Report by <Your Name> for the Name and click Apply. Note: Save as Named Report is available to all authenticated end users (when saving reports is enabled for the region).  The report settings are displayed as a tab above the report, with the name you provide, and is visible *only* for the APP_USER who saved it. Save as Default Report Settings is only available to developers.  These settings are applied to anyone who comes to the page for the first time in a session. If you are a developer and save it as Default report the changes will be displayed to all users running this report.


32. Notice that you now see a tab with all the display changes you made. This customized view will be available each time you login.


33. You can view a record in the report by clicking the icon before the row you want to view.


34. The record is displayed. You can navigate back to report, click Report View


35. To show the original report (without the customizations), click the Working Report tab.


36. Click the Action menu and click Reset.


37. Click Apply


38. The report without changes is displayed. As developer you can change the way that the Interactive Report is rendered for all users. Perform the following steps:

Click the Edit Page <N> link in the Developer Toolbar.


Note: If a login is required (Maybe because the session was timeout) login again as your developer user <YourName>. If the Properties of the report is not displayed got to Application Builder – Project tasks Application <N> - Interactive External Employees


39. You can hide columns so that they do not appear in the report. For the EMPLOYEE_ID , select Hidden for Display Text As and click Apply Changes.


40. Run the page to see that the column are now hidden from the report. Note that the columns will also no longer appear under Select Columns from the Actions menu. You want to make some additional changes, click Edit Page <N> in the Developer toolbar.


41. Under Regions, select the Interactive Report link again.


42. Select the Search Bar tab.


43. You can change the way that your Interactive report is rendered to the end user. You can change the search bar and Actions menu in this section. In this case, deselect the Highlight check box. Then click the Download tab.


44. Notice that CSV is the only download format selected. Select XLS and PDF. Then click the Region Definition tab


45. You can change the query that is executed when the report is run. In this case, you want to change the column for COMM to the COMMISSION in the EMP table. Click the Source tab.


46. After the modification click Apply Changes


47. Since you modified the query, a confirmation window appears that tells you that you need to select Reset from the Actions menu in order for you to see the changes in your report. Click Apply Changes.


48. Click Run Page <N> to see the changes you made.


49. The report is displayed. In order for the Department Name column to appear, you need to add the column to the report.


50. Select the Actions menu. Notice that the Highlight option is no longer in the list. Click Select Column.


51. Select Commision from the list of Do Not Display columns and click the move icon so that Department Name now appears under the list in the Display in Report area.


52. Click Apply.


53. The Commission column is now shown.


54. To check that the other download options are displayed, click the Actions menu and select Download


55. The three options you selected are displayed. Click PDF

NOTE: To perform this action you MUST start the BI Publisher. It must be running if you have not stop it, from the previous lab #9, if it is down please double click the BI Publisher ICON from the Desktop



56. When the download dialogue appears, select the Open With Document Viewer option and click OK.


57. The PDF is displayed. Close this window.


58. Navigate to the Page <N> Page Definition. Under Regions, select the Interactive Report link again.



59. From the Interactive Report, you can create a group that will organize the single row view and group columns together in Select Columns from the Actions menu. Click the Groups tab.


60. Click Add Group.


61. Enter Sensitive Data for the Group Name and click Create.


62. To add the columns to assign to the Sensitive Data Group, click the Edit icon.


63. Select Sal and Commission Pct from the list and click >. Then click Apply Changes.


64. Click Run Page <N>.


65. Select the Single Row View icon in front of a row.


66. Notice that on the Single Row View page, the two columns are now grouped together. Click Report View to return to the report


67. Now we would like to eliminate the option to edit a single row view. Click Edit Page <N>


68. Under Regions, select the Interactive Report link again.


69. Click the Link Column tab.


70. Select the Link Column drop down list.


71. Notice that you can link to a Custom Target (or another page or URL) or Exclude this column altogether. SelectExclude Link Column.


72. Click Apply Changes


73. Click Run Page 1.


74. Notice that the Single Row View icon for each row in the report no longer appears. Click on Application <N>


No comments:

Post a Comment