Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Need: Report Writing Guru - Have to produce a Top 50 report

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Need: Report Writing Guru - Have to produce a Top 50 report

    Greetings,

    I need a "Report Writing Guru" !

    I have a Client File that contains over 1.2 million records. These are an extract of an A/R System that contains such things as the System_No, Divison_No, Acct_No, Billing_Name, some Classification_Codes, Totals, Aging Buckets (Current, Over30, Over60, etc.) and a Grouping Code. I need to determine the Top 50 consolidated accounts (By Grouping Code) ! !

    If this were under 65,000 lines (with subtotal lines...), in Excel I could sort, by the Grouping Code and then do a Sub_total on the Grouping Code and sum the buckets and the Total column.

    Then by collapsing the detail levels to Sub-Totals only (by clicking on the "2" at the top left of the Spreadsheet area) I could do a Descending Sort on the Totals Column (which results in a Paragraph Sort) so that once I clicked on the "3" again (so all detail could be seen) I would have the accounts grouped in Descending Dollar sequence with the respective detail right with the Descending Totals.

    Now, is there any way to accomplish this in Alpha5 without writing summary records, and then using the summary records to write the 1.2+ million records to another file, already in Descending Group Sequence so that I could produce a Top 50 report.

    I have banged my head for a couple of hours today on this and it feels so much better at the moment while I am not smaching my head... can anybody help here?

    Regards,
    Keith
    Keith Weatherhead
    Discus Data, Ltd
    [email protected]

    #2
    Keith,

    There are several ways to accomplish this, but it depends on a couple of things. I'm not sure what determines the top 50, but I am assuming that the group with the most records with that group # is #1.

    Create an index by grouping code and unique, and one with group code and not unique. Then use Table count() in a summarize operation on the unique index making sure you have LQO as you go through the unique indexes record. Sort the count and copy the 1st 50 groups which are the desired groups. Now copy those records with the same group # or whatever.

    This can probably done without Xbasic, but it actually would be more involved, I suspect.
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture


    Comment


      #3
      Originally posted by csda1
      Keith,

      There are several ways to accomplish this, but it depends on a couple of things. I'm not sure what determines the top 50, but I am assuming that the group with the most records with that group # is #1.
      Nope, actually Open A/R Balance is what determines the ranking order and it could be 1 account with $20.0 million dollars followed by 50 accounts with a total of $19.8 million, followed by 3 accounts with a total of $19.2 million followed by 125 accounts in a group totaling $19.0 million, and so on. Groupings could be like all Sams and WalMarts (which share the same Corporate Ownership) or all E.I.DuPont accounts as well as one huge company and others might be 3M, General Electric, and General Motors, etc. and their related entities. That is what the Grouping Code allows for. The consolidating of related entities so that we can determine the total Open A/R by Consolidated Obligor (e.g. Corporate Parent).

      Regards,
      Keith
      Keith Weatherhead
      Discus Data, Ltd
      [email protected]

      Comment


        #4
        Originally posted by csda1
        Keith,

        Create an index by grouping code and unique, and one with group code and not unique. Then use Table count() in a summarize operation on the unique index making sure you have LQO as you go through the unique indexes record. Sort the count and copy the 1st 50 groups which are the desired groups. Now copy those records with the same group # or whatever.
        I guess I'm not clear on the two (2) indexes. I presume I could do a Summary operation on the Total field, subtotaling, but I'm not clear how to then sort those subtotalled amounts without generating intermediate files for each step... is that what you are indicating needs to be done here?

        Regards,
        Keith
        Keith Weatherhead
        Discus Data, Ltd
        [email protected]

        Comment


          #5
          Keith,

          1 index includes only unique keys (Group #), so there is 1 and only 1 record per group. This index is used to summarize (could be the summary operation) the totals for each key (in other words group). The tablecount would not be used, but a tablesum() of the number you want to sum up in each group using the non-unique index (which has all records of the group for all groups). Sort the summary table, take the 1st 50 records and link the group to the group info in a set as needed for a report
          Regards,

          Ira J. Perlow
          Computer Systems Design


          CSDA A5 Products
          New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
          CSDA Barcode Functions

          CSDA Code Utility
          CSDA Screen Capture


          Comment


            #6
            Thanx, Ira

            While it is still a little foggy, I'm going to try to duplicate your suggestions and see if I can get the results I need. Thanx for the suggestions !

            Regards,
            Keith
            Keith Weatherhead
            Discus Data, Ltd
            [email protected]

            Comment


              #7
              Other ways to do this...

              Originally posted by csda1
              Keith,

              There are several ways to accomplish this, but it depends on a couple of things.
              .
              .
              .
              This can probably done without Xbasic, but it actually would be more involved, I suspect.
              This is in no way a slight to Ira and his suggestions... ! ! !

              While Ira suggested a possible solution, that I am still playing with this morning, I am coureous about additional methods that could be used to accomplish what I am trying to do... see other of my posts (within this thread) for enough of an explanation to be clear what I am trying to do, please do not hesitate to ask if you think I am missing something...

              After 20 years of working with a non-OOP tool my thinking is certainly in a different mode than A5's and coupled with a day here and a day there, things are progressing much slower than I would wish for and that is mainly due to my time availability to be able to learn it quicker, not A5's inabilities.

              That said of all the areas I have played with, the Report Writer is my least explored and at the moment could be my biggest friend and time saver. Isn't that the way it always goes, huh?

              If anybody else has another method or suggestion I would be most grateful for the help, lessons, kink-in-the-pants, etc. and it would certainly help me past the current hump.

              Regards,
              Keith
              Keith Weatherhead
              Discus Data, Ltd
              [email protected]

              Comment


                #8
                Keith,

                What information do you want to include in the actual report, once the top 50 consolidated "groups" have been identified?

                -- tom

                Comment


                  #9
                  Information to include...

                  Originally posted by Tom Cone Jr
                  Keith,

                  What information do you want to include in the actual report, once the top 50 consolidated "groups" have been identified?

                  -- tom
                  Tom,

                  Sorry for the delay, this is why it is taking so long to get a handle on A5, I keep getting pre-empted for something that is always hotter...

                  I need to produce 6 reports in total.

                  -There is a division field that contains either of two (2) values for which division that A/R data belongs to.

                  -I need to produce a Summary version, e.g. one line per Consolidated Obligor.
                  -I need to also create a Detail version that will have the totals for all buckets, like the Summary version, but will also include (list) all the detail accounts that are included in that total.

                  -I have to produce the reports (Summary & Detail) for Divisions 1 & 2 and a Consolidated set, yielding the 6 versions of the reports.

                  Layout wise they are identical except which data is presented and the title on the report identifying the data set. What I have done so far and I am VERY close although I do not know if there is a better or easier way.

                  Based off of Ira's suggestions, I have...

                  -Built an index on all items in (Group Code, Sys-Code, District, Account) order, in the A/R master item file.
                  -Built an index with only unique combinations of the Group Code.

                  -Using a Summary Operation I created a file that has the Unique Accounts with all of the required fields totaled and a number of accounts included in that group, for all data.

                  -Using Summary Operation and a Query, built totals for Division 1 Only and another set for Division 2 only.

                  -Created set(s) using these Summary files as the Parents linking back to the all inclusive total A/R table.

                  -Created a Detail version of the report (or each of the three sets) that does a Descending sort on Total A/R Balance and picks the Top 50 records (greatest A/R Balances, and lists those records with the accompanying detail line items.

                  Need to collapse those Detail reports to a Summary version, hiding the details from printing...

                  I had to print a Top 35 report and it produced 320+ pages, the Top 35 for each of the Division created over 150+ pages each... and I never got to printing the Top 50 as I felt I had already done my share of forest cutting for the month ! ! !

                  So, basically I have gotten to almost the very end, BUT I don't know, from an A5 point of view, if this is the short, quick and easy way or whether I just took a 2,000 mile scenic tour???

                  Regards,
                  Keith
                  Keith Weatherhead
                  Discus Data, Ltd
                  [email protected]

                  Comment


                    #10
                    Running Count on Group Total

                    *** NOTE ***

                    This question was moved to a seperate thread titled:
                    "Running Count on a Group Break?"

                    Thanx,
                    Keith
                    -----

                    Disregard the text below... -kfw


                    Another question...

                    Can you do a "running_count" on Group Breaks?

                    I want place a Rank Ordering Number on each Group Break and I always seem to end up with a Rank of "1" on each Group Break.

                    If my Group Break was "My_Group", what would the proper expression be:

                    running_count( ... , ... , ...)

                    Thanx,
                    Keith
                    Last edited by KeithW; 04-12-2006, 06:11 PM.
                    Keith Weatherhead
                    Discus Data, Ltd
                    [email protected]

                    Comment

                    Working...
                    X