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 7.1 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 actually 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 typeitself:

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 ResultThis 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, and on the attributes tab we mark “Report task”.  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:
/wp-content/uploads/2014/06/1_440560.jpg/wp-content/uploads/2014/06/2_440585.jpg/wp-content/uploads/2014/06/3_440586.jpg
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 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. If you want
      to use the same CSS, you can find a reference to the file at the end of the article.  Nevertheless, 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 1st row 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:


         

/wp-content/uploads/2014/06/4_440587.jpg

      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.

/wp-content/uploads/2014/06/5_440624.jpg

The “Create Report” task is opened and we fill the attributes needed, then click “Save”.
/wp-content/uploads/2014/06/6_440625.jpg

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:
Untitled.jpg

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:

/wp-content/uploads/2014/06/8_440633.jpg

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

To report this post you need to login first.

23 Comments

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

  1. Matt Pollicove

    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

    (0) 
  2. Bernhard Stutz

    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.

    (0) 
    1. Emanuil Ivanov Post author

      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)

      (0) 
  3. Rene Wohllebe

    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.

    gr

    rene

    (0) 
  4. Rene Wohllebe

    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

    br

    rene

    (0) 
  5. Bernhard Stutz

    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

    (0) 
    1. Tero Virta

      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

      (0) 
    2. Emanuil Ivanov Post author

      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

      (0) 
      1. Bernhard Stutz

        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

        (0) 
        1. Emanuil Ivanov Post author

          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

          (0) 
  6. Bernhard Stutz

    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

    (0) 
  7. Chenyang Xiong

    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

    (0) 
      1. Chenyang Xiong

        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

        (0) 
        1. Tero Virta

          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

          (0) 
  8. Steffi Warnecke

    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! 🙂

    (0) 
    1. Tero Virta

      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

      (0) 
  9. Rimesh Tiwari

    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?

    (0) 

Leave a Reply