Report with expanding fields

[matttan]

I am trying to set up a report that is effectively a crosstab query. The problem, of course, is that once you set up the crosstab, the column headings are determined, and so when you report on them, it locks you into the values that existed at that time.

For example, I am trying to report on certain data (row headings are people's names, values are values), by month (so column headings are month end dates). But if I set up the report now, there's no 'room for growth' for next month, the month after, and so on.

Is there any easy (or even not so easy!) way of allowing the report to be flexible to possible column headings in crosstabs - or some other way of designing the report to allow for this?

Ta,

Matt.

 

[Andrew Fergus]

Hi Matt

What you are asking for can be done but it is a little tricky. The way to get around this is to fix the column headings in the crosstab query using generic names (such as period1, period 2 etc.) and using the same names in the report. This report will then stand the test of time.

The generic period names must be set up in a query that is used as the basis for the crosstab query. You can pass the actual date of the last period through the two queries onto the report via a separate row heading (on the crosstab query) and then convert / replace the period numbers on back into real dates on the report - the basis for the conversion being the actual date of the last period that was passed through the queries. The limitation with this method is that you have a fixed number of periods on the report.

For further information about converting dates on the report heading, have a read of my recent replies to bmurch's requests for help with dates and you'll see that there are many ways of handling dates using the functions such as dateadd & now() etc. In another of bmurch's posts you will a find a reference to a very similar question on crosstab report headings that I answered some months ago (I think the original response was to a question from David Skov).

HTH, Andrew.