2. ASPRunnerPro General questions
  3. [SOLVED]  Total Group in a report with criteria
This topic is locked
[SOLVED]

 Total Group in a report with criteria

 		7/21/2012 2:01:46 PM
ASPRunnerPro General questions
lefty author

I have a report that generates the following
Brand

Customer

cust_num

totpos
I use the group by cust_num and group by Brand in the report settings.
I run the report and have a summary with a group total for totpos for each BRAND
Now I need to add criteria to that group total field which asprunner generates in report.
My criteria: I want to take the group totals and add criteria to those totals in a query . I have tried below but am missing something.
Select

fldbrand,

totpos

From Total_Pos

Where totpos > 499

Group by fldbrand, totpos
Problem is I need the the criteria on the grand total of totpos grouped by brand and > 499 Not sure of this type sql have used ROLLUP but get onweraccess Option error message
Sergey Kornilov admin 7/22/2012

Check SQL HAVING clause that is applied after the grouping:

http://webcheatsheet.com/sql/interactive_sql_tutorial/sql_having.php
Something like this may work:

Select

fldbrand,

totpos

From Total_Pos

Group by fldbrand, totpos

having sum(totpos)>499
lefty author 8/2/2012



Check SQL HAVING clause that is applied after the grouping:

http://webcheatsheet.com/sql/interactive_sql_tutorial/sql_having.php
Something like this may work:

Select

fldbrand,

totpos

From Total_Pos

Group by fldbrand, totpos

having sum(totpos)>499



Thanks for the reply .
Tried Having clause ; but it only looks for one record with an amount over 499.

I was trying to get the sum of group fldBRAND which would include multiple records. I thought ROLLUP or CUBE will work here but keep getting above error message .

I can get all customers and brands grouped in report which shows the totals in the summary ( of each customer and each brand) but I need a report to show just the customers with the above criteria and show totals.
Sergey Kornilov admin 8/3/2012

I'm afraid I'm not following.
Anyway, if you can get what you need using a SQL query - create a view in your database on the top of this query and use this view as a datasource in ASPRunnerPro.
lefty author 8/4/2012



I'm afraid I'm not following.
Anyway, if you can get what you need using a SQL query - create a view in your database on the top of this query and use this view as a datasource in ASPRunnerPro.


found a workaround for getting grand total of records with criteria based on groups.
SELECT

fldcut_num,

fldbrand,

fldcust,

fldsales_num,

category,

totpos,

SUM(totpos) AS grand_tot

FROM Total_Pos

GROUP BY fldcut_num, fldbrand, fldcust, fldsales_num, category, totpos

Having category = "OFF"
Now to get grand_tot ( grand total of grouped records ) with criteria Since Alias grand_tot cannot be used in the having clause. I used the ADVANCED search in the report to get the URL of the criteria I needed . Then in ASPRunner EDITOR I changed the report URL to get the criteria I needed.
grand_tot > 499