Automatically generate custom views/reports based on a database

Re: Automatically generate custom views/reports based on a database

...and you don't like pivot tables?

Re: Automatically generate custom views/reports based on a database

Anybody gotta pivot table 101 link to offer?

Probably something here in Dave's collection to look at I'm sure.

Automating it should be no more difficult than using the macro recorder I would think.

Re: Automatically generate custom views/reports based on a database

Ok pivot tables are great but sometimes they can be a little hard to get the way you want them. The reports sometimes don't get "businesslike".

I am working on something similar as you. One datasheet, one Querysheet using Excel database functions and then the reportsheet (the formated report using the query.

Check out http://www.ozgrid.com/FreeDown…nctionsWithValidation.zip

It´s an XLent article.

I havent used these functions before, they are a little hard to grasp but very powerfull.

Best of luck to you!

/Mats

Re: Automatically generate custom views/reports based on a database

[pt]*[/pt] can be a little daunting at first, but well worth persevering with. Added to that is the GetPivotData Function. By combining the two I can produce Monthly & Year End accounts in a flash.

Re: Automatically generate custom views/reports based on a database

Yes Roy I agree with you BUT I find that when trying to use pivottables with accounting etc. a very common display of columns like:

This year - Last year - Diff +/- - Diff%

Is this possible to do? I only manage to get

(See my file for exakt display)

This Year Last year
Diff+/-
Diff %

Wich I think is is a poor display. I always strive to put the same kind of values in the same column. I think the database functions give you more controll of display.

I haven't been using pivottables a lot but are now commited to try to master them. Any help/input much appreciated. /Mats

Re: Automatically generate custom views/reports based on a database

Remember, no one says you're forced to use pivot tables exclusively.

Perhaps in your case the pivot data is just one step in the process of building the macro and amassing the data to populate your report.

But... yeah you're probably right, most times I don't even bother with the pivots in favor of just pulling/summarizing the data in code myself. Suggesting pivot tables is usually the first direction I'll give because I generally assume people would like to avoid the coding.