Report Based on User Defined Timeframe

[Josh_Klassen]

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-8...

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

[Aleksi]

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

[Josh_Klassen]

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.

[Lynn]

Hi @Josh_Klassen
Is that your Email or your template?

[Aleksi]

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.

[Josh_Klassen]

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

[Aleksi · Accepted solution]

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)>>

[Josh_Klassen]

Hi,

I would like to further the table by adding total number of hours and total number of overtime hours.

This is what I have done so far however it doesn’t work:

[Josh_Klassen]

Just Incase anyone reads this post:

This is what the template ended up looking like:

[Aleksi]

The << End >> needs to be after <<[Notes]>> and in the same cell as well. Now it’s in a wrong place.

[Josh_Klassen]

Okay now the table works again.

However my question is more how to make this formula work? Is there any way to see if there are errors in a formula when putting them into a email template like one would see in the Expression assistant?

I am looking to have a sum of all hours that are of regular time and a sum of all hours that are of overtime at the bottom of the sheet

SUM(SELECT(Timesheet[Number
Of hours],AND(([Date]>=[_THISROW].[Date
From].[Date]<[_THISROW].[Date]))))

Thanks again,
Josh

[Aleksi]

The workaround is to add a virtual column and test the formula there.