Data Assembly

It's very important to consider the structure of the intended report layout when modeling the data structure for the report. The data assembly process involves modeling the report, generating XML schemas, generate Report Definition File (RDF) and sample XML data. The developer only needs to model the data in *.report file and generate code from IFS Developer Studio.

Once the data is modeled and saved, IFS Developer Studio will generate the complete RDF file. Though we recommend to use the generated RDF file as much as possible, this is not a final file. Users can change it if the code generation functionality is not enough for them. If the generated file is used, it will be much easier when it come to maintenance of the report later.

Note: When creating a new report (i.e. deploying a RDF with a new report view) it's important to grant access to new database objects to the IFSSYS user, otherwise you will not see any of the columns in the report view when running the new report schema wizard. If you deploy your code using a complete install.tem, the grant will be added automatically for you. Else you have to do this grant manually by executing the method Installation_SYS.Grant_Ifssys in the database.

Define and model the structure of the data

Report Designer works with XML data as input. XML is a hierarchical data format, this is also reflected in the Report Designer layout and the building blocks available in Report Designer. Most reports have some sort of master detail structure. XML is excellent for describing this sort of data structure. Modeled using the IFS Developer Studio a master detail relationship is described using a child view and connecting this to the master view. For the modeling tutorial please follow this link. >>

Code Generation

After modeling the report, save the .report file or use RMB and "Generate code" option to generate files. As mentioned earlier, 3 files will be generated. Report Definition File (RDF), Schema File(XSD) and Sample XML data file.

The Report Definition File

The Report Definition File, the RDF, contains the PL/SQL code that assembles the data and contain all the code needed to create the desired XML. The code queries the database for the correct data and inserts it into a result table and generate the necessary XML data. IFS Report Designer is XML based and therefore it uses the XML data created. With Crystal Reports this result table (or a view of the table to be precise) is used by the layout tool to populate the layout and render the report. (In some special cases, Report Designer reports also uses the result table)

Create XML

The code below shows how the generated template RDF would look for the component information (module_rep) report.

   General_SYS.Init_Method(lu_name_, 'MODULE_RPI', 'Execute_Report');
   result_key$_                 := Client_SYS.Attr_Value_To_Number(Client_SYS.Get_Item_Value('RESULT_KEY', report_attr_));
   binds$_.version              := Client_SYS.Get_Item_Value('VERSION', parameter_attr_);                                       
   binds$_.component            := Client_SYS.Get_Item_Value('COMPONENT', parameter_attr_);                                       

   Xml_Record_Writer_SYS.Create_Report_Header(xml$_, 'MODULE_REP', 'Component Information');

   has_module_ := FALSE;
   par_module_ := binds$_;
   Xml_Record_Writer_SYS.Start_Element(xml$_, 'MODULES');
   OPEN get_module(binds$_.component, binds$_.version);
   LOOP
      FETCH get_module INTO rec_module_;
      has_module_ := get_module%FOUND OR get_module%ROWCOUNT > 0;
      EXIT WHEN get_module%NOTFOUND;
      Xml_Record_Writer_SYS.Start_Element(xml$_, 'MODULE');
      Xml_Record_Writer_SYS.Add_Element(xml$_, 'MODULE', rec_module_.module);
      Xml_Record_Writer_SYS.Add_Element(xml$_, 'NAME', rec_module_.name);
      Xml_Record_Writer_SYS.Add_Element(xml$_, 'DESCRIPTION', rec_module_.description);
      Xml_Record_Writer_SYS.Add_Element(xml$_, 'VERSION', rec_module_.version);
      Xml_Record_Writer_SYS.Add_Element(xml$_, 'VERSION_DESC', rec_module_.version_desc);
      ...
      ...

The binds$ Type is defined in the report method to hold all the user inputs and the root level attributes. The type binds$ is access from the variable binds$_. This make easier to access them in anywhere from the report.

TYPE binds$ IS RECORD (
      version                   VARCHAR2(2000),
      component                 VARCHAR2(2000),
      module                    VARCHAR2(2000),
      object_name               VARCHAR2(2000));

The general principle is that a CLOB variable (xml$_) is used to hold the XML. For performance reasons no XML parser is used when creating the XML document. Instead simple string concatenation is used. A XML_Record_Writer_SYS API has been developed to make it easy.

Xml_Record_Writer_SYS.Create_Report_Header(xml_, '&VIEW', package_name_) starts off the XML document. This method should always be called before starting to build the XML document/data. Creates the root XML element using the report_id (view name).
Xml_Record_Writer_SYS.Start_Element(xml_ , 'MY_ELEMENT') starts an XML element with the name MY_ELEMENT, meaning that <MY_ELEMENT> will be appended to the current XML.
Xml_Record_Writer_SYS.End_Element(xml_, 'MY_ELEMENT') closes an XML element, meaning that </MY_ELEMENT> will be appended to the current XML. The important thing with this method is that it should always be called on the same loop level as the corresponding start element call, this to ensure that the generated XML is syntactically correct independent of the report data.
Xml_Record_Writer_SYS.Add_Element(xml_, 'MY_ELEMENT', 'rec_<module_name>.<value>') creates an XML element with the supplied value and also closes the element, meaning <MY_ELEMENT>my vale</MY_ELEMENT> will be appended to the current XML.
Report_SYS.Finish_Xml_Report('&VIEW', result_key_, xml$_) stores the XML in the database with a reference to the correct result key. Failing to call this method in the end of the RDF will result in no XML being saved and no XML being available when trying to format the report using a Report Designer layout.

All these create XML statements are generated from the IFS Developer Studio. There's no need to manually edit it anymore.

Report Table

There is a separate table created for each and every report.
This table consist of Result_key, Row_no, Parent_row_no, Rowversion, all the parameters and attributes in report model.

Report View and Column Comments

Report table is used to generate the report view. The parameter and attribute properties set in the model will come under column comments.

CREATE OR REPLACE VIEW FND_SECURITY_PER_USER_REP AS
SELECT *
FROM   FND_SECURITY_PER_USER_RPT t
WHERE EXISTS (SELECT 1 FROM allowed_report a WHERE a.result_key = t.result_key)
WITH   read only;

COMMENT ON TABLE FND_SECURITY_PER_USER_REP
   IS 'LU=FndSecurityPerUserRep^PROMPT=Fnd Security Per User^MODULE=FNDBAS^';

COMMENT ON COLUMN FND_SECURITY_PER_USER_REP.result_key 
   IS 'FLAGS=M----^DATATYPE=NUMBER^';
COMMENT ON COLUMN FND_SECURITY_PER_USER_REP.row_no 
   IS 'FLAGS=M----^DATATYPE=NUMBER^';
COMMENT ON COLUMN FND_SECURITY_PER_USER_REP.module
   IS 'FLAGS=A----^DATATYPE=STRING(20)^TITLE=Module^QUERY=Module:^QFLAGS=OC--L^REF=MODULE/NOCHECK^';
COMMENT ON COLUMN FND_SECURITY_PER_USER_REP.read_only_methods
   IS 'FLAGS=A----^DATATYPE=STRING(200)^TITLE=Include Read Only Methods^QUERY=Read Only Methods:^QFLAGS=OCS--^ENUMERATE=Fnd_Yes_No_API.Enumerate^QVALUE=:Fnd_Security_Per_User_RPI.Default_Read_Only_Methods^';
COMMENT ON COLUMN FND_SECURITY_PER_USER_REP.user_id
...
...

The following column attributes are used:

Item

Value

DATATYPE

The data type of the column.

FLAGS

Compatibility with logical units

TITLE

Column title, as it will appear in the report.

QUERY

Make this column 'Queryable' for the end user (if value is not NULL). The value is the actual prompt given, and this prompt is in turn translated. The prompt is limited in length to 50 characters.

QFLAGS

Allows an easy control of end user input and allows or disallows certain combinations.
QFLAGS = MCSBL
M=Mandatory C=Custom (simplified or 'values only') S=Single flag B=Allow Between expressions L=Allow wildcards ('%' and '_').
All flags have opposites specified by a '-' (minus). The opposite of Custom is Advanced or 'Allow functions as values', which in turn corresponds to the functionality in the Query Dialog. The opposite of 'Single' is of course multiple values (separated by ';') and are specified by a '-'.

QVALUE

Default value in report order dialog. This calls the method in question and assumes a single OUT parameter which is propagated to the corresponding field.
Example: QVALUE =%^ or QVALUE=:Demo_Customer_RPI.Default_Company^ (the colon is required and notifies Foundation1 that this is actually a method).

ENUMERATE

Populates a drop down menu in the report order dialog for the actual field.
Should calls an enumerate method and assumes a single OUT parameter, and that the values are 'field separator' separated.
Example: ENUMERATE =Demo_Customer_RPI.Enum-erate_Company^

VALIDATE

Performs a call to allow special validation of user input. Should call a validate method and assumes a single IN parameter. Values are validated by simply returning without exception. Use Error_SYS.Appl_General to signal error.
Example: VALIDATE=Demo_Customer_RPI.Validate_Com-pany^

REF

Reference to logical unit, or to a specific view, to be used for ListOfValues.
Example: REF=DemoOrder(company_id)^.
It is important to differentiate between LU-name and view name. If the entire name is uppercase or if it contains underscores, It is assumed to be a view, else the view is obtained by checking the corresponding logical unit.

Item	Value
DATATYPE	The data type of the column.
FLAGS	Compatibility with logical units
TITLE	Column title, as it will appear in the report.
QUERY	Make this column 'Queryable' for the end user (if value is not NULL). The value is the actual prompt given, and this prompt is in turn translated. The prompt is limited in length to 50 characters.
QFLAGS	Allows an easy control of end user input and allows or disallows certain combinations. QFLAGS = MCSBL M=Mandatory C=Custom (simplified or 'values only') S=Single flag B=Allow Between expressions L=Allow wildcards ('%' and '_'). All flags have opposites specified by a '-' (minus). The opposite of Custom is Advanced or 'Allow functions as values', which in turn corresponds to the functionality in the Query Dialog. The opposite of 'Single' is of course multiple values (separated by ';') and are specified by a '-'.
QVALUE	Default value in report order dialog. This calls the method in question and assumes a single OUT parameter which is propagated to the corresponding field. Example: QVALUE =%^ or QVALUE=:Demo_Customer_RPI.Default_Company^ (the colon is required and notifies Foundation1 that this is actually a method).
ENUMERATE	Populates a drop down menu in the report order dialog for the actual field. Should calls an enumerate method and assumes a single OUT parameter, and that the values are 'field separator' separated. Example: ENUMERATE =Demo_Customer_RPI.Enum-erate_Company^
VALIDATE	Performs a call to allow special validation of user input. Should call a validate method and assumes a single IN parameter. Values are validated by simply returning without exception. Use Error_SYS.Appl_General to signal error. Example: VALIDATE=Demo_Customer_RPI.Validate_Com-pany^
REF	Reference to logical unit, or to a specific view, to be used for ListOfValues. Example: REF=DemoOrder(company_id)^. It is important to differentiate between LU-name and view name. If the entire name is uppercase or if it contains underscores, It is assumed to be a view, else the view is obtained by checking the corresponding logical unit.

Report Registration

Following code is generated for report registration.

Report_SYS.Define_Report_('MODULE_REP', 'FNDBAS', 'Module', 'Component Information', null, 'MODULE_RPI.Execute_Report', 0);
Report_SYS.Define_Report_Text_('MODULE_REP', 'COMPANY_NAME', 'IFS');
Report_SYS.Refresh_('MODULE_REP');
Report_Lu_Definition_API. Clear_Custom_Fields_For_Report(‘MODULE_REP’);
Report_Lu_Definition_API.Enable_Custom_Fields_for_LU(‘MODULE_REP’,  ‘Module’, ‘MODULES/MODULE’);

Report Refresh
Report_SYS.Refresh_ is essential for the report cache to be updated.

Define Report
The report definition contains properties for the report objects and is used by Foundation1 in runtime to perform specific tasks.
Report_SYS.Define_Report_(<Report_Id>, <Module>, <Lu_Name>, <Report_Title>, <Table_Name>, <Report_Method>, <Life>, <Master>, <Override_Method>, <Remove_Method>, <Report_Mode>)

Variable

Description

Report_Id

Id of this report. Same as report view name.

Module

Component that this report belongs to.

Lu_Name

Logical unit that this report belongs to.

Report_Title

The report title, as it will appear when referenced. This title can be translated, and in runtime the translation to the proper language is used instead. The title is limited in length to 50 characters.

Table_Name

The table where the result set for a report is stored.

Report_Method

This point to the actual report method, contained in the report package.

Life

The number of days this report is kept in the report archive before it is removed.

Master

This refers to the master for this report. Only to be used when defining an alternative report. Default value is NULL.

Override_Method

This points to the override method, contained in the report package. Only to be used when defining a master report. Default value is NULL.

Remove_Method

This point to the remove method used by the framework when removing the report. Only to be used if custom operations are needed to complete the removal of a report. Default value is NULL.

Report_Mode

This is a report mode, used to differentiate between different implementations of reports. Default value is PLSQL1.2.

Variable	Description
Report_Id	Id of this report. Same as report view name.
Module	Component that this report belongs to.
Lu_Name	Logical unit that this report belongs to.
Report_Title	The report title, as it will appear when referenced. This title can be translated, and in runtime the translation to the proper language is used instead. The title is limited in length to 50 characters.
Table_Name	The table where the result set for a report is stored.
Report_Method	This point to the actual report method, contained in the report package.
Life	The number of days this report is kept in the report archive before it is removed.
Master	This refers to the master for this report. Only to be used when defining an alternative report. Default value is NULL.
Override_Method	This points to the override method, contained in the report package. Only to be used when defining a master report. Default value is NULL.
Remove_Method	This point to the remove method used by the framework when removing the report. Only to be used if custom operations are needed to complete the removal of a report. Default value is NULL.
Report_Mode	This is a report mode, used to differentiate between different implementations of reports. Default value is PLSQL1.2.

Only one definition can be made for a report.

Report_SYS.Define_Report_Text_(<Report_Id>,<Text_Name>,<Text>)

Variable

Description

Report_Id

Id of the report that uses this text.

Text_Name

Unique name of this text. This name is used as an input item in Report Builder.

Text

Text to be used in the report. This text is translatable.

Variable	Description
Report_Id	Id of the report that uses this text.
Text_Name	Unique name of this text. This name is used as an input item in Report Builder.
Text	Text to be used in the report. This text is translatable.

Texts are optional and several texts can be defined for a report.