Results 1 to 12 of 12
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Dynamic headings on crosstab-based report? (2000/SR-1a)

    <P ID="edit" class=small>Edited by jscher2000 on 08-Apr-02 21:24.</P>I am generating a report that lists data for the "previous 12 months" in 12 columns. I calculate the periods 1-12 in descending order (1 = most recent previous month, 12 = oldest previous month), which is the order in which I want to display them. A crosstab-based report is perfect, EXCEPT that using these numbers as column headings is too obscure. Because the headings will change every month, I cannot hardcode the column headings property.

    In an earlier post, it was suggested that one could use a date field and the Format function to generate correctly sorted columns. All I get is alphabetically sorted columns when I do this in my query. I tried "mmm-yy" my preferred format, and also "mmm" and both sort alphabetically. I suppose it makes more sense to put the formula in a textbox in the report itself, but to what field can I refer; the field name would be the period ending date, which changes every month.

    If those normal methods cannot be made to work, perhaps there is a way to dynamically update the column headings property with VBA? I can calculate the headings on the fly from a table, or store the headings in that same table using an update query, but this seems somewhat difficult to me. Actually, I can also calculate all of them just from a single parameter in the first record of a different table (the one that stores the report parameters). <font color=blue>It occurs to me that changing the properties in the query on the fly probably is the wrong approach, and that I should change the labels in the report, if there is a way to do that.</font color=blue>

    I don't know anything about sub-reports, but if making the crosstab report a subreport would allow me to assign the desired column headings (as part of the "main" report), I would consider that, too.

    What do you suggest? Thanks in advance for all sanity-saving advice.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    What I have done is the past is to build the query 'on the fly' and specifically the PIVOT part of the query.

    You can build the PIVOT such that only the columns defined by the PIVOT will be shown on the report.

    The order you define them in the PIVOT is the order the columns are presented on the report.
    HTH
    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    Have a look at this thread. You might get some ideas for your situation.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    I have encountered problems similar to this before. To correct the sorting issue, In the queries for the report or pivot table, I have added a column which stores a number related to the date field. (i.e,. 01 for Jan, 02 for Feb, etc) This data as well as the date field is passed to the report or pivot table for sorting purposes and then changed to a hidden field so it is not visible. The date fields then sort properly.

    I have encountered a similar problem with large WBS fields. Francois suggested a function called enlarge which worked great. (Search on the word Enlarge to see this)

    Good Luck
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    Pat, thanks. Can you give me an example of how to specify the order in the PIVOT clause? Would this be:

    PIVOT [Query].Field in ('First','Second',...,'Last')

    How do you ensure that the column headings match up with the relevant data values? In my case, these values are 1 through 12, and if I substitute arbitrary column headings, everything is blank. Thanks.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    Hans, thanks. He is using a subreport to generate the column headings from a table. I have never used a subreport, but perhaps this is a good reason to learn how. For reference, for anyone else who might have this need, the graphic below shows: (top) the final report with custom column headings, (middle) the report layout showing the subreport in a red box, (bottom left) subreport layout, and (bottom right) columns property for the subreport.
    Attached Images Attached Images

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    Thanks, Gary.

    > This data as well as the date field is passed to the report or pivot table for sorting purposes and
    > then changed to a hidden field so it is not visible. The date fields then sort properly.

    I have two ways to sort the columns properly, either by period number (1-12, ascending order) or by period ending date (descending order), but then I cannot figure out how to get the column headings to display this data in a more user-friendly form, such as "mmm-yy". If you were hinting at a solution for that next step, please spell it out in more detail. Thanks.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    Your suggested PIVOT clause id correct. (PIVOT [Query].Field in ('First','Second',...,'Last')

    The PIVOT clause in fact holds the column headings that match the data in the report. (eg. in your suggested PIVOT clause, only those that you specified will show as columns on the report).

    I had a problem once where I had to show product groups as column headings in a specified order.
    I created another table which had 2 fields, product group and a sequence number (the sequence number being used as the order to show the product groups across the report).
    I built the PIVOT clause by reading each record from the special table in sequence number order and appended the associated product group to the PIVOT clause.

    This may seem a round about way of doing it but it works.
    HTH
    Pat

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    What I have done in the past is pass both sets of data. (Sounds like an illness!!) For example the fields would be 1 and FEB-2002, 2 and APR-2002, 3 and JUL-2002, ETC. In the pivot table, sort on the first field to get the order correct and hide it. The second field will then have your dates. Of course, your queries would then need to have the correct order on the first field.

    I am not sure how else you could do this when passing data to a pivot table.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    Thanks, Pat. One more question. The Report Wizard created the column headings as labels that I need to dynamically update to match the new data each month. Were you doing that on the fly as well and, if so, how?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    PMFJI -- you've already received lots of good advice...

    Couldn't you just print your "raw" crosstab report but use dynamically-generated textboxes instead of the numbered columns for the column titles? Use a function as the recordsource for the textboxes -- make the function calculate the correct name of the month.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Dynamic headings on crosstab-based report? (2000/SR-1a)

    There's a new acronym I haven't seen before (pardon me for jumping in).

    I like this suggestion, but I'm curious about the best way to implement it.... I'm saving a highly customized report, so I'm trying to find a solution that works for all time.

    The report source (crosstab) query knows the period number (1-12), and this is the best field to make the PIVOT field because it never changes. Instead, the actual month it represents changes. Seems the best thing would be to add one date to the crosstab query (as a row heading, I guess, not bothering to make it visible on the report), and use that date to calculate the "column headings" as displayed on the report. I'll test it out.

    <img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2><img src=/w3timages/blueline.gif width=33% height=2>

    Yes, this is a good solution, even if it does require making 12 new TextBoxes. The formula for the headings is:

    =Format(DateAdd("d",-1,DateAdd("m",CInt(0-PeriodNumber),DateAdd("d",1,[CurrentPeriodClose]))),"mmm-yy")

    where PeriodNumber must be typed in manually in each TextBox (it did not find the original field by that name because after pivoting it, the unique data values become the field names). This formula is more complicated than it needs to be, because I am calculating the last date of the earlier month. For purposes of the format chosen, any day will do, but this way, if we change the format, the calculation won't need to be revisited.

    Thank you to everyone for your suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •