We face a problem with an SQL query to create custom tabular report, and we are requesting your help.
We have extended the application role schema to add 2 attributes Entity (AccessSpecField1) and Plaform (AccessSpecField2)
Exemple of an application role :
This report should list the number of users for each couple Entity/Plateform that have an application role and a Profile Code equal to ‘EXECUTION_SIGNER’.
It should also list the AD Login, company, country, Full Name, Region, and supervisor.
The expected result:
- As many lines as triplets Entity / Plateform / user that has the role 'EXECUTION_SIGNER' for the couple Entity / Plateform.
- The Number of Execution Owners per couple Entity / Platform on the 3rd column
Simplified example desired by the customer:
Entity ;Platform ;Number;Full Name
Entity 1 ;Plateforme1 ;3 ;User1
Entity 1 ;Plateforme1 ;3 ;User2
Entity 1 ;Plateforme1 ;3 ;User3
Entity 1 ; Plateforme2 ;1 ; User4
Entity 1 ;Plateforme3 ;2 ;User5
Entity 1 ;Plateforme3 ;2 ;User6
We were unable to display the expected result (the number at the same level as the identity) and we thought of another solution as below:
Here is the simplified query by filtering on the entity '30VOF'
(select Entity,Platform,"Full Name" from (select distinct T1.cas3 as Entity,T1.CAS6 as Platform ,T1.NAME,T4.NAME as "Full Name" from avuser.t_entitlement_groups T1 , avuser.V_AVR_USER_ACC_MAP T2,
avuser.V_AV_OOR_EXPLODEDUSERENTS T3, avuser.T_AV_ACCOUNTS T4
and T1.cas3 = '30VOF'
and T1.cas1 = 'EXECUTION_SIGNER' and T1.cas6 is not null and T3.ENTITLED_TYPE = 'account'
and T1.APPLICATION_ID=(select id from avuser.T_APPLICATIONS where lower(name)='trax'))
group by Entity,Platform,"Full Name")
In the tab Column , we checked in the summary the count on the Full name and we did a grouping by Entity and platform.
The result is consistent except that on the last page the total count is also displayed.
Entity; Platform;Number;Full Name
We added other attributes (AD Login, Company, Country, Region...) and we executed the report. Il contains 29000 pages.
Is there another solution for the display of our proposal?
Thanks in advance.