Can Browse Data, But It Isn't Displayed on Report 1

[kc27]

I am using Crystal Reports 9 to make some basic reports. In the database in which I've done most of my queries, if a I want a simple report that lists the values for a field, I use use the Report Wizard and choose a table, then choose a field, then click "finish" and I have my list.

Now there is another database I am querying. However I cannot generate a simple list following the above steps. I can see the data if I browse the field at the field selection screen, so I know my connection to the database is fine. But the report is always empty.

I can filter the field to show only values that start with a letter, for example "w" and I will get values, but I cannot get a list for "any value"

Does anyone have any ideas on what I need to do to generate a simple list of all the values found in a field?

 

[LyleU]

Can the field contain a null?

If so, Crystal usually stops reporting when it hits a null value so ideally you should test for those in your selection.


Lyle
----
Insanity - a perfectly rational adjustment to an insane world. -RD Laing, (1927-1989)

 

[lbass]

Have you verified the database? Database->verify.

-LB

 

[Turkbear]

Hi,
To get all values be sure you have NO criteria specified in the Report or group selection formulas.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kc27]

I am sure there are null values - the field isn't always populated. So this seems like I would need to go into the Select Expert and enter a formula for this field using "IsNull" to get past this. Is that correct, and if so can someone advise me as to what the forumula should read?

The field I am querying is a varchar, if that matters.

The database is verified, and I double checked, there is no criteria spcified in the Report or group selection.

 

[lbass]

What is showing in database->show SQL query? Please copy and paste the query into the thread. Also identify the field you are having trouble displaying.

-LB

 

[kc27]

lbass

here is the SQL query

SELECT "COPY"."BT_C_EXCLUSIONNOTES"
FROM "Connect3"."c3"."COPY" "COPY"

the field is BT_C_EXCLUSIONNOTES

 

[lbass]

I wonder if you are just not seeing the entire results set because of a lot of nulls. Try adding a selection formula:

not isnull({COPY.BT_C_EXCLUSIONNOTES})

-LB

 

[Turkbear]

Hi,
What database is being used ?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kc27]

Maybe I am doing this wrong

1. I clicked the "Select Expert" button
2. Selected COPY.BT_C_EXCLUSIONNOTES
3. Choose formula from the dropdown menu
4. Entered the formula lbass provided: isnull({COPY.BT_C_EXCLUSIONNOTES})
5. Clicked "OK"
6. Clicked "Refresh Data"
7. Crystal shows that is reading records
8. Report is blank

 

[Turkbear]

Hi,
In addition to the SQL you posted that returned no records, can you post what the SQL is when you use the starts with 'w' selection formula?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kc27]

Here is the "starts with w" SQL

{COPY.BT_C_EXCLUSIONNOTES} startswith "w

 

[Turkbear]

Hi,
I actually meant the actual SQL code like the one you posted previously,not just the seelction criteria,


It is odd that the one with a selection criteria returns records and one with no selection formula at all does not.

Try using another field, instead of
COPY.BT_C_EXCLUSIONNOTES

BTW,Are you using multiple tables?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kc27]

I got results using this

{COPY.BT_C_EXCLUSIONNOTES} <> ""

As I started out my initial post, my queries are usually pretty basic. This seems to deliver the values I would expect. Does it require any further modification?

 

[lbass]

My suggestion was for "NOT is null"--you did the opposite. But it looks like you might have your database set to convert nulls to default values (in file->report options). Your current formula should be fine, but to cover all bases, you could use:

not isnull({COPY.BT_C_EXCLUSIONNOTES}) and
{COPY.BT_C_EXCLUSIONNOTES} <> ""

-LB

 

[kc27]

lbass

Thanks for the additional information. I made the change you suggested. I'm not sure what it does, but I still get the results I need.

Looking under file > report options. "Convert database null values to default" I see that it is unchecked.

I know that LyleU mentioned in his answer that If so, "Crystal usually stops reporting when it hits a null value". To me it looked like Crystal was still going through all the records (based on the what I was seeing happen at the bottom of the Crystal screen).

 

[lbass]

Actually, the following would be even better:

not isnull({COPY.BT_C_EXCLUSIONNOTES}) and
trim({COPY.BT_C_EXCLUSIONNOTES}) <> ""

-LB

 

[kc27]

lbass - Thanks the "better" one worked, too. What makes it better, anyway?

 

[lbass]

It removes the possibility of entries that are one or more spaces.

-LB