Cross Tab Report : X pages wide by Y pages tall? 2

[srmclean]

I have a crosstab query which is of completely variable number of columns & rows.

Currently I am able to fit up to 24 data columns on an A3 landscape page but what I'd really like it to be able to do is, if required, have up to 48+ data columns and simply repeat the 3 left most title columns on the left had side of each page as the report "grows" to the right. Like you can do when you print wide reports in MS Excel.

Any ideas?

I am currently setting up the 24 columns with generic heading names and then using VB to take the column headings off the query to insert the proper names and datasources.

Many thanks,

Shaun

 

[dhookom]

There is a sample Crosstab Report download at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

It allows you to define 24 (or any other number) of columns and dynamically creates your report. Columns beyond 24 will wrap under the first 24.

Duane
MS Access MVP

 

[srmclean]

Thanks but I don't want any wrapping as in those reports, I really need to be able to go onto the next page, length ways, keeping columns distinct.

In essence I have a list of car models down the left column, and then columns of colours from left to right and the cross tab is a count of each in stock. Given there may be 35 colour variations for 100s of cars the report may be 2 x 2 pages large but to read the report the car models would have to be repeat on all pages.

 

[dhookom]

Did you try the solution? It will "repeat the 3 left most title columns on the left had side of each page" for every group of columns.

Duane
MS Access MVP

 

[srmclean]

Duane,

Okay, I've relooked at the reports ...

I'm just a bit thick. There are two .mdb files in the zip, which one has the report you mention? (then which report is it, if the file has more than 1 report?)

Or do I have to put some extra data into the tables to make it go onto a second page? (to the right, ie report grows in width)

Please give me a bit more info.

Many thanks

Shaun

 

[dhookom]

The Crosstab.mdb is the one you want to review. You would need to understand how the solution works and then modify the report and heading subreport to meet your needs.

Duane
MS Access MVP

 

[Prattdakota]

I realize this thread goes back a few weeks, but I was looking for a way to report off a crosstab query. I found this thread and downloaded Duane's solution. Very slick solution and it works great. I have one problem. I'm a newbie with subreports and cannot get the subreport in the level header to create a column heading for more than 8 columns. I want to print up to 15 columns, but when it gets to the 8th column it wraps immediately to the next line in spite of having plenty of page space.

My crosstab query has 15 columns. The page width is over 14". The alias table allows columns "A" to "O". I've modified the code to only increase the level in the alias table if there are more than 15 columns. The data in the detail section prints fine, it's just the level heading that is a problem, and only then if I've selected more than 8 columns. I've also made the actual subreport width as wide as possible.

I've examined the properties for the subreport and the text box in the subreport numerous times looking for some setting that is preventing it from printing across the page.

If either of you guys has an idea for what might be going wrong, I'd appreciate your input. I'm out of ideas.

Again Duane, you've provided a really elegant solution.

Thanks
Dave

 

[dhookom]

Glad to hear this solution works for you. It seems to be much more flexible and efficient than others I have seen.

Open the subreport "srptCustomerHeadings" in design view. Select File|Page Setup and change the Columns from 8 to whatever.

Come on back if you have other questions.

Duane
MS Access MVP

 

[Prattdakota]

Duane,

Thanks much. That was just the ticket. I had gone to the print setup columns tab previously, but had done it by selecting the subreport from within the main report, thinking I was getting the subreport settings.

Thanks again, I appreciate the info.

Dave

 

[smsaji]

Hi,

Using CR10, reporting off stored procedure. Using crosstab report. Have varying columns from 2 to 40 depending on the parameter.

Looking for a way to display 10 columns in each page.

Now using one of the solution from the posts here. It displays columns in different page instead of virtual page, using the foll. Formula

IF {?@Parm} ='X' then

(if {ProcName.ColName} in ["A" to "G"]
then 1
else if { ProcName.ColName } in ["H" to "P"] then 2 else 3)

But some Parameters bring out more than 10 columns in “H” and it runs to an virtual page. So Looking for a way to print specific number of columns, say 10 columns in each page.

Downloaded the solution mentioned here by Duane. But it’s having only two .mdb files. Maybe it’s changed since the original post in 2003.

Will appreciate anyone’s solution.

Thanks
saj

 

[dhookom]

Is this a Crystal Report or Access Report question? My solution is for Access.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[smsaji]

This is Crystal Report 10 question. Sorry Duane, for misunderstaing the solution.

After posting the question, I realized it was not showing up in Crystal Report forum. Then
I posted it in Crystal Report also.

-saj