TimeLog Reporting API and Microsoft Power BI

With Microsoft Power BI you are able to generate your own reports based on TimeLog data.

This page will guide you through the basic setup for a single TimeLog resource. In order to generate more comprehensive reports based on more TimeLog resources, we recommend that you find a consultant with expertise in Microsoft Power BI.

TimeLog does not have the expertise to guide or assist customers in PowerBI development and setup. For that reason, TimeLog will not do paid or unpaid work inside PowerBI for any customer - we can refer customers to great PowerBI consultants that knows both PowerBI and TimeLog. TimeLog will gladly assist customers in understanding the various TimeLog data available in the APIs and how they are interrelated and can be utilized the best way.

This below example is made in Power BI Desktop.

Walk-through

Open "Power BI Desktop".

Close the welcome screen.

Click "Enter data".

Click "Edit".

Click "Advanced Editor".

Paste the following code into the editor.

let
    PostContents = "siteCode={your site code}&apiID={your api id}&apiPassword={your api password}&employeeID=-1&initials=&departmentID=-1&status=1",
    GetEmployeesRaw = Web.Contents(
        "https://app5.timelog.com/soxdemo/service.asmx/GetEmployeesRaw",
        [
            Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
            Content = Text.ToBinary(PostContents)
        ]
    ),
    ImportedXml = Xml.Document(GetEmployeesRaw),
    #"Removed Other Columns" = Table.SelectColumns(ImportedXml,{"Value"}),
    #"Expanded Value" = Table.ExpandTableColumn(#"Removed Other Columns", "Value", {"Name", "Namespace", "Value", "Attributes"}, {"Name", "Namespace", "Value.1", "Attributes"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Value",{"Value.1", "Attributes"}),
    #"Expanded Attributes" = Table.ExpandTableColumn(#"Removed Other Columns1", "Attributes", {"Value"}, {"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Attributes",{{"Value", "EmployeeID"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"EmployeeID", "Value.1"}),
    #"Expanded Value.1" = Table.ExpandTableColumn(#"Reordered Columns", "Value.1", {"Name", "Value"}, {"Name", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Value.1", List.Distinct(#"Expanded Value.1"[Name]), "Name", "Value")
in
    #"Pivoted Column"

Adjust "{your site code}", "{your api id}", "{your api password}" and your site URL. And click "Done".

You can find the site code, api id and api password inside the TimeLog system administration like below:

The "PostContents" will for this particular example look like:

PostContents = "siteCode=5a7316fabc8343d6b4204874f&apiID=timelog&apiPassword=verySecret2You&employeeID=-1&initials=&departmentID=-1&status=1",

If asked, click the "Edit credentials" button and choose "Anonymous" and the root URL in the dropdown. Then click "Connect".

Click "Close & Apply".

Now your employee data is available for Power BI.

Each TimeLog site has its own unique SiteCode required on all requests. You may see the SiteCode from the System Administration inside TimeLog together with the API credentials.

Additional, each TimeLog site can define one set of Reporting API credentials that are also required for each request.