running total divide by rows 1

[Out2work]

I need to work out productPrice/ totalproductPrice (sum of DISTNCT productprice) the report is all designed in the group footers and works great so far

this last coloumn is a pain

I have data which will show duplciates of product price There is a reason for this.

ABC and ABCNA are not always presented together

ABC locationname1 =abc1 productPrice=$500 Category1
ABCNA locationname2 =abc1 productkPrice =$500 Category1
ABC locationname1 =abc1 productPrice=$800 Category2
ABCNA locationname2 =abc1 productkPrice =$800 Category2
ABC locationname1 =abc1 productPrice=$900 Category3

Now In the group 3 footer im getting unique productPrice which is correct.

--Storelocation (group1)
------manufacturer(group2)
--------Category (group 3)

what i want is in the group 3 footer is to get a SUM of all the DISTINCT productPrice and use the forumla
Price/ totalprice
I will then need to work out the subtotals in group2 and totals in group 1

Can this be done in crystal ?

 

[CoSpringsGuy]

in your sample data the product price for ABC and ABCNA is always the same. I believe that's what you were referring to when you said this will always happen? Is ABC etc .. the manufacturer referred to in grouop 3? Will there be other values besides ABC and if so, will it duplicate product price always end in NA?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[Out2work]

The reason is ABC & ABCNA & productPrice are coloumns in my report so i only need to show the value once

Abc is the manufacturer

Im not sure this is possible with running totals or with functions (previous function to get a distinct value).


Thanks

 

[CoSpringsGuy]

Please answer the question above. There are other manufacturers besides ABC I assume? If there are will the "duplicate" value have the manufacture code ending in NA every time? If so .. do you need the NA code to appear in your report?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[Out2work]

Yes there are many other manufacturers

No i dont need NA to appear as in group 3 i can display distnct values in coloumn productprice.

--Storelocation (group1)
------manufacturer(group2)
--------Category (group 3)

i need to the new coloumn which is productPrice/ totalproductPrice
but getting a running total which is an evaluate of group 3 and RESET of group1 and using this on each row is the issue

 

[CoSpringsGuy]

Im still not completely understanding.... I am going to assume that every manufacturer code will have a "duplicate" entry for the locationname1, productPrice, and Category1 but will end in NA.

create a formula
//
if right({manufacturercode},2) <> "NA" then {productprice} else 0

put that in details and insert a sum summary to group 3

or you could add code to avoid those NA codes in your record selection formula

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[Out2work]

No that wont work as there arnt always duplicates is it possible to do multiple checks like this

im looking into the previous function am i able to use this in group footers. I need to use this in the group footers

if Storelocation <> previous(Storelocation) and{manufacturercode}) <> previous(manufacturercode)
and Category <> previous(Category )
then {productprice} else 0

is there an alternative to this?

 

[Madawc]

Try running totals - see FAQ767-6524 if you're not already familiar with them.


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

 

[Out2work]

Thanks for the link.

I have found an alternative by using SQL view and doing a lookup to work out totals per location.