• Page:
  • 1
  • 2

TOPIC: Logicity Pro VB Script Variables Reference

Logicity Pro VB Script Variables Reference 07 Nov 2012 14:37 #1216

  • aellis
  • aellis's Avatar
  • Offline
In Logicity 1.7 we have the new feature that allows you to utilize VB Script in your variables within Logicity Solution Builder. So, for example, if you have some date parameters on your report and you want them to get filled in automatically with something dynamic this will do the job for you. The possibilities are endless as VBScript can do a lot of stuff. This post will start to collect some of the helpful formulas we have come up with. Please reply with any of your own or any questions you might have:

Todays Date:
%{Date}%

The First Day of This Month:
%{DateSerial(DatePart("yyyy",Date),DatePart("m",Date),1)}%

The Last Day of This Month:
%{DateAdd("d", -1, DateAdd("m",1,DateSerial(DatePart("yyyy",Date),DatePart("m",Date),1)))}%

The First Day of Last Month:
%{DateSerial(DatePart("yyyy",DateAdd("m", -1, Date)),DatePart("m",DateAdd("m", -1, Date)),1)}%

The Last Day of Last Month:
%{DateAdd("d", -1, DateSerial(DatePart("yyyy",Date),DatePart("m",Date),1))}%
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 09 May 2013 22:43 #1349

  • swordfrog
  • swordfrog's Avatar
  • Offline
Hi all,

Using variables to name my output files, I've had success with these in Solution Builder / Action / Save file name field

1. to save as filename_yyyymmddhhmmss.pdf
filename_%{(Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2))}%.pdf

2. to save as filename_yyyymmdd.pdf
filename_%{(Year(Date) & Right("0" & Month(Date),2) & Right("0" & Day(Date),2))}%.pdf

With reference & thanks to poster PHV over at Tek-Tips :) www.tek-tips.com/viewthread.cfm?qid=979415

Cheers,
Brad
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 06 Mar 2014 21:28 #1535

  • karenliu
  • karenliu's Avatar
  • Offline
Does this not work in the free version? I'm trying to use the first day of this month, and it keeps getting:

Conversion from string "%{DateSerial(DatePart("yyyy"" to type 'Date' is not valid.

Am I missing an escape character?
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 06 Mar 2014 21:41 #1536

  • bellis
  • bellis's Avatar
  • Offline
Yeah, VBScript support is a pro feature option.

Thanks!
Brian
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 01 Nov 2014 21:57 #1697

  • JoelM
  • JoelM's Avatar
  • Offline
swordfrog wrote:
Hi all,

Using variables to name my output files, I've had success with these in Solution Builder / Action / Save file name field

1. to save as filename_yyyymmddhhmmss.pdf
filename_%{(Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2))}%.pdf

2. to save as filename_yyyymmdd.pdf
filename_%{(Year(Date) & Right("0" & Month(Date),2) & Right("0" & Day(Date),2))}%.pdf

With reference & thanks to poster PHV over at Tek-Tips :) www.tek-tips.com/viewthread.cfm?qid=979415

Cheers,
Brad

From my own testing the above two examples don't actually work, as the & symbol breaks the parsing of the RRD file, however I've managed to create the below lines to produce similar filenames:

Today's date, with leading zero, in the format YYYY-MM-DD

%{DatePart("yyyy",date)}%-%{Right("0" + cstr(DatePart("m",date)), 2)}%-%{Right("0" + cstr(DatePart("d",date)), 2)}%

Yesterday's date, with leading Zero, in the format YYYY-MM-DD
%{DatePart("yyyy",DateAdd("d", -1, date))}%-%{Right("0" + cstr(DatePart("m",DateAdd("d", -1, date))), 2)}%-%{Right("0" + cstr(DatePart("d",DateAdd("d", -1, date))), 2)}%
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 01 Nov 2016 19:20 #2449

  • Dan231
  • Dan231's Avatar
  • Offline
I'm looking to get the last day of the month, 3 months ahead.
So if today is 10/26/16, I need the date of 1/31/17
I cannot figure out the VB script examples
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 02 Nov 2016 19:34 #2453

  • JoelM
  • JoelM's Avatar
  • Offline
Dan231 wrote:
I'm looking to get the last day of the month, 3 months ahead.
So if today is 10/26/16, I need the date of 1/31/17
I cannot figure out the VB script examples
Hi Dan,
I think the below should do what you want. It returns the date in format YYYY-MM-DD of the last day of three months from now. I've used this format as it's what I had to hand, I've explained what I've done though, so hopefully you can work out how to adjust the format yourself, the below notes should also help explain how my examples above work.
%{year(DateAdd("m", 3, date))}%-%{Right("0" + cstr(month(DateAdd("m", 3, date))), 2)}%-%{Right("0" + cstr(day(DateAdd("d", -1, DateAdd("m", 4,dateserial(year(date),month(date),1))))), 2)}%

We get this by first finding the year in three months:
year(DateAdd("m", 3, date))
year() returns only the year from the date passed to it, DateAdd() is used to add 3 (parameter 2) months (parameter 1) to the current date (parameter 3)

We then do the same for the month, but this time it's a bit more confusing as we want the leading zero.
Right("0" + cstr(month(DateAdd("m", 3, date))), 2)
We can see the same basics in the middle, of selecting only the month, from the current date plus 3 months, but this is also wrapped in a function that will add 0 to the beginning of the string returned by month, but truncated to the 2 right-most characters. This means if the month returns '11', we add 0 to make '011' and then select the two right-most characters leaving just '11', however if the month is 3, we add 0 to make '03', and then select the two right most characters, leaving '03'.

The day is yet more complicated, as we have elements of both of the above:
Right("0" + cstr(day(DateAdd("d", -1, DateAdd("m", 4,dateserial(year(date),month(date),1))))), 2)
We have the same functionality to add the leading zero to the day that is returned, but finding the last day of the month is slightly tricky. From the middle out, we start by making a date equal to the first of this month:
dateserial(year(date),month(date),1)
dateSerial() creates a date given 3 parameters, for year, month and day. dateSerial(Year, Month, Day)
For the year and month, we can just use the values from the current date, the day we hardcode to 1.

Once we have produced this date, we add 4 months, to give us the 1st of the month after you want the last day of.
DateAdd("m", 4,dateserial(year(date),month(date),1))
We can then subtract one day, using dateAdd, to give us the last day of the month before.
DateAdd("d", -1, DateAdd("m", 4,dateserial(year(date),month(date),1)))
The administrator has disabled public write access.
The following user(s) said Thank You: Dan231

Logicity Pro VB Script Variables Reference 02 Nov 2016 21:14 #2457

  • Dan231
  • Dan231's Avatar
  • Offline
Perfect -thank you very much. This helps a lot!
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 29 Dec 2016 03:44 #2485

  • VV
  • VV's Avatar
  • Offline
Duplicate post, sorry!
Last Edit: 29 Dec 2016 03:50 by VV.
The administrator has disabled public write access.

Logicity Pro VB Script Variables Reference 29 Dec 2016 03:46 #2486

  • VV
  • VV's Avatar
  • Offline
Hello,
I wanted to create a filename that would adjust the date based on the current weekday. I have a report that is run for data 10 days in the future on Monday -Wednesday and 12 days in the future on Thursdays and Fridays. So, on M-W, it would be filename_yyyymmdd + 10 days, on Th-F, filename_yyyymmdd + 12 days.
Any help is appreciated. Thank you.
The administrator has disabled public write access.
  • Page:
  • 1
  • 2