Role Required
To set up your Reporting & Analytics dashboard you need to be assigned to the Application Administrator or the Records Manager role.
Make sure you have the latest version of Power BI Desktop installed. Power BI Desktop is free.
Introduction
Our Reporting & Analytics solution offers a comprehensive reporting data feed as well as pre-build Microsoft Power BI dashboards enabling you to rapidly gain insights into content managed by Records365. You can leverage the pre-built dashboards as a starting point to build your own tailored dashboards. In addition, you can use Power BI to integrate over 60 other data sources to create powerful visualization across different reporting repositories in your organizations.
You need to download two (2) Records365 reporting files from the Records365 portal in order to run reports in Records365:
- Our prebuilt Power BI report (a .pbix file)
- Our Power BI custom data connector (a .mez file)
For more information about Power BI Custom Data Connectors, click here.
Installing the Records365 Reporting & Analytics dashboard
If you are unable to download the reporting files because the Reporting & Analytics page appears disabled, please contact support to enable this feature for you.
Minimum system requirements
The table below documents the system requirements that must be met by the computer that is running Power BI.
| # Records365 records | Memory required |
|---|---|
| Up to 2.5 Million Records | 8GB |
| Up to 5 Million Records | 16GB |
| Up to 10 Million Records | 32GB |
| More than 10 Million Records | 64GB |
Downloading reporting files
Follow these steps to access the Records365 reporting files:
- Login into Records365
- Navigate to the Administration page by clicking the cog wheel on the top bar
- Click on Reporting from the left-hand navigation menu
- Click on the files to download them
Installing the Custom Data Connector
- Open Power BI Desktop
- Click File on the ribbon
- Click Options and Settings then Options.
- From the left hand menu, select Security.
- Under Data Extensions, change the selection to “(Not Recommended) Allow any extension to load without validation or warning”. More information about this setting is available here.
- Click OK.
- Now, close Power BI Desktop
- Open a Windows Explorer window, and navigate to your My Documents directory
- Create a New Folder named “Power BI Desktop”, and within it, another folder called “Custom Connectors”. Please note that this is Power BI’s requirement. The folders must be named and located exactly as described
- Now, copy the downloaded Data Connector (.mez) file to this directory
Now, re-open Power BI Desktop, and to check that the Custom Connector has successfully been installed:
- Click File from the ribbon
- Click Get data and then Get data to get started
- On the pop up window, type Records365
- Verify that you can see the Records365 connector under “All”
- If the connector is not present and no error is displayed when starting Power BI Desktop then check that the above settings are correct
If you have used the previous version of the reporting solution, you might see a Records365 Beta connector. This connector can be removed from your Custom Connectors folder and replaced with the one retrieved on the previous step. You must have the latest version of the Data Connector & dashboard Template to successfully connect with your data. The previous template is no longer compatible. Any custom reports need to be recreated using the new template.
Configuring the Records365 Reporting & Analytics dashboard
Now that the Custom Data Connector is installed, you can access data from Records365 in Power BI! Start by opening the Records365 Reporting & Analytics dashboard.pbix downloaded in the previous step.
You should see the dashboard visualizations display:
The dashboard is still displaying sample data at this point. Let’s set-up the dashboard and populate it with live data from your Records365 tenancy:
- Open your browser and login into Records365 using as a Application Administrator or Records Manager
- Navigate to the Administration page by click the cog wheel on the top bar
- Click on Reporting from the side panel
- Click Copy Shared Access Signature URL. This URL is used to access your data. We recommend sharing it only with people who need access to reporting and via secure means.
- Navigate to the Records365 Reporting & Analytics dashboard in Power BI
- From the ribbon, click on the arrow in Transform data and then Edit parameters
- On the pop up, paste the URL copied in the previous step on the URL field
- Click OK
- Click Apply Changes on top of the dashboard
- Your data will start loading
You may be prompted with pop-ups to establish connection with your data. Select “Anonymous” and click connect. The Shared Access Signature URL contains all the information needed to securely access your data.
Tips for first time users:
- Most of the visualizations in the dashboard page filter the remaining items on the page when you click on them. For example, clicking on a slice of the “Uncategorized Records by Content Source” pie chart will filter the counts at the top of the dashboard as well as some of the other visualizations, like the Daily Records Submitted on the right of the page. Not all the visualizations behave in this way as some of the metrics don’t provide additional insight when used as a filter.
- When hovering over a data point in a visualisation, like a slice of a pie chart, a point on a line chart, or a bar in a bar chart, a tooltip will appear showing the detailed metrics that this data point or ‘slice’ represents.
- The contents of all the visualizations can be exported by hovering over the chart and clicking on the ‘…’ icon at the top right of the chart area, then selecting ‘Export data’.
- The dashboard pulls down a lot of data from the Records365 service. We are continuing to make performance improvements, but be aware the refresh may take a while.
Building your own dashboards & reports
Understanding Power BI
Walking through all of the features of Power BI that our Reporting & Analytics dashboard uses is beyond the scope of this documentation. We recommend that you look at this article to help you get started with using Power BI.
Our prebuilt reporting dashboard should be used as template to build additional visualizations and reports. The following articles provide additional guidance on how to extend the prebuild dashboard:
- Add visualizations to a Power BI report
- Data View in Power BI Desktop
- Best design practices for reports and visuals
You are encouraged to create your own custom reports. However, we highly recommend you use the provided template as your starting point. Many data relationships and queries structures have been built in the template so that you can achieve best performance. Building custom reports without using the Records365 dashboard Template can be done but it is outside our support terms.
Understanding Records365 data feeds
Our Reporting & Analytics solutions gives you access to the following data feeds
- Records (GetRecords Power BI query)
- Connectors (GetRecords Power BI query)
- Aggregations (GetAggregations Power BI query)
- Loans (Get PhysicalLoan and PhysicalLoanItem Power BI queries)
- Holds (GetItemHold Summarized Power BI query)
You may mix and match reporting data from any of these reporting data feeds in order to create tailored visualizations and reports.
Understanding Records365 Hold fields
Hold (also known as Freeze) information is made available in the reporting solution to give you insight over the content that is subject to one or more holds within Records365. This information can be found in the GetItemHold summarized Power BI query. A record can have one or more holds applied, each hold applied will be represented a separate row in the reporting dataset. This allows you to report on
- all holds applied to a record
- all records within a hold.
Below is a summary of the fields available for Records365 holds reporting and visualization within Power BI.
| Field Name | Description |
|---|---|
| Item_internal_itemnumber | Record number for the item on hold |
| HoldComments | Comments entered at the time of adding the record to the hold. |
| HoldAddedDate | Date the record was added to the associated hold. |
| HoldAddedBy | Email address of the user who added the record to the associated hold |
| HoldID | Unique ID of the hold |
| HoldTitle | Title of the hold |
| HoldCreatedDate | Date when the hold was created |
| HoldCreatedBy | Email address of the user who created the hold |
| HoldDescription | Description for the hold |
| HoldModifiedDate | Date when the hold was last modified |
| HoldModifiedBy | Email address of the user who last modified the hold |
Understanding Records365 source fields
Commonly used source fields are now available for reporting. Currently, preselected fields from SharePoint Online and the physical records management module are available. The table below shows the fields that are currently available:
| Field Name | Content Source |
|---|---|
| ContentTypeName | SharePoint Online |
| CreatedMonth | SharePoint Online |
| FileDirRef | SharePoint Online |
| FileLeafRef | SharePoint Online |
| FileRef | SharePoint Online |
| LibraryName | SharePoint Online |
| ListUrl | SharePoint Online |
| SiteName | SharePoint Online |
| SiteUrl | SharePoint Online |
| Title | SharePoint Online |
| WebName | SharePoint Online |
| WebUrl | Share Point Online |
| Container | Physical Module |
| ProfileName | Physical Module |
You can easily find the source fields by searching for “String|” on the Fields side panel within Power BI.
Optimizing report refresh duration
Over time you may notice that your report takes longer and longer to refresh as the amount of reporting data increases. You can optimise the refresh time by configuring your Power BI installation to only fetch the most recent reporting data from Records365.
Use the below steps to reduce the time needed to refresh your reports:
- Open the Records365 Reporting & Analytics dashboard in Power BI
- Click Transform Data on the ribbon
- On the left panel, under queries, expand the Sources folder
- Right click on past queries (for example, 2020 Q2)
- Make sure Include in report refresh is unticked
- Click Close & Apply on the ribbon
Before removing the quarter from the Refresh Button, make sure you have synced the data from that quarter to avoid missing records.