Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Levi D
    Workspace/Workflow Usage by Profile - Analytics Cookbook...
    Topic posted April 27, 2016 by Levi DSilver Medal: 2,000+ Points, last edited August 10, 2017 
    706 Views, 4 Comments
    Workspace/Workflow Usage by Profile - Analytics Cookbook Recipe

    Workspace/Workflow Usage by Profile

    Find Out Who is Using What

    Author: Levi D

    Oracle Service Cloud Support

    Difficulty rating:   (Intermediate)

    Target persona: Site Administrator


    11 Columns (10 are directly output, 1 has an interesting mix of nested IF() and concat() statements)

    5 Tables (The same table is used in 3 different places)

    1 Filter

    Overall description/purpose: Provides details of a given Workspace/Workflow (WS/WF) and the profiles that utilize the WS/WF.  NOTE: This is meant for custom WS/WFs only.

    Use case being served: As a site administrator it is important to know who is utilizing which WS/WF.  This is valuable information when creating new, editing, or deleting a given WS/WF.  The output of the “Profiles as” columns will tell you which profiles utilize a given WS/WF.

    The value of this report can be associated to the following factors on a site:  # of profiles, # of custom WS/WFs, and # of interfaces. 

    Consider the following Scenario:  As a site admin, I maintain all of the WS/WFs for every profile on our site.  I am tasked to re-implement a current WS/WF for all users of the given WS/WF.  I am provided at least 1 profile that utilizes this WS/WF.  I build a new WS/WF and now need to replace the existing WS/WF with the new WS/WF on all applicable.  I have 100 profiles on my site.  I run this report to determine which profiles need to replace the old WS/WF with the new.  Here is the value addition of this report.  Based on the data of the report I only have to open and edit n number of profiles as opposed to open 100 profiles and edit n applicable profiles.

    Potential time saved = (100 – n) * the amount of time it takes to open a profile

                    -When n = 10 and time to open = 20 seconds, then Time saved = 30 minutes

    Sanity saved from NOT needing to open 100 profiles = priceless

    Screen Shots:

    The above screenshot shows that the “Support Team A WS” (ID = 100001) is a “Workflow” in use on interface 1 for 3 profiles.

    Master Chef Approved (a.k.a. Data Validated)? Yes

    Report Configuration Details

    The following section is intended to detail specific areas of the report that are not well described by the PDF report definition.

    Columns, filters, and variables are shown in the PDF definition.  These are additional comments around:

    1. Tables:  There are three tables that many may not be familiar with in this report.  These are Workflows (workflows), Profile to Workspaces (profile2cwf), and Workflow Folders (workflow_folders).  These hold information about the WS/WF, their association to profiles, and the folders that these WS/WF live in. More importantly and perhaps easily overlooked, the Profile to Workspaces table is mapped into the workflows table with an outer join 3 times:

      This was done to be able to see the single edit (profile2cwf), multi edit (profile2cwf2), and Agent Browser UI (BUI) (profile2cwf3) workspaces all in one report. The mappings on these joins is as follows:

      Single Edit --> workflows.wf_id = profile2cwf.single_cwf_id

      Multi Edit    --> workflows.wf_id = profile2cwf2.multi_cwf_id

      Browser UI  -->workflows.wf_id = profile2cwf3.web_cwf_id

    2. Columns: The first 8 columns are all data specific to WS/WF. Much of this similar to what would be seen in the “Workspaces / Workflows” explorer.  These 8 columns all are configured to with the option to “Hide repeating values” as this data would be the same for every record output within the report.  The last 3 columns, “Profile as…” , output a record for each profile that utilizes that WS/WF.

      a.  Location:  The "Location" column is interesting because it takes into consideration all of the levels of the folder structure using nested if() function statements and the concat function for each level.  This has the basic form of the expression is:

      IF folder X level IS NOT NULL (i.e. the workspace is in a folder level X),

      THEN concatenate a backslash and the folder label for level X,

      ELSE concatenate nothing (two single quotes).

      This is done 12 times to account for all possible folder levels. The full expression for this column is:

      concat(if(workflow_folders.lvl1_id IS NOT NULL, concat('\\', workflow_folders.lvl1_id), ''),

      if(workflow_folders.lvl2_id IS NOT NULL, concat('\\', workflow_folders.lvl2_id), ''),

      if(workflow_folders.lvl3_id IS NOT NULL, concat('\\', workflow_folders.lvl3_id), ''),

      if(workflow_folders.lvl4_id IS NOT NULL, concat('\\', workflow_folders.lvl4_id), ''),

      if(workflow_folders.lvl5_id IS NOT NULL, concat('\\', workflow_folders.lvl5_id), ''),

      if(workflow_folders.lvl6_id IS NOT NULL, concat('\\', workflow_folders.lvl6_id), ''),

      if(workflow_folders.lvl7_id IS NOT NULL, concat('\\', workflow_folders.lvl7_id), ''),

      if(workflow_folders.lvl8_id IS NOT NULL, concat('\\', workflow_folders.lvl8_id), ''),

      if(workflow_folders.lvl9_id IS NOT NULL, concat('\\', workflow_folders.lvl9_id), ''),

      if(workflow_folders.lvl10_id IS NOT NULL, concat('\\', workflow_folders.lvl10_id), ''),

      if(workflow_folders.lvl11_id IS NOT NULL, concat('\\', workflow_folders.lvl11_id), ''),

      if(workflow_folders.lvl12_id IS NOT NULL, concat('\\', workflow_folders.lvl12_id), ''))

      b.  Profiles as Single column uses the first join between workflows and profile2cwf. NOTE:  Again, this join is pointing at the profile2cwf.single_cwf_id field.  The column definition must match the table name for the first join profile2cwf in order to return the single edit workspace/workflow:


      c.  Profiles as Multi column uses the second join between workflows and profile2cwf2. NOTE:  Again, this join is pointing at the profile2cwf2.multi_cwf_id field.  The column definition must match the table name for the second join profile2cwf2 in order to return the multi-edit workspace/workflow:


      d.  Profiles as BUI column uses the third join between workflows and profile2cwf3. NOTE:  Again, this join is pointing at the profile2cwf3.web_cwf_id field.  The column definition must match the table name for the third join profile2cwf3 in order to return the Agent Browser UI (BUI) workspace/workflow:


    3. Filters: There is one pretty straightforward filter used in this report.  You are required to enter the workspace/workflow ID.  If we just created the filter workflows.wf_id you would be required to pick from a list of workflows (you may have to search for a while to find it if you have a lot of WF/WS).  By adding the ".id" to the end of the expression (i.e. you can put in the integer value of the workspace/workflow ID. NOTE:  This addition of .id can be used in column definitions to get the ID values for fields as well. It is important to note that this filter has been set to required.

    4. Formatting: Hide Repeating Values: As mentioned previously, the "Hide Repeating Values" formatting option was used on the first 8 columns in this report to show only the top row value of the data in the report.  This is to give the report a cleaner look and there is no need to read the same data points for these columns when we know they will be the same for each record output.  Also each column uses “Display as Blank” for NULL value handling.  This is a personal preference as I prefer to see nothing as opposed to “No Value” for strings or “0” for integers. These formatting options make it easier to follow the data within the report (especially when your report data grows).  

      To get to the "Hide Repeating Values" option  you right click on the column you wish to add this formatting to, select "Edit Format".  Select the "Alignment" tab in the Column Format window and you will find a checkbox for Hide Repeating Values" near the bottom of the window.  Simply check that box.

    5. Options (Home Tab > Options > More Options):

      a. Reporting vs. Operational DB: Real-time data is preferred so Operational DB was selected for this report 

      b. Uncheck the "Allow server to change data source as necessary option" to avoid automatic switching from operational to reporting DB if report approaches query size limit (2 million rows analyzed). Although unlikely, if this report queries too much data, it will fail with a "query processes too much data" error requiring further report optimization.

    Included are the following (in a .ZIP file):

    • Human readable PDF report definition
    • XML Report definition 
    • Screen shot 



    • Erica (Leep) Anderson

      Just wanted to give a BIG thanks to you, Levi, for contributing this report recipe! 

      We are experimenting with leaving these report recipe posts open for comments, because we want report chefs to hear how their report recipes helped you. If you found this report recipe helpful, please give Levi's post a thumbs up and leave a comment letting him know you appreciate his help, how this report helped you and your organization, etc.

      If you have a question about this report recipe, please start a new topic in the "Reporting and Analysis" forum board and link to this report recipe in your question. DO NOT post your reporting question here. We will remove any comments asking for help in the comments of this post.

      Erica, Community Manager

    • Sara Knetzger

      Thank you SO much Levi!!  This report is invaluable!!  I'm working on a project to archive/clean-up our out-dated workspaces, workflows and profiles and your report recipe has saved me a ton of time and a whole lot of frustration!!!  

      Thank you again!!!

    • Levi D

      I received some feedback regarding this recipe/post and one of the issues was that I mention BUI workspaces however the field I used was not for BUI.  This recipe and the content of this post has been amended and version 2 of this recipe has been uploaded.  Here is a summary of the changes made:

      1.  Changed this join condition from the profile2cwf3 table from "workflows.wf_id = profile2cwf.mobile_cwf_id" to "workflows.wf_id = profile2cwf.web_cwf_id"
      -Why?  This recipe originally noted BUI workspaces which is in the "web_cwf_id" field and NOT in the "mobile_cwf_id" field.  I had incorrectly used the "mobile_cwf_id" field previously.  The "mobile_cwf_id" field is for TAP APP workspaces.

      2.  Removed the "Is Mobile?" (workflows.is_mobile) column.  This field aligns with TAP APP workspaces.  Since version 2 of the report does not include TAP APP workspace data this field has been removed.

      3.  Renamed the "Profiles as Mobile" column to "Profiles as BUI".  This change correlates with the change in item 1.

    • Danette Beal

      Thanks for making the change Levi!!!