none
How do I paginate a wide crosstab report... RRS feed

  • Question

  • I have a crosstab report that is very wide.  For viewing purposes, I've set the paper to A3 - great for now.  However, at some point, I'm going to run out of real estate and be forced to go to a second page.  Is there a way to insert a page break after a certain column?

    Thanks for your help!!

    Wednesday, March 22, 2017 9:03 PM

Answers

  • Can you use 'NOT IN' with the PIVOT clause, or do you need to somehow dynamically build the IN list?
    Don't confuse this with the use of the IN operator in a WHERE clause.  What it is doing here is not returning a Boolean TRUE, but specifying the columns to be returned by the query.  The values must correspond exactly with values  in the Product column of course.  You need to identify which of those values will appear as column headings on each page and include a subset of these values in each query.  The values will (or should be) distinct values in a referenced Products table so it is just a case of dividing the set into subsets, each of which will have a fixed number of values corresponding to the number of columns which can be accommodated in a page, with the possible exception of the last set, which might well be fewer.

    One other thing this will do is ensure that an empty column is returned if a product is not represented in any store's sales data.  This is usually considered desirable as you'd want to know which products have not sold at all, as well as those which have been flying off the shelves.

    Ken Sheridan, Stafford, England

    • Marked as answer by plynton Thursday, March 23, 2017 11:46 PM
    Thursday, March 23, 2017 5:43 PM

All replies

  • Hmm, not sure but you might try playing with the Number of Columns in the Page Layout Settings.

    Hope it helps...

    Wednesday, March 22, 2017 10:06 PM
  • A report will vertically break the page automatically if the controls in the design area exceed the width of the printable area of the page, but the break will not necessarily occur between two columns; it could be in mid-column.  Consequently you will need to determine where the break occurs by trial and error, and if it occurs in mid-column, move that column and all following columns to the right until the break occurs in the correct position.

    Note, however, that it will still be regarded as a single page as regards the page numbering, so if you are showing the page numbers in a footer or header, these will not match the number of pages.  One way to get round this, albeit a not entirely satisfactory one,  is to place two page number controls in the left and right parts of the design area respectively, the one on the left with a ControlSource of =Page, the other with a ControlSource of =Page & "a".


    Ken Sheridan, Stafford, England

    Wednesday, March 22, 2017 11:37 PM
  • Thanks Ken,

    This may be a stupid question, but given that this is a crosstab report, is there any way to repeat the row headers after a page break?

    I have stores as column headers, and each row is a product sold by the stores.  Would like to have the products repeated on each page.


    Thanks for your help!!

    Wednesday, March 22, 2017 11:54 PM
  • A possible solution with a crosstab query would be to create separate reports, identical in layout, but each based on a variation of the crosstab query which includes an IN clause, so that each returns a set number of columns, just enough to fit within a page. If for simplicity we assume that you can only fit three products per page, then the first report's query would have something like this:

    PIVOT Product IN("Apples", "Bananas", "Cherries")

    The second would have:

    PIVOT Product IN("Gooseberries", "Oranges", "Pears")

    and so on.  You could then embed the reports as subreports side by side in a parent report, being careful to place each so that the page breaks vertically before the next subreport.  The parent report could be unbound, or bound to another query if you wanted to return overall summary data in the report header or footer for instance.  It's not something I've ever tried, but I see no reason why it should not work.  An IN clause also means you can order the columns however you wish, so you don't have to accept the default alphabetic order.

    Ken Sheridan, Stafford, England

    Thursday, March 23, 2017 12:08 PM
  • Ken,

    That sounds like what I'll need to do.  Can you use 'NOT IN' with the PIVOT clause, or do you need to somehow dynamically build the IN list?

    Thanks again!!


    Thanks for your help!!

    Thursday, March 23, 2017 2:35 PM
  • Can you use 'NOT IN' with the PIVOT clause, or do you need to somehow dynamically build the IN list?
    Don't confuse this with the use of the IN operator in a WHERE clause.  What it is doing here is not returning a Boolean TRUE, but specifying the columns to be returned by the query.  The values must correspond exactly with values  in the Product column of course.  You need to identify which of those values will appear as column headings on each page and include a subset of these values in each query.  The values will (or should be) distinct values in a referenced Products table so it is just a case of dividing the set into subsets, each of which will have a fixed number of values corresponding to the number of columns which can be accommodated in a page, with the possible exception of the last set, which might well be fewer.

    One other thing this will do is ensure that an empty column is returned if a product is not represented in any store's sales data.  This is usually considered desirable as you'd want to know which products have not sold at all, as well as those which have been flying off the shelves.

    Ken Sheridan, Stafford, England

    • Marked as answer by plynton Thursday, March 23, 2017 11:46 PM
    Thursday, March 23, 2017 5:43 PM
  • Perfect, thanks Ken!

    Thanks for your help!!

    Thursday, March 23, 2017 11:46 PM