Hi Everyone,

Firstly thankyou for previous guidance on dealing with my crosstab
data, I have implemented the suggestions and it has made a huge
performance increase (a table to base the reports off). Had to make a
large re-design of the db's query structure to feed the 'data' table,
but the whole capture (raw data from a live system) and re-build of
the data table takes only 20 seconds or so, and then after the reports
run in under a second. Pretty snappy :-)

I now have a request to implement a change to the crosstab reports,
whereby the data from 'present day' is displayed (as per the existing)
as well as a comparison to previous data (historical). I do have all
this data in the newly made 'data' table that is the basis of the
existing crosstab reports. I have no idea how to implement a 'multi-
WHERE' scenario for doing the date based comparison.

My data table looks like this:
VS Text
ORGANISATION Text
category Text
subcategory Text
segment Text
SOLL Number Double
IST Number Double
report_date Date/Time Short Date (Indexed - Duplicates
OK)

This data is already 'pre-aggregated' for the reporting structure,
cutting down ~75k rows per raw data set to ~280-290. Obviously much
quicker to work with less rows!

A sample crossstab report is:
TRANSFORM Format$(Sum([IST])/Sum([SOLL]),"Percent") AS Achieved
SELECT data.ORGANISATION
FROM data
WHERE (((data.report_date)=(SELECT Max(data.report_date) FROM data) Or
(data.report_date) Is Null))
GROUP BY data.ORGANISATION
PIVOT data.VS;

What is now desired is to take data from a previous date (one of the
available - these are all in the data table hence the WHERE clause
above), and have the difference between its value compared to the
latest value. Eg/

todays' Sum[IST]/Sum[SOLL] = x
yesterdays' Sum[IST]/Sum[SOLL] = y
comparison desired => x/y = z

What is wanted on the report is per VS (column), per ORGANISATION
(row) values for x and z.

Is there a way to handle this inside a single crosstab query, or do I
need to build some queries behind the scenes to make it happen (seems
messy)? Any help or guidance would be appreciated.

Cheers

The Frog

OK, some progress made.....

I have created two queries that provide the data in an appropriate
way, one for 'today' and another for 'historical'. I have a third
query that joins these two together via the ORGANISATION field, and
produces an output with all the relevant fields from the 'today'
query, and the [IST] field from the 'history' query as [OLD_IST]. So
now I know what the current value is [IST], what it should be [SOLL],
and what it was [OLD_IST]. So far so good. I call this query
qryCrosstab_MASTER.

I built a table in Access to house some fieldnames for the crosstab
report(s), since it seems that subverting the crosstab this way is the
only way to get multiple values for a column - actually produces two
columns for each regular column, one for each fact you want to show. I
have the two fields in my table (xHeaders) as Achieved and Variance
stored in the field [FieldName].

I use the following sql for the crosstab and I am getting pretty close
to the actual right results:

TRANSFORM Avg(IIf([FieldName] ="Achieved",[IST]/[SOLL],([IST]/[SOLL])-
([OLD_IST]/[SOLL]))) as TheValue
SELECT [ORGANISATION]
FROM xHeaders, qryCrosstab_MASTER
GROUP BY [ORGANISATION]
PIVOT [VS] & " " & [FieldName]

What I really need is the equivalent of:
IIf([FieldName]="Achieved",Sum([IST])/Sum([SOLL]),(Sum([IST])/
Sum([SOLL]))-(Sum[OLD_IST])/Sum([SOLL])) as TheValue

but this wont parse in a crosstab........
Does anyone know a way to get around this slightly inconvenient
problem? Doing the Sum(IIf( approach wont cut it here as the result
would be meaningless, I really need the logical order of the above,
but in a way that the crosstab can process without being a problem.

Any ideas?
Cheers

The Frog

Solved the problem, but with a little added complexity (sadly).

There is no way to perform aggregations in any form (it seems) inside
an IIf. This even applies to using sub selects. Attempts to have
calculated (but not shown) columns failed because it seems that even
if you name them in the query designer, you cannot reference them in
another column inside the same query. If there is a way to do this
please let me know!

In the end I created a relatively simple query structure that provided
the 'today' data alongside the 'historical' data in a single query,
along with fields needed later to filter for the different reports. I
think I stated this above in an earlier post. Anyway, to get to the
crosstab bit, it was necessary to pre-aggregate the data. To do this I
created a 'pre' query for each crosstab that did the formula
expressions for me using a normal select type query. I named the
expressions to what I wanted for easier use later in the crosstab.

In the crosstab, I based the data on the pre-query, and included the
unjoined xHeaders table I mentioned in an earlier post, and did the
column header as formula thing as listed before (previous post). Now
all I had to do was get the data into the appropriate cells, and to do
this I used the following:
TheValue: FormatPercent(Max(IIf([FieldName]="Achieved",[Achieved],
[Variance])))

[Achieved] and [Variance] are the expression fields from the pre-
aggregate query. Since there is only one value for each combination,
the summary function you choose to use really could be any of Max,
Min, Sum, Avg, and they can only ever return that one value. In short
the right value goes into the right cell, and it is quick to run.

The only part that I wish I knew how to do, and I am so far unable to
find a way to do it, or for that matter even a reference to it in
Google, is to be able to use the value in a calculated column in a
crosstab that isnt shown (ie/ not a row, column or value). If this is
possible then the pre-aggregation query can be eliminated.

Can anyone shed some light on the use of calculated fields that arent
shown in crosstab queries? Do they serve any purpose at all?

Cheers

The Frog