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

    dynamic data

    Christina Chandra

      Dear All,

       

      I have a report like this:

      Note: All lines/columns have many rows, but it is difficult to arrange and show them in good layouts, thus to avoid more confusion, I just show one row of each column

       

      =================================================

      Supplier Name: ABC

      Supplier Num: 1

       

      Site Name    Address  

      -


      SBY          Rungkut  

       

      Site Uses

      -


      Purchasing

       

      Contact Name: Andy

       

      Bank Account Name: Andy

      Curr: IDR

       

      Bank Account Name: Andy

      Curr: USD

       

      ===============================================

      I need to get the data in this format:

       

      Supplier Name, Supplier Num, Site Name, Site Uses, Contact Name, Bank Account Name, Curr

       

      ABC,1,SBY,Purchasing,Andy,Andy,IDR

      ABC,1,SBY,Purchasing,Andy,Andy,USD

       

      I have done this thing:

      - Supplier Name: Append1

      - Supplier Num: Append2

      - Site Name: Detail

      - Site Uses,Contact Name, Bank Account Name, Curr: one footer with many rows with floating traps (using preceeding string)

       

      This model works fine, but problems occured if there is more than one bank account. The model could not trap the second,third,.. banks.

       

      Problems will also occur more multiple contact name, but I still do not know how do I want to present them in the excel ;b

       

      Anyway, any help would be great..

      Thx

      Regards,

      Christina

        • dynamic data
          Christina Chandra

          Dear All,

           

          I have a report like this:

          Note: All lines/columns have many rows, but it is difficult to arrange and show them in good layouts, thus to avoid more confusion, I just show one row of each column

           

          =================================================

          Supplier Name: ABC

          Supplier Num: 1

           

          Site Name    Address  

          -


          SBY          Rungkut  

           

          Site Uses

          -


          Purchasing

           

          Contact Name: Andy

           

          Bank Account Name: Andy

          Curr: IDR

           

          Bank Account Name: Andy

          Curr: USD

           

          ===============================================

          I need to get the data in this format:

           

          Supplier Name, Supplier Num, Site Name, Site Uses, Contact Name, Bank Account Name, Curr

           

          ABC,1,SBY,Purchasing,Andy,Andy,IDR

          ABC,1,SBY,Purchasing,Andy,Andy,USD

           

          I have done this thing:

          - Supplier Name: Append1

          - Supplier Num: Append2

          - Site Name: Detail

          - Site Uses,Contact Name, Bank Account Name, Curr: one footer with many rows with floating traps (using preceeding string)

           

          This model works fine, but problems occured if there is more than one bank account. The model could not trap the second,third,.. banks.

           

          Problems will also occur more multiple contact name, but I still do not know how do I want to present them in the excel ;b

           

          Anyway, any help would be great..

          Thx

          Regards,

          Christina

          • dynamic data
            Grant Perkins

            Christina,

             

            The report structure suggests that the "normal" Monarch model would be something like:

             

             

            Detail:

             

            Bank Account Name: Andy

            Curr: IDR

             

            Append 1:

             

            Site Uses

            -


            Purchasing

             

            Contact Name: Andy

             

            (Although it may be that you need the contact separately? If Site Uses have a 1 to 1 relationship between the use and the Contact name then they should be in one append. If there can be many contacts per usage they may require separate appends but that might depend on the report structure - for example is it presented sorted by Purchase use and then Contact? If it is then the Purchase use may be constant while the Contact name changes. In that case purchase use and contact should be separate appends. However if at a change of contact the purchase use is always reported as well then they can both be in the same append.)

             

            Append 2:

             

            Site Name Address

            -


            SBY Rungkut

             

             

            Append 3:

             

            Supplier Name: ABC

            Supplier Num: 1

             

             

            Note that the order of the Appends, in naming convention or creation, is not important. But they do have a logical hierarchy within the report (or seem to from the sample).

             

            If you define the report this way and then set up a Tree View you for that structure you should see how it helps.

             

            HTH.

             

             

            Grant

            • dynamic data
              Grant Perkins

              Christina,

               

              The report structure suggests that the "normal" Monarch model would be something like:

               

               

              Detail:

               

              Bank Account Name: Andy

              Curr: IDR

               

              Append 1:

               

              Site Uses

              -


              Purchasing

               

              Contact Name: Andy

               

              (Although it may be that you need the contact separately? If Site Uses have a 1 to 1 relationship between the use and the Contact name then they should be in one append. If there can be many contacts per usage they may require separate appends but that might depend on the report structure - for example is it presented sorted by Purchase use and then Contact? If it is then the Purchase use may be constant while the Contact name changes. In that case purchase use and contact should be separate appends. However if at a change of contact the purchase use is always reported as well then they can both be in the same append.)

               

              Append 2:

               

              Site Name Address

              -


              SBY Rungkut

               

               

              Append 3:

               

              Supplier Name: ABC

              Supplier Num: 1

               

               

              Note that the order of the Appends, in naming convention or creation, is not important. But they do have a logical hierarchy within the report (or seem to from the sample).

               

              If you define the report this way and then set up a Tree View you for that structure you should see how it helps.

               

              HTH.

               

               

              Grant

              • dynamic data
                Christina Chandra

                Dear Grant,

                 

                I forget to mention that Contact Name and Bank Account does not always exists for every vendor.. Thus it can not use Detail

                 

                Thx

                Christina

                • dynamic data
                  Christina Chandra

                  Dear Grant,

                   

                  I forget to mention that Contact Name and Bank Account does not always exists for every vendor.. Thus it can not use Detail

                   

                  Thx

                  Christina

                  • dynamic data
                    Grant Perkins

                    Originally posted by Christina Chandra:

                    Dear Grant,

                     

                    I forget to mention that Contact Name and Bank Account does not always exists for every vendor.. Thus it can not use Detail

                     

                    Thx

                    Christina /b[/quote]Hi Christina,

                     

                    You have some interesting reports out there!

                     

                    This is a similar problem (but a little less complex - so far) to your other question.

                     

                    I really think you need a way to use the bank account information as the detail template. The potential for a variable number of records in that part of the report layout really only offers two approaches to a solution.

                     

                    Either you look at developing a multi step process - one step to deal with the records with account information and another step to deal with those without account information and then combine the the results of the two steps - or you look to see if there is a way to 'modify' the report so that the site records without account information still have something that can be trapped by a detail record template.

                     

                    In other words the detail trap should be based on a something that always exists at the Site Uses> Contact level but does not require any account information to be present.

                     

                    If the report included at least one occurrence of the tag(s) 

                     

                    Bank Account Name:

                    Curr:

                     

                    even if they reported no data you would not have a problem. The two fields of the record would still exist but would be empty.

                     

                    Does the report offer this sort of opportunity? Or could you change it so it does?

                     

                    HTH

                     

                    Grant

                    • dynamic data
                      Grant Perkins

                      Originally posted by Christina Chandra:

                      Dear Grant,

                       

                      I forget to mention that Contact Name and Bank Account does not always exists for every vendor.. Thus it can not use Detail

                       

                      Thx

                      Christina /b[/quote]Hi Christina,

                       

                      You have some interesting reports out there!

                       

                      This is a similar problem (but a little less complex - so far) to your other question.

                       

                      I really think you need a way to use the bank account information as the detail template. The potential for a variable number of records in that part of the report layout really only offers two approaches to a solution.

                       

                      Either you look at developing a multi step process - one step to deal with the records with account information and another step to deal with those without account information and then combine the the results of the two steps - or you look to see if there is a way to 'modify' the report so that the site records without account information still have something that can be trapped by a detail record template.

                       

                      In other words the detail trap should be based on a something that always exists at the Site Uses> Contact level but does not require any account information to be present.

                       

                      If the report included at least one occurrence of the tag(s) 

                       

                      Bank Account Name:

                      Curr:

                       

                      even if they reported no data you would not have a problem. The two fields of the record would still exist but would be empty.

                       

                      Does the report offer this sort of opportunity? Or could you change it so it does?

                       

                      HTH

                       

                      Grant

                      • dynamic data
                        Christina Chandra

                        Dear Grant,

                         

                        Site Name and Site Uses always exists for every vendor but contact name and bank account does not.

                        This supplier report, unfortunately, is a built-up Oracle reports, thus we are encouraged to not make any changes on it unless it is really necessary.

                        In other words, I would make it (change the report) as a last effort if there is no other ways.

                        Is it really no other ways to get the data without changing the report?

                        Pls advice..

                         

                         

                        Thx,

                        Christina

                        • dynamic data
                          Christina Chandra

                          Dear Grant,

                           

                          Site Name and Site Uses always exists for every vendor but contact name and bank account does not.

                          This supplier report, unfortunately, is a built-up Oracle reports, thus we are encouraged to not make any changes on it unless it is really necessary.

                          In other words, I would make it (change the report) as a last effort if there is no other ways.

                          Is it really no other ways to get the data without changing the report?

                          Pls advice..

                           

                           

                          Thx,

                          Christina

                          • dynamic data
                            Grant Perkins

                            Originally posted by Christina Chandra:

                            Is it really no other ways to get the data without changing the report?

                            Pls advice..

                            /b[/quote]Christina,

                             

                            This is like your other problem, but a little simpler as we don't have the challenge of 2 columns and wrapped text to deal with.

                             

                            The difficulty is simply that you don't know how many, if any, account information records there may be.

                             

                            From your sample information one possiblity is that you set the Site information as you detail. On the line below the Contact Name, assuming there is always at least a blank line, you define a field wide enough to pick up any text in the 'accounts' area before the next record. Make it a multi-line field with "End on" set to "None of the above". It may need to be a MEMO field type if there can be a lot of accounts.

                             

                            That will give you a block of text which will range from empty to any number of account information lines. If having the account information as a single text field is OK for you them the job is finished. If you need to split the account out into separate fields that can be done quite easily (in the case of the sample report you posted) EXCEPT THAT you may not know how many fields you need for the largest number of accounts appearing on a report. If it might be 100 account for one customer, with each account requireing 2 fields, your output would be very large for not very much purpose for 99.99% of the records.

                             

                            If there were never more than, say, 2 accounts the effort required to cut the field up into separate fields would be very small.

                             

                            So yes, there are alternatives, but they depend on some decisions related to unknown information from the reports (such as how many accounts the longest record contains) and also how you need to present the information once it is extracted. (If a block of text field listing the account number and currencies would be OK then the multi-line field concept would probably work just fine.)

                             

                            Does this help?

                             

                             

                            Grant

                            • dynamic data
                              Grant Perkins

                              Originally posted by Christina Chandra:

                              Is it really no other ways to get the data without changing the report?

                              Pls advice..

                              /b[/quote]Christina,

                               

                              This is like your other problem, but a little simpler as we don't have the challenge of 2 columns and wrapped text to deal with.

                               

                              The difficulty is simply that you don't know how many, if any, account information records there may be.

                               

                              From your sample information one possiblity is that you set the Site information as you detail. On the line below the Contact Name, assuming there is always at least a blank line, you define a field wide enough to pick up any text in the 'accounts' area before the next record. Make it a multi-line field with "End on" set to "None of the above". It may need to be a MEMO field type if there can be a lot of accounts.

                               

                              That will give you a block of text which will range from empty to any number of account information lines. If having the account information as a single text field is OK for you them the job is finished. If you need to split the account out into separate fields that can be done quite easily (in the case of the sample report you posted) EXCEPT THAT you may not know how many fields you need for the largest number of accounts appearing on a report. If it might be 100 account for one customer, with each account requireing 2 fields, your output would be very large for not very much purpose for 99.99% of the records.

                               

                              If there were never more than, say, 2 accounts the effort required to cut the field up into separate fields would be very small.

                               

                              So yes, there are alternatives, but they depend on some decisions related to unknown information from the reports (such as how many accounts the longest record contains) and also how you need to present the information once it is extracted. (If a block of text field listing the account number and currencies would be OK then the multi-line field concept would probably work just fine.)

                               

                              Does this help?

                               

                               

                              Grant

                              • dynamic data
                                Christina Chandra

                                Dear Grant,

                                 

                                Just to let you know, we decide to make a new report due to the problem below.

                                 

                                We could not make Contact Name as detail since we need the detail template for Site. Site needs to be in detail since one Supplier can have many Sites. So if we make the Site as append, then the result would be incorrect.

                                 

                                In addition, the user require additional field which currently does not exist in the report.

                                 

                                Though, thanks a lot for your time and information

                                 

                                By the way, do you have any update for me about my previous topic: Multiple Footers?

                                 

                                Appreciate your help a lot..

                                 

                                Christina

                                • dynamic data
                                  Christina Chandra

                                  Dear Grant,

                                   

                                  Just to let you know, we decide to make a new report due to the problem below.

                                   

                                  We could not make Contact Name as detail since we need the detail template for Site. Site needs to be in detail since one Supplier can have many Sites. So if we make the Site as append, then the result would be incorrect.

                                   

                                  In addition, the user require additional field which currently does not exist in the report.

                                   

                                  Though, thanks a lot for your time and information

                                   

                                  By the way, do you have any update for me about my previous topic: Multiple Footers?

                                   

                                  Appreciate your help a lot..

                                   

                                  Christina

                                  • dynamic data
                                    Grant Perkins

                                    Originally posted by Christina Chandra:

                                    Dear Grant,

                                     

                                    Just to let you know, we decide to make a new report due to the problem below.

                                    ....

                                     

                                    In addition, the user require additional field which currently does not exist in the report.[/b][/quote]That is always a good reason to consider creating a new report, especially in this case.

                                     

                                    However, if it had been easy to extract from your original report you might have been able to consider other ways to add the extra information the user needed linking to it from another report for example.

                                     

                                    [b]By the way, do you have any update for me about my previous topic: Multiple Footers?

                                     

                                    /b[/quote]Nothing to add since my email of 15th December outlining the problem points and asking one or two questions.

                                     

                                    Since there have been some email delivery problems it is possible you did not receive that mail from me or I did not receive your reply. I cannot see anything from you in my incoming mail folder since 15th December.

                                     

                                    Let me know if you do not have my mail from 2006/12/15.

                                     

                                    Grant

                                    • dynamic data
                                      Grant Perkins

                                      Originally posted by Christina Chandra:

                                      Dear Grant,

                                       

                                      Just to let you know, we decide to make a new report due to the problem below.

                                      ....

                                       

                                      In addition, the user require additional field which currently does not exist in the report.[/b][/quote]That is always a good reason to consider creating a new report, especially in this case.

                                       

                                      However, if it had been easy to extract from your original report you might have been able to consider other ways to add the extra information the user needed linking to it from another report for example.

                                       

                                      [b]By the way, do you have any update for me about my previous topic: Multiple Footers?

                                       

                                      /b[/quote]Nothing to add since my email of 15th December outlining the problem points and asking one or two questions.

                                       

                                      Since there have been some email delivery problems it is possible you did not receive that mail from me or I did not receive your reply. I cannot see anything from you in my incoming mail folder since 15th December.

                                       

                                      Let me know if you do not have my mail from 2006/12/15.

                                       

                                      Grant