Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Multiple Query Totalling Issue

edited April 2003 in End User
I am trying to make a report using the end user tool that returns the Total
Amount for a user selected date range. This part is easy. I also want to
display the Total Amount for the previous date range and for the total. I am
testing using the DemoDB.

Example:
Customer Jul 1 - 31 June 1-30 Total
Amount
Smith, Joe $500 $300
$15,000

I know this can be done by bringing back all the data and using variables
for the Jul and August totals.

I want to prompt the user for the first date range then have the other date
ranges auto calculated. I also want the queries to perform the calculations
(count and sum).

I have tried several ways without any luck. I know the second date range
will be tricky (passing autosearches), but the last total should be straight
forward. I can't get it to work correctly especially when I try it on our
database. I know the report engine joins the queries together before it runs
the report, so it is probably something in there.

Do you have an example of this? And/Or can you point me in the right
direction?

Comments

  • edited April 2003
    Sorry, DADE doesn't support grouping on an expression type calculated field
    where you would extract the month and group the amount by month. You'll have
    to edit the SQL text manually in order to save this inside a dataview. Set
    Allow Edit SQL in the data settings of DADE and right click over the SQL
    text view in the query designer.


    Cheers,

    Jim Bennett
    Digital Metaphors


This discussion has been closed.