Selection Criteria: only if Records Returned

[bernie321]

Hi

We have a sub report with this selection criteria:

{Orders.Number} = {?Pm-Orders.Number} and
{Orders.CustRef} = {?Pm-Orders.CustRef} and
{Payments.Type} = "Cheque"

The problem is that our table relationships are as follows:

If there are no payments the order details on the report are blank.

Is there any method of making the criteria optional if no records are returned (based on the criteria).

Thanks
B

 

[Turkbear]

Hi,
More details needed:
What tables are in the main and sub reports and how are they joined?





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

 

[synapsevampire]

I don't think that this is your relationship of the tables, this is the result of using the {Payments.Type} = "Cheque" criteria, which states to ONLY return those where there is a row which has that criteria, hence you won't receive those that don't exist.

I believe what you want is something like:

(
{Orders.Number} = {?Pm-Orders.Number}
)
and
(
{Orders.CustRef} = {?Pm-Orders.CustRef}
)
and
(
isnull({Payments.Type})
or
{Payments.Type} = "Cheque"
)

Also make sure that the link/join is a Left Outer.

-k

 

[bernie321]

Hi

Thank you both for your posts:

Main Report: Orders Table only linking to subreport Orders Table
Sub Report:
Orders > Left Outer > Payments
> Left Outer > CreditDetails

I also now have this selection code:

(
{Orders.Number} = {?Pm-Orders.Number}
)
and
(
{Orders.CustRef} = {?Pm-Orders.CustRef}
)
and
(
isnull({Payments.Type})
or
{Payments.Type} = "Cheque"
)

But it is still not showing any of the orders/creditdetails data that is in the page header.

Thanks
B

 

[synapsevampire]

Now you've lost me, what do you mean by what is in the Page Header?

Try removing this part:

(
isnull({Payments.Type})
or
{Payments.Type} = "Cheque"
)

Does it show what you need?

If so, you might use suppression instead to show those that are Cheque of null (best would be a database object, however that's more complex).

-k

 

[synapsevampire]

That should be:

are Cheque OR null

 

[bernie321]

Hi

Thanks, I have suppressed the field but there are some summaries that need to be modifed, to allow for the suppression.

I have added these conditions but errors stating a group condition is not allowed here

Sum ({Payments.Final_Value}, {Payments.Type}, "Cheque")

Thanks

B

 

[synapsevampire]

Is not allowed where?

-k

 

[bernie321]

Sorry: "Cheque

 

[synapsevampire]

Ahhh, I see, sorry, I should have reviewed your formula closer.

In lieu of summarizing the way you are, you can either use Running Totals and palce the criteria in the Evaluate->Use a Formula, or you can create a formula to sum:

if {Payments.Type} = "Cheque" then
({Payments.Final_Value}
else
0

-k