Last week we touched on a couple of useful conversion functions that are available to us in Monarch. Today we’ll continue looking at functions; this time focusing on one of the smaller function categories, that of the “report” functions. Report functions are those which give us data related to the actual report file in use, not the direct data included in the report itself.

File()

The File() function returns the full path and filename of the report from which your record was extracted. While this is a pretty straightforward function, and you might wonder why you would ever need to include text like “D:\Reports\Shipping Report.txt” in your data, there are times when I find it absolutely indispensible.

I like to keep my historical data handy, effectively creating mini data warehouses. I like to create topic folders for these mini data warehouses. For instance, I might have an AR folder and a Shipping folder. Within each folder I’ll store reports representing the monthly activity for that topic, as this makes it much easier to extract periodic data which can easily analyzed graphically.

When you store many instances of a report within a topic folder, you need to organize your reports a little. My convention is to start with the topic name, like Shipping, and always include a three letter month and a four digit year, along the file type extension, like “Shipping Jun 2008.prn”. At a glance I know what the file is, and for what period the report was run. Additionally, your report may or may not include data that you can extract to determine a period. And if that’s not troublesome enough, you may share my situation, in which the date that appears in the report isn’t actually properly representative of the actual fiscal period.

So my solution is to extract the period from the filename. Since I know that I will always use a “.prn” (or “.txt” extension, I know that the last four characters in the filename will be an extension. Further I know that I’ll always use a “mmm yyyy” structure just before the extension. That’s seven characters, plus the four for the extension, for a total of 11 characters. So using a Right(File(),12) on “D:\ReportsShipping\Shipping Jun 2008.txt” will give me “Jun 2008.txt” regardless of the category name. Changing that slightly to Left(Right(File(),12),3), I get “Jun”. To get the year, Left(Right(File(),8),4) will return “2008″. Put it together with a “/1/” string in the middle, and you get Left(Right(File(),12),3) + “/1/” + Left(Right(File(),8),4).

Finally, using all of that as the input for a CtoD function, like:

CtoD(Left(Right(File(),12),3)+"/1/"+Left(Right(File(),8),4),"m/d/y")

and you get a valid period in your data that you’ve derived from your filename.

ID()

I find ID() to be a bit of an odd one. It returns the number of the report from which your record was extracted. As you open multiple reports in Monarch simultaneously, Monarch assigns a value of 1 to the first report opened, and adds one to each successive report. In all these years, I’ve yet to find a use for this. Have you?

Line()

The Line() function returns the line on which this record appears in the report. You can get some odd looking, yet accurate, values with Line() when you’re using filters.

Page()

Similar to Line(), Page() returns the page number of the report from which the record was extracted. From time to time I’ve taken advantage of the Page() function when building filters. Some times data appears on the first page of a report that gets captured by an extraction template (based on the trap created in the template), but I didn’t necessarily want that part of the data set. Rather than playing with the trap to somehow exclude that bit, I’ll use the Page() function and filter out all the page one data (Page()<>1).

Recno()

Recno() returns the sequential detail record number, starting at 1. If your data set has 100 records, and your filter only returns three records, you may see 7, 43 and 89 as the only values returned.

Rowno()

Rowno() is similar to results of the Recno() function for some reports, but the notable, and useful, exception is that Rowno() isn’t affected by filters. If your filter returns 100 records, Rowno() will return 1 through 100.

It’s the Function of the Function

The importance of the various functions available isn’t so much what the function actually does, in and of itself, but more how you can use it creatively to achieve something that isn’t immediately obvious. Use your imagination and a little experimentation, and you’ll excel with Monarch.