Getting only one record from simple report

[Former Member]

Hi.  I'm pretty new to CR.  Wrote one substantial report on version 8, using a lot of the basics anyway.  Upgraded to version 2011.  I tried to write an extremely simple report linking three tables by customer number (Great Plains accountng system).  There is one input parameter, a date.  The selection criterion is simply that the "last transaction date" (table 2) be equal to or greater than the input date.  I then ask for the customer name, city, state and telephone (table one), last transaction date, year-to-date and last-year total activity (table 2) and email address (table 3).  It works fine--except I'M ONLY GETTING ONE RECORD!  It's so simple, I can't find anything to change.  Is there a problem with date comparisons?  There could be a null in the last tranaction date of the next record, though that isn't likely.  Are nulls a problem in date comparisons?  Surely this is very simple for someone who knows what he or she is doing.  Sure would appreciate a tip!

[venkateswaran_k · Accepted solution]

Can you show your Database Expert - how the tables are linked?

or

SQL statement?

Also, How do you pass the parameter to the SQL statement for the last transactin date?

In Select Expert - can you show me how you pass the parameter?

REgards,

Venkat

[Former Member · Accepted solution]

Venkat-    Thanks for looking.  Here are two screenshots, one of the links and one of the formula:

[abhilash_kumar · Accepted solution]

Hi Jerry,

Could you copy the SQL generated by CR and fire it on the database?

Go to Database tab > Show SQL. Run this query on the database using a supported db client and see how many records you get.

-Abhilash

[venkateswaran_k · Accepted solution]

Hi Jerry

Your prblem is identified.

In the Select Expert....

You have set as    RM00103.lsttrxdt >= {?LastTrDt}

You do not put  >=,  You put only =

Like this.....    RM00103.lsttrxdt = {?LastTrDt}.

Reason....

Here you are not set the condition,  in fact you are passing the parameter.

So it should be =.  NOT >=.

(I did the same mistake earlier once...)

Hope this should fix your issue... 

Please revert back still you face the problem

Regards,

Venkat

[Former Member · Accepted solution]

Hello Abhilash-

I'll have to figure out how to do that.  It will be a day or two.  But thanks.

[Former Member · Accepted solution]

Hello Venkat-

But that will only give me records with that exact last transaction date.  I need to extract all having that date or later.  (Or at least later.)

The one record I get does have a last transaction date greater than the input date.

Jerry

[venkateswaran_k · Accepted solution]

Oh Okay...  i was under the impression that you are connecting to a Standard function that returns a data set by accepting the Date as parameter

Okay.. I got it now.. You are directly acessing the database by joins...  (referring to your database expert)..

In this case, I feel there is no issue with your coding..  You have to just verify with the database what actually it returns with the SQL same query.

Or otherwise, give some other date as your parameter and check it..  give much earlier date....say for example give it like 01/01/2011.

Regards,

Venkat

[Former Member · Accepted solution]

We have many active customers, so there would be many records.  I did try a very early date-- 2 1/2 years ago--and it makes no difference.

I also tried a date range, i.e. using two input parameters, beginning date and ending date.  No change.

[venkateswaran_k · Accepted solution]

Okay

do one thng..

Try like this...

In Select Expert

Instead of

RM00103.lsttrxdt >= {?LastTrDt}

Just hard code it and try....

RM00103.lsttrxdt >= Date (2011, 01, 01);

Post me back with your results...

If that works, we may have to look in the view of date format...  I feel there is a date format mis match

Regards,

Venkat

[Former Member · Accepted solution]

Venkat:  OK, I've tried that.  Still no change. Only one record.

Whatever I have tried, and whatever dates I have used, I have always received this same record (unless the input date is too late--then I get nothing--so this one record responds OK).

Jerry

[Former Member · Accepted solution]

test your links by removing your date data selection.  Are you getting all the records?

If no, then something is wrong at the table linking level.

If yes, now try your date selection as Date({RM00103.lsttrxdt}) >= {?LastTrDt}) 

     does this make a difference?

[Former Member · Accepted solution]

Debi-   That makes sense, thank you.  I removed the selection formula and still got only one record.  I don't see any other common field to link by.  I've tried "link by name", but it always switches back to "key".

But hey! I just tried breaking the link to table 106, the one with the email address (and nothing else that I want) and I get all the records.  That is with the selection formula back in place.  The "email address" column shows the same address that appeared in the one record I was getting before, duplicated down the column this time.  There is something strange about where and how this information is stored; but I can work on that.

Regardless of what is in the email address field, though, why should it stop the selection process?

[venkateswaran_k · Accepted solution]

Hi Jerry,

You joined it from 103 to 106... 

Break that link

You again join it from 101 to 106 on Customer number and make it as outer left join

Regards,

Venkat

[Former Member · Accepted solution]

Never use automatic linking. Not all database designers are sensable and they may not have named linking fields in different tables the same thus autolinking may not work. If your version of Crystal will not let you turn autolink off, delete all the links and make them manually. If you do not have a map to your database, look at each table and the contents of the fields, and find the correct linking fields.

[Former Member · Accepted solution]

Debi-     Thanks for the tips.  I really appreciate your input.

Jerry

[Former Member · Accepted solution]

Venkat-     It works!  Thank you very much!  (I told you it was simple......)

I thank everyone involved.  This has been very helpful and instructive for me.

Jerry