7 Replies Latest reply on Jan 7, 2012 3:02 AM by Sorbsbuster

    Impossible Summary field in SQL

    Fagreement

      Title

      Impossible Summary field in SQL

      Post

      hello,

      I had a summary field that i created in FM table to calculate a sum of records that i used in a report.

      Know I'm switchinig all my database to SQL, in SQL i don't have this summary field, how can i proceed?

        • 1. Re: Impossible Summary field in SQL
          philmodjunk

          Generally, any calculation--whether a summary total or a simple calculation can be defined as part of the SQL expression. It's been years though, since I did this with SQL in an Access database, so I forget the exact syntax. You may need to research the specific flavor of SQL that you are using to figure out the needed expressions to make this happen for you.

          • 2. Re: Impossible Summary field in SQL
            Fagreement

            I don't know how to do it in SQL, is there a way to do it in FM? I don't isnit to do it in the DB, I'm ready to do it in a script.

            • 3. Re: Impossible Summary field in SQL
              Fagreement

              hello!

              is there a way to do it in FM? in a report? using variables may be?

              • 4. Re: Impossible Summary field in SQL
                Sorbsbuster

                Do what?  Export the summary field result as part of the SQL export?

                • 5. Re: Impossible Summary field in SQL
                  Fagreement

                  No, in fact In a report I want to use to count the number of records displayed, thus i used the summary field when i had a FM database.

                  Know I'm migrate to SQL database, and in SQL database i don't want to spend too spend too much time on searching how to do summary field (calculation field) in sql? SO i'm wondorreing if there's a workaround in FM itself.

                  in another term in FM how to calculate the sum of a subset of records in a report without using the summary field in the database.

                  thanks

                  • 6. Re: Impossible Summary field in SQL
                    Fagreement

                    No, in fact In a report I want to use to count the number of records displayed, thus i used the summary field when i had a FM database.

                    Know I'm migrate to SQL database, and in SQL database i don't want to spend too spend too much time on searching how to do summary field (calculation field) in sql? SO i'm wondorreing if there's a workaround in FM itself.

                    in another term in FM how to calculate the sum of a subset of records in a report without using the summary field in the database.

                    thanks

                    • 7. Re: Impossible Summary field in SQL
                      Sorbsbuster

                      You will still have to use the summary field you created, but you can use the GetSummary function to export the result of the summary.  It will export one value with each record.

                      For example, if your summary if a count of invoice lines per month, then you could create a calculation field:

                      GetSummary ( SummaryInvoiceCount ; Month )

                      And each record you export will have that value - note that you need to sort the records by the break filed, just as you would to view the summary values.

                      Is that what you mean?