How to use the radio button with macro

[laba1957]

I'm trying to use radio buttons on my prompt page that will pass the parm value to my SQL.

I have the 4 separate buttons each with their own parm. Each button is set up with a collection like this

Button 1 ...

Use Display
1 VA

Button 2 ...

Use Display
1 PHS

etc...

The SQL where clause looks like this

WHERE I.item_id = CM.cat_map_id
AND CM.cat_map_id = CMF.cat_map_id(+)
AND CMF.FLD_NAME (+) = 'lowDispUnitsPerPack'
AND I.realm_num = 1024
AND
( ( #prompt('VA', 'STRING','')# = '1' AND I.ELIG_VA = 1 )
OR
( #prompt('PHS', 'STRING','')# = '1' AND I.ELIG_PHS = 1 )
OR
( #prompt('ASP', 'STRING','')# = '1' AND I.ELIG_ASP = 1 )
OR
( #prompt('MEDCAID', 'STRING','' )# = '1' AND I.ELIG_MEDICAID = 1 )
)

All the button are set to Required "No"

The problem is that when I execute the report all the buttons are showing as required and if I select one I can't deselect it.

I want to be able to tell if the user selects any combination of buttons and interigate them in my SQL .

I'm using ReportNet 1.1

Thanks

Steve

 

[yphilogene]

I hope my answer will help:

As you need to select a combination of choices, let's use a "Value Prompt" multi-select with a set a static choices: 'VA', 'PHS', 'ASP' (use value=display value).

Let's say this "Value Prompt" is linked to the parameter named ?choices?. This parameter is a string. I you choose 'VA' and 'PHS', ?choices?='VA,PHS'.

Therefore, you need to adapt the SQL WHERE clause:

...
#prompt('choices','STRING','')# like '%VA%' AND I.ELIG_VA = 1 )
OR
( #prompt('choices','STRING','')# like '%PHS%' AND I.ELIG_PHS = 1 )
OR
( #prompt('choices','STRING','')# like '%ASP%' AND I.ELIG_ASP = 1 )
OR ...

 

[laba1957]

This almost works. When I select more than one option I get an Oracle error message.

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

UDA-SQL-0446 Oracle returned an error message.

ORA-00920: invalid relational operator

UDA-SQL-0450 An unexpected error encountered during the "prepare" operation.

If I use just prompt I can only select one val;ue at a time. So I put in promptmany.

Any ideas would be helpful

Thanks

Steve



( ( (#promptmany('choices', 'varchar2')#) like '%VA%' AND I.ELIG_VA = 1 )
OR
( (#promptmany('choices', 'varchar2')#) like '%PHS%' AND I.ELIG_PHS = 1 )
OR
( (#promptmany('choices', 'varchar2')#) like '%ASP%' AND I.ELIG_ASP = 1 )
OR
( (#promptmany('choices', 'varchar2')#) like '%MEDICAID%' AND I.ELIG_MEDICAID = 1 )
)

 

[yphilogene]

Is it possible to check the generated SQL?

 

[laba1957]

I'll take a look at the generated SQL.