6 Replies Latest reply: May 15, 2014 10:10 AM by Eric Petersen RSS

    Need to add a field to group on based on a counter

    Eric Petersen

      I can't quite figure out if I can do what I need to do.  Basically I want to add a 'groupnumber' to the output but I need to use a counter like this:

       

      groupnumber=0

      if (field2="=",groupnumber+1,groupnumber)

       

      So it evaluates on each line of data, giving a 0 until it encounters a line where the field2 has an "=" then it gives a 1 until the next line that has an "=".  In this case field2 is the 2nd character of each line.

       

      My data looks like this (the actual report is fixed length fields):

       

      [FONT="Courier New"][SIZE="1"]0  AAAAAAA              AAAAA             01/01/2011  M  54-47-CASE001-0-05  000-00-0001 6      MPEN AAAAAAA,NNNNNNN    

      +___________________________________________________________________________________________________

       

      • AAAAAAA              BBBBBB            01/02/2011  F  54-8E-CASE-01-029D  000-00-0002 W 385  HC20 AAAAAAA, NNNNNNN   

       

      • AAAAAAA              RRRRRR            01/03/2011  F  54-IE-CASE001-0-03  000-00-0003 W 717  P210 AAAAAAA,NNNNNNN    

       

      • AAAAAAA              NNNNNNN           01/04/2011  F  54-IE-CASE001-0-01  000-00-0004 A      P210 AAAAAAA,NNNNNNN    

       

      • AAAAAAA              UUUUU             01/05/2011  M  54-IE-CASE001-0-02  000-00-0005 A 717  P210 AAAAAAA,NNNNNNN    

      0  AAAAAAA              AAAAA           A 01/01/2011  M  54-8E-CASE-02-950F  000-00-0006 9 835  JK19 AAAAAAA, NNNNNNN   

      +___________________________________________________________________________________________________

      0===================================================================================================

      0  ACCCCC               JJJJJJJ           01/06/2011  F  54-30-CASE002-0-02  000-00-0007 A      V248 PPPPPPPP,AAAAAAAA  

      +___________________________________________________________________________________________________

       

      • PPPPPPPP             AAAAAAAA        L 01/07/2011  F  54-30-CASE002-0-01  000-00-0008 W      V248 PPPPPPPP,AAAAAAAA  

      0  ACCCCC               JJJJJJJ         M 01/06/2011  F  54-8U-CASE-03-818E  000-00-0009 9      V264                    

      +___________________________________________________________________________________________________

      0===================================================================================================

      0  ACCCCC               MMMMM             01/08/2011  M  54-47-CASE003-0-04  000-00-0010 7      MPEN PPPPP,EEEEEEEEEE   

      +___________________________________________________________________________________________________

       

      • MMMM                 NNNNNN          A 01/09/2011  M  54-37-CASE003-4-02  000-00-0011 W      MPEN PPPPP,EEEEEEEEEE   

       

      • MMMM                 SSSSSSSS          01/10/2011  M  54-37-CASE003-5-03  000-00-0012 W      MPEN PPPPP,EEEEEEEEEE   

       

      • PPPPP                EEEEEEEEEE        01/11/2011  F  54-37-CASE003-6-01  000-00-0013 W      MPEN PPPPP,EEEEEEEEEE   

      0  ACCCCC               MMMMM           E 01/08/2011  M  54-8E-CASE-04-550F  000-00-0014 7 043  CL20 PPPPP, EEEEEEEEEE  

      +___________________________________________________________________________________________________

      0===================================================================================================

      0  AGGGGGG              CCCCCCCCC       A 01/12/2011  F  54-30-CASE004-0-01  000-00-0015 W      D218 AAAAAAA,CCCCCCCCC  

      +___________________________________________________________________________________________________

       

      • GGGGGGGGG            IIIII           A 01/13/2011  M  54-30-CASE004-0-02  000-00-0016 A      D218 AAAAAAA,CCCCCCCCC  

       

      • GGGGGGGGG            RRRR              01/14/2011  M  54-3N-CASE004-3-04  000-00-0017 A      D218 AAAAAAA,CCCCCCCCC  

      0  AGGGGGG              CCCCCCCCC       A 01/12/2011  F  54-60-CASE-05-1873  000-00-0018 A                              

      +___________________________________________________________________________________________________

      0===================================================================================================

      /SIZE[/FONT]

       

      The report is such that the 1st person listed is a 'key person' and the lines of data that follow are linked to them until the double dashed line.  I've given up trying to associate the lines below the key person with the key person but if I can add a 'group number' to all the lines and increment it by one everytime it encounters the double dashed line I can us another field "RecNo()" to help output the data in Crystal reports the correct way.

       

      Any suggestions on creating a counter?

       

      Thanks in advance,

       

      Eric 'the Monarch nube' Petersen

        • Need to add a field to group on based on a counter
          Data Kruncher

          Hi Eric. Welcome to the forum.

           

          Every once in a while you new friends arrive here with a nasty first problem, and, lucky you, this looks like one of them at first glance.

           

          The answer to the first question is "No", I don't have a counter solution at this time. But instead I think that I've figured out how you can build templates to get what you need from your sample without a counter system. Here's how I went about it...

           

          For the (single line sample) detail template, I trapped on the date field. Nothing fancy, just ÑÑ/ÑÑ/ÑÑÑÑ.

           

          For the append template (to tack on the key person data), I started with a two line sample. For the trap line, I used only 0= in the first position to match the sample's first line. Then I painted the fields in the second line of the sample area and saved the append template as Key Person Data for the template name.

           

          Now it looks a bit odd in the report window because it may look like all of the fields are captured with the detail template (provided you have different colors for the different template types, and that you've painted the fields the same for the append and detail templates). But have a look at the table, and you'll see that the key person data has in fact been appended to the various detail records.

           

          The additional oddity or hurdle is that the append data was itself captured as a detail record. I couldn't find a way to avoid that when defining the template, but that's OK.

           

          We can use a filter in the Table window to eliminate all records which have the detail person name equal to the appended Key Person Name. Set your filter expression similar to:

          [Detail Name]<>KeyName /CODEBased on your sample, this gave me a list of 14 records. The first six have empty values for the key person information as that part wasn't included in your sample.

           

          Does that help to get you closer to what you wanted to do with this sample?

           

          Kruncher

            • Need to add a field to group on based on a counter
              Eric Petersen

              Kruncher, thanks for the quick response.

               

              I did set up the templates as you suggested but I obviously did not give enough sample data to you.  The 'blank' key person is due to the first group of data not having the double dashed lines above the key person at the beginning of the report.  Also, on the actual data file the order of the data changes sometimes.  It might help if I could send/attach the 3 pages of sample data I created based on the original report that contains examples of the various ways the data is presented.  There's literally no way to uniquely distinguish the Key Person line of data from the next appearance of the Key Person with slightly different County or MEDSID information. 

               

              Here's some scenarios:

               

              Scenario 1

              Key person with case A data

              Person1 related as case A member

              Person2 related as case A member

              Key person with case B data (this needs to be treated as linked data to the key person just like the casemembers - but it's coded on the report just like key person data line)

               

              Scenario 2

              Key person data on case A

              Key person data on case B

              Person1 related as case B member

               

              Scenario 3

              Key person data on case A

              Key person data on case B

              No additional casemember data.

               

              The more I look at it the more I think I'm going to have to just pull every line of data under the headers, use a RecNo() calculated field and export to a csv.  Then I can open it in Excel, add a column with the formula =IF(E2="=",S1+1,S1)  where E2 is the field that shows the 2nd character in each row of data.  There's a few additional steps I'll have to take manually before I can upload the csv file to a database table to use in Crystal Reports.  The end result will be a column I can group on and I can use the RecNo column to sort within the group.

               

              My task is to convert a 'print ready' text file to a Crystal Report document that can be audited in Business Objects Enterprise.  The other 7 reports were simple in Monarch, but this one just has some odd logic behind it to create the print ready version.

                • Need to add a field to group on based on a counter
                  Olly Bond

                  Hello,

                   

                  I think the simplest counter would be Page(), once you've defined the lines with ==== as page breaks in a Page Header template. This will work as long as the maximum number of lines on each page won't ever exceed 254, which is Monarch's limit.

                   

                  Hope this helps,

                   

                  Olly

                    • Need to add a field to group on based on a counter
                      Eric Petersen

                      I think the simplest counter would be Page(), once you've defined the lines with ==== as page breaks in a Page Header template. This will work as long as the maximum number of lines on each page won't ever exceed 254, which is Monarch's limit.

                      /QUOTE

                       

                      Olly!  You did it!  That does exactly what I needed it to do!  I so appreciate your assistance with this problem...it's been vexing me for weeks.  I had resigned myself to do a manual process of loading the line by line data into Excel and adding a formula to act as the counter.  This will help me automate the whole thing.

                       

                      Now if only I could figure out how to get the output columns to line up in the order I need...

                       

                      Thanks much!

                       

                      Eric P

                        • Need to add a field to group on based on a counter
                          Olly Bond

                          Hello Eric,

                           

                          Thanks for the feedback! Lining up output columns should be easy - you can move these around in the Field List (in the Edit menu), just remember to click on "set Display Order" to save your changes.

                           

                          Best wishes,

                           

                          Olly

                          • Need to add a field to group on based on a counter
                            Eric Petersen

                              That does exactly what I needed it to do!  [/QUOTE]

                             

                            I spoke too soon.  It grabbed all but one record.  It appears that the line count per page for my report is 101.  I have one 'group' that has 102 lines of data (including actual page header data) before the next ==== line.  There's a 'hard page break' right before the last record of the 'group' and then a 'templated' page break after that 102nd line of data.  Result is that the 102nd line of data record doesn't make it into the group output data and the page() count skips a number.  It shows it is a selected record, but it does not appear in the output data.

                            I tried modifying the margins in the 'Page Setup' but it doesn't affect the 101 line limitation.

                             

                            Sooo close.

                             

                            Thanks for the tip on moving the output columns around.

                             

                            edited @ 2:54pm - I found the setting to increase the hard page break to 256 like you mentioned!  Now it's perfect again!  Helps to really read all the documentation in the help file sometimes.

                             

                            Eric