Before you can use the reports, you will need to configure the SQL data connections to use your own ConfigMgr database.
You will need to have at least db_datareader access to the ConfigMgr database with your logged-on account.
When you first open a report, you may get prompted with a security warning (depending on how your security settings are configured) such as the ‘Data connections have been disabled‘ warning below. If you do get this prompt, you can go straight to Configure the Data Connections section below, before clicking Enable Content. If you clicked Enable Content first, proceed to Cancel the Data Connection Prompts.
Cancel the Data Connection Prompts
After you have enabled the data connections, they will attempt to connect to the SQL server in the current connection string, and after a few seconds will fail with an error like this:
The data connections are set to refresh upon opening, so that the report updates itself with the current data whenever it is opened.
Click OK. Then you will see the SQL Server Login window. Simply click Cancel to close it.
Configure the Data Connections
Now go to the Data ribbon, and click Connections.
You will see a number of data connections that will vary depending on the report.
Select one of the connections, and click Properties. On the Definition tab, you will see the Connection string field.
Simply remove the existing connection string and paste in the connection string to your own database. All you need in the string is the driver name (should be “SQL Server”), SQL server name (and instance) if applicable, and the database name, like this:
Excel will complete the rest of the string for you after you save the connection. Once you have updated all the data connections in the workbook, the report will display the data for your environment.