Using nulls with parameters 1

[jcl5]

Hi

I'm running Crystal 9 and Oracle 9.

I have the following selection criteria in my report:-

{VW_MODULE_MO.M_REFERENCE} = {?ModuleRef} and
(ISNULL(({VW_GRADESET.G_REFERENCE})) OR {VW_GRADESET.G_REFERENCE} = {?Resit})

The {?Resit} parameter is a multiple value select from set default values. This works ok if I want to display all records including those with a null gradeset reference. But when I select only one value from the list I also get the nulls which I don't want.

Basically I want to be able to select everything inlcuding nulls or selected criterias without nulls.

Can anyone suggest a way around this.

Many thanks

jcl5

 

[lbass]

Try:

(
if {?Resit} <> "All" then
{VW_GRADESET.G_REFERENCE} = {?Resit} else
if {?Resit} = "All" then
true
) and
{VW_MODULE_MO.M_REFERENCE} = {?ModuleRef}

You would need to add "All" to your parameter list of values.

-LB

 

[jcl5]

Thanks v much - this works!!!..... but I don't understand why. Can you tell me how 'All' works since there isn't a tue {VW_GRADESET.G_REFERENCE} = 'All' option.

jcl5

 

[lbass]

The formula basically says that if the parameter value = "All" then don't use it to select records, otherwise set it equal to the reference field.

-LB

 

[jcl5]

Thanks, I now need to take this a step further and have included two sub-reports to which I have passed the same parameters and linked accordingly. (change sub-report links - VW_MODULE_MO.M_REFERENCE=?ModuleRef and VW_GRADESET.G_REFERENCE=?Resit)

On the main report, the data returned is correct in each of the following scenarios:-
selection of 'ALL'
selection of 'a single criteria'
selection of 'multiple criteria'

but when I run the sub-reports, the data is correct only in the first two of the above scenarios. When I select multiple criteria, the data is not correct.


Any ideas on what I'm doing wrong?

Many thanks

jcl5

 

[synapsevampire]

You can manually edit the Report->Select Formulas-Record of the subreports, ake them reflect what the main report does and it should be fine.

-k

 

[lbass]

Where are the subreports located? What report section?

-LB

 

[jcl5]

The reports are in the sub-report report footer and are crosstabs.
They return correct data with all scenarios when run independently.

Also, correction to my previous thread, the data is not correct in the sub-report when choosing 'All'.
To confirm, data is only correct if choosing single value.

Thanks

jcl5

 

[lbass]

Please try again. I don't know what you mean by:

"The reports are in the sub-report report footer and are crosstabs."

-LB

 

[jcl5]

Sorry LB, the subreports themselves are crosstabs which I have placed in the report footer (of the sub-report).
Additionally, I have placed the sub-reports into the report footer of the main report.

Hope this helps.

Thanks

jcl5

 

[lbass]

You cannot nest subreports within subreports.

But, if you mean you have a crosstab in the subreport report footer and you are placing the subreport in the report footer of the main report, then you must create the same parameters in the subreport as in the main report, set up record selection formulas using the parameters in the subreport, and then link the subreport to the main report by linking the two parameters to each other. In the subreport linking screen, be sure to use the dropdown in the lower left corner to select {?your parm}, NOT the default {?pm-?your parm}.

-LB

 

[jcl5]

Thanks LB - this is what I'm trying to do and all parameters are the same. As I said before it works fine if I select one of the criteria from the list so demonstrates the links are set up correctly - it's only when selecting the 'All' or multiple criteria that doesn't work.

Thanks
jcl5

 

[lbass]

Please copy the record selection formula from the subreport into the thread.

-LB

 

[jcl5]

Hi lb,

The selection formula for the container report is:-

{VW_MODULE_MO.M_REFERENCE} = {?ModuleRef} and
(
if {?Resit} <> "All" then
{VW_GRADESET.G_REFERENCE} = {?Resit} else
if {?Resit} = "All" then
true
)

The selection criteria for the sub-report is:-

{VW_MODULE_MO.M_REFERENCE} = {?ModuleRef} and
(
if {?Resit} <> "All" then
{VW_GRADESET.G_REFERENCE} = {?Resit} else
if {?Resit} = "All" then
true
)

The change subreport links says:-

VW_MODULE_MO.M_REFERENCE = ?ModuleRef
VW_GRADESET.G_REFERENCE = ?Resit

My results when choosing All are:-

Mark Range
10-19 2
Total 2

Grade Summary
Fail 2
Total 2


When I choose Pass, I get

Mark Range
40-49 3
50-59 11
60-69 13
70-79 3
Total 30

Grade Summary
Pass 30
Total 30

So, to sum up, in this example, ALL should be a total of Pass + Fail which should be 32.

Many thanks for your assistance with this.

jcl5

 

[lbass]

In the subreport links you should be linking the parameters from the main report and the subreport to each other, not to fields.

-LB

 

[jcl5]

Thanks so much LB, can't believe I am such an idiot - couldn't see the wood for the trees!!