I have a report that I would like to sum on consecutive dates and show that total, then restart the sum and begin again within the same MANum group .

The report looks like this

GH #1 County

GH #2 Service code

GH #3 MANum unique Id contains the reset formula ttlunits:= 0

Details fields: Number of Units, rate, begin date and the below formula

GF #3 MANum contains sum of all units and the sum of consecutive units

GF #2 Service Code

GF #1 County

example output:

7/1/2023 10 units

7/2/2023 5 units

group of 15 units for date range for same MANum

gap

7/5/2023 5 units

7/6/2023 3 units

Group of 8 units for date range for same MANum

I use this formula in the details section:

whileprintingrecords;

numbervar ttlunits;

if onfirstrecord

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date} = previous({Billings.Begin Date}) +1 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday)=1 and

{Billings.Begin Date} = previous({Billings.Begin Date}) +3 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date} = previous({Billings.Begin Date}) + 1 and

{Billings.Begin Date}<> next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date}<> previous({Billings.Begin Date}) + 1 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if {Billings.Begin Date} <> next({Billings.Begin Date}) - 1

then (

ttlunits:= 0;

)

else

if onlastrecord

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

ttlunits:= {Billings.Number of Units}

numbervar ttlunits;

if onfirstrecord

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date} = previous({Billings.Begin Date}) +1 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday)=1 and

{Billings.Begin Date} = previous({Billings.Begin Date}) +3 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date} = previous({Billings.Begin Date}) + 1 and

{Billings.Begin Date}<> next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if not onlastrecord and

dayofweek({Billings.Begin Date}, crSunday) in [1 to 7] and

{Billings.Begin Date}<> previous({Billings.Begin Date}) + 1 and

{Billings.Begin Date} = next({Billings.Begin Date}) - 1

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

if {Billings.Begin Date} <> next({Billings.Begin Date}) - 1

then (

ttlunits:= 0;

)

else

if onlastrecord

then (

ttlunits:= ttlunits+{Billings.Number of Units}

)

else

ttlunits:= {Billings.Number of Units}

For the most part this formula works but there are some anomolies IE:

Units date formula calculation

24 7/1/2023 24

25 7/2/2023 49

gap

24 7/5/2023 73

24 7/6/2023 97 should be 48

Any help would be greatly appreciated and thank you in advance.

Steve

I have some basic formulas I use for reporting that don't seem to work for all.

The two fields below can have null values in them. The default is null, if checked, value is "Y", if checked and then unchecked, the value is "N":

{CI_Item.UDF_PK_ORDERBYKIT}

{CI_Item.UDF_PK_ORDERBYSWK}

I'm doing a very simple calculation for

if {CI_Item.UDF_PK_ORDERBYSWK} = "Y" then ({SO_SalesOrderDetail.QuantityOrdered} - {SO_SalesOrderDetail.QuantityShipped})*{CI_Item.UDF_PK_QTYPERSHRINK}

else if {CI_Item.UDF_PK_ORDERBYKIT} = "Y" then ({SO_SalesOrderDetail.QuantityOrdered} - {SO_SalesOrderDetail.QuantityShipped})*{CI_Item.UDF_QTYPERBOX}

else ({SO_SalesOrderDetail.QuantityOrdered} - {SO_SalesOrderDetail.QuantityShipped})

Most of the items in my report will work, but will make any new items

if also created null value type formulas to force the N and Y and substitute them into the above formulas and have the same result. I've used this in reports over and over and have no idea why this doesn't work here.

My null value type formulas look like this:

OrderByBoxIsNull:

if isnull({CI_Item.UDF_PK_ORDERBYKIT}) then "N"

else If {CI_Item.UDF_PK_ORDERBYKIT} = "N" then "N"

else "Y"

OrderBySWKIsNull:

if isnull({CI_Item.UDF_PK_ORDERBYSWK}) then "N"

else If {CI_Item.UDF_PK_ORDERBYSWK} = "N" then "N"

else "Y"

When I put these fields in the report they show up correctly on 90% of the items, but the rest show 0.

What am I missing? Thanks for the help!

I have limited knowledge of what the Exceptions for Nulls selection does. Currently, that is what is selected in the formula editor.]]>

Hi

I need to set a formula that in sql is like that

select * from anagart where codiceart not in (select code from historycode)

in order to have a list of codart..

How can i do that?

thanks

]]>
Good morning, an employee is attached to a particular cost centre code in our org. If they move to different department they are attached to new cost centre code.

Cost centres and the effective date are stored in two separate tables.

I have tried to use Maximum({Eff_Date}) but this gives the error "Boolean required"

I have a group for the cost centre code and one for the effective date but I cant seem to get a formula working using "Maximum" Does anyone have experience with this?]]>

note:

Total Visit using formula

Hi,

I have a report as below.

--------------------------------

Group1 dateadm

Group2 episodeno 1 total visit=2 (AM, PM)

serviceitem 1 morning (AM)

serviceitem 2 morning (AM)

serviceitem 1 afternoon (PM)

serviceitem 2 afternoon (PM)

Group2 episodeno 2 total visit=1 (AM)

serviceitem 1 morning (AM)

Group2 episodeno 3 total visit=1 (PM)

serviceitem 1 morning (PM)

serviceitem 2 morning (PM)

-----------------------------------------

I like to summarize for the total visit in group dateadm.

so, it will be like this:

Group1 dateadm

Group2 episodeno 1 total visit=2 (AM, PM)

serviceitem 1 morning (AM)

serviceitem 2 morning (AM)

serviceitem 1 afternoon (PM)

serviceitem 2 afternoon (PM)

Group2 episodeno 2 total visit=1 (AM)

serviceitem 1 morning (AM)

Group2 episodeno 3 total visit=1 (PM)

serviceitem 1 afternoon (PM)

serviceitem 2 afternoon (PM)

=======================================

How to achieve it?

thanks in advance.

emir_w

Edited by Emir_W - 05 Oct 2023 at 2:24pm]]>

Because all of the data is joined to the fleet ID and there is no apparent relationship between PO, Service Order, and Sublet other than the Fleet ID, you'll need to use subreports for this. Here's how I would set up the data:

1. Main report - Group by fleet ID and show the PO data in the group header.

2. Add a fleet ID group header section below the one where the PO data is located. In that section add a subreport that shows the Service Orders and use the fleet ID as a subreport link to get the service orders for the specified fleet ID.

3. Add a third group header section and put a subreport that shows the Sublet Reference data, using the fleet ID as a subreport link to show just the sublets for the current fleet ID.

-Dell

]]>

Add the Employee table to your report and create a left outer join

-Dell]]>

Actually, you could use a cross-tab in the report footer to get this, the Row field is the product and the column is the sum of the total. If you suppress the column and row grand totals, you should get what you're looking for.

-Dell]]>

When you place a subreport in a section that contains other data, the space for the subreport is reserved. The best way to resolve your issue would be to create two new group header sections and place a subreport in each of them. Make sure that "Suppress if blank" is turned on for the subreports AND for the sections that they're located in.

-Dell]]>