New Topic

Community Topic Question Report Builder

Nathan Schroeder

NA Business Team

Assigned

Category Report Builder

Product Version 7.1

Dynamic Date Range Report Filter

Mohammad Rehman shared this question ago

Answered

Experts,

I am looking to create a date field filter between current date at 4:0PM to tomorrow 3:59PM it should be calculated when ever the report run. I need help to implement this.

Regards

Mohammad

The same question

Replies (1)

Nathan Schroeder ●

Hi Mohammad,

I think the best way to do this would be to create a custom query cached filter for your current date at 4:00. In mysql this would be:

timestampadd(HOUR, 16, CURDATE())

You can then set the default value to be "Max". For your next either link to filter, you can either link with a dynamic offset (+23 hours), or create a second custom query with +39 hours (from midnight on the current day).

Let me know if this makes sense.

Regards,

Nathan

Hi Mohammad,

I think the best way to do this would be to create a custom query cached filter for your current date at 4:00. In mysql this would be:

timestampadd(HOUR, 16, CURDATE())

Let me know if this makes sense.

Regards,

Nathan

Reply URL

Mohammad Rehman ●

Hi Nathan,

Appreciate as always for your prompt replies and help. I will try and let you know.

Regards

Mohammad

URL

Mohammad Rehman ●

Hi Nathan,

I probably in a Friday mode and brain is not working. Would you please send me steps to create this custom query filter. When I clicked on the Filter "Submit Date" field, i see this option. am I doing something wrong? How would i use Submit Date between 4:00PM to next day 3:59PM?

/a29BCQgAQlIQAISkIAEJCABCbSRgKK8jfAtWgISkIAEJCABCUhAAhKQgAR6m4CivLfb39pLQAISkIAEJCABCUhAAhKQQBsJKMrbCN+iJSABCUhAAhKQgAQkIAEJSKC3CSjKe7v9rb0EJCABCUhAAhKQgAQkIAEJtJGAoryN8C1aAhKQgAQkIAEJSEACEpCABHqbgKK8t9vf2ktAAhKQgAQkIAEJSEACEpBAGwn8H8jcodUU0FG+AAAAAElFTkSuQmCC

Regards

Mohammad

URL

Mohammad Rehman ●

Hi Nathan,

when I try to use the defined values and select Current Date + 16 hours and Current Date + 39 hours It does not work properly.

see screen shot.

/Aau7FXw1xlMJAAAAAElFTkSuQmCC

What I have noticed the Current Date in the defined values does not starts 00 hours it starts with current timestamp.

Regards

Mohammad

URL

Mohammad Rehman ●

Hi Nathan,

Just FYI, I am using BMC AR System and I am sure the direct database date function call will not work here.

Thanks

Mohammad

URL

Nathan Schroeder ●

Hi Mohammad,

If custom query is available to select, then it should be possible to use this.

I would suggest first obtaining the SQL statement from your report by clicking "View SQL" in the lower right hand corner.
Once this is done, you can go back to your custom query menu, and type the relevant freehand SQL function into the prompt area provided.
Test the query
If results are returned, the query was successful and click "Refresh Now"

4937452b3277ef58c991ed60549d47de

You can then link the sub-query to this date value.

Unfortunately if this will not work, I am not sure that there is another good way to achieve what you are looking for outside of possibly stored procedure view.

Regards,

Nathan

URL

Mohammad Rehman ●

Hi Nathan,

Thanks for your help and time. I will try which i believe BMC has prevented to do direct SQL query. You can mark the answer correct and close the thread.

Regards

Mohammad

URL

Nathan Schroeder ●

Hi Mohammad,

Sounds good, sorry for the trouble.

Nathan

URL