How would you publish pivot tables to non-Access users?

[kmacd6951]

Looking for feedback on the best way to publish data from Access.

Currently I am exporting Access Pivot Tables out to Excel (manually by clicking on the 'Export to MS Excel' button). Everyone who needs to see these reports has Excel, but not everyone has Access here.

I would like to maybe post these pivot tables to an Intranet, but need to be able to publish as Pivot table and need to allow the users to change their pivot view - so can't be static HTML.

Seems that Data Access Pages might be where I need to head? Or is there something more simple? I am having some problems with the DAP - for example a calculated field (for a percentage) from Access that works fine when I am in datasheet view doesn't seem to come over correctly in Pivot table view... I don't see way to add a 'calculated field' in DAP's.

Can anyone recommend a good place to get information on limitations of DAP's? Or am I making this whole project more complicated than I need to and you could suggest something really easy?

Thanks
Katie

 

[mdmilner]

If you want something *like* Excel Pivot Tables, why not use Excel Pivot Tables?

Two basic options.

You can work from within Access and export data including building all aspects of an xls

or

Use something like MSQuery inside Excel to import.

Either can be automated to the point of a few clicks to perform. Personally, it's a little cleaner from an end-user perspective to export out of Access and hand them the xls to use instead of asking them to select the data to use (users like things to be done for them - even removing a single step is generally good -- also removes the possibility of one more mistake opportunity)


Mike

 

[kmacd6951]

The piece that I fall short on with Excel is that I have to manually export the pivot tables to Excel.... I can't seem to find a way to do this through a macro.It works when I click the button to 'Export' but doesn't work through a macro. Have you had any experience with that?

 

[mdmilner]

Yes I have, but I'm probably not the best person to explain all the interactions with Excel. Here's a basic explanation of how to export data.
Three different methods - none use transfer spreadsheet.

http://www.mvps.org/access/modules/mdl0035.htm

As I rarely play with Excel objects (shapes, charts, etc) - I'm not the best person to show you code for working with those objects but I'd assume it's as easy as working with any other objects in VBA.



Mike