parameters in SPs

[Eany]

I have XI, & my report accesses a SP with 2 dates @startdate & @enddate. My report needs to set @enddate to today, and @startdate to a year ago, so that I can compare dates in the last year.
How do I do this ?
I added 2 functions using CurrentDate & CurrentDate - 365, but couldn't pick them up when attempting to use a Selection Formula.
Also, how do I suppress the prompting of the 2 parameters from the SP ?
I do not need to enter values at run time, as the report, hopefully, will be setting the values.
I clicked on 'Edit Parameter' & set 'Prompt Text' to False, but this didn't work.
Many thanks in anticipation !

 

[PBE]

Hi
I do not know what an SP is but I do know that formulas fields can be picked up by selection formula. Perhaps if you created CurrentDate and CurrentDate-365 as two formulas fields these can be selected to give you your automatic date range.

 

[TheReportGuy]

I have XI, & my report accesses a SP with 2 dates @startdate & @enddate. My report needs to set @enddate to today, and @startdate to a year ago, so that I can compare dates in the last year.
How do I do this ?
I added 2 functions using CurrentDate & CurrentDate - 365, but couldn't pick them up when attempting to use a Selection Formula.
Also, how do I suppress the prompting of the 2 parameters from the SP ?
I do not need to enter values at run time, as the report, hopefully, will be setting the values.
I clicked on 'Edit Parameter' & set 'Prompt Text' to False, but this didn't work.
Many thanks in anticipation !

You could take two different approaches:
1) Create another stored proc which calls the sp that takes the two params and pass the calculated date values.

or

2) at the report level create two formulas in the main report. 1 which calculates current date and the other which calculates the year prior.
You need to create a sub report which uses the 2 param stored proc as its datasource then do a subreport link on the two params with their corresponding formulas.

-------
These two methods will do the trick. method 1 is probably the better and easiest approach.

 

[Eany]

Thanks ReportGuy. I adopted approach 2, but had great difficulty because I used "currentdate" in the formula. The link to the subreport didn't work, as the 2 parameters were not presented for linking at the bottom left of the screen. It was only when I changed to use "currentdatetime" that the parameters finally appeared for linking, and then it worked beautifully. Many thanks for your response.

 

[racer25]

Here is Something I use

The user has to key in date ranges but it would be very easy to adopt to make it formula based.

Local DateVar StartDateCurrYear;
Local DateVar EndDateCurrYear;
Local DateVar StartDatePriorYear;
Local DateVar EndDatePriorYear;

StartDateCurrYear:= {?Start Date Curr Year};
EndDateCurrYear:= {?End Date Curr Year};
StartDatePriorYear:= {?Start Date Prior Year};
EndDatePriorYear:= {?End Date Prior Year};

// Test Invoice is Current Year and Effective is less then End Date Selected of Current Year
if ({CIC_CLIENTINCOME.CIC_INVOICEDATE} >= StartDateCurrYear 
AND {CIC_CLIENTINCOME.CIC_INVOICEDATE} <= EndDateCurrYear 
AND {CIC_CLIENTINCOME.CIC_EFFDATE} <= EndDateCurrYear) then "CurrYear" 

// Test Effective is Current Year and Invoice is less then Start Current Year
else if ({CIC_CLIENTINCOME.CIC_EFFDATE} >= StartDateCurrYear
AND {CIC_CLIENTINCOME.CIC_EFFDATE} <= EndDateCurrYear 
AND {CIC_CLIENTINCOME.CIC_INVOICEDATE} < StartDateCurrYear) then "CurrYear"

// Test Invoice is Prior Year and Effective is less then End Date Selected of Prior Year
else if ({CIC_CLIENTINCOME.CIC_INVOICEDATE} >= StartDatePriorYear 
AND {CIC_CLIENTINCOME.CIC_INVOICEDATE} <= EndDatePriorYear 
AND {CIC_CLIENTINCOME.CIC_EFFDATE} <= EndDatePriorYear) then "PriorYear" 

// Test Effective is Prior Year and Invoice is less then Start Prior Year
else if ({CIC_CLIENTINCOME.CIC_EFFDATE} >= StartDatePriorYear
AND {CIC_CLIENTINCOME.CIC_EFFDATE} <= EndDatePriorYear 
AND {CIC_CLIENTINCOME.CIC_INVOICEDATE} < StartDatePriorYear) then "PriorYear" else "Other"