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.>>
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)
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.
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:
|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.
|REF||Reference to logical unit, or to a specific view, to be used for ListOfValues.
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.
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_SYS.Refresh_ is essential for the report cache to be updated.
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>)
|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>)
|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.