Report Expressions Make my Head Hurt

[Scott_Hall]

So you know, I’ve spent about 3 hours total looking into report expressions without much yield. I’m trying to generate a monthly report (Service Log). The table at play here is “Service Location Repair” and the Columns I need in the report are

[Timestamp]
[Service Location Name],
[Waste Type], [Waste Type 2] [Waste Type 3]
[Container Type] [Container Type 2] [Container Type 3]
[Qty] [Qty 2] and [Qty 3]

I’m trying to get the report to give me a each day waste is collected (omit if none) with a table layout format as per the screen shot. Any chance someone would be willing to help with what this report expression would look like? I feel like this is asking a lot but after a couple of months of weekends working on this thing I am almost done.

[Aleksi]

I’m afraid this is not possible directly because we don’t have grouping functionality at this moment. Though you could this if you create 31 separate IF & EndIf statements into your template. Not very nice solution, but doable.

[Scott_Hall]

Thank you @Aleksi What would an expression for this look like if I dumped the grouping and just went with a chronological log? The start expressions have been hard to pick up on. I’ve made a number of attempts with no luck. Thanks.

[Timestamp] [Service Location Name] [Waste Type] [Container Type] [Qty]

[Aleksi]

<<Start:
ORDERBY(
SELECT(Service Location Repair[YourKeyColumnName],
EOMONTH(DATE([Timestamp]),0)=EOMONTH(TODAY(),0)),
[Timestamp],FALSE)>>

[Scott_Hall]

@Aleksi With that expression I get an error “can’t find column ‘Timestamp’”. But I definitely have a column ‘timestamp’. Double checked that it is spelled correctly etc… Thoughts?

Workflow rule ‘Waste Log’ action ‘Waste Log’ Attachment template. Expression ‘Start:ORDERBY(SELECT(Service Location Repair[Key],EOMONTH(DATE([Timestamp]),0)=EOMONTH(TODAY(),0)),[Timestamp],FALSE)’ is invalid due to: Unable to find column ‘Timestamp’.

[Aleksi]

Are your table and key column names correctly typed?

[Scott_Hall]

@Aleksi I believe so. Screenshot. Am I missing something?

[Aleksi]

Please take a printscreen from your template.

[Scott_Hall]

@Aleksi

[Aleksi]

TIMESTAMP	COLUMN#1	COLUMN#2
<<Start:ORDERBY(SELECT(Service Location Repair[Key],EOMONTH(DATE([Timestamp]),0)=EOMONTH(TODAY(),0)),[Timestamp],FALSE)>><<[TIMESTAMP]>>	<<[Column#1]>>	<<[Column#2]>><< End >>

[Aleksi]

So… you need to add a table into your template.

[Scott_Hall]

Okay. So after the start expression, it begins to look more like a workflow template with <<[Column]>>

Thank you. I think I can manipulate this to my needs now.

[Aleksi]

You’re welcome

[Scott_Hall]

@Aleksi

The report that was designed (see up the thread) was fired on September 1st as planned. The email contained the individual records in long format but the PDF was blank. I attached a screen shot of the report expression. Any idea what would be wrong? The report is supposed to fire on the first of the month and include all records from the previous month.

I added one record for today as a test and ran the report manually. It returned in the email the same list of last month’s records in long format but this time included the test record from today in the PDF as the only record on the PDF.

[Aleksi · Accepted solution]

Instead of using “0” with the EOMONTH expression when checking today’s date, you should use “-1”. Because you are triggering the TODAY() on 9/1/2019 both values are like 8/31/2019 and 9/30/2019 and result is FALSE, your template is empty (execpt the record from 9/1/2019)

[Scott_Hall]

Always spot on. Thanks. You guys are awesome.

[Aleksi]

You’re welcome