Connecting WorkBook with Microsoft Power BI

MS Power BI

Connecting to the WorkBook API

  1. Open your browser and input the URL to the HTML version of your WorkBook system, followed by this address: api/metadata
    http://url-to-workbook/api/metadata
    Example: http://demo.workbook.net/api/metadata
  2. You will now access the list of API calls available. Notice that you can filter in the upper right corner.

    Tip: in general, lists in WorkBook is named with a “Request” naming convention. As an example, if you want to open a jobs list, the API call is
    http://url-to-workbook/api/Job


    Each API call can be queried with parameters (for example: if you only want to see data from a specific client or a single job, you can add parameters to the API string.
    http://url-to-workbook/api/Job/{Id}
    ID: jobID(job number)
  3. Locate API call and the click on the JSON link.
  4. Create your API call. In the following example the goal is to create a visualization of overall job progress on each client.

Example: Over-serviced clients

  1. Open Power BI and click Get Data and select Web
  2. API call: http://demo.workbook.net/api/jobs/virtualization/list/standard
    (Please note that if your WorkBook system is setup with https, this will need to be a part of your API string)
  3. Type in Username and Password to a WorkBook user with appropriate system access level (if the user doesn’t have access through the WorkBook access rights system to the data you request, the data will not show).
  4. The requested data is now being collected. Right click on the “List” option and select “To Table”.
  5. Select the columns you would like to work with.
    Note: if you would prefer to have friendly names outputted, you can un-check this option:

  6. Next you want to select “Data type” to define what kind of data is in each column.
  7. Click Close and Apply
  8. Next, you want to join the first data set with another data set. In this example, we want to join the Client ID and Client name.
    Note that we now adjust the API call with the parameter/Resources?Resourcetype=3
    Resources: the main table for clients, contacts, suppliers etc.
    Resource type 3: Clients
  9. Click “List” and click “To Table”.
  10. Select ID and Name
  11. On the left-hand side, click the “Relationships” tab and drag the ID to the Customer ID to make a relationship between the data.
  12. Navigate to the Output tab

    The data is now shown as table data
  13. You can now create different visuals based on the dataset available. In this example we want to find out how much we over-service our clients, based on Quote values and Incurred costs.Drag n’ drop the Fields into the data area, like this:

    Select this chart type:
    You can continue to explore your data:
  14. Have a look at the online example, located here!
Was this article helpful? Useful Useless 6/9 found this article helpful.