Antivirus install to db server: Question

Hello all,

I have the following dataset returning top 20 records for a day, changing daily

STATE

USER ID

SALES REP

SALES

QLD

6758

Liam Maddrell

QLD

6677

Lisa-Maree Findlay

QLD

6133

Benjamin Matthews

QLD

6299

Simon Tabet

QLD

6112

Deborah Williams

VIC

3428

Peter Mahar

QLD

5134

Russell Den Engelse

5279

Julie Isitt

NSW

5677

Pranav Agarwal

NSW

6093

Rosa Delizia

QLD

6684

Ron Prasad

7578

Francis Williams

Qld

7511

LigIA Borodi

QLD

6804

Rupert Ryan

QLD

6856

Maxwell MacLean

QLD

6904

Trent Sticklen

QLD

7236

Drazen Knezevic

QLD

7406

Amy Donaldson

NSW

5601

Michael Phillips

QLD

5940

Harnake guraya

I would like too now sum up the figures to get in a separate layout (total sales off the above reps by state)

Queensland 168

South
Australia

Western
Australia

176

Victoria

New South
Wales

Northern
Territory

I have tried a different dataset but it doesnt allow me to do this due to the use off top 20 in the query which needs to be grouped by state this time and to do that it doesnt know the topn 20 records.

...I am trying to figure out a way by using textboxes , tables etc, I have managed to get the results coming out in a table using grouping by state and sum of the values but because there are no records for Sth Australia and Northern Territory these are not on the list...and i need these also in the list...can someone please help

thanks

Can someone help me with some code to achieve the above ?

thanks

|||

I don't have code for you, but in your query, you need to specify something like isnull(NorthernTerritory,0)

This will return a 0 (zero) instead of a null and then the data should show up on your report.

Since you are doing a topN, why don't you use a group by in your report?

|||

I cant do a group by as a rep can have more than 1 sale and to get the top 20 it is adding up all his sales already

wa John 20 item 15

wa john 15 item 20

To get the top 20 i add the sum off the values together to get the rep...

wa john 35

if i group by state john will be treated as 2 records and the numbers wont be for the top 20 sales rep...but just the top 20 sales

thanks for trying can anyone else help?

|||

Hi,

I found it hard to understand why cant you use grouping and othere data sets,

I have a solution but It is an ugly one.... (-:

first change your Data set to return a sum colums
by addind a join in your SQL to a query tha group by the State and sum the sales

after you do that you will have all the results you need in the same data set

now you jost have to add the colums to the feilds and use the First function in the expresions to

get the currect total sales for the state