5 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Capturing Period Titles

    Data Kruncher

      Hello all.

       

      Using v502, and I'm stumped. I've looked through the KnowledgeBase a bit without success.

       

      I'm trying to devise an approach to capture the period titles in a report with a layout like this:

      [font="courier"]             Sep-02    Oct-02   YTD

      Category Heading 1

      00001 Title1     99        99    999

      00002 Title2     99        99    999

      Category 1      999       999   9999

       

      Category Heading 2

      00003 Title3     99        99    999

      00004 Title4     99        99    999

      Category 2      999       999   9999 /font[/quote]The 0000 lines are detail lines, category headings are appends, and the 99 values are fields I'm naming Period1 through Period12.

       

      Ideally, I'd like to change the name of the fields "Period1" to "Sep-02", "Period2" to "Oct-02", etc. dynamically, based on the content of the report (nice for running different years, or different fiscal calendars). Might be a dream. If it is just that, do you have any other ideas on how to get this in a usable form for the end user's Excel data?

       

      I'm not keen on trapping the period titles as page header fields, which would add something like "Pd1Name", "Pd2Name", etc. to each data row.

       

      In the end, am I stuck with Period1 to Perod12?

       

      Thanks,

      Sandy

        • Capturing Period Titles
          Grant Perkins

          Hi Sandy,

           

          We had a similar requirement posted a few months ago.  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000096"]Here[/url] is the discussion that ensued.

           

          It looks pretty much the same as your requirement as far as I can tell so it may help. It would be interesting to know if it does.

           

          Best regards,

           

          Grant

           

           

            Originally posted by Data Kruncher:

          Hello all.

           

          Using v502, and I'm stumped. I've looked through the KnowledgeBase a bit without success.

           

          I'm trying to devise an approach to capture the period titles in a report with a layout like this:

          [font="courier"]             Sep-02    Oct-02   YTD

          Category Heading 1

          00001 Title1     99        99    999

          00002 Title2     99        99    999

          Category 1      999       999   9999

           

          Category Heading 2

          00003 Title3     99        99    999

          00004 Title4     99        99    999

          Category 2      999       999   9999 /font[/quote]The 0000 lines are detail lines, category headings are appends, and the 99 values are fields I'm naming Period1 through Period12.

           

          Ideally, I'd like to change the name of the fields "Period1" to "Sep-02", "Period2" to "Oct-02", etc. dynamically, based on the content of the report (nice for running different years, or different fiscal calendars). Might be a dream. If it is just that, do you have any other ideas on how to get this in a usable form for the end user's Excel data?

           

          I'm not keen on trapping the period titles as page header fields, which would add something like "Pd1Name", "Pd2Name", etc. to each data row.

           

          In the end, am I stuck with Period1 to Perod12?

           

          Thanks,

          Sandy /b[/quote]

           

          [size="1"][ May 19, 2006, 11:50 AM: Message edited by: Todd Niemi ][/size]

          • Capturing Period Titles
            Data Kruncher

            Thanks for the info Grant.

             

            As described, this should be an excellent solution. I was able to add the period titles to the detail trap. The problem I encounter now is in how to limit the headings extracted to only one row, which I could use as column headings instead of the field names.

             

            A possible solution was to trap the page number printed in the header and then filter to only the headings on page 1, but I can't do this because (all you programmers/report designers pay attention) the report is run for multiple departments and the page number resets for each department. The more departments included, the more page 1's there are!

             

            Unfortunately, having spent much time on this, I'm back were I started. Period1 through Period12 works and I am stuck with it, but it's not complete, and it's certainly not very elegant. Oh well.

             

            Anyway, thanks again Grant for your continued assistance to the forum.

             

            Best regards,

            Sandy

             

              Originally posted by Grant Perkins:

            Hi Sandy,

             

            We had a similar requirement posted a few months ago.  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000096"]Here[/url] is the discussion that ensued.

             

            It looks pretty much the same as your requirement as far as I can tell so it may help. It would be interesting to know if it does.

             

            /b[/quote]

             

            [size="1"][ May 19, 2006, 11:50 AM: Message edited by: Todd Niemi ][/size]

            • Capturing Period Titles
              Grant Perkins

              Sandy,

               

              I wondered if you could overcome the repeating page 1 by combining with the the department data somehow (difficult to suggest what as I don't know what the code format may be) and then just picking the lowest 'number' from whatever you get out.

               

              The other idea that Mike had in the original post was to do something with the data row number (the first header would presumably be the lowest record number.

               

              Alternatively if you use the Page() function to add the page number of the report (as Monarch sees it rather than as the programmer opted to print) as a calculated field you may be able to do something with that. I assume that you probably would not need the Line() function as well - unless you end up with 2 'header' lines on the same page.

               

              There may be something in that to experiment with.

               

              Grant

               

                 Originally posted by Data Kruncher:

              Thanks for the info Grant.

               

              As described, this should be an excellent solution. I was able to add the period titles to the detail trap. The problem I encounter now is in how to limit the headings extracted to only one row, which I could use as column headings instead of the field names.

               

              A possible solution was to trap the page number printed in the header and then filter to only the headings on page 1, but I can't do this because (all you programmers/report designers pay attention) the report is run for multiple departments and the page number resets for each department. The more departments included, the more page 1's there are!

               

              Unfortunately, having spent much time on this, I'm back were I started. Period1 through Period12 works and I am stuck with it, but it's not complete, and it's certainly not very elegant. Oh well.

               

              Anyway, thanks again Grant for your continued assistance to the forum.

               

              Best regards,

              Sandy

               

                  /size[quote]quote:[/size]Originally posted by Grant Perkins:

              Hi Sandy,

               

              We had a similar requirement posted a few months ago.  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000096"]Here[/url] is the discussion that ensued.

               

              It looks pretty much the same as your requirement as far as I can tell so it may help. It would be interesting to know if it does.

               

              /b[/quote][/b][/size][/QUOTE]

               

              [size="1"][ May 19, 2006, 11:51 AM: Message edited by: Todd Niemi ][/size]

              • Capturing Period Titles
                Data Kruncher

                Page() function works! Filtered with a calculated field which uses the Page() function. Turned off the option which uses field names as the first row of output, and voila!

                 

                I had to develop a calculated field for each corresponding "regular" field (AcctNum becomes CalcAcctNum) so that the first data row can show the proper Field name or the data value as necessary. That is to say that my new "dynamic" titles are correct, but other field values in the title row still held other values generated by the append and page templates.

                 

                If there is a dash in the value of the Period1 field this is the title row, so plug in a field name, otherwise duplicate the original value. After hiding the original fields, and only showing the calculated fields, we've achieved the desired result: Dynamic titles in row 1, followed by a correct data set.

                 

                Well done Grant!   [img]smile.gif[/img] 

                Thanks,

                Sandy

                • Capturing Period Titles
                  Grant Perkins

                  Excellent news - and thanks for providing the details. It's nice when a theory works. Sometimes the reports fight back and invalidate the idea!

                   

                  Grant

                   

                  [size="1"][ June 06, 2003, 06:47 PM: Message edited by: Grant Perkins ][/size]