ACS Commons Page Report - Add Date Range Field

[carbfrze_crown]

Hello,

 I am wanting to build my own Page Report. The default Page Report is almost what I need. However, I am looking to add a Date Range field or a Date field that I can enter a date and it will only return results that are Newer than that date. 

Any guidance is appreciated. 

The purpose of this report is that we need to send a list of pages we have published to our management team weekly. 

Thanks

[konstantyn_diachenko]

Hi @carbfrze_crown ,

Actually, ACS Commons Report tool is very powerful tool. It's easy to create new report.

1) Open Tools -> ACS AEM Commons -> Reports

2) Create new report (Enter title and click + Add report)

3) Click Edit (pencil) and copy configuration from Page report to your newly created report. Hint: Use edit toolbar copy action.

4) In the configuration modify query to:
SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([{{path}}]) AND [jcr:content/jcr:created] >= CAST('{{date}}' AS DATE)

5) In the search parameters section add new ACS Commons Report Builder Basic Parameter

• Label: Date
• Name: date
• Required: true
• Field type: Date Picker

6) Clean up other parameters. Leave only path. 

7) Now you are able to generate you own report:

Let me know if you have any questions.

Best regards,

Kostiantyn Diachenko.

[carbfrze_crown]

Thanks for the suggestion. However, these parameters return blank results no matter what date I choose. 

Is there a way to have a date range? Or perhaps a starting date and then an ending date? 

[konstantyn_diachenko · Accepted solution]

Hi @carbfrze_crown ,

Sometimes fetching results takes some time. You can introduce 2 field: start data and end date.

You query should look like: SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([{{path}}]) AND [jcr:content/jcr:created] >= CAST('{{startDate}}' AS DATE) AND [jcr:content/jcr:created] < CAST('{{endDate}}' AS DATE)

Using Explain Query, check that this query is covered by any Oak index.

Best regards,

Kostiantyn Diachenko.

[carbfrze_crown]

Thanks for the updated query. This worked for what I was needing. 

Thanks!

[Shashi_Mulugu]

@carbfrze_crown please review the solution provided and mark it as correct if it suits. If you have find solution yourself.. please post it here for Community reference. Thank you for your cooperation.