Report wont run but SQL view runs fine

[cjbates]

Hi Bob,

I am having a problem with a report that I did on a new sql join that uses a sql view. The view and join execute without error and I can browse the data, but when I run the report I am getting an error that DAI cant open the report and I am prompted to open the Join.
Ive attached a screen shot of the Join. Do you see something wrong?

Thanks
Cynthia

[Bob Cergol]

Hi Cynthia,

I don't see an error in your dyn-join. I notice your first "table" is actually a dynamic data source -- probably a join since I don't think you are using dyn-views at this time. You are allowed to do this by the way.

What is the error you are getting?

If it is "invalid use of null" then it could mean you have null values in your data that is causing a problem.

If so, you will have to either eliminate the null values -- or use a dyn-view to convert them to a value like:

Select
isNull(order.[date],'1751-01-01') as order_date,
isNull(order.amount,0) as order_amount,
etc.

You don't have to convert every column, just the ones involved in joins, groupings and some formulas -- especially if "case" statement is involved.

Regards,
Bob

[cjbates]

Hi Bob,

The only error I get is that DAI cant open the report. There is no other information....

I already decided to just create a sql view with all the fields I need instead of using the Join....

Thanks.

Cynthia

[Bob Cergol]

Cynthia,

The system log "General Alerts" will contain an entry for each time you got the error. Sometimes this gives additional clues for discovering what is causing the error.

Bob