Custom Report about Roles

[amaliszewska (Aleksandra Maliszewska)]

Hi, I am writting custom report and right now I have situation like this:

if you have Business Role 1 with required: IT Role 1 and IT Role 2, permitted: IT Role 3, my report will show me in one row:

Business Role | Required IT Roles | Permitted IT Roles
Business Role 1 | IT Role 1, IT Role 2 | IT Role 3.

But I prefer to have report like this:

Business Role | IT Roles | Permitted/Required
Business Role 1 | IT Role 1 | Required
Business Role 1 | IT Role 2 | Required
Business Role 1 | IT Role 3 | Permitted

How I can create report like that? I don’t how to change a row when I have more than one IT Roles related to Business Role and I don’t know how to add another column with information about relation IT Role to Business Role (permitted/required). Here is my code:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition executor="sailpoint.reporting.LiveReportExecutor" name="Roles Report TEST" progressMode="Percentage" resultAction="Rename" subType="Custom Reports" template="true" type="LiveReport">
<Attributes>
	<Map>
      <entry key="report">
        <value>
<LiveReport title="Roles Report TEST">
    <DataSource objectType="Bundle" type="Filter">
        <QueryParameters>
            <Parameter defaultValue="business" property="type" valueClass="string" />
        </QueryParameters>
    </DataSource>
    <Columns>
        <ReportColumnConfig field="businessRoles" header="Business role" property="id" sortable="true" width="110">
            <RenderScript>
                <Source>
                    import sailpoint.object.*;
					Bundle bundle = null;
                    try{
                        bundle = context.getObjectById(Bundle.class,value);
                        return bundle.getName();
                    }
                    catch (Exception e){
                        if (null != bundle &amp;&amp; null != bundle.getName()){
                            return "error getting profiles for " + bundle.getName() + ": " + e.getMessage();
                        }
                        else{
                            return "null role";
                        }
                    }
                </Source>
            </RenderScript>
        </ReportColumnConfig>
		<ReportColumnConfig field="ritRoles" header="Required IT roles" property="id" sortable="true" width="110">
            <RenderScript>
                <Source>
                    import sailpoint.object.*;
					Bundle bundle = null;
                    try{
                        bundle = context.getObjectById(Bundle.class,value);
                        Collection requirements = bundle.getRequirements();
						List rolesStrings = new ArrayList();
                        for (Bundle itRole: requirements){
                            String itName = itRole.getName();
							rolesStrings.add(itName);
                        }
						return rolesStrings;
                    }
                    catch (Exception e){
                        if (null != bundle &amp;&amp; null != bundle.getName()){
                            return "error getting profiles for " + bundle.getName() + ": " + e.getMessage();
                        }
                        else{
                            return "null role";
                        }
                    }
                </Source>
            </RenderScript>
        </ReportColumnConfig>
		<ReportColumnConfig field="pitRoles" header="Permitted IT roles" property="id" sortable="true" width="110">
            <RenderScript>
                <Source>
                    import sailpoint.object.*;
					Bundle bundle = null;
                    try{
                        bundle = context.getObjectById(Bundle.class,value);
						Collection permits = bundle.getPermits();
						List rolesStrings = new ArrayList();
                        for (Bundle itRole: permits){
                            String itName = itRole.getName();
							rolesStrings.add(itName);
                        }
						return rolesStrings;
                    }
                    catch (Exception e){
                        if (null != bundle &amp;&amp; null != bundle.getName()){
                            return "error getting profiles for " + bundle.getName() + ": " + e.getMessage();
                        }
                        else{
                            return "null role";
                        }
                    }
                </Source>
            </RenderScript>
        </ReportColumnConfig>
    </Columns>
</LiveReport>
</value>
      </entry>
    </Map>
  </Attributes>
  <Description>Displays information about each role in detailed format.</Description>
  <RequiredRights>
    <Reference class="sailpoint.object.SPRight" name="Roles Report TEST"/>
  </RequiredRights>
</TaskDefinition>

[MVKR7T (Krishna Reddy Mummadi)]

@amaliszewska

Business Role | IT Roles | Permitted/Required
Business Role 1 | IT Role 1 | Required
Business Role 1 | IT Role 2 | Required
Business Role 1 | IT Role 3 | Permitted

I doubt this feasibility, never tried, let’s see if any other developer provide insight

How about this instead

Business Role | IT Roles (Required) | IT Roles (Permitted)
Business Role 1 | IT Role 1, IT Role 2| IT Role 3

You can modify your code a little for this

[drosenbauer (Devin Rosenbauer)]

No, you cannot do this with either a Filter or HQL report type. It would be straightforward to do the three-column version in HQL (just do a join on Bundle.requirements and Bundle.permits), but the reporting engine doesn’t support HQL “UNION” queries. At best, you’d have a report that looks like

    Role name       | Permitted | Required
    Business Role 1 | (null)    | IT Role 1
    Business Role 1 | (null)    | IT Role 2
    Business Role 1 | IT Role 3 | (null)

And so forth.

You can use my JDBC Reporting library and just use SQL. That’s probably the easiest way to get the results you want, since you can trivially do a UNION in SQL.

[amaliszewska (Aleksandra Maliszewska)]

Do you have some idea what I should change in my code to have report that looks like

    Role name          | Permitted | Required
    Business Role 1 |   (null)       | IT Role 1
    Business Role 1 |   (null)       | IT Role 2
    Business Role 1 | IT Role 3  |   (null)

I think it will also fit my needs.

Honestly, I don’t know how to go to a new row when listing IT roles. When I tried this, only the first IT roles (first from permitted and required roles) appeared for the business role and in the next row there was another business role and its first IT roles found. Maybe it’s better to show you on example how it looked like:
We have Business Role 1 with required: IT Role 1 and IT Role 2, permitted: IT Role 3. We also have Business Role 2 with required: IT Role 4 and IT Role 5.

When I tried to do this report I had result like:

    Role name          | Permitted | Required
    Business Role 1 | IT Role 3  | IT Role 1
    Business Role 2 |   (null)       | IT Role 4

[amaliszewska (Aleksandra Maliszewska)]

@drosenbauer could you help me improve my code to get the report you mentioned?

[Remold (Remold Krol)]

Hi Alexandra,

Please look at your other related thread:

Report from rule

Hi Alexandra, Hereby the report class file: BusinessRoleReport.class (9.0 KB) Save this file to the following location: <IIQ Root Folder>/WEB-INF/classes/nl/remold/sailpoint/reports Restart the application server (i.e. Tomcat) Import the following report definition (/template) via Gear [image] → Global Settings → Import from File TaskDefinition-BusinessRoleReport.xml (2.8 KB) Create a new report using the new report template Business Role Report to be found in category ‘Role Management Repo…

– Remold

[bellamkonda_vb (VIJAYA BHASKAR BELLAMKONDA)]

Hi @Remold , Could you please share the Java Code for this report. I have a similar requirement. So, it will be helpful.

Thanks

[Remold (Remold Krol)]

Hi @bellamkonda_vb,

The report is now shared in the tools community tools:
IIQ Business Role Report

Here you can find the link to the git-repository which holds the source file of the report.

– Remold

[bellamkonda_vb (VIJAYA BHASKAR BELLAMKONDA)]

Thank you for report @Remold