Report Based on User Defined Timeframe

Hello,

I would like to give my users the ability to create and print a report of Timesheets based on user defined timeframe.

For Example: A user would be able to select a “Date From” Dec 1,2019 and Then a “Date To” Dec 31, 2019 and then a report would be generated showing all timesheets that are within the month of December. Then they would be able to print this report.


The last virtual column in the screenshot above is where I would like all items that are matching the criteria set by the user to be ( I just have a fake formula for the time being).

I was able to find a sample app that shows a basic principle of what I am trying to accomplish.

https://www.appsheet.com/samples/Reports-that-users-can-customize?appGuidString=69a02679-bfe6-45fa-8643-58a7d28cfa1b

In the column Matching Items in the Report Request table there is this formula:

SELECT(Data[Name], AND(IN([Size], [_THISROW].[Size Choice]), IN([Color], [_THISROW].[Color Choice])))

However instead of it using colours and sizes I want it to show all matching items that are within the dates that the user would select.

Any help with accomplishing this would be greatly appreciated.

Thanks,
Josh

Hi Josh, you could write it directly into your template like <<Start: SELECT(Timesheets[KeyColumn],AND([_THISROW].[Start]>=[Date],[_THISROW].[End]<=[Date]))>>

Hi,

This is as far as I got with the formula. There is something wrong still.

This is the columns from the timesheet table


In which there is a “Date” column.

These are the columns from the Email Timesheet table.

I would also like to further narrow the results by employee.

Hi @Josh_Klassen
Is that your Email or your template?

Correct formula is probably…
<<SELECT(Timesheet[Timestamp],AND([Date]>=[_THISROW].[Date From],[Date]<=[_THISROW].[Date To],[Name]=[_THISROW].[Employee]))>>
This will give you a list of Timestamps. If you need to show those records on a table, you should use Start: & End formula as well.

I was able to successfully make this formula work:

However the problem I now have is the PDF creates the rows in order of when they were added and not in order of the date:

Would there be any way to fix this?

Thanks,
Josh

You need to use ORDERBY() expression with it, like…

<<ORDERBY(SELECT(Timesheet[Timestamp],AND([Date]>=[_THISROW].[Date From],[Date]<=[_THISROW].[Date To],[Name]=[_THISROW].[Employee])),[Date],FALSE)>>

2 Likes

Just Incase anyone reads this post:

This is what the template ended up looking like:

1 Like