Data Sources

To easily create charts and analyses with Power BI, it is helpful if the data to be analyzed is already available in a form that makes complex filtering and restructuring within Power BI unnecessary. Filtering can thus, for example be outsourced to the data source and do not have to be replicated in the analysis tool.

For these and other reasons, the Allegra Power BI Connector offers a range of data sources for different types of reports and analyses.

Select Data Source

When you open a new „Navigator“ in Power BI for the Allegra Connector, you will be offered a number of data sources.

Select a suitable data source and configure it.

Power Bi will then take you to the Data View.

You can also display the data hierarchy. Go to TransformData > Recent sources > Last used

Set Hierarchical to TRUE.

In the new view, scroll to Child items, double-click on Table (item with children) and a new table with subordinate items will open at the bottom.

Data Sources for Planning

These data sources provide easy access to the budget, planned value, remaining effort and the sum of the actual efforts.

AccountingByFilter

This data source provides the planning data for all items that match the selected selected query filter.

Parameters:

• filterID: String, single value, mandatory; one of the saved query filters

• excludeParentItems: boolean (true/false), optional, default false;

  If this parameter is set to true, the parent items of the selected items are not are not automatically selected.

AccountingByWorkspace

This data source provides the planning data for all tems in the selected workspaces.

Parameters:

• workspace: List<int>, multiple value, mandatory; list with workspace id’s

• excludeParentItems: boolean (true/false), optional, default false;

  If this parameter is set to true, the parent items of the selected items are not automatically selected.

Accounting Attributes

The following item attributes are available for Power BI:

"fieldNames": [
"itemID",
"itemTitle",
"parentID",
"totalExpenseCost",
"totalExpenseTime",
"totalPlanCost",
"totalPlanTime",
"budgetCost",
"budgetTime",
"remainingCost",
"remainingTime",
"itemProjectID",
"itemProjectLabel",
"itemStatusID",
"itemStatusLabel",
"itemTypeID",
"itemTypeLabel",
"originatorID",
"originatorLabel",
"managerID",
"managerLabel",
"responsibleID",
"responsibleLabel",
"totalExpenseTimeUnit",
"totalExpenseTimeUnitLabel",
"totalPlanTimeUnit",
"totalPlanTimeUnitLabel",
"budgetTimeUnit",
"budgetTimeUnitLabel",
"remainingTimeUnit",
"remainingTimeUnitLabel",
"currencyName",
"currencySymbol"
],
"dateFields": [],
"numberFields": [
"totalExpenseCost",
"totalExpenseTime",
"totalPlanCost",
"totalPlanTime",
"budgetCost",
"budgetTime",
"remainingCost",
"remainingTime"
]

The following figure shows the attributes in the Allegra user interface (item detail view). The numbering refers to the entries in the mapping table.

The following table describes the attribute mapping between Allegra and the Power BI data source.

Allegra		Power BI
Total actual	Work	totalExpenseTime	totalExpenseTimeUnit	totalExpenseTimeUnitLabel
	Cost	totalExpenseCost
Budget	Work	budgetTime	budgetTimeUnit	budgetTimeUnitLabel
	Cost	budgetCost
Planned value (PV)	Work	totalPlanTime	totalPlanTimeUnit	totalPlanTimeUnitLabel
	Cost	totalPlanCost
Estimated remaining effort	Work	remainingTime	remainingTimeUnit	remainingTimeUnitLabel
	Cost	remainingCost

Data Sources for Expenses

These data sources provide the individual postings for the selected transactions for the time and monetary expenditures.

ExpenseByFilter

This data source provides the accrued expenses for the items determined by the filter, optionally limited to a certain period of time.

Parameters:

• filterID: String, single value, mandatory; one of the saved query filters

• dateFrom: Date ( format „day/month/year“) Expenses are only taken into account from this date

• dateTo: Date ( format „day/month/year“) Expenses are only considered until this date

ExpensesByWorkspace

This data source provides the expenses incurred for the items in the listed workspaces, optionally limited for a certain period of time.

Parameter:

• workspace: List<int>, multiple value, mandatory; list with workspacce id’s

• dateFrom: Date ( format „day/month/year“) Expenses are only taken into account from this date

• dateTo: Date ( format „day/month/year“) Expenses are only considered until this date

Attributes for Expenses

"fieldNames": [
"accountID",
"personID",
"workItemID",
"hours",
"cost",
"subject",
"effortDate",
"description",
"lastEdit",
"accountName",
"accountNumber",
"status",
"costcenterID",
"costcenterName",
"costcenterNumber",
"personName",
"personEmployeeID",
"workItemTitle",
"issueType",
"projectID",
"originator",
"manager",
"responsible",
"projectLabel",
"currencySymbol",
"currencyName",
"statusLabel",
"issueTypeLabel",
"originatorLabel",
"managerLabel",
"responsibleLabel",
"subproject",
"subprojectLabel"
],
"dateFields": [
"effortDate",
"lastEdit"
],
"numberFields": [
"cost",
"hours"
]

Data Sources for Items

These data sources provide a list of items.

ItemsByFilter

This data source provides a list of items that the specified filter passes.

Parameters:

• filterID: String, single value, mandatory; one of the saved query filters

• hierarchical: boolean (True/False), optional; if True the result will

  be returned as a hierarchical list, otherwise as a flat list.

ItemsByWorkspace

This data source provides all items from the specified workspaces.

Parameters:

• workspace: List<int>, multiple value, mandatory; Liste mit Bereichs-IDs

• hierarchical: boolean (True/False), optional; if True the result will

  be returned as a hierarchical list, otherwise as a flat list.

Item Attributes

The item attributes are created dynamically. The system attributes are returned as well as the non-empty custom attributes used at least once.

All item attributes are of type String.