Grouping by parameter triggers additional parameter 1

[FishKiller]

LB this is probably for you. I am running CR9 and SQL2K. The report that I am tring to create allows the user to select each level of grouping and from their selection allows them to select exactly which values they want in the parameter.

This is what I have so far:
In the first group I have a formula that looks like this-

// GROUP 1 PICK FROM PARAMETER {GROUP1}
IF
{?Group1} = "Referring Physician"
then
{NDC_VR.REFERRING_PMIS}
ELSE
IF
{?Group1} = "Specialty Code"
then
{NDC_VR.SPECIALTY}
ELSE
IF
{?Group1} = "Referring Zip Code"
then
{NDC_VR.ADDR_ZIP}
ELSE
IF
{?Group1} = "Modality"
then
{NDC_VR.Modality}

This works fine. However some of these choices require additional filtering such as "Modality" I do not want all Modalities returned only those from a pick list and only when "Modality" is selected from {?Group1}.

I created a parameter called {?Select Modality} and added the following values "which come directly from {NDC_VR.Modality}"

2 HD, 3 HD, BD, CARD, CT, HOTLAB, MAM 1, MAM 2, MR1, MR2, PET HOT LAB, PET/CT, STEREO, US1, US2, X-RAY

I believe this is called an array [please excuse my ignorance for not understanding syntax]

If the user selects 3 HD and CARD I only want those records to be returned.

I was tring to do the following to the above sample with no success:

IF
{?Group1} = "Modality"
then
{?Select Modality}={NDC_VR.Modality}


Any help would be much helpful

 

[GJParker]

This should be added as part of the record selection formula.

i.e.

If {?Group1} = "Modality" Then
{NDC_VR.Modality} = {?Select Modality}
Else
True

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[FishKiller]

Thanks for the post sorry about the delay on getting back to you. When I use the code below I get an error message which indicates it must be a string. I rechecked the parameter's {?Group1} and {?Select Modality} to ensure they are both set to string. Any suggestions?

If {?Group1} = "Modality" Then
{NDC_VR.Modality} = {?Select Modality}
Else
True

 

[Turkbear]

Hi,
Where is it indicated a string is needed - If at the
'True' line then leave it off..

If {?Group1} = "Modality" Then
{NDC_VR.Modality} = {?Select Modality}

The results of a formula must all be of the same type and True is a boolean..





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

 

[FishKiller]

OK, Thanks for the help. Let me attempt to explain what is happening:

When I use only:

If 
{?Group1} = "Modality" 
Then
{NDC_VR.Modality}

Crystal formula works correctly returning no errors as it should.

When I attempt to change the last line {NDC_VR.Modality}={?Select Modality} I get "A string is required here."

IF
    {?Group1} = "Modality" 
    then
    {NDC_VR.Modality}={?Select Modality}

when I tried to use GRPARKER's post by adding to the end of the above code "Else True" I get the same error "A string is required here."

IF
    {?Group1} = "Modality" 
    then
    {NDC_VR.Modality}={?Select Modality}
Else True

I tried to remove the "Else true" per "Turkbear's" advice and I get the same error. "A string is required here."

*** On all of the errors Crystal Formula Editor highlights the entire If then.. statement.

- Fish

 

[lbass]

I had no problem setting this up in the formula editor:

IF
{?Group1} = "Modality"
then
{NDC_VR.Modality}={?Select Modality}
Else True

I think you need to double check the datatypes of both the parameters and the field.

-LB

 

[FishKiller]

All of the data types match. I did some changes that seem to work however, not the way I want them I think I am going to have to use a custom front end. I am tring to get the parameter to only show depending on the {?Group 1} answer.

EX: Group1 = "Modality" then a second parameter shows listing which modalities to list.

This is what the orginal code looked like:

// GROUP 1 PICK FROM PARAMETER {GROUP1}
IF
    {?Group1} = "Referring Physician" 
    then
    {NDC_VR.REFERRING_PMIS}
ELSE
IF  
    {?Group1} = "Specialty Code" 
    then
    {NDC_VR.SPECIALTY}
ELSE
IF
    {?Group1} = "Referring Zip Code" 
    then
    {NDC_VR.ADDR_ZIP}
ELSE
IF
    {?Group1} = "Modality" 
    then
    {NDC_VR.Modality}

This is what I moved/changed:

// GROUP 1 PICK FROM PARAMETER {GROUP1}
//the following section was listed on the bottom. the ; works but I think it is wrong. If I remove the ; I get an error stating "The remaining text does not seem to be part of the formula"

IF
    {?Group1} = "Modality" 
    then
    {NDC_VR.Modality}={?Select Modality}
Else True

;

IF
    {?Group1} = "Referring Physician" 
    then
    {NDC_VR.REFERRING_PMIS}
ELSE
IF  
    {?Group1} = "Specialty Code" 
    then
    {NDC_VR.SPECIALTY}
ELSE
IF
    {?Group1} = "Referring Zip Code" 
    then
    {NDC_VR.ADDR_ZIP}

Thank you everyone for the help.

 

[lbass]

Yes, you won't be able to get the modality parameter only to display when the group 1 parameter = "modality", but if modality wasn't chosen, any selected parameter options for modality would not be honored.

I think you are mixing grouping and record selection. Try adding a record selection formula like this:


IF
{?Group1} = "Modality"
then
{NDC_VR.Modality}={?Select Modality}
else true

Then create a formula for grouping:

IF
{?Group1} = "Referring Physician"
then
{NDC_VR.REFERRING_PMIS}
ELSE
IF
{?Group1} = "Specialty Code"
then
{NDC_VR.SPECIALTY}
ELSE
IF
{?Group1} = "Referring Zip Code"
then
{NDC_VR.ADDR_ZIP}
IF
{?Group1} = "Modality"
then
{NDC_VR.Modality}

-LB