Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Changing User Prompts to Default Values.

I have a report that has 2 user prompts.  The first one asks users to enter a code, and the second one asks users to specify how many records they want to see.  I want to make this report automated so that users are not prompted for anything: ideally, the report will bring in all of the codes and limit the results to 10 records per code.  I have pasted the code below.  Can anyone help me with the necessary changes?

with hstfldata as (select a.rmsfilenum, a.hstoldvalu as prerecallsts, a.hstnewvalu as recallsts, a.rmstrandte as recalldate, a.rmsbalance as recallbal,

b.HSTNEWVALU as AGCYRESP, b.rmsbalance as respbal, b.rmstrandte as RESPDATE, c.hstnewvalu as agency, MISDDESC,

RAND(int(REPLACE(CHAR(CURRENT DATE, ISO),'-',''))) AS RANDNUMGEN, C.RMSTRANDTE as plcdate

from reporting.rhistfl as a

left join reporting.rhistfl as b on a.rmsfilenum = b.rmsfilenum and a.hstnewvalu = b.hstoldvalu and b.rmstrandte >= a.rmstrandte

left join reporting.rhistfl as c on a.rmsfilenum = c.rmsfilenum and c.rmstrandte <= a.rmstrandte

left join reporting.RSASRECV as s on c.hstnewvalu = s.RMSRECVRCD

where a.RMSTRANCDE = 'MT' And a.RMSFLDIMPL = 'RMSSTATUS' And

a.rmstrandte > Replace(Char(Current Date - 90 days, ISO),'-','') and a.HSTNEWVALU in ('A14','A15','A24','A26','A44','A45','U01','U02','U03','U04','U05','U06','U07','U08','U09','U10',

'U11','U12','U13','U14','U15','U17','U18','U19','U20','U21','U22','U23','U24','U25','U29','U70',

'U71','U72','U73','U74','U75','U76','U77','U78','UU1','UU2','UU3','UU4'

) and

b.RMSTRANCDE = 'MT' And b.RMSFLDIMPL = 'RMSSTATUS' And

b.rmstrandte > Replace(Char(Current Date - 90 days, ISO),'-','') and b.HSTOLDVALU in ('A14','A15','A24','A26','A44','A45','U01','U02','U03','U04','U05','U06','U07','U08','U09','U10',

'U11','U12','U13','U14','U15','U17','U18','U19','U20','U21','U22','U23','U24','U25','U29','U70',

'U71','U72','U73','U74','U75','U76','U77','U78','UU1','UU2','UU3','UU4'

) and

c.RMSTRANCDE = 'MR' AND c.RMSFLDIMPL = 'ATTRNYCODE' and c.hstnewvalu in ({?AGCY_CODE})

Order By RandNumGen

Fetch First {?#_of_Accts} Rows Only),

MaxValue as (select rmsfilenum, max (plcdate) as maxplacedate

From hstfldata group by rmsfilenum),

FinalData as (select a.* from hstfldata a join MaxValue b on a.rmsfilenum = b.rmsfilenum and a.plcdate = b.maxplacedate)

select RMSSTATECD As "State Code", MISDDESC, agency, rmsacctnum, CASE

WHEN RMSCUSTTYP = 'I'

THEN

CASE

WHEN

Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM)

) <> ','

THEN

Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM))

ELSE

''

END

ELSE

RMSCMPNAME

END AS "DEBTOR NAME", recallbal, RECALLSTS, DATE (    SUBSTR ( DIGITS (recalldate) , 1 , 4 ) || '-' ||

    SUBSTR ( DIGITS (recalldate) , 5 , 2 ) || '-' ||

    SUBSTR ( DIGITS (recalldate) , 7 , 2 ) ) as recalldte, AGCYRESP, DATE (    SUBSTR ( DIGITS (RESPDATE) , 1 , 4 ) || '-' ||

    SUBSTR ( DIGITS (RESPDATE) , 5 , 2 ) || '-' ||

    SUBSTR ( DIGITS (RESPDATE) , 7 , 2 ) ) as RESPDTE

from FinalData as a

left join reporting.rmaster as b on a.rmsfilenum = b.rmsfilenum

left join reporting.rcomker as c on a.rmsfilenum = c.rmsfilenum

WHERE

recordtype =  'D'

Former Member
replied

Hi Dee,

See if this helps:

with hstfldata as (select a.rmsfilenum, a.hstoldvalu as prerecallsts, a.hstnewvalu as recallsts, a.rmstrandte as recalldate, a.rmsbalance as recallbal,

b.HSTNEWVALU as AGCYRESP, b.rmsbalance as respbal, b.rmstrandte as RESPDATE, c.hstnewvalu as agency, MISDDESC,

RAND(int(REPLACE(CHAR(CURRENT DATE, ISO),'-',''))) AS RANDNUMGEN, C.RMSTRANDTE as plcdate

from reporting.rhistfl as a

left join reporting.rhistfl as b on a.rmsfilenum = b.rmsfilenum and a.hstnewvalu = b.hstoldvalu and b.rmstrandte >= a.rmstrandte

left join reporting.rhistfl as c on a.rmsfilenum = c.rmsfilenum and c.rmstrandte <= a.rmstrandte

left join reporting.RSASRECV as s on c.hstnewvalu = s.RMSRECVRCD

where a.RMSTRANCDE = 'MT' And a.RMSFLDIMPL = 'RMSSTATUS' And

a.rmstrandte > Replace(Char(Current Date - 90 days, ISO),'-','') and a.HSTNEWVALU in ('A14','A15','A24','A26','A44','A45','U01','U02','U03','U04','U05','U06','U07','U08','U09','U10',

'U11','U12','U13','U14','U15','U17','U18','U19','U20','U21','U22','U23','U24','U25','U29','U70',

'U71','U72','U73','U74','U75','U76','U77','U78','UU1','UU2','UU3','UU4'

) and

b.RMSTRANCDE = 'MT' And b.RMSFLDIMPL = 'RMSSTATUS' And

b.rmstrandte > Replace(Char(Current Date - 90 days, ISO),'-','') and b.HSTOLDVALU in ('A14','A15','A24','A26','A44','A45','U01','U02','U03','U04','U05','U06','U07','U08','U09','U10',

'U11','U12','U13','U14','U15','U17','U18','U19','U20','U21','U22','U23','U24','U25','U29','U70',

'U71','U72','U73','U74','U75','U76','U77','U78','UU1','UU2','UU3','UU4'

) and

c.RMSTRANCDE = 'MR' AND c.RMSFLDIMPL = 'ATTRNYCODE')

Order By RandNumGen

Fetch First 10 Rows Only),

MaxValue as (select rmsfilenum, max (plcdate) as maxplacedate

From hstfldata group by rmsfilenum),

FinalData as (select a.* from hstfldata a join MaxValue b on a.rmsfilenum = b.rmsfilenum and a.plcdate = b.maxplacedate)

select RMSSTATECD As "State Code", MISDDESC, agency, rmsacctnum, CASE

WHEN RMSCUSTTYP = 'I'

THEN

CASE

WHEN

Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM)

) <> ','

THEN

Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM))

ELSE

''

END

ELSE

RMSCMPNAME

END AS "DEBTOR NAME", recallbal, RECALLSTS, DATE (    SUBSTR ( DIGITS (recalldate) , 1 , 4 ) || '-' ||

    SUBSTR ( DIGITS (recalldate) , 5 , 2 ) || '-' ||

    SUBSTR ( DIGITS (recalldate) , 7 , 2 ) ) as recalldte, AGCYRESP, DATE (    SUBSTR ( DIGITS (RESPDATE) , 1 , 4 ) || '-' ||

    SUBSTR ( DIGITS (RESPDATE) , 5 , 2 ) || '-' ||

    SUBSTR ( DIGITS (RESPDATE) , 7 , 2 ) ) as RESPDTE

from FinalData as a

left join reporting.rmaster as b on a.rmsfilenum = b.rmsfilenum

left join reporting.rcomker as c on a.rmsfilenum = c.rmsfilenum

WHERE

recordtype =  'D'

-Abhilash

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question