smart report framework

8/3/2019 Smart Report Framework

http://slidepdf.com/reader/full/smart-report-framework 1/14

Smart Report framework

Smart report framework which helps in creating simple reports which can be generat

nstantaneously, scheduled and executed on condition. The report can be sent as a formatted html

an electronic email.

Features currently working:

1. A Single report created in framework can be used to generate reports instantaneously as wel

as scheduled to be sent as email attachments.

2. Security to limit reports available to a person can be controlled. (Profile: Unity : Smart Report

Group)

3. Reports can be conditionally generated

4. Framework also can be used to created search only forms. This is useful for management to

instantaneously generate report from the instance.

5. Report output can be downloaded to excel

6. Report parameters can be defined and the parameters can also have list of Values. This woul

help AD team to build simple reports declaratively for business users.

7. Reports can be scheduled to run at specific times on specific days of the week.

8. Report Output will be sent as email attachment.

9. Up to 6 different email addresses can receive the output of the scheduled email report.’

10. Queries written here can have access to all objects (GV$ views etc).

Groups

A group is a Collection of reports. A group defines a set of reports which can be run by a user. A

group is defined for a user in profile “Unity : Smart Report Group”. Use the Report Groups screen to define

roups.



Setup Reports

A report is a Query which can be queried adhoc on screen , exported to excel and also sent as email

attachment.

t can have parameters and LOVs to pick parameters.

Framework has a limitation of 20 Select Columns and 10 Parameters.

Parameters can be VARCHAR2, Number, Date or have a LOV.

Use the reports Setup screen to define Reports

The Screen above is the first screen in report Setup. You will reach this screen when you click the

Report Setup link from menu.

Query for reports you want to edit. If you want to create you can do so by clicking on Add Another

Row Button.

Click on “Update Report” to enter /update the report definition. Please save the details before you

click on Update Report



The above form provides the ability to create or Update report definition.

A report has 3 sections

1. Report Select columns – columns which need to be printed on the report

2. From and Where clause



3. Parameters – The parameters which need to be bound to the query before execution

Lets take an example : I want to have a report which prints me out all the values which are define

n system (at site, application, resp, and user level) for a given Profile Name

SELECT SUBSTR(e.profile_option_name,1,30) PROFILE,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') profile_Level,

DECODE(a.level_id,10003,(select responsibility_name from apps.fnd_responsibility_vlwhere responsibility_id = a.level_value ) , 10001,(a.level_value ), 10002,(select APPLICATION_NAME from apps.fnd_application_vl

where application_id = a.level_value ),10004,(select user_NAME from apps.fnd_user

where user_id = a.level_value ) ,a.level_value ) profile_level_value ,

profile_option_valueROM apps.fnd_profile_option_values a ,

apps.fnd_profile_options_vl e WHERE a.profile_option_id = e.profile_option_idAND e.profile_option_name LIKE '&PROFILE_OPTION_NAME'

There are 2 ways to create this report in this System. You can choose a style which suits

you.



STYLE 1:

Write your query in toad and create a wrapper to in reports . You can see example of this approach i

creen shots above.

Select Columns

Column Sequence Column Name Column Title1 Profile Profile Name2 profile_level Profile Level3 profile_level_value Profile Level Value4 Profile_option_value Profile Option Value

FROM and WHERE clause

SELECT SUBSTR(e.profile_option_name,1,30) PROFILE,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User')

profile_Level,DECODE(a.level_id,10003,(select responsibility_name from apps.fnd_responsibility_vl

where responsibility_id = a.level_value ) ,10001,(a.level_value ),10002,(select APPLICATION_NAME from apps.fnd_application_vl

where application_id = a.level_value ),10004,(select user_NAME from apps.fnd_user

where user_id = a.level_value ) ,a.level_value ) profile_level_value ,

profile_option_valueFROM apps.fnd_profile_option_values a ,

apps.fnd_profile_options_vl e

WHERE a.profile_option_id = e.profile_option_idAND e.profile_option_name LIKE :1)

Please use numbered bind parameters. Named bind parameters are not supported

Sequence

Parameter Name Title Parameter Type

Required

LOVRequired

Parameter LOV Query

1 PROFILE_OPTION_NAME

ProfileName

Varchar2

Y Y selectPROFILE_OPTION_NAME value,

USER_PROFILE_OPTIO_NAME Descriptionfromfnd_profile_options_v

Parameter LOV Query can only have two columns and have to aliases “Value” and “Description

Value will be bound to the report Query.



The above approach is easier as you can do a copy paste from Toad and encapsulate in

braces “(“ and “)”



STYLE 2 :

Break your query into parts and define in reports system. You can see the example of that in screen

hot below.



Select Columns

ColumnSequence

Column Name ColumnTitle

1 RESPONSIBILITY_NAME Responsib

lity Name2 RESPONSIBILITY_KEY Responsib

lity Key

FROM and WHERE clause

nd_responsibility_vlWHERE menu_id IN (SELECT menu_id

FROM ( SELECT *FROM fnd_menu_entries_vl

START WITH function_id IN

(SELECT function_IdFROM fnd_form_functionsWHERE function_name LIKE

:1)CONNECT BY PRIOR menu_id = sub_menu_id)

)

Please use numbered bind parameters. Named bind parameters are not supported

Sequence ParameterName

Title ParameterType

Required LOVRequired

Parameter LOVQuery

1 Function Function Varchar2 Y Y selectFUNCTION_NAMEValue ,USER_FUNCTIONNAME descriptiofromfnd_form_functios_vl

Parameter LOV Query can only have two columns and have to aliases “Value” and “Description

Value will be bound to the report Query.



Assignments

Reports can be assigned to Groups. Access to a group is set in Profile "Unity : Smart Report Group". Users w

be able execute all the reports assigned to the group which is set in profile "Unity : Smart Report Group". If the profile

Unity : Smart Report Group" value is set to ALL , then user will be able to execute all reports defined in system

ou can access this form by clicking “Assignments” in Smart Reports menu.



Run Reports

Reports defined and assigned (use assignments screen) can be run by clicking on Reports in Smart

Reports menu. User will be able to see reports only those which are assigned to his group.

Click on “Run Report” to run the report.

n the Reports screen , enter the parameters and click on “Generate Report” to execute the report



Click on “Export to Excel” to Download the Report into an excel File.

Note: If a report doesn’t have any parameters, the report will be automatically executed when you

click on “Run Report”



Scheduling

Any report without parameters can be scheduled to run at defined time intervals and send HTML

ormatted reports if the query returns any results

You can access this form from by clicking “Scheduling” in Smart Reports Menu.

Schedule – Can be 15Minutes, 30 Minutes, Hourly, Daily

Report Code – Code of the report

Enabled Flag – Enabled or Disabled.

Start Hour (0-24)-Start Min (0-60) – Specify when the report should Start in GMT

End Hour (0-24)-End Min (0-60) – Specify when the report should end in GMT

Recipient Role (1-3) – These are employee Global UIDs.

Email Address (1-3) – Group email addresses like ‘[email protected]’ etc

Conditional Report? – Execute this report only when a condition is met (defined in Query for

Condition)

Query for Condition – Query can return ‘TRUE’ or’FALSE’ only . Report will be sent only if returned

value is ‘TRUE’



Concurrent Program

un the program Unity: Smart Report to send all reports defined in the Repor

scheduling.

smart report framework

Documents