CivilPro offers a Power BI connector to streamline setup and reporting of CivilPro data in Power BI. The connector is provided as-is, and connects directly to your CivilPro API. It supports a simple username/password authentication. The connector will resolve the authentication, and provide endpoint connection stubs for:

Primary CivilPro registers (listed below) for querying, select, sorting, filtering in accordance with our datasource documentation
Statistics endpoints for querying metrics on one or more projects
Generic endpoints that can be used to query most Get and Post endpoints available through the API

Important Note: SAML / SSO authentication is not supported by the connector. If your database is set to require SAML / SSO authentication for specific email domains (Subscription Settings -> Security), user credentials with an email that matches a specified email domain will not be able to authenticate the connector. In this case, an Associate user should be created with an email address that is not within specified email domains requiring SAML / SSO authentication, and username / password credentials used.

We strongly encourage users to limit any automations to just the data required using select and filters where possible. This is critical to ensuring undue pressure is not placed on your resources resulting in either poor user experience or additional costs for higher resourcing. Remember, this is the same API that powers the Web UI that you are querying.

The CivilPro Power BI API Connector is priced based on user seats on a CivilPro database:

Up to 50 Seats - $550 + GST per year
50 Seats or more - $950 + GST per year

Any support for the Power BI connector is outside our normal subscription support, and will be charged at hourly rates.

Installing the Power BI connector

We do not certify our connector. This is for two main reasons:

certification is a Microsoft-only process that requires months and places specific limitations on the connector that we cannot agree to; and
Microsoft has paused the certification of all new Power BI connectors.

If using a non-certified connector is not an option for your business, CivilPro can provide scripts to satsify certification requirement - and guidance in setup - however this will be charged at an hourly rate.

For Network Distribution:

If using Power BI in a networked environment, you will need to implement our connector using an on-premises gateway (https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors)

On Desktop:

Each user must place the .mez file in the correct Custom Connectors folder. The default location depends on their setup:

Standard location:

%USERPROFILE%\Documents\Microsoft Power BI Desktop\Custom Connectors

If using OneDrive Documents folder:

%USERPROFILE%\OneDrive\Documents\Microsoft Power BI Desktop\Custom Connectors

If the folder doesn't exist, users should create it manually.

Enable Custom Connectors in Power BI Desktop (Each user must do this)
Open Power BI Desktop.
Go to File > Options and settings > Options.
Under Security, enable "Allow any extension to load without validation or warning"
Or alternatively: "Allow extensions to load without certification"
Restart Power BI Desktop.

Accessing Datasources

You can see all of the available endpoints that can be accessed through the connector by reviewing the Swagger documentation at https://{yourinstance}.civilpro.com/swagger (e.g. https://yourcompany.civilpro.com/swagger).

There are 3 main classes of endpoint you can work with

The shapeable root GET endpoints for each data type. These accept a parameter of type DataSourceLoadOptions.
Other specific GET endpoints
POST endpoints.

TIP: You can add a query parameter to any GET to get records for any project that you have access to. Simply add 'qryProjectId=MyProjectId' to the query.

Shapeable GET Endpoints

These are the most useful of all of the endpoints. The GET methods exist for each controller type and return all of the records for the controller type for the project you are logged on to by default. The data is shaped by using the DataSourceLoadOptions parameter.

You can specify JSON to this parameter like this:

{ “Filter” : [“ControlLineId”.”=”,3456], “Sort” : { "Selector": "DateOpen", "Desc": true}}

{“Skip” : 100, “Take” : 50, “Sort” : { "Selector": "DateConf" }}

{“Select”:[“LotId”,”Description”]}

The full documentation for controlling filtering and shaping is at https://js.devexpress.com/Angular/Documentation/ApiReference/Data_Layer/CustomStore/LoadOptions/

Please ensure that any repeated queries apply shaping to only retrieve the data they require. This will ensure minimal disruptions to your users who also use the API for their web experience.

Pre-Built Shortcut Stubs

There are prebuilt shortcut stubs for the main registers:

Lots
NCRs
Approvals
Survey Requests
Test Requests

Any other DataSourceLoadOptions GET methods can be accessed by specifying the controller path to the genericGetDSOptions stub in the connector.

Other Specific GET Endpoints

Any of the other GET endpoints can also be accessed using the genericGetOther stub.

POST Endpoints

The POST endpoints can be reached using the genericPost stub - though this can get tricky if the post accepts multiple parameters. Luckily the most useful of the POST endpoints are the statistics. There are dedicated stubs for these:

lotStats
lotEOMStatusStats
lotActivityStats
lotStatsByUser
testRequestStats
testRequestEOMStatusStats,,
testRequestActivityStats,
testRequestStatsByUser ,
NcrStats ,
NcrEOMStatusStats ,
NcrActivityStats,
NcrStatsByUser,
SurveyStats,
SurveyEOMStatusStats,
SurveyActivityStats,
SurveyStatsByUser,
ApprovalStats,
ApprovalEOMStatusStats,
ApprovalActivityStats),
ApprovalStatsByUser

Related to