Formatting MS Access report with subreports and subforms in pivot views
March 17, 2012 11:45 AM Subscribe
Microsoft Access: Help me streamline (or completely automate) creating weekly reports that include tables and charts. I'm almost there, but I just can't wrangle the formatting. Is it me or is it Access?
We keep statistics of our library's reference desk in an Access (2007, 2010) database I put together. It's a small enough DB that I can manage creating queries and reports on the fly as needed. I do, however, send around a weekly report that hits the highlights. Thus far, I've been exporting data to Excel and making tables and charts -- like this.
I've been trying to recreate that in the Access database by selecting a date on a form, and pushing a button that opens a PDF preview of a report that has subforms that open in pivot table and pivot chart views. But it comes out looking like this. Serviceable, but I need to:
- understand how reports create blank pages. I've been fiddling with the margins (and googling endlessly) but I can't get the preview to stop making blank pages.
- control column width (and other formatting issues) for the tables.
- show zero value hour/dates on the chart.
But, of course, I might be missing the forest for the trees. Is there another way I can accomplish this? Not only would I like to save myself some time with the weekly reports, but I'd like to make it so that my colleagues can do it themselves. Picking a date, pushing a button, and hitting print is about as sophisticated as it can get.
We keep statistics of our library's reference desk in an Access (2007, 2010) database I put together. It's a small enough DB that I can manage creating queries and reports on the fly as needed. I do, however, send around a weekly report that hits the highlights. Thus far, I've been exporting data to Excel and making tables and charts -- like this.
I've been trying to recreate that in the Access database by selecting a date on a form, and pushing a button that opens a PDF preview of a report that has subforms that open in pivot table and pivot chart views. But it comes out looking like this. Serviceable, but I need to:
- understand how reports create blank pages. I've been fiddling with the margins (and googling endlessly) but I can't get the preview to stop making blank pages.
- control column width (and other formatting issues) for the tables.
- show zero value hour/dates on the chart.
But, of course, I might be missing the forest for the trees. Is there another way I can accomplish this? Not only would I like to save myself some time with the weekly reports, but I'd like to make it so that my colleagues can do it themselves. Picking a date, pushing a button, and hitting print is about as sophisticated as it can get.
When I needed reports formatted just so in OpenOffice Base, whose inbuilt report generator is totally sucky, I ended up writing StarBasic code to generate them by automating OpenOffice Writer and attaching that code to buttons in one of the Base forms. The same approach should work with Access, Excel, and VBA.
posted by flabdablet at 12:12 PM on March 17, 2012
posted by flabdablet at 12:12 PM on March 17, 2012
Best answer: You might find it's actually easier to use Excel's native ability to pull data from Access and just update your spreadsheet weekly, if you find the formatting and charting easier in Excel. Also, you can control formatting better on forms in Access than for tables, so maybe take a look at creating forms (even in datasheet view, which looks just like a table) for finer formatting control. Blank pages are definitely a function of page sizes and margins, so if you keep fiddling there you will find a way to get your pages the way you want. As far as including zero values, that depends on your data source, but have a look at the documentation of the Nz function and see if you can work that into your query to provide what you need.
posted by notashroom at 4:23 PM on March 17, 2012
posted by notashroom at 4:23 PM on March 17, 2012
Response by poster: "You might find it's actually easier to use Excel's native ability to pull data from Access and just update your spreadsheet weekly[.]"
This works pretty darn well, but not entirely seamlessly. Once I understood the way Excel and Access interact, it became pretty easy. The book, Microsoft Excel & Access: integration with Office 2007, was very helpful getting me there.
Basically, I put a table in an Excel worksheet that draws from a Microsoft Query of an Access database. The Query parameters (thus far, just date ranges) are set to cells on the worksheet. In turn, I created several worksheets that contain PivotCharts and PivotTables linked to the main Table linked to the Access DB. THEN, I put a hyperlink on the Access form that opens the excel file and the main worksheet. The main worksheet has a brief description that tells the user to adjust the dates and hit a button that triggers a macro that refreshes all the data and prints the entire workbook to a PDF.
So, it works *almost* like a report in Access (there's an extra step for clicking the link to open the excel file), with much, much nicer formatting. The presence of macros and data connections, however, means that Microsoft security settings prevent it from opening initially on some machines. There aren't so many people that need to get at it that it presents much of a hurdle.
Thanks for the suggestions.
posted by GPF at 8:00 AM on April 20, 2012
This works pretty darn well, but not entirely seamlessly. Once I understood the way Excel and Access interact, it became pretty easy. The book, Microsoft Excel & Access: integration with Office 2007, was very helpful getting me there.
Basically, I put a table in an Excel worksheet that draws from a Microsoft Query of an Access database. The Query parameters (thus far, just date ranges) are set to cells on the worksheet. In turn, I created several worksheets that contain PivotCharts and PivotTables linked to the main Table linked to the Access DB. THEN, I put a hyperlink on the Access form that opens the excel file and the main worksheet. The main worksheet has a brief description that tells the user to adjust the dates and hit a button that triggers a macro that refreshes all the data and prints the entire workbook to a PDF.
So, it works *almost* like a report in Access (there's an extra step for clicking the link to open the excel file), with much, much nicer formatting. The presence of macros and data connections, however, means that Microsoft security settings prevent it from opening initially on some machines. There aren't so many people that need to get at it that it presents much of a hurdle.
Thanks for the suggestions.
posted by GPF at 8:00 AM on April 20, 2012
It shouldn't be terribly hard to work out how to write a tiny scrap of VBA to open the spreadsheet for you, and attach that to a button in an Access form; that way your users wouldn't necessarily even notice you were using Excel.
posted by flabdablet at 6:42 AM on April 22, 2012
posted by flabdablet at 6:42 AM on April 22, 2012
I'm glad you've got it sorted. It sounds like you've gotten it much more manageable than before. You can probably sort out a macro or a little VBA to auto-calculate your dates when the worksheet is opened (as long as they're any sort of pattern), and refresh the data for you so the end user won't have to, if it's worth the extra effort to you.
posted by notashroom at 1:42 PM on April 23, 2012
posted by notashroom at 1:42 PM on April 23, 2012
« Older How to view geo specific web content from anywhere... | in search of an apartment exchange website Newer »
This thread is closed to new comments.
I found that the best strategy was to create pivot tables in Excel. They're easier to manage, you can easily set print margins, etc. I'd just take the underlying data table, export it to Excel, and create pivot tables there.
As for getting some of your colleagues to do these reports themselves--depends on their computer literacy, honestly. Access is not especially user-friendly, even though it claims to be able to create "reports" that can be generated merely by pressing a button.
posted by dfriedman at 11:55 AM on March 17, 2012