dynamic columns number in crosstab report

[Bobi]

Hi

I have made a crosstab query that can display a dynamic number of columns
titled the name of the date it finds in the table.

But when I add the crosstab to a report it stops working.

I can then hard-code a date from the table in properties of the column
heading and make the report show the results, but then it's not dynamic
anymore.

How can I make it work?
regards
Bobi

 

[Gina Whipp]

Have a look at http://www.tek-tips.com/faqs.cfm?fid=5466.&quot

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Bobi]

Hi
Nice code Gina but I'm not there yet.

I have these tables:

tab_location
tab_sampels
tab_test

Here is my setup:

I have a table with some locatione. Each location can have an amount of
samples that has a date. There will be from 0 to about 5 samples per
location. Each sample can then have a list of parameters with a value stored
in the tbl_test.

My report will have a number COL's reflecting the number of different sample
dates in my table - if 5 different dates the it will give 5 col's with the
data as the heading.

As a crostab it gives me no problems, but when I want to use it in a report
the col headings must be defined in the col headings.

Example:
With this table data:

The tbl_ test contains 7 entries:

Param Date Value
p1 1-1-2001 5
p1 3-1-2001 3
p2 1-1-2001 8
p3 1-1-2001 1
p3 3-1-2001 7
....

The report must look like this:

Params 1-1-2001 3-1-2001 7-1-2001
p1 5 3
p2 8 9
p3 1 7 1

If there was no data from 7-1-2001 the last column should not be there.


Any idea of how to make a report like this?

Regards

Bobi

 

[Gina Whipp]

Bobi,

What happens when you you the wizard to create the report?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Bobi]

HI

Finaly got it working!!

I followed this guide from microsoft:

http://support.microsoft.com/kb/328320

First i made a modification on the Nothwind db and it worked fine, but when
it came to my own db I was only able to show the first line with data in the
crosstab. The column names was ok and the first row too, but then nothing
else showed up. I used days at comparing the two sourcecodes. And then I
found at difference in one of the codes and changed it - and then it worked.

The in my quest for the answer I saw that other people has had the same
problem. The solution was to add the crosstabquery to the record source of
the report.

I'm still not able to add the report as a subreport to my main report but
I'm working on that. Other wise I must use it as an appendix.

When I try to add the report to my main report I get a error named:

Your can't use a pass-through query or non-fixed-column crosstab query as a
record source for a subform or subreprt.

Before you bind the sufborm or subreport to a crosstab query, set the
qurye's collumnHeadings property.


regards

bobi