Skip to Content
0

Date Diff in Detail on Grouped Report

Mar 02 at 06:47 PM

29

avatar image
Former Member

Have a report with (5) groupings, (3) of which are suppressed, the detail section is not suppressed. I need to return the number of days between today and the max date found in the UPCDateFinish field, where Production Complete = 0. Grouping on jmpJobID and others as shown below.

The (4) fields and (3) tables I'm working with are "Jobs.jmpJobID", "JobOperations.jmoProcessID", "JobOperations.jmoProductionComplete", "UPortalClocks.UPCDateFinsh".

Example data would be:

JOBID.....ProcessID.....UPCDateFinish.....ProductionComplete (Boolean 0/-1)
123.....010.....3/1/2018.8:02am.....-1

123.....020.....3/1/2018.8:15am.....-1

123.....030.....3/1/2018.8:25am.....-1

123.....040.....(null).....0

456.....010.....3/1/2018 8:03am.....-1

456.....020.....3/1/2018 8:17am.....-1

456.....030.....(null).....0


Design View
Print Preview

printpreview.jpg (176.6 kB)
designview.jpg (736.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Ian Waterman Mar 05 at 08:47 AM
0

Should be able to create a formula

If jmoProductionComplete = 0 then

Datediff("d", today, maximum({UPCDateFinish}, {jmpJobID})

else 0

Ian

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

This formula does return the datediff needed. However, it also returns multiple rows of the same data, equal to the number of occurrences of the field UPDateFinish containing a date.

datediff.jpg (181.6 kB)
0

Can't see why a formula would cause data to duplicate unless it is over riding some sort of suppression condition.

You will need to check original SQL, select statement and investigate section experts to see why data is duplicating

Ian

0