Forcing values for summary purposes

[lauriesamh]

Hi I'm using CR 8.5 I am trying to create at cross tab counting distinct sales based on location type for two Regions.

Each sale should be assigned to a specific location type based on what is in the detail lines for each sale regardless of the Region. The problem is the detail lines double count and put a sale into two separate location types. I would sales to be forced into one type based on a heirarchy. For example if the sale has details that would make it an "In Store" and also "Other Sales Location" I would like to force the sales type to be "In Store". I have created the following formula. Any help is appreciated as I've worked on this for sometime:

if {Sales.SalesType} = "H" and ({REGION1SrvLine.ItemCode} in ("201" to "205") or
{REGION1SrvLine.ItemCode} in ("211" to "215") or
{REGION1SrvLine.ItemCode} in ("241" to "245") or
{REGION1SrvLine.ItemCode} in ("381" to "387") or
{REGION1SrvLine.ItemCode} in ("391" to "397") or
{REGION1SrvLine.ItemCode} in ("401" to "404") or
{REGION1SrvLine.ItemCode} in ("411" to "412") or
{REGION1SrvLine.ItemCode} in ("420" to "429") or
{REGION1SrvLine.ItemCode} in ("431" to "440"))
Then "InStore"&{Sales.SalesID} & {REGION1SrvLine.REGION1Number} else

If {Sales.SalesType} = "H" and {REGION1SrvLine.ItemCode} in ("281" to "228")
Then "Sales Call”&{Sales.SalesID} & {REGION1SrvLine.REGION1Number} else

If {Sales.SalesType} = "H" and not ({REGION1SrvLine.ItemCode} in ("281" to "228"))
Then "Other Sale Location"&{Sales.SalesID} & {REGION1SrvLine.REGION1Number} else

If {Sales.SalesType} = "U" and (({REGION2SrvLine.RelCode} startswith "11")
or ({REGION2SrvLine.RelCode} startswith "12") or ({REGION2SrvLine.RelCode} startswith "18"))
Then "Internet Sale"&{Sales.SalesID} & {REGION2SrvLine.RelCode} else


If {Sales.SalesType} = "U" and (({REGION2SrvLine.RelCode} startswith "13")
or ({REGION2SrvLine.RelCode} startswith "14"))
Then "Phone Sale"&{Sales.SalesID} & {REGION2SrvLine.RelCode}else


If {Sales.SalesType} = "U" and (({REGION2SrvLine.RelCode} startswith "45")
or ({REGION2SrvLine.RelCode} startswith "46") )
Then " Sales Call "&{Sales.SalesID} & {REGION2SrvLine.RelCode} else


If {Sales.SalesType} = "U" and (not ({REGION2SrvLine.RelCode} startswith "45")
or ({REGION2SrvLine.RelCode} startswith "46") or ({REGION2SrvLine.RelCode} startswith "13")
or ({REGION2SrvLine.RelCode} startswith "14")or ({REGION2SrvLine.RelCode} startswith "11")
or ({REGION2SrvLine.RelCode} startswith "12") or ({REGION2SrvLine.RelCode} startswith "18"))
Then "Other Sale Location"&{Sales.SalesID} & {REGION2SrvLine.RelCode

 

[synapsevampire]

WHat would identify "For example if the sale has details that would make it an "In Store" and also "Other Sales Location" I would like to force the sales type to be "In Store". "?

Try posting example data and expected output, describing data and intent is far more difficult.

-k

 

[lauriesamh]

Example:

Sales ID: 12345 Sales Type H
Detail records: Item Code 211 1.00
Item Code 281 1.00

This would put a count of 1 in both "In Store" and "Other Sale Location" on my crosstab. What should happen is a total count of 1.00 in "In Store" and nothign in "Other Sale Location"

Does this help?