Advanced record selection formulas

[willisa]

I currently have the record selection formula below in a report, if basically means if the choice All has been selected then it wont filter the records other wise it will fiter by what was choosen.
Becasue there are two variables I have to stipulate each case that can happen, hence the four formulas.

I now have another report where I have about 5 choices and want to write a similar formula (but would end up with a ton of cases) and I was wondering is there another way.

THE QUESTION IS
The only idea I can think of (but am not sure if it can be done) I can build a string (the easy bit) and then execute that string. Or set the record slection formula to that string.
Is this possible?????


if {?Organisation} = 'All' and {?Month} = 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}

else if {?Organisation} = 'All' and {?Month} <> 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
and {employee_full_time_summary.proc_per_name} in {?Month}

else if {?Organisation} <> 'All' and {?Month} = 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
and {employee_full_time_summary.organisation} in {?Organisation}

else if {?Organisation} <> 'All' and {?Month} <> 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
and {employee_full_time_summary.organisation} in {?Organisation}
and {employee_full_time_summary.proc_per_name} in {?Month}

 

[dgillz]

Please explain what you mean by 5 choices. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[willisa]

In the example above there is two parameters making up the record selection formula, however the one I am trying to do is based on five parameters.
So I am really looking for another way of writing this sort of record selection formula.

 

[Naith]

Nesting your IFs like that is not going to work the way you think it is.

Each ELSE IF clause is a clause of the most recent IF, not of the main IF. If you don't follow what I mean, it may help you to envisage what I'm getting at by using indentations for ease of reading:

if {?Organisation} = 'All' and {?Month} = 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
else
if {?Organisation} = 'All' and {?Month} <> 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
and {employee_full_time_summary.proc_per_name} in {?Month}
else
if {?Organisation} <> 'All' and {?Month} = 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
and {employee_full_time_summary.organisation} in {?Organisation}
else
if {?Organisation} <> 'All' and {?Month} <> 'All' then
{employee_full_time_summary.fiscal_year} = {?Fiscal Year}
and {employee_full_time_summary.organisation} in {?Organisation}
and {employee_full_time_summary.proc_per_name} in {?Month};

You would have to close each IF statement with a ; and drop all the ELSEs.

Probably a better, and certainly shorter solution would be to lose all your repetition. By catching all your scenarios in one IF statement with the use of multi-ANDs, it would be easier to do something like this, wouldn't it?

If {?Organisation} <> 'All'
Then {employee_full_time_summary.organisation} in {?Organisation}
Else True and
If {?Month} <> 'All'
Then {employee_full_time_summary.fiscal_year} = {?Fiscal Year}
Else True;

You can test this out for yourself. You might also like to check your SQL is passing to the database too.

Good luck with your report,

Naith

 

[willisa]

okay I have used this as my record selection formula, however I am not getting the result I want. If more than one of them is not &quot;All&quot; then it only filters by the first one thats not all and doesn't filter by the rest. Can you see where I am going wrong?


if {?employee_office} <> &quot;All&quot; and {@f_Grouped_By} = &quot;Employee&quot; then
{stp_get_filtered_portfolio.employee_organisation} in {?employee_office}
else
TRUE

and

if {?engagement_office} <> &quot;All&quot; then
{stp_get_filtered_portfolio.engagement_organisation} in {?engagement_office}
else
TRUE

and

if {?engagement_type} <> &quot;All&quot; then
{stp_get_filtered_portfolio.engagement_type} in {?engagement_type}
else
TRUE

 

[synapsevampire]

Share the SQL Statement being generated.

Database->Show SQL Query

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[willisa]

Because its based on a stored procedure its not showing that stuff on the record selection formula.


EXEC anz_dw.dbo.stp_get_filtered_portfolio_no_responsibility 'Engagement_Organisation', 'All', 'All', 1

This is the stock standard stored procedure, and the three parameters that I am using to filter further are actually in the report.

 

[willisa]

The wierd thing if I put in the following (take out the ifs)and make the selections this works. Which is what the resulting record selection formula wouldbe

TRUE

and

{stp_get_filtered_portfolio.engagement_organisation} in {?engagement_office}


and
{stp_get_filtered_portfolio.engagement_type} in {?engagement_type}