Before using the templates described in this chapter, make sure to have the following data ready (ask your Administrator): 

  • Server / Instance Name
  • Database Name
  • User / Credentials with access to Reporting. This is defined by Role Based Access


TM comes with a series of evaluation templates located in the setup folder. The available report files and their contents are as follows: 

Report ContentPower BI FilesExcel Files
Team Member Task ResolutionsTM_TeamMember_3.3.pbixTM_TeamMember_3.3.xlsx
Team Member Presence StatesTM_TeamMember_3.3.pbixTM_TeamMemberState_3.3.xlsx
Service ComparisonTM_TeamService_3.3.pbixTM_TeamService_3.3.xlsx

(info) Template files are available for both Excel and Power BI and located in the folders "03_Excels" and "04_PowerBI" accordingly.

(lightbulb) The characteristics of each template are explained on the Reporting Evaluation Templates page.


Evaluation via Power BI

  1. In Power BI, open either the TeamMember or the TeamService template
    → Example default data will be shown
  2. On the "Home" Tab, click the the "Edit Queries" Icon
    → A new window opens

    Power BI - Configuring parameters
  3. On the Top Left "Parameters" section you can change/adjust the parameters needed for your company.
    (info) Note that Fact & Dimension entries marked with (warning) are protected and should not be changed.
    1. Language = target language for data labels (e.g. "month" is called (January (eng) ←→  Januar (deu))
    2. Timezone = timezone to which timestamps in the report data are adjusted towards.
    3. MonthLimit = how many months back the report will pull data from, including the current (ongoing) month
    4. ServerName = on which Server LUCS is located
      (info) this is specified during installation, ask your local administrator if needed
    5. DatabaseName = on which DataBase LUCS is deployed 
      (info) this is specified during installation, ask your local administrator if needed
    6. Version = correlates with Facts and Dimensions and therefore the calculation in the reporting. In general you can go with the initially provided version that comes with the report template
  4. Press "Close&Apply" on the top left icon
    → Window closes. You may be asked to re-confirm the database connection credentials.
  5. Press "Refresh" in PowerBI
  6. Confirm any PopUps stating that you are connecting to the Facts and Dimensions with "Run

You should now see an updated set of data and can start using the tabs and filters to your liking → See Reporting Evaluation Templates page for further info.

Evaluation via Excel

In Excel you may be warned about external data connections as part of the security checks. You need to allow these (add to trusted documents) in order to utilize reports.

With PowerQuery

  1. Open any excel report template file (see table above) with "PowerQuery" in the filename
  2. In Excel, → "Data" ribbon → "Get & Transform" click on "Show Queries"
    → a "Workbook Queries" panel on the Side opens, showing Facts, Dimensions, "Other Queries"

    Excel - Workbook Queries
  3. In the Workbook Queries panel locate "Other Queries" at the bottom. Use double-click on any parameter and change/adjust as needed
    (info) Note that Fact & Dimension entries marked with (warning) are protected and should not be changed.
    1. MonthLimit = how many months back the report will pull data, including the actual month
    2. Language = Language how f.e month is called 
    3. Timezone = to what timezone timestamps are set
    4. ServerName = on what Server LUCS is located
    5. DatabaseName = on what DataBase LUCS is deployed
    6. Version = correlates with Facts and Dimensions and therefore the calculation in the reporting. In general you can go with the initially provided version that comes with the report template
  4. press "Refresh All" in Excel
  5. Confirm any PopUps stating that you are connecting to the Facts and Dimensions with "Run

Without PowerQuery

  1. Open any excel report template file (see table above) without "PowerQuery" in the filename
  2. In Excel, go to "Data" ribbon → under "Data Tools" click on "Manage Data Model"
    (info) You might be requested to enable extra "Data Add-ins" to use this feature
    → A new window opens

    Excel - Manage Data Model
    1. under "Home" ribbon → "Get External Data" click on "Existing Connections"
      → A new window "Existing Connections" opens
    2. Under "PowerPivot Data Connections" click on the existing connection, then click on "Edit"
    3. Enter a new name of your choice in "Friendly connection name"
    4. Enter the Server on which your LUCS instance is located under "Server name"
    5. Credentials via "Log on on the Server" is handled according to company policy (question) Ask your Administrator when your user has no access permissions
    6. Under "Database name" when clicking on the DropDown button, the Database Name should appear → select it

      Excel - Edit Database Connection
    7. Click on "Advanced" 
      → The "Advanced" window opens
    8. Under "Providers" change the Value to "Microsoft OLE DB Provider for SQL Server" → Press "Test Connection" → Press "OK"
      → The Connection Test performs successfully
      → The "Advanced" window closes

      Excel - Edit Database Connection Advanced Properties
    9. In "Edit Connection" → click "Test Connection" then → Press "Save"
      → The Connection Test performs successfully
      → The "Edit Connection" window closes

  3. Close all windows under "Existing Connections". Return to your original Excel report file and save the document changes. 
    1. Test the Data connection via "Data" Ribbon by click on "Refresh All"
      → The Report should retrieve and update current data.