| Info |
|---|
We see users leveraging Microsoft Access Database files (.mdb) for a number of reasons. You may want to combine Projector data with your own databases, calculate your own columns or stored procedures in Access, or set up what we call a Data Refresh File. These files can then be used to auto-update your reports with customized data. |
Dataset Refresh Files
The dataset refresh functionality within Projector allows you to create Microsoft Access databases that contain the raw data underlying a Pivot Table report. Access to this raw data provides additional flexibility to advanced reporting users who wish to customize their reports through custom formulas and formatting, but need to refresh the data within those customized reports periodically. In addition, this technique may be used to combine information from multiple Projector reports if needed.
| Tip | ||
|---|---|---|
| ||
Report web services are a great preferred alternative to data refresh files. They offer much of the same functionality, without the reliance on a shared network location nor the need to manually resave files out from Projector. |
| Info |
|---|
The dataset refresh functionality within Projector allows you to connect an Excel file to an Access database file. This provides additional flexibility to users who wish to customize their reports using formulas, formatting and combining data from multiple Projector reports. |
...
Create a report and
...
link it to a data refresh file
- Go to the Reports tab of the Projector Management Portal
- Double click to open a report and check that it supports data refresh files
- On the Output tab select Data Refresh File
- Leave the default setting, "Spreadsheet" selected for now and run the report with whatever options you wish. Please keep in mind that you will not be able to change the field settings later on and use the data refresh functionality.
- Once the report completes save it to your computer
- Run the same report again, but this time on the Format tab enable the "Data refresh file" radio button.
- Save the data refresh file (mdb) to your computer.
- Open your original Excel Report
- Click anywhere inside your Pivot Table and the "PivotTable Tools" ribbon menu should appear in Microsoft Excel
- Choose Options -> Click Refresh (not Change Data Source)
- A popup window appears. Navigate to the data refresh file (.mdb) you created earlier
- The spreadsheet updates with the new data. If you want to update the data again you can overwrite the .mdb file and click the refresh button again
...
Refresh a report that uses a data refresh file
- From the Management Portal save your .mdb file and overwrite your old mdb file. If you do not wish to overwrite your old mdb file then save as a new file and follow the instructions for Updating a report to point at a new .mdb file.
- Open your report in Excel
- Click in your report data so that the PivotTable tools appear in Excel
- Select the Options tab under PivotTable Tools
- Click Refresh
...
Update a report to point at a new .mdb file
- If you want to point the spreadsheet at a different .mdb file click the drop arrow below refresh and choose "Connection Properties."
- Under the Definition tab change the file path to the new .mdb file.
...