How to query for date ranges such as "last week" or "last month" ?

[BenAvdicevic]

Hi,

I am trying to write some queries that return objects modified or created  in specific time frames. For example:

• yesterday
• past 7 days
• past 30 days
• etc..

Right now, I only know how to query for objects created/modified today  using the "&CurrentDate" parameter.

For example, here is a query that returns all diagrams modified today

Select Diagram WHERE [Modification Date] >= "&CurrentDate"

Now, what I would like is to have a query that returns diagrams modified in "past 7 days" or "past 14" days etc..

Is there any special keywords for this in HOPEX, or are any calculations allowed and if so - how?

[CLAVALLEE]

Hello,

You need to have access to report Studio.
So a profile of Hopex Customizer, with a token MTS2 (I am not sure if other tokens give you access to this too)
In the menu Report studio, you can easily create queries implemented through macros, and write the macro itself.

[BenAvdicevic]

A follow up on Macros. 

How can SaaS customers develop macros?  Which profile/license is needed for that ?  

[BenAvdicevic]

@PBessodes Thank you!! this works now. I can now enter a date as query parameter.

[PBessodes]

Hello,

This query works...as soon as you have activated the "Display repository administration properties..." option :

Patrick Bessodes

[BenAvdicevic]

A quick followup @PBessodes @CLAVALLEE 

Do you know if we can use Date as a Request Parameter.

I envision something like this

SELECT Application WHERE [Modification Date] >= &"Date"

[BenAvdicevic]

@PBessodes @CLAVALLEE  Thank you both!
Good to know that macros is the way to go about this.

[PBessodes · Accepted solution]

Hi,

ERQL doesn't offer this kind of capability today. You can only use system variable : &CurrentDate

But you can implement a query by a macro.

Regards

[CLAVALLEE]

Hello,

As far as I know, you would have to create query with a macro implementation.
For example, for the implementation : 

Sub FillSelectionCollection(mgobjObject as MegaObject, vntSelectorID as Variant, mgcolCollection as MegaCollection)
  Dim mgRoot, col
  mgRoot = mgobjObject.getroot
  col = mgRoot.getSelection("Select [Diagram] where [Modification Date] > '" & CDate(Now-7) & "'")
  mgcolCollection.insert col
End Sub

Hope this can help.

[BenAvdicevic]

@PBessodes Are you able to offer any help on this question?

I am writing some queries - and would really like to be able to have a way to return results "from last week" or "last month" or something like that.