dynamic filter using singleton

[kevind0718]

Hello All:

I have monthly reports that could be run in the current month, for instance if the month-end falls on the weekend.
Or run early in the next month , most likely on the first.   
So my query filter needs to figure out based on the current date  which month to look at.
If month end is run on the 30th  the period is 1st to last of current month.
If month end is run on the 2nd look at the prior month.

Lets say there are a half dozen query that need this query filter.

I wrote a bit of SQL that based on the current date to figure out the Begin and End dates.
This query returns a single record, so I believe I can pop the results into two singletons.

Can I reference these singletons from a query filter?
Or is there another clever way to apply the filters to the queries?

Thanks for your attention to this matter.

KD

[dougp]

You can't use a visualization in a filter.  That makes no sense.

It seems you have developed a code snippet.  Just paste it into the filters where you need it.

[BigChris]

Ok, depending on your requirements, you can probably build a filter statement along these lines:

day(current_date) < 10 and [YourDateField] between first_of_month(_add_months(current_date,-1) and last_of_month(_add_months(current_date,-1)
or
[YourDateField] between first_of_month(current_date) and Last_of_month(current_date)

You might need to check the syntax etc. - I've just thrown that together off the top of my head...but you should get the idea  :)