SQL Report builder question

Epicor ERP 10
1

I think this should be a quick and easy question to answer that I just couldn’t find in any searches… I have a custom report that prints a date code on a label and its made up of the Year YY followed by the Week of the year. Now the week of the year is only printing the relevant number, but I guess they want it in this format YYWW so they want to see the leading zero. Today it’s printing as 20 6, they want to see 20 06. My current expression in the report is,

=DatePart(DateInterval.WeekOfYear, today())

I also have this setup as two separate fields, if there is a way to combine the two into one that would be great as well.

the other one is

=Format(Now(), “yy”)

2 
=Format(Now(), "yy") + Format(DatePart(DateInterval.WeekOfYear, today()), "00")

Is just one of many ways

1 Like
3

Awesome, thank you. I wasn’t sure if that DatePart command was going to accept a simple mask at the end like that. I was hoping it was that easy. :slight_smile:

4

You really should only use the functions for the current date and time in fields that will show when the report is rendered. Other wise they will update each time the report is re-rendered.

This mostly applies to archived reports.

When a report is archived, the dataset generated when the report was run is saved, and is used to re-render the report in the future.

If you had a title on the report like “Inventory Value ending 20-04” (for the 4th week of 2020), and used the Today() function, to generate the week part. If this archived report was re-printed (not re-run), a week later, the title would be “Inventory Value ending 20-05” will showing data from the week of 20-04.

5

In a nutshell, you should tie fields to the dataset whenever possible.

6

Good to know, luckily these are just labels that get printed at the time of part packaging and the results are not archived or re-run in the future.