building a chart

[rmcguire80]

Hello,

Im trying to build a chart displaying a count of the number of records in my SHIPMENTS table for 3 years prior to the entry date field in my QUOTES table.

So it will have a count for each year, but I need to have this for a specific customer id from my QUOTES table


So far I have my chart in a group by entry date for each year but I need it to only pull by one individual customer id.

Any help would be appreciated

 

[Madawc]

Create a formula field that only counts for some detail lines. Something like

If {code} in ["A", "E", "O"]
then 1
else 0

This can be used in summary totals or charts, for any version above 8.5

It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[rmcguire80]

ok, I think I got my chart working but the problem Im running into is the way my tables in my report are linked which is QUOTES.CUSTOMERID linked to SHIPMENTS.ORDEREDBYID.

The issue is if there aren't any shipments with the orderedbyid which match the customer id the report prints blank as it should. I have to link the tables in my report this way to get the chart to display correctly


I also have a report without the chart and it only consists of the QUOTES table as it doesn't have the chart involved.

Is there a way to have a paramter field to pick between 2 reports when running it? Basically if the ORDEREDBYID in shipments is null run the report without the chart have some sort of conditional linking between the SHIPMENTS AND QUOTES as long as ORDEREDBYID is not null.

 

[Madawc]

This is a new issue, you should really have started a new thread.

I think you can overcome your problem by changing the link to SHIPMENTS.ORDEREDBYID to a Left Outer.

If you also do a selection test on SHIPMENTS.ORDEREDBYID, start with a test for nulls.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[rmcguire80]

how do I do a left outer join the visual linking expert. I have Crystal version 8.5 btw....sorry for the double threads

thanks

 

[Madawc]

Select Datebase Expert > Links. Click on the link. Choose [Link Options]

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[rmcguire80]

problem is with version 8.5 I cannot do left outer joins

Basically the problem that Im running into is I have 2 tables QUOTES and SHIPMENTS and I have to link them via QUOTES.CUSTOMERID to SHIPMENTS.ORDEREDBYID


My report with my chart comes up perfect, as long as there is at least one record in my SHIPMENTS table with an ORDEREDBYID equal to the CUSTOMERID from the quote record Im running the report from.


If there isn't of course the report prints blank....I need to find a way if there is a SHIPMENTS record with a blank ORDEREDBYID by the way I have the tables linked that it will still display my fields from THE QUOTES table as the only fields which are used from SHIPMENTS are in the chart evereything else is from the QUOTES table.

 

[lbass]

You should be able to do a left join FROM Quotes TO Shipments. You cannot use any selection criteria on the shipments table fields, however, as this would effectively "undo" the left join.

-LB

 

[rmcguire80]

The problem it seems though is with crystal 8.5 I cannot do a left outer join

 

[lbass]

What is your database? The issue is not with CR--yes you can do left outer joins--so it must relate to your database or connectivity.

-LB