Skip to content
Branch: master
Find file History
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
ReadMe.md
Todo Items.pbix
source.PNG
ss_columns.PNG
ss_query.PNG
vis.PNG

ReadMe.md

Power BI Todo Item Sample

'Todo Items.pbix' is a sample Power BI report showing To do Items not completed by person. Techniques similar to those used in this sample could be used for other visualizations, such as:

  • all Records created this week by Record Type
  • Records checked out to me by weeks checked out
  • Activites Due my action
  • Records due for deletion
  • Audit events by type

Instructions

To use this sample:

  1. in CM create a Todo Item saved search of items not completed (see image 1),
  2. select the columns To Be Done, Uri, Assignee, and Date Due (see image 2),
  3. download and open the PBIX file and 'Edit Queries'
  4. change the URL and saved search Uri (or name) in the source (see image 3),
  5. in Data Source settings set the correct credentials for your ServiceAPI instance, 6, close the Query Editor and edit the URL computed column to point to your instance of the CM Web Client, then
  6. assuming you have some incomplete To Do Items you should see something like image 4.

Things to look for

Things to take note of in this sample:

  • a Pie chart does not support conditional formatting of Data Colors, to see the formatting:
    • switch the pie chart to a bar chart
    • in Data Colors select 'Conditional Formatting' from the ellipsis menu
    • note the color comes from the computed column ColorPriority
  • the URL is based on a computed column in the data
  • Todo items can be filtered based on the Assignee using row level security
  • reports like this can be published to Power BI in the cloud and connected to CM on-premise using a gateway

Row level security

For row level security (RLS) to work there needs to be a value in either LogsInAs or AdditionalLogin that matches the user name Power BI hs for you. If published to the cloud this will likely by your UPN, for example (david@acme.com). To make RLS work you may need to add the UPN for each of your users to the 'Alternative Login' in their Location.

Images

Image 1

Saved Search

Image 2

Saved Search Columns

Image 3

Source

Image 4

Visualization

You can’t perform that action at this time.