Grouping and Totals

[bitsmith2k]

Hey guys, thought I'd make a post here before I called it a day. Hopefully someone can point me in the right direction.

I'll try to provide as much info as possible.

I can query the db for my desired results with this:

SELECT
    sum (vwReportA.QTY)'QTY', vwReportA.Event,        vwReportA.Category, vwReportA.Subcategory
FROM
    XPress.dbo.vwReportA vwReportA
WHERE
    vwReportA.Category LIKE '%' AND
    vwReportA.Subcategory LIKE '%' AND
    vwReportA.Date >= "Jul 1 2003 00:00:00AM" AND
    vwReportA.Date <= &quot;Jul 31 2003 11:59:59PM&quot; AND
    vwReportA.Event LIKE '%'  and
    vwReportA.Site LIKE 'Butterfly'
group by
    event, category, subcategory
order by  event, category, subcategory

It gives my the correct numbers in the output.
(the query basically came from my report, I put the group by, and sum in it so that i could see the totals in the qa)

When I try to format the report, I use a SUM on the QTY field, but I am not getting the proper numbers. It comes up short.

Any suggestions?

thanks,
mike

here's the view:

create view [ReportA] AS 
select transid'ID', numofpeople'QTY', category'Category', subcategory'Subcategory', eventdate'Date', event'Event', eventsite'Site', NetPrice, GrossPrice, ItemTax, Status, 'TICKS' as  tbl
from tblMain_TransTickets
where status not like '%Returned%' and tblMain_TransTickets.Category not like '%PASS%'

 

[synapsevampire]

These are pointless:

vwReportA.Event LIKE '%' and
vwReportA.Category LIKE '%' AND
vwReportA.Subcategory LIKE '%' AND

And this should be =, not like:
vwReportA.Site = 'Butterfly'

Add the View to a new report, group by the columns as in your test, go to Report->Edit Record Selection->Record and place:
(
vwReportA.Site = 'Butterfly'
)
and
(
vwReportA.Date >= cdate(2003,7,1) AND
vwReportA.Date <= CDATE(2003,7,31)
)

Add the qty field to the details, right click it and select insert->summary->Insert Summary Fields for all groups, and select insert gradn totals.

You can verify the SQL being passed in the Database->Show SQL Query

Part of the info you should supply is version information for Crystal and the database, what form of connectivity you're using, and if you have the latest service packs.

-k

 

[bitsmith2k]

thanks.. sorry.. it was the end of the day..
using sql server 7, and cr 8.5.

the like's are in there so the user can search based on wildcards.

thanks again for the post, i'll try it asap..

 

[bitsmith2k]

ok..
my QTY value in the query analyzer comes up as
25956 Butterfly General Admission Regular Adult

but in cr it comes up as 25161 for the same grouping..

is there any reason that there would be a difference between the query run on the back end and the crystal?


thanks

mike

 

[bitsmith2k]

i figured out what was going on..

i rebuilt my report, this time with group on the server selected...

strangely i got the correct total.. i've also noticed that when i have a formula in the select expert, it reverts back so that crystal does the grouping, even though i have it set to group on the server.

thanks
mike