List all customers - even if there is no data for that customer

[SimonPetherick]

Hi,
I have a database that is automatically updated daily with data sent via telemetry from all our customer sites.

I have written a crystal report that has a simple cross-tab listing the "customer name" (column) and the "gas consumption" (row) for a particular day. However, if we experience problems with our equipment that sends the data back to our database, hence a couple of customers do not have data registered against their name for a particular day, the crystal report does not list that customer name.

Is there a way that I can list all customers, even if no data exists against one (or many) of these customers for a given day?

Ta.

 

[dgillz]

Do you have a table with ALL customers in it? If so link this table to your transaction table, by Customer ID. Place the Customer ID for the customer table on the report first, then the fields from the transaction table.

This way you will have ALL customers. Regardless if they are in the second table.

Let menow if you have any questions. Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[kenhamady]

The traditional solution is an outer join - which might be where DGilz is heading. I think the problem is going to be the "in a given day" part of the requirment. That means a criteria on the outer table, which will prevent an outer join.

I have an article that deals with your options at:

http://www.kenhamady.com/news0208.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[SimonPetherick]

Hi Guys,

I do have a table that lists all customers but unfortunately, it doesn't seem to be working. I also had a look at your article (kenhamady) and none of those solutions will work either.

Any other suggestions?????

Cheers.

 

[kenhamady]

If none of the options that I have listed will help, you might be stuck - but that would surprise me.

Why don't you tell me why each option won't work. That will give me a better idea of what you are facing. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[SimonPetherick]

Ok - this is the situation......

I have written a report that uses a processing extension (DLL) when viewed through CE. The DLL checks the userid when a user logs into CE and hence, only outputs the appropriate data within the report.

The customer and their retailers are set-up in our interactive database (Phoenix). This is where the DLL looks to find out what data should be available for each retailer.

So, in practise, when a retailer logs onto CE, they will only see their customers data - not customer data that belong to other retailers.

Looking at the 6 options listed in your article.....

1) I cannot hard code any data because if a customer decides to go to another retailer, I will have to alter the crystal reports (which is not what I want to do as there is a possibilty of error).

2) If the data I need to report on is not coming back from a customer site via our telemetry, that means all the data is not coming back. It is most likely a problem with our telemetry equipment.

3) I tried this but it did not seem to work. Not sure why????

4) Same as no 1. I can't afford to hard code with the possibility of error when a customer changes retailer.

5) Time is a factor. We have such a large database that we cannot afford for a report to take 10 minutes to run. Currently they take anywhere up to 5 minutes to run. Any longer would be insufficient. Also, cannot create cross-tabs using this technique.

6) Not using consecutive numbers or dates. I need to list all customers names which is a string.

Hope I haven't confussed you.........

Ta.

 

[kenhamady]

There aren't many options left. The reason that #3 doesn't work (in most cases) is after you do the outer join, you are probably placing criteria on the outer table (like a date range). That cancels the effect of the outer join.

You seem to be assembling the table from external sources each day. Can you add a step to the routine that appends to the table one empty record for each customer on that day? If they have to match their current vendor, then add the current vendor into the transaction. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

When a user logs in, you could have a CustomerID which links to their retailers and data associated with them, why would you want to further qualify with a DLL?

Presumably the database knows who the user is, which can be used in the Where of a reference table select, which is joined to the customer, which is joined to the retailer, etc...

In SQL Server I use the following to discover the user:

(substring(suser_sname(),(charindex('\',suser_sname()) + 1),25))

I think that you've made this overly complicated, or perhaps I've oversimplified, but I always try to let the database do all of the work, it's faster and less maintenance.

-k

 

[lbass]

Maybe I'm missing something here, but I think you should be able to create a "default" for customers with no reported gas consumption on a particular date. When you have telemetry failures, are these limited to one or two days, so that you could reasonably expect an active customer to show some gas consumption within a period of, e.g., three days? If so, this might work:

1-Join the customer table to the gas consumption table with a left join.

2-Create a date parameter for the date under consideration.

3-Select records for a date range to include a reasonable period of time, e.g. {datefield} in {?date}-3 to {?date}

4-Create a formula {@gas}:

if isnull({gasconsumption}) or {datefield} <> {?date} then 0 else {gasconsumption}

This will return a record for each customer who is active during the three-day period even if they had no gas consumption on the specified date. If you then group on customer and sum {@gas}, you will have consumption figures for all customers on the specified date. You could suppress the zeros to indicate missing information.

-LB

 

[kenhamady]

LBass,

The problem is usually that step 3 (the date range) is on the outer table. This cancels the effect of the outer join you did in step 1.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

Ken,

I know, but my point was that if you could pick a range of dates from the right table where you could expect a customer to appear at least once, the customer name would appear in the report and you could then create a default to exclude data from irrelevant dates from consideration. I suppose this is in effect creating an equal join, but from the perspective of the particular date, it acts like a left join.

For example, select {?date}-2 to {?date}
4/1 4/2 4/3
Customer A 25 22 --
Customer B -- 23 --
Customer C 21 -- --
Customer D -- -- 20

This assumes that telemetry failures never happen three days in a row. Using the formula, and a {?date} = 4/3
you'd get the following data:

Customer A
4/1 0
4/2 0

Customer B
4/2 0

Customer C
4/1 0

Customer D
4/3 20

Then if you sum by customer, Customers A - C show &quot;0&quot; and Customer D shows &quot;20&quot;. Display the selected parameter date to indicate the group values are for that date.

Am I still missing something here?

-LB

 

[kenhamady]

Sorry, I missed that assumption. The technique you outline is option #2 in the list of options that I gave him. Maybe your explanation will inspire another approach. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[SimonPetherick]

Unfortunately I do not have a guarantee that when the telemetry fails, it will be fixed within 3 days. It could be fixed within 1 hour or it could be 5 months. I just don't know.

The other problem with this solution would be the time factor. The solution you have proposed would bring unnecessary data back and hence delay the running of the report.

I can see where you are coming from but unfortunately I don’t think I can get around it.

Cheers,

Simon