Our modern Reporting API has replaced the older technology of the reporting database. The reporting database will no longer be available for existing systems that never activated the database nor for new test systems created since 09/10/2020.
The reporting database will remain active in existing systems that are currently using the feature. We are happy support you with any questions concerning the migration to the new Reporting API before replacing the reporting database. If you have any questions, please contact us at support@meisterplan.com.
This article describes how to prepare Meisterplan for accessing reporting data with Microsoft Power BI Desktop or Microsoft Excel.
- Step 1: Activating External Reporting in Meisterplan
- Opening the Reports View
- Activating External Reporting
- Selecting Reporting Data
- Confirming the Settings and Retrieving Access Information
- Step 2: Setting up an ODBC Connection
See the respective articles in this section to learn how to actually connect the Meisterplan reporting database with Microsoft Power BI Desktop or Microsoft Excel once you are done preparing the connection.
Step 1: Activating External Reporting in Meisterplan
Once you are done with this step, you are ready for setting up an ODBC connection to access Meisterplan data with your reporting software.
Opening the Reports View
- In the left hand side sidebar, select the Reports view.
Activating External Reporting
- In the Reports view, click the Reporting Data toolbar button to configure external reporting.
- In the Reporting Data window, select Activate reporting database to provide planning data for external access.
Once you select this option, the reporting database, user and password will automatically be generated and entered here. You can display the password by clicking on the eye icon.
Selecting Reporting Data
- Under Allocation and Capacity Data, select monthly or weekly extraction of allocations, capacities and financials as required.
- If you wish to use the report templates for Power BI provided by us, please note that some of them require monthly extraction. Weekly extraction is not required for integrating the template reports.
- Enter the desired amount of time periods for monthly and/or weekly extraction.
Please note that the maximum amount of time periods is 60 for each monthly and weekly extraction. Please also note that a higher amount of time periods takes more time to process.
Confirming the Settings and Retrieving Access Information
- Click OK to confirm all settings and prepare the reporting database.
- Back in the Reports view, click Reporting Data again to reopen the view for copying the server name, database name, user name and password. You can display the password by clicking on the eye icon.
Step 2: Setting up an ODBC Connection
Please note that the architecture, 32 Bit/x86 or 64 Bit/x64, of both the driver and the ODBC Data Source Administrator you are using must correspond with the architecture of your reporting software.
Details about the exact components to use are given for the individual steps, where required.
Installing the ODBC driver for PostgreSQL
- Download the latest ODBC Driver for PostgreSQL from https://www.postgresql.org/ftp/odbc/versions/msi/ (scroll all the way down).
- Please note that the driver version you need (32 Bit/x86 or 64 Bit/x64) depends on the architecture of the reporting software you are planning to use:
- If you are using 32 Bit reporting software (e.g., Microsoft Excel 32 Bit or Microsoft Power BI Desktop 32 Bit), download the 32 Bit/x86 driver.
- If you are using 64 Bit reporting software (e.g., Microsoft Power BI Desktop 64 Bit), download the 64 Bit/x64 driver.
- On this page, you can find out which Microsoft Office version you are using.
- Install the downloaded driver.
Creating an ODBC Data Source
- Open the Microsoft Windows ODBC Data Source Administrator.
- Please note that the required version of this configuration program depends on the architecture of the reporting software and Windows system you are using:
- If you are using 32 Bit reporting software under a 32 Bit version of Windows, there is only one version of the program located in "%SystemRoot%\system32" (e.g., "C:\Windows\system32\odbcad32.exe").
- You can also access this program via the start menu under "Control Panel > Administrative Tools > Data Sources (ODBC)".
- If you are using 32 Bit reporting software under a 64 Bit version of Windows, run the 32 Bit version of the program located in "%SystemRoot%\SysWoW64" (e.g., "C:\Windows\SysWow64\odbcad32.exe").
- You can also access this program by searching for "ODBC Data Sources (32 Bit)".
- If you are using 64 Bit reporting software under a 64 Bit version of Windows, run the 64 Bit version of the program located in "%SystemRoot%\system32" (e.g., "C:\Windows\system32\odbcad32.exe").
- You can also access this program by searching for "ODBC Data Sources (64 Bit)".
- If you are using 32 Bit reporting software under a 32 Bit version of Windows, there is only one version of the program located in "%SystemRoot%\system32" (e.g., "C:\Windows\system32\odbcad32.exe").
- Click Add... to create a new data source.
- Select the PostgreSQL Unicode driver and click Finish.
- Copy the Server, Database Name, User Name and Password displayed in the Reporting Data window in Meisterplan into the associated fields for the new data source.
- Set the SSL Mode to require.
- Enter 5432 in the Port field.
- Click Test to test your database connection.
- If the test fails, make sure that your firewall allows connections to Meisterplan over the designated port.
- After successfully testing your connection, finish creating the new data source by clicking on Save.
The new data source is now available in Microsoft Windows applications.
- Click OK to close the ODBC settings
Next Step
The next step is connecting Microsoft Power BI or Microsoft Excel with the ODBC data source you just created, and accessing your Meisterplan data with your reporting software:
Troubleshooting
SSL certificate verification failed
Issue: I receive an "SSL error: certificate verify failed" when testing the ODBC connection.
Solution: Please note that if you are using a PostgreSQL root certificate stored under "c:\Users\[WINDOWS_USERNAME]\AppData\Roaming\postgresql\root.crt", this needs to be an AWS RDS certificate in order to connect to the Meisterplan reporting database. You can find a download link for a supported root certificate on this page in the AWS user guide.
Server connection timed out
Issue: I receive an "could not connect to server: Connection timed out ..." error when testing the ODBC connection.
Solution: This error is most often caused by the user's firewall blocking access to Meisterplan's database. Please contact your network administrator.
Remote certificate invalid
Issue: I receive an "The remote certificate is invalid according to the validation procedure" error when testing the ODBC connection.
Solution: The default full qualified domain name of the PostgreSQL Server (SSL) is "reporting.eu.meisterplan.com". If you need the full host name instead, you can contact our Support team. If it's still not possible to establish a connection, you need to install the SSL certificate of our hoster. You can find a guide on how to do this in the Power BI forum.