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.]]>

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?]]>

You have probably already figured this out.

I would have the user input be a paramater, and I would set the date range to be DATEADD("day", -6, @userInput)

This could be set to a variable or hopefully passed to procedure or used in the select clause. Whichever way you are retrieving your data.

It's been a while, I think that should work.]]>

shared variables would work...

Way too late I fear.

The basic idea is to reset the counters when the grouping changes...it looks like when 623458 changes.

The increment part would be in the details section and would be something like:

shared totCount := totCount + 1;

shared zeroCount;

if {field} = 0 then zeroCount := zeroCount + 1;

HTH]]>

I have a Crystal Report with a sub-report that can return many rows sometimes just too many. I want to limit the number of rows that are returned. Hard coding the limit is fine (how can this be done) but can it also be a parameter so users can choose if they want to see 4, 5, or more, or less, or even make a selection that allows all rows to be included?

Any help would be appreciated.]]>

Hello I have a date parameter where the user will select a date on a Saturday.

I then want it to pull the data for Sunday-Saturday.

Example they enter 7/1/2023 for the parameter date and then I want to bring back the data for Sunday 6/25 - Saturday 7/1.

Thank you ]]>

So I have a Graph that is built off the count of the number of inspections that an employee has done (Count of INSPECTION.INSPETIONBY). By using the count it will only show if an inspection has been completed by an employee in the last week. I would like to show on the graph the employee's name every if there are not inspections completed by the employee shown for that week, basically a null. any help will be appreciated!

]]>

Hi all

Im trying to work out how I what a percent of a total is of the group.

623458 <- grouped Total

623458 Cable 1mm 12

623458 Cable 2mm 0

623458 Cable 4mm 4

623458 Cable 7mm 0

623458 Cable 10mm 5

I need to total up the items that are 0 and display it as a percentage of that group. In this case it would be 40%.

not sure how to go about it.

]]>

I found a field in one of the tables that I didn't know existed, so I was able to get it working!]]>