help please 2

[kisasama]

I'm really confused since it's about three weeks into using crystal reports and I'm stuck. Here's what's happeneing I'm working on a cobol database with two tables. the first a distributor table and the second is an orders table. I grouped the orders and distributers together and put in a range parameter to suppress any dates (which by the way is in a string) that aren't in the range. Now I want to check for orders made by customers outside that range and compare them with the range. then exclude those cutomers out of the range. A better example of this is having a report that shows a range of orders and customers from jan 01 2002 to dec 31 2003 and exclude anyone customers who have bought in 2004. can this type of thing be done in crystal reports?

tIA

 

[lbass]

You would need to use a record selection formula that included both the parameter range plus the dates of activity you wanted to exclude. In your example, you could write a record selection formula like:

{table.date} >= minimum({?daterange})

Then create a formula {@outofrange}:

if {table.date} in {?daterange} then 0 else 1

Insert a group on {table.customer} and then go to report->edit selection formula->GROUP and enter:

sum({@outofrange},{table.customer}) = 0

-LB

 

[kisasama]

Thanks lbass

i created a parameter field for the report where someone can enter a date range. say from 2000 to 2004. Is there another way for me to input something where it will exclude the customers who have ordered in 2004 as well and exclude them from the report entirely even if they ordered say in 2002 or 2003??

 

[lbass]

You could create a SQL expression {%maxdate}:

(select max(AKA.`date`) from Table AKA where
AKA.`customer` = Table.`customer`)

Substitute you table name for "Table" and your exact field names for "date" and "customer", but leave "AKA" as is, since it is an alias table name.

Then go to report->edit selection formula->RECORD and enter:

{%maxdate} in {?daterange}

This would only return those customers whose most recent date fell within the parameter range.

-LB

 

[kisasama]

great I will try this thanks LB

 

[kisasama]

ok I've entered this

(select max(AKA.`ENTRY_DATE`) from odh2db AKA where
AKA.`DIST_ID` = odh2db.`DIST_ID`)

it doesn't seem to be right? also what if say customer names were in a different table and the date field in another table. how would i put them together for the comparision?

KS

 

[lbass]

Please provide the name of the table with the date, and the name of the table containing the customer name. Is `Dist_ID` your customer name?

Also please try to be more descriptive of the issue than saying something doesn't work. Did you get an error message? What was it? The syntax/punctuation for SQL expressions can vary depending upon the data source. If you were to double click on a field to add it to the SQL expression, you would get an idea of the correct punctuation.

-LB

 

[kisasama]

sorry I wasn't as specific. I'm working on a cobol database the two tables are

"odh2db" which contains all the orders, dates, etc a 'distid' field which links to the customer database called "dstdb" which has customer information like 'first_name' and 'last_name' as well as a 'dist_id field'.

"dstdb"."DIST_ID" --> unique id
"dstdb"."FIRST_NAME" --> Customer first name
"dstdb"."LAST_NAME" --> Customer last name
"odh2db"."DIST_ID" --> unique identifier from other table
"odh2db"."ENTRY_DATE" --> entry date of the order.

hope that makes sense.

 

[lbass]

Try the following which assumes that {dstdb.DIST_ID} is a unique ID for your customer field:

(select max(AKA.`ENTRY_DATE`) from odh2db AKA, dstdb B where
B.`DIST_ID` = dstdb.`DIST_ID`)

If this doesn't work, please be sure to provide the contents of the error message.

-LB

 

[kisasama]

thanks lb for your reply here's the error of what I get when i input your expression in.

 

[lbass]

I'm not sure what that message means, although I wonder whether it is the punctuation. If you add a field like {dstdb.DIST_ID} to the SQL expression, how does it display?

-LB

 

[kisasama]

I was wondering would there be a way where we can suppress the distributor ID everwhere. like say if the distributor id has a entry date in 2004 then can we supress all those distID??

 

[lbass]

Then we're back to my original suggestion. You would create a formula like {@in2004}:

if year({table.date}) = 2004 then 1 else 0

Then you could go to report->edit selection formula->GROUP and enter:

sum({@in2004},{table.customer}) = 0

Or if you don't want to do a group select, you can use suppression by going to the select expert->group header/details/group footer (each)->suppress->x+2 and entering:

sum({@in2004},{table.customer}) > 0

Either approach will work to eliminate customers with activity in 2004.

-LB

 

[kisasama]

but would that eliminate the distid throughout the whole report? that's the my goal I was wondering if all those distid's could be stored somewhere and if in the report we can supress all those ids;

 

[lbass]

I should have used {table.Dist_ID} in my last formulas instead of {table.customer}. Your last post is confusing though. I thought you had a group on {table.Dist_ID}. If so, if the District_ID meets the criteria in the formula, that GROUP will not appear using either group selection or suppression.

-LB

 

[kisasama]

lbass seriously thanks for your patience. I'm terribly new at this that's why I'm so lost.

My firm pretty much wants a report that has a range of orders from 2002-2004.

In that report would be a list of people and their orders.

Next the want any customer who ordered in 2004 not to appear in that year and the previous.

Basically they want to see what customers haven't ordered in a long time.

the two tables that have been used are of course
dstdb and odh2db.

dstdb ---> all the distributors with distid and names etc
odh2db ---> all orders distid reference and entry_date for the orders.

so the confusing part is that they do not want to show anyone with who placed an order in 2004 to be seen in 2002 and 2003.

hope this is clearer.

If it was ASP you can pretty much relate it to putting all the distid's in a array who's ordered in 2004 and use that array of dist id's to reference the report. If they appear on both the array and report then they shouldnt show up whatsoever.

 

[kisasama]

I hope this helps with the kind of group selection I'm trying to do.

 

[synapsevampire]

Create a subreport for the report footer and in the Report->Edit Selection->record of the subreport filter out all of those for 2004, as in:

{table.date}) < cdate(2004,1,1)

The subreport can be formatted however you'd like as it will display separately.

-k

 

[kisasama]

but wouldn't that filter out everything under 2004?

 

[lbass]

The simplest method would be to use the SQL expression for max date. Please go to the SQL expression expert and double click on one field from each of your tables and report back with how the field is displayed.

The other method would be to create a subreport that is limited to records with dates in 2004 and place this in the report header. You could then create a formula to accumulate the {dstdb.DIST_ID} which appear in that report:

//{@distID2004} to be placed in the subreport detail section:
whileprintingrecords;
shared stringvar distid04;

if instr(distid04,totext({dstdb.DIST_ID},0,"")) = 0 then
distid04 := distid04 + totext({dstdb.DIST_ID},0,"") + ", ";

//{@displ} to be placed in the subreport report footer:
whileprintingrecords;
shared stringvar distid04;

Suppress all sections within the subreport and place it in the main report report header.

Then in the main report go to the section expert->group header (distID)->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar distid04;
instr(distid04,{dstdb.DIST_ID}) <> 0

Repeat for the detail section and the group (DistID) footer.

-LB