Selection Criteria needing to select a formula?

[brendanj68]

Hi,

Am running CR9 on SQL2K. My relatively straight-forward report uses a typical (user) selection criteria as follows:

{?Sort Choice} is 'By Region', By CPA Rating', etc All of which are defined as default values - works well, no problems. GH1 = {?Sort Choice}

{@Sort Choice} is set up as:
if {?Sort Choice} = 'By Region' then {MIS_Project_Personnel.LA_Region} else
if {?Sort Choice} = 'By Client Name' then {MIS_Project_Personnel.Company_Name} else
if {?Sort Choice} = 'By CPA Rating' then {MIS_Project_Personnel.CPA_Category} else
if {?Sort Choice} = 'By Business Unit' then {MIS_Project_Personnel.Project_Type} else
if {?Sort Choice} = 'By Date' then ToText ({MIS_Project_Personnel.Start_Date}, "dd-MM-yyyy") else
{MIS_Project_Personnel.Company_Name}

What now needs to be factored in is a selection based on a person's 'role'-in this case Project Manager. I have a very simple formula, which is used in the report (if {MIS_Project_Personnel.Role}= "Project manager" THEN
{MIS_Project_Personnel.person} ELSE ""). I tried to amend {@Sort Choice} to either utilse the formula (which did not work well and caused very erratic results. Additionally I tried an amended nested IF THEN, but had zero joy. Could you please let me know, if poss, what alternatives there are - as I can see this functionlity being requested alot over the next few weeks. Thanks in advance as always.

B

 

[Wallie12]

{@Sort Choice} is set up as:

if {?Sort Choice} = 'By Region' then
{MIS_Project_Personnel.LA_Region}
else
if {?Sort Choice} = 'By Client Name' then
{MIS_Project_Personnel.Company_Name}
else
if {?Sort Choice} = 'By CPA Rating' then
{MIS_Project_Personnel.CPA_Category}
else
if {?Sort Choice} = 'By Business Unit' then
{MIS_Project_Personnel.Project_Type}
else
if {?Sort Choice} = 'By Date' then
({MIS_Project_Personnel.Start_Date}, "dd-MM-yyyy")
else
if {MIS_Project_Personnel.Role}= "Project manager" THEN
{MIS_Project_Personnel.person}
else
{MIS_Project_Personnel.Company_Name}

 

[lbass]

It's a little unclear what you are trying to do, but if you want project manager to be another sort choice, then try:

if {?Sort Choice} = 'By Region' then
{MIS_Project_Personnel.LA_Region}
else
if {?Sort Choice} = 'By Client Name' then
{MIS_Project_Personnel.Company_Name}
else
if {?Sort Choice} = 'By CPA Rating' then
{MIS_Project_Personnel.CPA_Category}
else
if {?Sort Choice} = 'By Business Unit' then
{MIS_Project_Personnel.Project_Type}
else
if {?Sort Choice} = 'By Date' then totext
({MIS_Project_Personnel.Start_Date}, "dd-MM-yyyy")
else
if {?Sort Choice}= 'By Project Manager' THEN
(if {MIS_Project_Personnel.Role} = "Project Manager"
then {MIS_Project_Personnel.Role} else "")
else
{MIS_Project_Personnel.Company_Name}

-LB