Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to use the radio button with macro

Status
Not open for further replies.

laba1957

Programmer
Nov 3, 2007
3
US
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

 
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 ...
 
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 )
)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top