AnsweredAssumed Answered

Reporting

Question asked by elie al nahas on Jan 13, 2017

Hello,

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

 

Proposal:

 

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
where  T3.ENTITLEMENT_ID=T1.ID
and T4.id=T3.ENTITLED_ID
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.


Do you have a better way to display the result as requested by the customer in this form?

 

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.

 

Best regards

Outcomes