Is this Report Condition Correct?

Scott_Hall
Participant V

Report (built on a slice), “for each row in table”.

If This is True Condition is [Date Next Service] = Today()

The report is built on a slice that sends an email for each service that is scheduled to occur for that day. (Sends report at 3AM)

The report is firing but is sending all scheduled services including those in future days, not just today.

This is puzzling to me. Can’t figure out why this is happening unless I just still don’t understand reports correctly.

0 17 597
17 REPLIES 17

Steve
Participant V

Because of a quirk with the is-equal-to operator (=), if the left-side value is blank, the expression will be TRUE regardless of the right-side value. To avoid this quirk, ensue the left-side value is never blank. In your case, try swapping the operands:

Today() = [Date Next Service]

Another thing to consider is the data type. if [Date Next Service] is of DateTime type, it will only be equal to TODAY() of Date type if the Time portion is 12:00:00 AM - i.e. midnight. Basically, Date data types are treated as if the Time is midnight.

You can easily fix that by using the DATE() function like this:

TODAY() = DATE([Date Next Service])

@WillowMobileSystems Thank you. The column type is just “Date” so that wasn’t a factor however your work around is helpful as I’ve run across that issue at other times and didn’t know there was a solution. Thank you for sharing! That expression will be in my snippets for future use.

Happy New Year.

@Steve In the expression I was using the left side value would have been [Date Next Service] and records included in the report were not blank…just for a future date. So not sure what to make of that.

Having said that I’ve changed the expression accordingly and running a test for tomorrow.

Happy New Year!

@Steve So the expression change did not fix the issue. The report is still returning everything in the slice instead of just the items for today.

Report is built on slice of table “Location” called “Schedule Slice”
The row filter condition for the slice is Today() <=[Date Next Service]
The slice is functioning correctly and showing what would be expected.

Report fires every day at 3am for table “Schedule Slice”
Report is set to run for “EachRowinTable”
If this is true condition to check is set to Today() = [Date Next Service]

I checked and the column [Date Next Service] is included in the slice.

Totally puzzled. If you have any thoughts it would be greatly appreciated.

The rows included in the report are determined by the <<Start>> tag in the report itself. The If this is true condition in the workflow configuration determines whether the report itself should be generated at all, not what is in the report.

You need to restrict your slice. Change your slice to say AND([Date Next Service]>TODAY(),[Date Next Service]<TODAY()+1). Then it will only send off today’s appointments in your report.

@Spencer_Jones It was determined that [Date Next Service] is of Date data type, so using “=” should suffice.

For the slice, Scott did mention he’s getting the rows expected:

If the report is showing future days then it can only mean that future days are part of the slice. Right? I understand that Scott is using that expression to use that for the trigger instead of the slice conditions. @Scott_Hall, would you be able to go to the slice, click on “View Table” and provide a screenshot of the [Date Next Service] column? That will show us everything that is supposed to fire off in that report according to the conditions of the slice.

First, Scott, have you checked for any reported errors on the running of the Report rule? You can look for these by expanding the report and then clicking on the Log button. Look for your report run record and if its colored red then open the details to see what the error is.

@Steve
The report is scheduled at 3AM which is the NEXT day. I am curious as to when the server actually updates the slice for the calendar date change? Will the system automatically refresh the slice in preparation for the Report rule to run?

Beyond the above questions, I do have a few things to try:

  1. Assuming no errors found, remove the condition on the report just to see if it runs and produces at all.
  2. Change the Report to run against the base table instead of the slice still using `TODAY() = [Date Next Service]’. This is effectively the same as when using the slice as far as rows selected. Does a report run then?

Right there is the problem. It’s showing ALL service dates that are Greater or equal to today. Tomorrow is greater than today. That’s why you want the slice condition to be AND(TODAY()<=[Date Next Service],TODAY()+1>=[Date Next Service])
This will correct your slice, therefore fixing your report.

As I understand it, the report is to show CURRENT day records. The Slice is picking up CURRENT and FUTURE [Date Next Service] dates.

Scott confirmed the slice has the records he expects.

The issue is the Report does not appear to run and uses the Slice as the source with a criteria of Today() = [Date Next Service] as noted below from a prior post.

Assuming all syntax is correct, The big question is why isn’t the report identifying TODAY’s records?

I say, see if the log say absolutely anything at all about it. What does it look like if we hit the test button on the report filter? What about possibly creating a completely new slice just for this report using the expression suggested by @Spencer_Jones?

@Steve @WillowMobileSystems @Spencer_Jones

**Problem solved. Start expression was wrong. Changed to **

<<Start:SELECT (Location[Location Key], [Date Next Service]=Today())>>

Apologies for the bru-ha-ha on New Year’s Day. Back to football

Happy New Year to all!

Good catch but something still seems off. I saw your Start expression before you edited the post. If anything, you should have seen MORE records than expected - not none. The change you made simply filters to a smaller subset. But if it works…!!!

There is one very important note to make about your Report Rule. When you use the option “EachRowinTable”, a separate report will be generated for each row matching the Report Rule condition. But due to the Start expression, each of those reports will be identical.

It seems your intention is to have all matching rows in a single generated report. For that you’ll want to change the option to “ForEntireTable”. Your Start expression in the template then chooses the records to be on the report.

@WillowMobileSystems My issue all along had been records from the future being included (too many) in the report. I had used the same start expression I used for the slice which was intended to show today and future scheduled services. The start expression for the report just needed to be set to “=” instead of “>=” and the problem was fixed. Chalk it up to my continuing education.

LOL! Somehow, over the days, my mind translated your problem as getting nothing on the report! It WAS the holidays. I’ll chalk it up to too much Egg Nog!

Glad you were able to resolve it in spite of us meddling kids!

Top Labels in this Space