Problem with using Parenthesis and OR's in Search Criteria

rbuser

Hi,

Apologies Nard for posting here as I was not getting a response from my
emails (could be our mail server)


I think I may have found a possible bug (not sure). If so you may have a
work around that would save my day.


PROBLEM:

The problem is when you create a report with autosearch criteria and are
using brackets and or's in the search tab.

If you create an auto search on a field and then add a few other fields
separated by OR's and add parenthesis around all the OR'ed fields you will
get a sql syntax error if the last field is selected as show all. This is
because it leaves a hanging OR in the syntax like

WHERE (( TableName.Field_1 = 0 )

OR ( TableName.Field_2 = 0 )

OR )


Eg Search Criteria.


Field A Auto Search Show All

Begin

Field B Auto Search

OR

Field C Auto Search

OR

Field D Auto Search

End

CAUSE:

If you run the report then you will get a SQL syntax error because Report
Builder does not seem to properly calculate if the last field is show all
that it should remove the previous OR so that a SQL error will not occur.

This seems to be a bug in the logic of TdaSQL.BuildSearchCriteria where it
checks the type of the last field. But it should also check the type and
ShowAllValues property of the next field (if the criteria has a field) and
should ignore the OR if it is not required. The boolean lbOrNeeded should be
false in the case where the next criteria type is dacrField or dacrEnd and
the dacrField type is not ShowAllValues

DEMO:

I am using Report Builder 7.03 on Delphi 5 and SQL Server 2000, but I was
able to replicate the problem with your demo program.

I have included a sample report created in your Demos for End User\Report
Explorer (ReportBuilder\Demos\3. EndUser\1. Report Explorer)

Import the report and run it and select the Show All checkbox for the last
field.

Regards,

Jarrod

nardmoseley

Please do not post attachments to the newsgroups.

This is a known limitation of the current release. I have researched the
issue, but to date have not found any solution or workaround. I sent to your
email a response yesterday to that effect, sorry if you did not receive it.

I will research this further as time allows (i.e. we have a other open
issues that we are researching as well).



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com