Hey all,

LN newcomer here. I'm putting together a report for production management. It sorts by planned start date, and also uses an after.field based on planned start date to show a subtotal ("Total" aggregate function) of planned production for a given date.

The issue I'm running into is that every single line shows the subtotal. My guess is that because planned start date is stored as both date and time, that it is seeing the different times within a date and thinking it needs to show the subtotal every time. Is there a way (function or otherwise) in my after.field to strip down the planned start date and time field to only contain the mm-dd-yyy information so that this subtotals correctly? Or am I looking in the wrong direction?

Much thanks,
Rich

Just search this forum for UTC Dates. There are a bunch of threads on converting UTC date to a date. This is just one such thread http://www.baanboard.com/baanboard/showthread.php?t=55205&highlight=UTC+Dates. From the threads I saw I would probably change the input field to the report to date field, then in the session script convert the UTC date to date using sprintf, then pass that to the report to be used. Not a LN user so wait for one of them for more suggestions.

Strange. Every time I try to do any kind of conversion on the date values I end up with empty variables.

Searched around and found some code I thought would work perfectly...


before.field.tisfc001.prdt.1:
before.layout:
long ret, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc

ret = utc.to.date(tisfc001.prdt, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc)
date.without.time = date.to.num(o.year, o.mnth, o.dayn)


"date.without.time" is an input field of domain tcmcs.date.

Then on my after.field layout I try to output date.without.time. Nothing shows.

Maybe I'm not putting the code in the right spot in the script?

Strange. Every time I try to do any kind of conversion on the date values I end up with empty variables.

Searched around and found some code I thought would work perfectly...


before.field.tisfc001.prdt.1:
before.layout:
long ret, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc

ret = utc.to.date(tisfc001.prdt, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc)
date.without.time = date.to.num(o.year, o.mnth, o.dayn)


"date.without.time" is an input field of domain tcmcs.date.

Then on my after.field layout I try to output date.without.time. Nothing shows.

Maybe I'm not putting the code in the right spot in the script?

Hello,
use sprintf$() function like :

string date.without.time(10)
date.without.time = sprintf$("%D(%02m-%02d-%04Y)", tisfc001.prdt)


Should give a string as mm-dd-yyyy

For sorting a format like yyyymmdd is:

date.without.time = sprintf$("%D(%04Y%02m%02d)", tisfc001.prdt)

or
tc.year, tc.week, tc.month

year = val(sprintf$("%u(%04Y)",tisfc001.prdt))
week = val(sprintf$("%u(%02W)",tisfc001.prdt))
month = val(sprintf$("%u(%02m)",tisfc001.prdt))


Regards

Debug it and make sure date.without.time is getting set correctly. I also make sure I declare the variable as extern in the report script(I don't think you want it on the input fields of the report). And if you want this report sorted with this field then you need to run this in the session script and send date.without.time to the report.

What needs to be done is that in your program script, just before you send record to printer using rprt_send().

You need to divide your Planned date UTC into Planned date by this...



Syntax:
function long utc.to.local (long utc, ref long local_days, ref long local_time [, ref string local_dst])

Description

This converts a UTC long format value to local date and time.


Arguments
long utc The UTC long format value.

ref long local_days The local date as the number of days since 01-01-0001.

ref long local_time The local time as the number of seconds since 00:00 hour.

[ref string local_dst] DST_ON: The number of seconds in local_time should be considered as Daylight Saving Time.

DST_OFF: The number of seconds in local_time should not be considered as Daylight Saving Time.

DST_ON and DST_OFF defines are automatically available. Size of local_dst should be 7 chars.

long date.without.time,time

utc.to.local(tisfc001.prdt,date.without.time,time)

I tried your suggestion. The output of my values is still 0 for everything. As you can see on the attachment, for each row I'm printing out the actual tisfc001.prdt value, and on the next line the 3 "0.00" values are month, day, year values from the sprintf(). I can't figure out why the values aren't being passed along to the report. :(

I guess I don't have access to the session script.

Hi Rich.

If have access to the program script, please check the following: year, weak, month and so on must be declared as "extern" so that they can be accessed by the report script (and they must be Input fields in your report, but that must be true to get them sorted).

If you don't have acces to the program script, you still can do that. Have a look at my pretty old thread [URL="http://www.baanboard.com/baanboard/showthread.php?t=9090&highlight=Advanced+Report"]. This will enable you to set year, weak, month and so on as input fields (and sort by them) even if they can't be set by your program script -- it's up to you to set them in your Report script just before the sorting starts.

Regards
Günther

The Report writer takes all the records from the Program Script, sorts the records according to the input field and then displays the record to the OP. It doesnt take all the records and then sorts it while the layouts are getting displayed simultaneously. It doesnt work that way..

What you could do is define a field like report.sort and declare it as a boolean. declare your after.field.report.sort, and the sub totals in that Layout. While in the detail section extract the date field from UTC and compare it with previous value, and if it is changed, change the value of the report.sort to true and then your Sub Totals will get displayed.

Only thing is that you will need to supress the conditions where this after.field will get displayed where you dont want it. You would need to find that out.

Also, the other thing would be when you would compare it with previous value sometimes you would get the same value even though it is a different record. This happens between the complex data interchange between PS and RW.

Progress! :cool: I'm able to define an input field, set its value in the report script, and sort by it. I did that by using the function from Gunther's post. Now, in theory I should be able to make an after.field for my sorted input field and do the Total aggregate on that, right? And that should trigger my subtotal line.

What's happening, though, is the very first line is a subtotal line with a subtotal value of 0 (picture attached). My input sort fields sequence is tisfc010.mcno (machine), then k.of.data.sfc010, and then new.concat.date (new date in format of yyyymmdd). Seems to be sorting correctly. Then I have an after.field layout for new.concat.date where the Total aggregate should give me a total of planned production for that date. I assume it gets triggered when new.concat.date changes, but apparently that isn't how it works.

Following Gunther's advice at the bottom of my report script I have:

#undef r.write.seq.file()

function extern r.write.seq.file()
{
new.concat.date = val(sprintf$("%u(%04Y%02m%02d)",tisfc010.prdt))
ORIGINAL.FUNCTION()
}

and the appropriate declarations at the top per his link to his older post. That's where the 3rd sequence part of my sorted input gets assigned. I'm possibly doing something wrong in the layouts listing.

And just as an FYI I appreciate the help you guys are offering up. It's helping me learn a lot very quickly.

did you miss the macros ?
#define ORIGINAL.FUNCTION() originally.generated.r.write.seq.file()
#define r.write.seq.file() ORIGINAL.FUNCTION()

Nope, I have them in the declarations. I just didn't write it out here. Sorry about that.

Making more progress (pictured). I wasn't able to use the Total aggregate function, so I'm manually accumulating into a variable on each row until I hit the after.field. I put in OLDDATE: and NEWDATE: to try and see what values are being looked at in each row.

One of my primary trip-ups in this whole process is understanding the flow of when certain variables are being assigned, processed, etc. Is there a flowchart, good documentation (I feel like I've looked everywhere) or something so I can get a better handle on what is happening when?

Nevermind, I found a reference and read through the whole thing multiple times. Figured out my problem. Thanks everyone for your help!

Can you tell us what the final solution is? Might help someone in the future.

Certainly. For starters I used gunther's advice for replacing the r.write.seq.file() function. This was so I could use my new.concat.date variable as a sorted input. Set them appropriately in the before.program section.


declaration:
domain tcmcs.long old.concat.date
extern domain tcmcs.long new.concat.date
extern domain tcbool report.sort

#define ORIGINAL.FUNCTION() originally.generated.r.write.seq.file()
#define r.write.seq.file() ORIGINAL.FUNCTION()



functions:
#undef r.write.seq.file()
function extern r.write.seq.file()
{
new.concat.date = val(sprintf$("%u(%04Y%02m%02d)",tisfc010.prdt))
ORIGINAL.FUNCTION()
}


I used the after.receive.data section to determine when to trigger my subtotal line. Finding the after.receive.data section in the documentation was a big help. old.concat.date is initially set to 0 in the before.program section, so I used that in the if statement to suppress the first empty subtotal that was printing (since the first record's date and 0 are always different, it triggered a subtotal line immediately. a simple "and old.concat.date <> 0" fixed that). In my detail section, I manually accumulate the number that will be displayed in the subtotal line instead of using the aggregate, so I also zero it out here if the subtotal line boolean is true in order to start over.


after.receive.data:
if new.concat.date <> old.concat.date and old.concat.date <> 0 then
report.sort = 1
else
report.sort = 0
endif

if report.sort = 1 then
subtotal = 0
endif



detail.4:
before.layout:

subtotal = subtotal + tisfc010.qpli

| some additional query code here...


After the detail layout prints I use the after.layout to set the old.concat.date variable equal to the new (current record) date for use on the next iteration.


after.layout:
old.concat.date = new.concat.date


In my layouts, my after.field triggers on the new.concat.date input field created earlier. The output expression is "report.sort = 1". And presto. It sorts based on date only and not time!