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

Report repeating values

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

    Report repeating values

    I'm making a sales tax paid report. I want the report to show the different tax rates and the total for each rate.

    The tax rates are stored in 'SalesTax' field and the dollar amount is in 'SalesTaxTotal'.

    I can't figure out the calculated field calculation to show just once for example 5.5% and then the total.

    Like this:

    Tax Rate Total
    5.5% $250
    5.6% $693

    Those numbers are fake, but that's the report I'm looking for. I can get them to repeat once for each record in the Detail view, but I just want it to show up once with the total for that specific rate.

    Can someone help with the calculated fields?

    #2
    Re: Report repeating values

    Is this a standard Alpha report on a dbf?

    If so you can create a group on the salestax field. Eliminate the detail section. Drop the salestax field into the group footer, specify first. Drop the salestaxtotal into the group footer and specify total.

    Alpha will write the expressions for you.
    There can be only one.

    Comment


      #3
      Re: Report repeating values

      It's an SQL report on a web app.

      Comment


        #4
        Re: Report repeating values

        if your data is
        Code:
        sales_tax_rate          sales_tax_total
        5.0                           100.00
        5.5                            20.00
        6.0                           200.00
        5.0                            13.00
        5.5                           213.00
        6.0                            12.00
        then the report should be based on
        Code:
        SELECT sales_tax_rate, sum(sales_tax_total) as totalTax
        FROM your_table_name
        GROUP BY sales_tax_rate;
        should give you the way you want.

        this is for mySql, if MS SQL i am sure the format may be the same.
        Last edited by GGandhi; 11-04-2016, 05:43 AM.
        thanks for reading

        gandhi

        version 11 3381 - 4096
        mysql backend
        http://www.alphawebprogramming.blogspot.com
        [email protected]
        Skype:[email protected]
        1 914 924 5171

        Comment


          #5
          Re: Report repeating values

          Thanks Gandhi. I can work with that.

          Comment


            #6
            Re: Report repeating values

            Now next issue.

            I'm taking data from 3 different tables. OrderItems, Tax, and OrderInfo. OrderItems contains the dollar amount of the tax and the tax name. Tax contains the state registered tax number. OrderInfo contains the date of the order.

            Eveything works fine until I add in the PODATE field from OrderInfo. Then it does the attached screen shot. It no longer groups by SalesTax. It adds a line for each PODATE. I'm adding the PODATE so I can do range searches.

            Here's the SQL Statement.

            Code:
            SELECT OrderItems.SalesTaxName, OrderItems.SalesTax,  Sum(OrderItems.SalesTaxTotal)  AS totalTax, OrderItems.User_Company_ID AS User_Company_ID, Tax.TaxNumber, OrderInfo.PODATE 
            FROM (OrderItems OrderItems
            	 INNER JOIN Tax Tax
            		 ON  OrderItems.SalesTaxName = Tax.TaxName  AND  OrderItems.User_Company_ID = Tax.User_Company_ID 
            	 INNER JOIN OrderInfo OrderInfo
            		 ON  OrderItems.User_Company_ID = OrderInfo.User_Company_ID  AND  OrderItems.ORDERINFO_ID = OrderInfo.ORDERINFO_ID )  
            GROUP BY OrderInfo.PODATE, Tax.TaxNumber, OrderItems.SalesTaxName, OrderItems.User_Company_ID, OrderItems.SalesTax
            This one is with the PODATE added.
            Screen Shot 2016-11-04 at 2.32.07 PM.png

            This is how I want it to appear. Only list the name and salestaxrate once and the sum of the Totaltax field. But want to be able to filter by date range, thus adding the PODATE.
            Screen Shot 2016-11-04 at 2.36.41 PM.png

            So the question is, how do I modify the SQL Statement so the PODATE is not inserted, thus screwing up my search results?

            Comment


              #7
              Re: Report repeating values

              GROUP BY OrderInfo.PODATE, Tax.TaxNumber, OrderItems.SalesTaxName, OrderItems.User_Company_ID, OrderItems.SalesTax
              can you skip OrderInfo.PODATE here? or you need it for range check?
              thanks for reading

              gandhi

              version 11 3381 - 4096
              mysql backend
              http://www.alphawebprogramming.blogspot.com
              [email protected]
              Skype:[email protected]
              1 914 924 5171

              Comment


                #8
                Re: Report repeating values

                You are grouping by PODate, so it's going to group by PODate. use

                Max(PODate) as PODate

                in the sql statement and take it out of the group.

                Comment


                  #9
                  Re: Report repeating values

                  I need the PODATE for the range check.
                  Yes, it works using Max(PODate) as PODate, but I can't do a range search now.

                  Comment


                    #10
                    Re: Report repeating values

                    I have a UX component with the StartDate and EndDate with a button. The button shows the report using the range search and the PODate as the field for that search. So it needs to add up the SalesTaxTotal for the date range and show.

                    Comment


                      #11
                      Re: Report repeating values

                      Or, can I include everything in the SQL statement and then figure this out on the actual report?

                      Comment


                        #12
                        Re: Report repeating values

                        Then I suggest the following instead.

                        Create a view out of your original query that groups by PO date. Then do your select statement based on that view, and group by everything except PO date.

                        The view will give you totals by PO date, then you can aggregate those totals in a larger view. Note: I don't know your data, you might need different viewing levels, but that is what you would do.

                        Create a view that gets you the searchable query, then search that query and group as necessary.

                        Does that help?

                        Comment


                          #13
                          Re: Report repeating values

                          Originally posted by dfricke10 View Post
                          I have a UX component with the StartDate and EndDate with a button. The button shows the report using the range search and the PODate as the field for that search. So it needs to add up the SalesTaxTotal for the date range and show.
                          here i don't understand. you have date fields and the sqlStatment need not have the dates as Group By clause for the date.
                          design the report add arguments in the report then bind the values of the argument in the sqlReport. that should only fetch the data for that dates inclusive.
                          thanks for reading

                          gandhi

                          version 11 3381 - 4096
                          mysql backend
                          http://www.alphawebprogramming.blogspot.com
                          [email protected]
                          Skype:[email protected]
                          1 914 924 5171

                          Comment


                            #14
                            Re: Report repeating values

                            The issue is that he wants to put dates into the report, but he can't necessarily group on the dates because that will mess up the totals. And, depending on how the data is laid out, just doing a max(Date) might not be right either.

                            That is why I suggest he work out a sql statement that gives him the data he needs - with dates and turn that into a view.

                            Then use another sql statement on that view to get the final results.

                            But without knowing exactly how the data is laid out, it's tough to give a more complete answer than that.

                            Comment


                              #15
                              Re: Report repeating values

                              yes, i agree with you larry. we are just blindly shooting in dark.
                              a make believe data might help.
                              thanks for reading

                              gandhi

                              version 11 3381 - 4096
                              mysql backend
                              http://www.alphawebprogramming.blogspot.com
                              [email protected]
                              Skype:[email protected]
                              1 914 924 5171

                              Comment

                              Working...
                              X