grand total top 5 1

[fsreport]

Hi All
cr 9.0
ms sql
i have a report and 1 group
in my report i have top5 records return
ex:
ph****company**** amount
gr1
detail @amount(created summary on gr1)
grft***ABC*********1000
*******DEF*********900
*******CDE*********800
*******GTH*********700
*******BYT*********600

Is there a way to have the sum of these top5 in a formula
i tried creating a summary but have option to grand total in report footer and gives me grand total of all not the top5 records
i would like to show it like this

ph 1 company**** amount
total************4000
gr1
detail @amount(created summary on gr1)
grft***ABC*********1000
*******DEF*********900
*******CDE*********800
*******GTH*********700
*******BYT*********600

thank you

fsreport

 

[fsreport]

hi found this
Scenario

I am using crystal 9 or 10 and I am wondering if there is a way
I can summarized my total based only on my top 5 values.
For example: My top 5 values would be something like this:
25
23
22
19
20
The total is 109.
However, the # I currently have is 150 and it is because
I also I other values in this report and that they are not
in the top 5.

Solution


Insert a running total that summarizes the field being summarized
for the topN sort.
If the topN sort is based on 'sum of {table.amt}',
then use a sum of {table.amt} in the running total,
evaluate for each record, reset on change of group.
thanks to -Lb previous post

fsreport

 

[fsreport]

hi All
i tried -LB post and does not work
return the top first value but not total of all 5


fsreport

 

[lbass]

The running total must be displayed in the report footer, not the report header.

-LB

 

[fsreport]

Hi -LB
thanks for the replied.
I know it goes in the report footer, but their asking if it's possible to have the values of the top5 records in the page header
thanks



fsreport

 

[Madawc]

A running total 'runs' along-side the data as it is printed. If you can't use a summary total, you'd need to have an inconspicuous sub-report in the report header and then show its values via a Shared Variable/

If you're not already familiar with Crystal totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You can either use a subreport in the page header that displays only the running total in the subreport report footer, or you could try creating a command that returns the summaries directly, as in:

select sum(table.`amt`) as sumamt, table.`company`
from table
group by table.`company`

Then create a formula like this:

nthlargest(1,{command.sumamt}) +
nthlargest(2,{command.sumamt}) +
nthlargest(3,{command.sumamt}) +
nthlargest(4,{command.sumamt}) +
nthlargest(5,{command.sumamt})

Place this in the page header.

-LB

 

[fsreport]

Thanks -LB
will i link my command to my transcation_history table?
i have a rapameter for the posting_date

thanks a million



fsreport

 

[lbass]

I don't know what you mean. For the best performance, you should use a command as the sole datasource for your report, but if you are using other tables, you would have to write the command differently. Don't know want a rapameter is.

-LB

 

[fsreport]

Hi -Lb
will copy my sql from my report and add what you gave me to it and see

Thanks
your the Lady



fsreport

 

[lbass]

It won't work that way. Why don't you post your current SQL as it appears in database->Show SQL Query?

-LB

 

[fsreport]

Hi -LB
Here's my sql query from the report

my parameter are
?end-date
?Compensation
?Territory
?Channel
?Dealer

**********************************
SELECT
"TRANSACTION_HISTORY"."POSTING_DATE",
"FIRM"."C_NAME",
"FIRM"."CHANNEL",
"TRANSACTION_HISTORY"."TERR1",
"TRANSACTION_HISTORY"."SALES_COMPENSATION",
"TRANSACTION_HISTORY"."TRADE_CLASS",
"TRANSACTION_HISTORY"."GROSS_AMOUNT",
"TERR1_LIST"."DESCRIPTION",
"TERRITORY_VERSION"."CURRENT_VERSION"
FROM
("FundSpace_LM"."dbo"."TERR1_LIST" "TERR1_LIST" INNER JOIN ("FundSpace_LM"."dbo"."FIRM" "FIRM" INNER "."TRANSACTION_HISTORY" "TRANSACTION_HISTORY" ON "FIRM"."FIRM_ID"="TRANSACTION_HISTORY"."FIRM_ID") ON "TERR1_LIST"."TERR1"="TRANSACTION_HISTORY"."TERR1") INNER JOIN "FundSpace_LM"."dbo"."TERRITORY_VERSION" "TERRITORY_VERSION" ON "TERR1_LIST"."VERSION"="TERRITORY_VERSION"."VERSION"
WHERE
("TRANSACTION_HISTORY"."POSTING_DATE">={ts '2007-01-01 00:00:00'} AND "TRANSACTION_HISTORY"."POSTING_DATE"<{ts '2007-06-13 00:00:00'}) AND "TERRITORY_VERSION"."CURRENT_VERSION"='Y' AND ("TRANSACTION_HISTORY"."TRADE_CLASS"='R' OR "TRANSACTION_HISTORY"."TRADE_CLASS"='S') AND "TRANSACTION_HISTORY"."SALES_COMPENSATION"='Y'
ORDER BY "TRANSACTION_HISTORY"."TERR1"

----------------------------------

fsreport

 

[fsreport]

Here is my record selection
**********************************
//Date selection
{TRANSACTION_HISTORY.POSTING_DATE} in Date(year({?End_Date}),1,1) to
Date(Year({?End_Date}),Month({?End_Date}),day({?End_Date})) and
{TERRITORY_VERSION.CURRENT_VERSION} = "Y"
and
{TRANSACTION_HISTORY.TRADE_CLASS} in ["S","R"]
and

//Territory
(
if {?Territory} <> "ALL" then
{TRANSACTION_HISTORY.TERR1} = {?Territory}
else if {?Territory} = "ALL" then
true
) and
//Channel
(
if {?Channel} <> "ALL" then
{FIRM.CHANNEL} = {?Channel}
else if {?Channel} = "ALL" then
true
) and
//Firm
(
if {?Dealer} <> "ALL" then
{FIRM.C_NAME} = {?Dealer}
else if {?Dealer} = "ALL" then
true
) and
//Compensation
(
if {?Compensation} <> "ALL" then
{TRANSACTION_HISTORY.SALES_COMPENSATION} = {?Compensation}
else if {?Compensation} = "ALL" then
true
)
********************************************

fsreport