Help with filtering SQL results in the report

[andyd273]

I am really new to CR 9, and am still trying to learn everything that it can do.
I need a way to work with the results from a SQL query in VB6 to filter out unneeded results.

basically, VB6 compiles a list of customers depending on what we're looking for (anywhere from 1 to 8000 records can be returned) then it passes the customer id numbers to CR9 using the report.RecordSelectionFormula method.
what I need to do is at some point filter the record based on if the address' is mail to or ship to, and based on addresse position (whether they are are a web admin, parts manager, owner, etc.) and that is where the problems start. not all customers have web admins or parts managers, and so I either have records cut out of the report, or get all of the positions listed (like have the mail to and ship to for both the web admin and parts manager from the same company)

oh, and then I need to be able to export it to excel, so the different fields have to be seperate so that excel doesnt stick them together.

if anyone has any ideas on where to start, that would be great.

 

[Madawc]

Crystal uses Report > Selectino Formulas > Record to filter out records, but only on the basis of something within that individual record.

What you could do is group by company (assuming the data comes from more than one record). Use summary totals "MAximum" to get the most suitable value for the group. Do tests to find what exists and what can be used. Remember to use isnull(value) when testing for something that may be missing, because Crystal normally stops for nulls.

There are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.

Directly Calculated Totals within a Formula Field can be coded directly, with commands like Sum ({ADV01.Advance}, {ADV01.AccType}). The same result can be achieved by picking up an existing Variable, and will keep the code even if the Variable itself is later deleted. Formula fields can also include Running Totals and other Formula Fields, with some limits depending on when the values are calculated.


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

 

[andyd273]

I'm going to look into that record selection, but for this report the counts and stuff arent what I'm looking for. basically I want to export the report into excel so I can then put it through our mailing program.
here is a sample of what would be returned:


DeVore Motors - Rex DeMoss OR CURRENT Parts Manager Ship To
DeVore Motors - Rick James OR CURRENT Parts Manager Ship To
DeVore Motors - Jeff Coleman OR CURRENT Parts Manager Ship To
DeVore Motors - Rex DeMoss OR CURRENT Parts Manager Mail To
DeVore Motors - Rick James OR CURRENT Parts Manager Mail To
DeVore Motors - Jeff Coleman OR CURRENT Parts Manager Mail To
Dunning Motor Sales - John Mohler OR CURRENT Parts Manager Ship To
Dunning Motor Sales - Laura Howard OR CURRENT Parts Manager Ship To
Dunning Motor Sales - John Mohler OR CURRENT Parts Manager Mail To
Dunning Motor Sales - Laura Howard OR CURRENT Parts Manager Mail To

As you can see, each person gets listed twice, once for each address, and they get listed even if they are not the parts manager. there is a customer contact type of parts manager, and I only want the person that is listed as the parts manager to show up. and if there isnt a parts manager listed, I want 1 and only 1 contact to be listed with a generic "Current Parts Manager" as the name.

 

[andyd273]

hopefully the record selection formula editor can do what I want, but if there are other ways to do it, then that would be cool too

 

[Madawc]

As I said before, the record selection selects individual records on the basis of some feature of that record. That's how Crystal works, no way round it.

If you group a set of records, such as those for 'DeVore Motors', then you can accumulate data for the group and show the most appropriate address.



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