CW Manage: Getting Started with PowerBI

Quinntin Comer Uncategorized 4 Comments

So…one article I have promised to post over and over is a “Getting Started” article on ConnectWise Manage and Power BI integration. So, here it goes…this will probably be rough just to get it up and then cleaned up later.

Useful Information/Links

  • Postman – Postman makes getting started with API and API Development a breeze. For a newbie, they make it very easy and there is a lot of documentation and guides out there surrounding Postman in use for testing and developing with numerous APIs.
  • ConnectWise Developer Network – This website provides access to the API library, database library and everything else you could desire for getting started with developing/automating/reporting from the ConnectWise suite. Best part — its FREE.
  • MSPGeek (Formerly LabTechGeek) Community – This community is a good resource for “all things ConnectWise” and is a fantastic resource for anyone new to the community.
  • Base64Encode – This website is useful for generating your authorization token instead of using Basic authentication.

Requirements

  • PowerBI (obviously) – If you are going to programatically refresh data or schedule data to refresh – when using an API/JSon datasource you need to configure an “on-premise data gateway”. Power BI will reference the gateway as a source which will reference the web source.
  • CW Manage API Member – https://comertechnology.com/getting-started-with-cw-manage-rest-api/
  • Base64 Conversion of the Public and Private key (Recommended, not required)
  • ConnectWise Client ID – You must sign up on the developer website and have a “Client ID”. https://developer.connectwise.com/ClientID

Process

  1. Convert your Basic authentication to an authorization token.
    1. Go to http://base64encode.org > Type in “CompanyID+PublicKey:PrivateKey” and encode it. This is your authorization token.
  2. Create a new Dashboard in Power BI
    1. Press Get Data > Select “Blank Query” (I will provide you a query below).
      1. In this example, we will use two queries. The API has a maximum return of 1,000 records. To get around this we will have an initial query to loop through all the pages until there are no more and a second to use that as our source.
      2. Right Click > on the new query.
        1. Select “Rename” > Name: PageData
        2. Select “Advanced Editor” and paste the following code. You will need to modify the following sections:
          1. <BOARDIDHERE> – If you want to specify a board, if not you can remove “?conditions=Board/id=x”
          2. <AUTHORIZATIONHERE> – This is the Authorization token you generated by encoding your CompanyID+PublicKey:PrivateKey to Base64
          3. <CLIENTIDHERE> – This is the Client ID you have generated at the CW Developer site.

            let
            Source = (PageNumber as number) => let
            Source = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets?conditions=board/id=<BOARDIDHERE>&pagesize=1000&page=" & Text.From(PageNumber), [Headers=[Authorization="Basic <AUTHORIZATIONHERE>",clientID="<CLIENTIDHERE>", ContentType="JSon"]]))
            in
            Source
            in
            Source

        3. Right click in the “Queries” section > Click New Query > Blank Query.
          1. Right click on our new query and rename it as you wish.
          2. Right click > Advanced Editor. This is where will specify our data from the PageData source. You can find the code at the bottom under “AllData”.

      3. In this case, I have already converted the results to a table and expanded several columns. You can modify these to your choosing or start from scratch.
      4. Press Close & Apply.
      5. The data will refresh and you are ready to create your visualizations.

AllData Code

Examples

We can create a basic table showing all “New” tickets with the below. Now, we have a number of statuses that are duplicates (i.e., New and New*). In our case, anything with an asterisk has a notification step. So, you can use a new column with “Fuzzy Matching” to concatenate the two statuses.

  1. Select the “Table” visual.
  2. In “Filters on this visual” > Drag “Status” over. Select the status you want to filter on. In our case, New*.
  3. Under Visualizations > Values (after selecting the table) > “Add data fields here” > Drag the Fields you would like to be displayed. Or, just check them.
    1. Voila! You have a basic table containing the columns you selected and filtering on the “New” status.

Comments 4

  1. Pingback: CW Manage: Getting Started with PowerBI (The Update!!) - Comer Technology Group

  2. I can’t seem to get it to authenticate. It asks me for credentials and I choose Anonymous, thinking it’ll use the creds built into the URL but it says it can’t authenticate with the credentials provided. No issues with my clientID and non-encoded basic auth info in Postman. Does this syntax look right?

    let Source = (PageNumber as number) => let Source = Json.Document(Web.Contents(“https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets”,[RelativePath=”?pagesize=1000&Page=”&Number.ToText(PageNumber),Headers=[Authorization=”Basic DFKJSDFASDF4982398r8uDOIUFODF”,clientID=”534rw34ecde-sdfsd-sdfs-sdf-sdfsdfsd24″, ContentType=”JSon”]])) in Source in Source

  3. Hello, I have been trying to connect Power BI and CW Manage for dashboard reporting. I tried to follow this article but am still getting errors. Any help would be much appreciated.

    1. Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *