Skip to Content

This blog shows how you can easily modify a sap standard report to your own company specific report with SAP NW IdM under a MS SQL server. The generated report will be contain information about the identity store and the propsing business roles. In order to be in the future flexible, the report from individual components, which are independent and again can be used, is provided.

Software / Installation

The conversion the report provided in this blog is accomplished, as of SAP intended with JasperReports. For this the following Libarys was used.

  • Jasperreports-1.3.0.jar
  • iText-2.0.8.jar
  • Commons-javaflow.jar
  • Commons-beanutils-core.jar
  • commons-beanutils.jar
  • commons-beanutils-bean-collections.jar
  • commons-collections-3.2.1.jar
  • commons-digester-1.8.jar
  • commons-logging-adapters-1.1.1.jar
  • commons-logging-api-1.1.1.jar
  • commons-logging-1.1.1.jar

The Jasperreports libraries is responsible for the production of the actual report. In order to let the report in the pdf format spend, the open and freely available Java library iText is used. Additionally different Commons libraries are used. It is very important that you use the iText-jar file in the version 2.0.8, because in newer versions the function names changed. Further you can’t use the file commons-javaflow-20060411.jar, which is spent with JasperReport. This must be downloaded separately.

In the first step the Jar files must be put down on the file system and be added over SAP NW IdM system to the classpath. For this you go in the menu on „tools “ and then on „option… “.

image

Under Classpath extension you add the individual Jar files.

image

Line of actions

The following slide shows the exactly proceeding. As the first a jrxml file existing in the installation listing is adapted and extended. Next a standard job is produced, which accesses a Java class. With their assistance the Jrxml file is converted into a Jasper specific format. In the last step from the Jasper file the report is provided. This can be produced depending upon job in the PDF or HTML format.

image

The following picture shows, how the report is to look later. The first table contains the information about the identity store. To use these information in another report again, we save these in a own jrxml file.The second table contains information over the approvee and the approver of a role, the date of requesting, the reason as well as the current status. The two jrxml files are merged over the tag “subReport” into a further jrxml file.

image

Structure of a Jrxml file

Each Jrxml file begins and ends with the JasperReport tag. Subsequently, follow property-, import- and style-tags. The appearance of the file can be adapted by the Style tags accordingly.

Within queryString-tag the SQL statement is defined, thus directly the SQL server one accesses. In the detail-tag static texts can be inserted by tag “staticText” as well as dynamic texts (textField).

Creation of the Jrxml files

You find the template jrxml files under the SAP NW installation path (%\SAP\IdM\Identity Center\Reporting\MSSQL\entryReport\template). Copy the files „Entry report.jrxml“, „subEntryInformation.jrxml“ and „subEntryValues.jrxml“ in new. Folder. Than rename the files accordingly the last picture. Notice the path to the files, we need it in the pass later. Open the file RequestForApproval.jrxml in an editor. First change the sql-statement in the “queryString”-tag.

SELECT e.avalue as [Approvee], r.taskname as [Taskname], a.approvetime as [Date], r.reason as [Reason], a.approver as [Approver], r.status as [Status] from mxwv_myrequests r, mcmv_old_approvals a, mxiv_entries e where a.mcmv_mskey = r.mskey and e.mskey = r.mskey and e.attrname = ‘mskeyvalue’

As next the columns indicated in the SELECT statement are to be defined as fields.

image

Define in “title”-tag under “text” [CDATA [Requests]] “text” a heading. In the “columnHeader”-tag you insert for the headings of the table “staticText”-tags (CDATA[Approvee]).

image

The “detail”-tag contains the fields, which were defined above. Importantly here are, the x and Y-coordinates, which must be set manually. Further the height and width can be adapted, the data may at the most to those of the defined band correspond.

image

The SQL statement of the file IdentityStoreInformation.jrxml reads as follows:

SELECT MXIV_ENTRIES.IS_ID, MXI_IDStores.IdStoreName as IDSTORENAME FROM MXIV_ENTRIES INNER JOIN MXI_IDStores ON MXIV_ENTRIES.IS_ID = MXI_IDStores.IS_ID WHERE (MXIV_ENTRIES.MSKEY = $P{MSKEY}) AND (MXIV_ENTRIES.AttrName = ‘MSKEYVALUE’) OR (MXIV_ENTRIES.MSKEY = $P{MSKEY}) AND (MXIV_ENTRIES.AttrName = ‘DISPLAYNAME’)

IS_ID and IDSTORENAME are to be put on as fields. Under “ColumHeader”-tag the two static texts are to be furnished and under “detail”-Tag the dynamic texts. In the last step of the XML treatment the file ApprovalReport.jrxml is adapted. This file forms the outside frameworks. Over the “image”-tag you can integrate your company logo, as well as defined the current date and a page number.

Create the job in the SAP NW IdM

In the next step you create a job in a folder at the SAP NW IdM (in the context menu, “next” -> “run job wizard…”)

image

Click on the button „to NEXT one “.

image

Under “Identity Center” -> “Jobs” -> “Jasper Reports”.

image

Choose the template „Java-Generate MSSQL entry report“.

image

Than rename the pass and the job in “ApprovalReport”. Subsequently, the job is to be activated over the check box „Enabled “and choose under „run by dispatcher “ a dispatcher.

image

In the pass you go to the “source”-tab, then on „Build SQL Query “and select under „attributes name “„MX_ENTRYTYPE “and under „filters “„MX_PERSON “.

image

After you confirm the operations with the „ok“-button, you can see the generated SQL statement.

image

In the destination-tab of the pass, here that takes place access on the Java class before-configured, at this nothing must be changed. Under the parameter REPORT_DIR you indicate the path to the jrxml file and under RESULT_DIR the path for the provided report. In the attribute OUTPUT_TYPE is indicated the format (HTML or PDF) of the report. Under SUBREPORT_DIR is the path to the subordinated jrxml files (RequestForApproval.jrxml and IdentityStoreInformation.jrxml). In the attribute TO_FILE_NAME must be indicated the name of the report file.

image

Before you start the job, you must give the MXMC_RT user on the sql server further authorizations, on the different tables, otherwise you get error messages in the job log.

image

Before the complete report can be provided, the files RequestForApproval.jrxml and IdentityStoreInformation.jrxml must be converted into Jasper files. The files must thus successively in SAP NW IdM in the pass under the parameter REPORT be indicated and the job be started.

The generated report looks as follows.

image

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply