Forum Discussion

vasantharaidu's avatar
vasantharaidu
New Contributor III
1 day ago

Security Report - Dashboard for Admin- Access

Good day to you.

Currently, we are working on a Dashboard for User Access. We have successfully implemented the logic to retrieve Security Group Unique IDs along with their corresponding Security Group details.

As part of this dashboard, we now need to retrieve Workflow Profile–level details, specifically:

  1. Entity assigned to the Workflow Profile
  2. Access Group
  3. Maintenance Group
  4. Workflow Execution Group
  5. Certification / Signoff Group

So far, we have been able to retrieve details for points (1), (2), and (3). However, we are unable to fetch the Workflow Execution Group and Certification / Signoff Group details using either Business Rules or SQL queries.

Could you please help us by suggesting:

  • The appropriate Business Rule method/API, or
  • A supported SQL approach,

to retrieve these remaining Workflow Profile security details?

Your guidance on the recommended approach would be greatly appreciated.

Books
dashboards
Extensible Documents
Parameters
Reports

1 Reply

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    12 hours ago

    I get the security groups into a dictionary keyed by the security group id. Then I read the workflow profile tables to get the security group id information and look up the name of the security group from the dictionary. This is the SQL Query I use to get the workflow security groups:

    thisSQLClause = "Select CASE WorkflowProfileAttributes.ScenarioTypeID "
thisSQLClause &= "when '-1' Then '(Default)' "
thisSQLClause &= "when '0' Then 'Actual' "
thisSQLClause &= "when '1' Then 'Budget' "
thisSQLClause &= "when '2' Then 'Flash' "
thisSQLClause &= "when '3' Then 'Forecast' "
thisSQLClause &= "when '4' Then 'FXModel' "
thisSQLClause &= "when '5' Then 'History' "
thisSQLClause &= "when '6' Then 'Model' "
thisSQLClause &= "when '7' Then 'Plan' "
thisSQLClause &= "when '8' Then 'Tax' "
thisSQLClause &= "when '9' Then 'Variance' "
thisSQLClause &= "when '10' Then 'Administration' "
thisSQLClause &= "when '11' Then 'Control' "
thisSQLClause &= "when '12' Then 'LongTerm' "
thisSQLClause &= "when '13' Then 'Operational' "
thisSQLClause &= "when '14' Then 'Sustainability' "
thisSQLClause &= "when '15' Then 'Target' "
thisSQLClause &= "when '101' Then 'ScenarioType1' "
thisSQLClause &= "when '102' Then 'ScenarioType2' "
thisSQLClause &= "when '103' Then 'ScenarioType3' "
thisSQLClause &= "when '104' Then 'ScenarioType4' "
thisSQLClause &= "when '105' Then 'ScenarioType5' "
thisSQLClause &= "when '106' Then 'ScenarioType6' "
thisSQLClause &= "when '107' Then 'ScenarioType7' "
thisSQLClause &= "when '108' Then 'ScenarioType8' END As ScenarioType, "
thisSQLClause &= "CASE AttributeIndex WHEN 1250 THEN 'Workflow Execution Group' WHEN 16100 THEN 'Certification SignOff Group' WHEN 6100 THEN 'Journal Process Group' WHEN 6200 THEN 'Journal Approval Group' WHEN 6300 THEN 'Journal Post Group' END AS Attribute,  "
thisSQLClause &= "WorkflowProfileAttributes.ProfileAttributeValue, WorkflowProfileHierarchy.ProfileName "
thisSQLClause &= "From   WorkflowProfileAttributes INNER Join "
thisSQLClause &= "WorkflowProfileHierarchy On WorkflowProfileAttributes.ProfileKey = WorkflowProfileHierarchy.ProfileKey "
thisSQLClause &= "Where (WorkflowProfileAttributes.AttributeIndex In (1250, 16100, 6100, 6200, 6300)) "