How to use the MP-Desktop Excel Pivot Link
Introduction
The export to Excel Pivot Link provides a quick and powerful way to output data from a Dataset to Excel for further manipulation, as a pivot table.
Using the Pivot Link
Once exported users will have a Pivot Chart and a Pivot Table.
From the Pivot Table, you can filter the data as required.
This is then displayed on the Pivot Chart.
There are many more options available for output. As these functions are all based in Excel and there are numerous online resources for this, they will not be duplicated here.
Saving the Pivot Link
You can save your Pivot Links containing any formatting and filtering you have completed. Simply Save As to a location of your choice.
To access the file again simply open it like any other Excel file. When reopening, you will need to Enable Content to establish the database connection saved in the Excel file. On the Data tab, you can refresh the data, which will update the Pivot with any new data. It's most powerful to use a Dataset which contains a dynamic date range, i.e. one that will continually update such as 'the last 12 months'.
To refresh the data, access is dependent on the method of Authentication you use to access MP-Desktop (more specifically the method being used when the Pivot was first created). If you are using Windows Authentication (i.e. you don't enter a username and password to access MP-Desktop) then the file will automatically connect to the database. If you use SQL Authentication (i.e. you do enter a username and password to access MP-Desktop) then you will be prompted for these credentials.
If you share the Pivot Link with another user they will be presented with the SQL Server Login box above if they refresh the data, unless that user also has a Windows connection to the database. Each user would need a valid username and password to be able to refresh the data within the Pivot Table.
Warning
Do not share your username and password with others to access data in the Excel Pivot as this could also give them access to MonitorPro, and make changes using your user's name.
Note
For EHS Data hosted systems these Pivot Tables will not be able to directly connect to the database for security reasons. Access to the databases in these cases is locked down to certain applications and fixed IP addresses. Please refer to support@ehsdata.com if this is required and they will provide a means for you to update the files on the hosting server with new data and then distribute them for reporting purposes.