Skip to Content
avatar image
Former Member

Date Diff in Detail on Grouped Report

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Mar 05 at 08:47 AM

    Should be able to create a formula

    If jmoProductionComplete = 0 then

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

    else 0

    Ian

    Add comment
    10|10000 characters needed characters exceeded

    • 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