Emanuil Ivanov

June 10, 2014 9 minute read

SAP Identity Management HTML Reporting: How to create HTML Reports.

HTML Reporting

This is an article focused on the reporting functionality of Identity Management with the main focus on HTML Reporting. The article is applicable to both Identity Management 7.2 and 8.0 versions.

Identity Management needs to satisfy certain reporting requirements, such as:

What are the attributes of a given user?
What are the business roles assigned to a given user?
What systems does a given user has access to?
Which business roles are available in the system?
How many users/business roles, etc. are available in
the system?

In this article, I will show you how to use Identity Management’s functionality in order to create pretty, complete and useful HTML Reports. With Identity Management, you can create reports using the information which is available in the Identity Center. The core of HTML Reporting is very simple – SQL Queries, which get values for a given entry type from the database. Wrapping up those queries with some HTML and CSS code makes a fully customizable appearance for the generated reports, which gives flexibility and better user experience to the end users. Let us start this magical tour into the wonderful world of HTML reporting with the entry type itself:

MX_REPORT

The MX_REPORT entry type is the entry type for report requests and exists in Identity Management as of 7.1 SP2. The report is executed as an action task on the MX_REPORT entry type. As the report is a task, the task status indicates the progress of the report, i.e. pending, ok or error. By default, the MX_REPORT is not listed as a searchable entry type. Note that the report task will always create an entry of type MX_REPORT, regardless of the provided entry type in the task definition.

Let’s take a look at the most important attributes of the MX_REPORT entry type:

MSKEYVALUE
DISPLAYNAME– display name of the report.
MX_OWNER
MX_REPORT_ENTRY
MX_REPORT_DATE– date on which the report was requested.
MX_REPORT_FORMAT– format of the report (this could be PDF, HTML, DOC, etc.)
MX_REPORT_RESULT– this attribute holds the full report result. It is saved as a binary in the database.
MX_REPORT_RESULT_REF– this attribute holds a reference to the report result, in case it is stored in a separate file server.

Reports are shown in the View Reports tab of the Identity Management User Interface. In this tab, we have a table with 5 columns:

Entry – This corresponds to the value of the MX_REPORT_ENTRY attribute.
Status – Status of the report task (pending, OK or Error). This status is calculated based on the status of the task execution, taken from the MCMV_AUDIT view.
Report Date – This corresponds to the value of the MX_REPORT_DATE attribute.
Report Name – The name of the report. If DISPLAYNAME has a value, this value is stored, otherwise the value of MSKEYVALUE is stored here.
Report Result – This corresponds either to the MX_REPORT_RESULT or to the MX_REPORT_RESULT_REF attribute.

In order for a report to be shown in the View Reports tab, it must have a value for either MX_REPORT_RESULT or MX_REPORT_RESULT_REF. Otherwise, the report will be stored in the database, but it won’t be visible in the User Interface, as it has no result file attached to it.

We’ve seen the entry type for reporting, now let’s get into some real action. Let’s create a report task, which returns all the assigned privileges and roles for a given user. To make things more beautiful, we will wrap it with some CSS and HTML, to create a good-looking report. Let’s begin…

HTML Reporting Task

We start by creating the task – let’s name it “Create Report”. We make it a UI task(in version 8.0, Regular Form), and on the attributes tab we mark “Report task” (in version 8.0, Report Form). This will automatically display all the attributes for the MX_REPORT entry type and create a new MX_REPORT entry type, regardless of what we have selected as the entry type of the task itself. We select the task entry type to be MX_PERSON (which means what it will be executed on a person, but will create a report) and select some attributes to be displayed. (check the screenshot below.) The last step is to add access control to the task – let’s say we want only the administrator user to be able to create reports for entries. So, we give access type – logged-in-user, with value- “Administrator” and on behalf of everybody relation. Here is how the task definition should look like:

SAP Identity Management 7.2

/wp-content/uploads/2014/06/1_440560.jpg

/wp-content/uploads/2014/06/2_440585.jpg

/wp-content/uploads/2014/06/3_440586.jpg

SAP Identity Management 8.0

So far we have only a UI task, that can be executed by an Administrator and creates a new entry of type – MX_REPORT. As there is no report_result, we don’t have an actual report ready. In order to create the report, we would attach a new empty job, with one To Identity Store pass.

In version 8.0, you cannot directly attach an action task to a form. That is why you need to create a process of type Form Action Process and add an action task to it. Then, open your Create Report form in the Identity Management Developer Studio, and on the Result Handling tab, select this process in the Execute Process on Submit field.

SAP Identity Management 8.0

You can continue with adding a To Identity Store pass an HTML Reporting Script(described below) in the action task.

In the pass Destination tab, we will add MX_REPORT as an entry type, because the pass will make modifications to the newly created MX_REPORT entry. The idea here is, that after we create the report in the UI, we would receive the value of the MX_REPORT_ENTRY attribute, which is actually the mskey of the user, we are running the Create Report task on, and use this value as MSKEY in our queries, which will return the assigned privileges and roles. We will need MSKEYVALUE, to point to the created report, MX_REPORT_FORMAT, which will be with value – “HTML”, because if it’s in another format, when opened in the UI, it will not be opened in the browser as needed; and MX_REPORT_RESULT, which will get its value from a custom script. In the script, we will use the magic of HTML5 + SQL Queries in order to create the report itself. Let’s create the script.

HTML Reporting Script

We create a new local script for the action task. Let’s name it HTMLReport. The script will take as input parameter the value of MX_REPORT_ENTRY (the mskey of the user, we are reporting on) and will return a binary representation of the report, which will be stored in the Identity Center. I will explain the process of creating the script:

1. We create a Header and Footer, which will be just static CSS and HTML code in the beginning and in the end of the HTML file. We do this to delimitate the static parts of the HTML code, so that they won’t interfere with our main logic. The header contains the opening HTML tags and the CSS used, and the footer just contains the closing HTML tags. For the sake of simplicity in this article, I won’t add the real CSS code I used in this example. Your header should look like this:
2.           <html>
  
            <head>
  
            <style type=”text/css”>
  
            <…………CSS here……………..>
  
            </style>
  
            </head>
  
            <body>
  
          And your footer should be like this:
  
            </table>
  
            </div>
  
            </body>
  
            </html>
  
            2. Now, let’s create the script itself. For now, we have the HTML opening tags and the CSS, and the HTML closing tags, along with the table closing                  tag. Now, let’s populate the table itself. We will create 2 rows and 3 columns. The 1^strow will contain the table headings: User Name, Assigned                Privileges and Assigned Roles. The second row will contain the username, privileges and roles, all extracted from the Identity Center.
  
                Privileges are extracted with the following query:
```
select  mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_PRIVILEGE' and
```
```
 mcthismskey='"+Par+"'
```
                For roles, the query is:
```
select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_ROLE'
and mcthismskey="+Par+"
```
  Since Par(which is the value of MX_REPORT_ENTRY) , contains the mskey of the entry, we have to extract the mskeyvalue of that user to be shown under User Name. This is done via the following query:
```
select mcmskeyvalue from idmv_entry_simple where mcmskey="+Par+"
```
  Getting the results from those queries as values is done via the uSelect() function.
  
  We store the results of those queries in variables and add them to the table elements and store this into a variable (oHTML), which represents the table body.
  
  The last thing left to do is to return the binary representation (hex code). This is done via the uToHex() function. We also need to add “{HEX}” as prefix.
  
  This is how our example script looks like in the end:
```
//
Main function: HTMLReport
function HTMLReport(Par){
var oHeader = uFromFile("C:\\Reporting\\Template\\header.html","-1","false");
var oFooter =uFromFile("C:\\Reporting\\Template\\footer.html","-1","false");
var AssignedPrivileges=uSelect("select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_PRIVILEGE' and
mcthismskey='"+Par+"'");
var oList="";
var oArray=AssignedPrivileges.split("!!");
for(var i=0; i<oArray.length; i++){
oList=oList+oArray[i]+'<br>';
}
var AssignedRoles = uSelect("select mcothermskeyvalue from idmv_link_ext2 where mcAttrName='MXREF_MX_ROLE' and
mcthismskey='"+Par+"' ");
var oList2="";
var oArray2 = AssignedRoles.split("!!");
for(var i=0; i<oArray2.length; i++){
oList2 = oList2 + oArray2[i] + '<br>';
}
var userName = uSelect("select mcmskeyvalue from idmv_entry_simple where mcmskey="+Par+" ");
var oHTML='<div class="HTMLReport"><table><tr><td>UserName:</td><td>Assigned Privileges</td><td>Assigned Roles</td></tr>';
oHTML+='<tr><td>'+userName+'</td><td>'+oList+'</td><td>'+oList2+'</td></tr>';
var oHex="{HEX}"+uToHex(oHeader+oHTML+oFooter);
return oHex;
}
```
  We save and go back to the To Identity Store pass definition.

3. As a final step, we add the value of MX_REPORT_RESULT to be calculated via the HTMLReport script, with MX_REPORT_ENTRY as input parameter. The Pass definition should look like this:

SAP Identity Management 7.2

SAP Identity Management 8.0

4. Save the task and log on to the User Interface with the “Administrator” user.

Starting the task via the User Interface

Let’s execute the task on an entry. We go to Manage tab and search for a person. We select the person, go to “Choose task” and select the “Create Report” task(in version 8.0, Create Report form).

The “Create Report” task is opened and we fill the attributes needed, then click “Save”.

This will save a new entry of type MX_REPORT, with MSKEYVALUE and Display Name – Superman Report, MX_REPORT_DATE – 25.04.2014, and values for DESCRIPTION and MX_REPORT_ERROR. Saving this will execute the action task and the To Identity Store pass, which will set HTML as value to the MX_REPORT_FORMAT attribute, and will calculate the value of MX_REPORT_RESULT, using the Script we created. To see our result, we will go to the View Reports tab:

We can see our newly generated report. We can see that it is for the Entry Clark Kent, the report task is successfully executed (Status – “OK”), the report name and the report result. If we click on the Result, we will see our report in the browser:

Pretty, isn’t it? It contains the UserName of the user and all the assigned privileges and business roles.

You can find the header and footer files, which I used with the abovementioned script to create this example report, attached to the article. If you like them, they are available for free usage 🙂

Yours truly,

Emanuil

25 Comments

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

Matt Pollicove

June 10, 2014 at 2:01 pm

This is excellent content!!!!!!

Just one note, while this technique will work for 7.1 and 7.2, it should be pointed out that your example should be based on mxiv_sentries rather than the idmv views.

Matt

Tero Virta

June 11, 2014 at 6:25 am

Good stuff Emanuil 🙂

JON PRYOR

June 16, 2014 at 1:29 pm

Thank you. It's always good to see a working example.

Former Member

July 30, 2014 at 3:27 pm

Hi,

thanks for your effort.

I want to report which priviliges and users are assigned to a role. I cannot get the HTML code correct. Can anyone help?

BTW: Report Date does not display.

Emanuil Ivanov

Blog Post Author

July 31, 2014 at 11:44 am

Hi Volker,

Firstly, you have to create a new report task for the MX_ROLE entry type, where you follow the same pattern as for MX_PERSON.

For getting the privileges assigned to a role, here is an example query:

uSelect ("Select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PRIVILEGE' and mcothermskey="+Par)

For getting the users assigned to a role, here is an example query:

uSelect ("Select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PERSON' and mcothermskey="+Par)

Former Member

July 30, 2014 at 3:53 pm

Hi all,

very nice blog. We also use technologic like that in our RDS.

Please check our RDS content there you have a lot of html reports. You can use these as templates.

rene

Former Member

August 5, 2014 at 2:56 pm

Dear Rene,

can you please post the link to the RDS HTML reporting downloads?

I might be too stupid to find them.

Thanks in advance

Volker

Former Member

August 5, 2014 at 3:08 pm

Hi Volker,

you have to import the whole RDS content. Because our implementation are Jscripts like Emanuil used.

You can find the RDS package at SCN.

http://service.sap.com/rds-idm

Navigate to Solution Deployment and download the package Identity Management RDS Content V1 (link will opened).

Please check the documentaion (D04_IDM720_BB_ConfigGuide_EN_XX.doc) how to import the related files. You need 0256_IDM72_Identity_Store_Schema.mcc and 0256_IDM72_Provisioning_Folder.mcc.

You can find the docu at same link like the content package; Identity Management RDS Service Enabl. SBS Guide.

Unzip: RDS_NW_IDM_IDM720_SERV.zip\SAP_BP\BBLibrary\Documentation

rene

Former Member

August 7, 2014 at 1:15 pm

Hi,

it is me again 🙂

Is there any way to sort the output alphabetically? How would I do this?

I have a request from my manager to create a report that shows by company (entry type BC_Conmpany) the roles with assigned users per role.

I have set up the task, but the script (select statement) won´t fly.

-------------

// Main function: HTMLReportCompany

function HTMLReportCompany(Par){

var oHeader = uFromFile("D:\\usr\\sap\\IdM\\Identity Center\\Templates\\header.html","-1","false");

var oFooter =uFromFile("D:\\usr\\sap\\IdM\\Identity Center\\Templates\\footer.html","-1","false");

var AssignedRoles = uSelect("select mcothermskeyvalue from idmv_link_ext where mcAttrName='MX_ROLE' and mcthismskey='"+Par+"' ");

var oList="";

var oArray=AssignedRoles.split("!!");

for(var i=0; i<oArray.length; i++){

oList=oList+oArray[i]+'<br>';

}

var AssignedUsers = uSelect("select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PERSON' and mcothermskey='"+Par+"' ");

var oList2="";

var oArray2 = AssignedUsers.split("!!");

for(var i=0; i<oArray2.length; i++){

oList2 = oList2 + oArray2[i] + '<br>';

}

var compName = uSelect("select mcmskeyvalue from idmv_entry_simple where mcmskey="+Par+" ");

var oHTML='<div class="HTMLReport"><table><tr><td>Location:</td><td>Business Role</td><td>Assigned Users</td></tr>';

oHTML+='<tr><td>'+compName+'</td><td>'+oList+'</td><td>'+oList2+'</td></tr>';

var oHex="{HEX}"+uToHex(oHeader+oHTML+oFooter);

return oHex;

}

------------

Sofar I was successfully listing the users belonging to the specified company, but some are listed twice.

Any help is highly appreciated.

Volker

Tero Virta

August 7, 2014 at 3:16 pm

Easiest way would be to do it in SQL like:

var AssignedUsers = uSelect("select mcthismskeyvalue from idmv_link_ext_active where mcthisocname='MX_PERSON' and mcothermskey='"+Par+"' order by mcthismskeyvalue");

regards, Tero

Former Member

August 8, 2014 at 8:20 am

Dear Tero,

works like a charm. Thank you.

Volker

Emanuil Ivanov

Blog Post Author

August 8, 2014 at 6:53 am

Hi Volker,

Could you clarify your scenario, by answering the following questions?

Is BC_Company a context entry type?
Are you executing the report task on the BC_Company entry type?

Best Regards,

Emanuil

Former Member

August 8, 2014 at 8:24 am

Dear Emanuil,

thanks for your great artikel. It opend the reporting world in IDM for me.

Regarding your questions: BC_Company is used for context information. Some priviliges are allocated when the context is correct.
Yes, I execute the task on entry type BC_Company

BTW: I cannot see the report date (which I set manually) on the 'View Reports' tab, although the column 'Report Date' is shown.

Any help is really appreciated.

Regards

Volker

Emanuil Ivanov

Blog Post Author

August 8, 2014 at 11:57 am

Hi Volker,

Try with this query in the uSelect:

uSelect("select mcothermskeyvalue, mcthismskeyvalue from idmv_link_ext2

where mcOtherOcName='MX_ROLE' and mcThisOcName='MX_PERSON'

and mcLinkState=0 and mcCtxMSKEYVALUE='"+Par+"' order by mcothermskeyvalue");

This is if you want to order the roles alphabetically. If you want to order the privileges, replace mcothermskeyvalue with mcthismskeyvalue in the order by clause.

Have in mind that this query will return 2 columns, one contains role name and the other contains an assigned privilege to that role, so you will have to change your HTML code and create more appropriate tables to store them 🙂

Best Regards,

Emanuil Ivanov

Former Member

August 12, 2014 at 2:30 pm

Dear Emanuil,

with below SQL statement I can see the data I want in SQL:

uSelect("select e1.mcOtherMSKEYVALUE, e1.mcthismskeyvalue from idmv_link_ext e1 with (nolock) inner join idmv_link_ext e2 with (nolock) on e1.mcThisMSKEY = e2.mcThisMSKEY where e1.mcThisOcName = 'MX_PERSON' and e1.mcOtherOcName = 'MX_ROLE' and e1.mcAttrName = 'MXREF_MX_ROLE' and e2.mcOtherOcName = 'BC_COMPANY' and e2.mcOtherMSKEYVALUE='"+Par+"'order by e1.mcOtherMSKEYVALUE, mcThisMSKEYVALUE");

Unfortunatly I cannot get any output from the script (even when I only select e1.mcOtherMSKEYVALUE (role name) and adjust the script I get nothing in the HTML file).

What do I do wrong?

Regards

Volker

Chenyang Xiong

October 7, 2014 at 1:37 am

Hi Emanuil,

Thanks for your blog. It is quite useful.

I have created a PDF report with a job (not a UI task). I wonder if you could share me some light on how to display this report on UI?

I used a toIdentityStore pass to create a MX_REPORT record in IDM. But I guess it needs a link to a MX_PERSON entry. But how can I do it in jobs?

Thanks & Regards

Chenyang Xiong

Tero Virta

October 7, 2014 at 5:59 am

In order to make it visible you need to set the MX_OWNER-attribute, owners will see the report in Report-tab.

regards, Tero

Chenyang Xiong

October 7, 2014 at 8:48 pm

Thank you Tero,

It works. After MX_OWNER is set, I am able to see the report from UI, though the entry and status column are empty.

I guess the report tab should have two categories, one for entry and one for system level report.

Best Regards

Chenyang

Tero Virta

October 8, 2014 at 6:07 am

I don't have access to the customer system where I developed some mx_reports, so cannot verify, but I would assume that the columns you mentioned are just attributes of the mx_report.

Looking at the mx_report entry type from IdM schema there's a column mx_report_entry which looks like a reference to the entry (try filling it if your report can be matched 1:1 to an entry/mskey).

regards, Tero

Steffi Warnecke

October 9, 2014 at 7:32 am

Hello Emanuil,

this was my first contact with those reports in IDM and thanks to your blog (and after pushing through a little confusion with the template location, because I didn't read closely enough) it was a successful experience! That you provided the templates as a starting point was really helpful, too.

Like Volker, the report date isn't shown in my UI either. But I'll look into that later to see, if I can fix this myself. ^^

Thanks again for this blog! 🙂

Former Member

May 14, 2015 at 9:26 am

Hi,

A nice Blog.. Any idea how do we delete just created reports visible in the view Report tab ?

Regards

Rimesh

Tero Virta

May 14, 2015 at 2:28 pm

You can delete them from Id Store like any other IdM-entry with toIdentityStore-pass.

It's probably easiest to create a job that deletes them based on criteria..

regards, Tero

Former Member

May 21, 2015 at 7:53 am

I havent been able to see Report date in my UI .. but I can see in database that MX_REPORT_DATE has the value which I entered through UI.. Any idea how to bring report date up in the UI?

Former Member

January 22, 2018 at 11:05 pm

Can this please be updated for IdM 8.0 since it is directly linked from the IdM 8.0 Configuration Guide? I am having difficulty in translating this to 8.0. Can I do this entire process in a single job or do I create a process with an action task?

Former Member

February 21, 2018 at 1:22 pm

Hi Casey,

I'm also having same issue. Do you have update.

SAP Identity Management HTML Reporting: How to create HTML Reports.

HTML Reporting

MX_REPORT

HTML Reporting Task

HTML Reporting Script

Assigned Tags