Intelligence - Custom Parameter for Date

[MaryS]

Hi,  I've created a report that's got a parameter that allows me to choose the date range so I can schedule this report to run by Sent for payment date:

• Yesterday
• Last Month
• Last Week
• Last Fiscal Year
• Current Fiscal to Date
• Last Calendar Year
• Current Calendar Year to Date

I want to add another choice to this list to allow me to run for the Prior 12 months, but I'm having trouble.  This is what I have, but it's not working.

WHEN 'Last12'
THEN ( [Sent for Payment Date] between
(_first_of_month(_add_months(getdate())-13 ) ) and
(_last_of_month(_add_months(getdate())-1))
)

Can someone out there help?

Thanks in advance!

[Felix · Accepted solution]

Hi Mary,

I'm quite interested in how you build the parameters using strings. Not familiar with that. Will be appreciated if you can further explain it a little. 🙂 

On testing your code, I found an error below:

WHEN 'Last12'
THEN ( [Sent for Payment Date] between
(_first_of_month(_add_months(getdate())-13 ) ) and
(_last_of_month(_add_months(getdate())-1))
)

Put the -13 and -1 into the second argument for _first_of_month and the codes run without error:

WHEN 'Last12'
THEN ( [Sent for Payment Date] between
(_first_of_month(_add_months(getdate(), -13) ) ) and
(_last_of_month(_add_months(getdate(), -1)))
)

The code example for _add_months:

Thanks!

Felix

[MaryS]

Thank you Felix, silly syntax error that works great.

To explain,  I created a filter in the report :

CASE (?ReportDate? )
WHEN 'PriorMonth'
THEN ([Expense].[Last Month (Based on Sent for Payment Date)] )
...
WHEN 'LastFY'
THEN ([Expense].[Last Fiscal Year (Based on Sent for Payment Date)])
WHEN 'Last12'
THEN ( [Sent for Payment Date] between
(_first_of_month(_add_months(getdate(),-13 ))) and
(_last_of_month(_add_months(getdate(),-1)))
)
ELSE ( [Sent for Payment Date] in_range ?Sent for Payment Date?)
END

Then on the Paramater page I added a Radio Group with Static Choices and also a date picker.  The user can select from the Radio group or choose a date range using the date picker.  This allows me to schedule monthly reports (paid in prior month).  I can now use the same report and schedule it to run annually.

Let me know if you have any other questions.

[Felix]

Thanks, Mary.  I add two parameters and build a prompt page per your description and it works perfectly. Another great and easy way to schedule the report! (just ignore the ugly looking of this prompt page...)

Thanks again! It's good to have you here.

Felix