Creating YSL Reports

Every custom YSL report begins with an existing YSL report, which is then considered a template.

To function as a template, a YSL report must meet two criteria. First, it must contain at least one worksheet called 'Template' (case insensitive) or a worksheet whose name begins with 'T-'. Second, the file name must not include the string 'Autoformat' (case insensitive). When the YSL report is generated, the worksheet named 'Template' is renamed as “Report'. Worksheets whose names begin with 'T-' are renamed without the 'T-' prefix.

To create a custom YSL report, first modify the YSL template and then save it with a different name. You can use any existing YSL report as the basis for a new YSL report.

YSL Classic: Always use an existing YSL report as a template for a new YSL report, because every YSL .xls file must contain special code (macros) that allows YSL to work with the spreadsheet. YSL.Net: There are three options when creating a new YSL report: c Start with a filled-in template with the required Rows 1 through 5 filled in, and with Rows 6 and greater also populated. c Start with a blank template with only the required Rows 1 through 5 filled in. c Start with a blank template with the required Rows 1 through 5 filled in, along with Special Row 1 Elements, and with Rows 6 and greater empty. For more information, see “Special Row 1 Elements”.

When creating a YSL report, a message may appear asking if you want to enable macros. Whether or not this message screen appears depends on the level of security you set in Excel. For more information, see“To enable macros: YSL.Net”.

To create a new YSL report: YSL Classic

The following procedure is illustrated using Excel 2000-2003. If you use a version of Excel other than 2000-2003, the commands and screens may be different from those described in this procedure.

1	From the Excel top menu, select File > Open. The Open screen appears.

2	Locate and select the YSL report file (.xls) that you want to use as the template for your custom YSL report; for example, YSL_Balance.xls. Click Open.

3	Edit the YSL report as needed.

To create a new YSL report: YSL.Net

The following procedure is illustrated using Excel 2010. If you use a version of Excel other than 2010, the commands and screens may be different from those described in this procedure.

1	If you want to initiate a YSL report with a filled-in template (with the required Rows 1 through 5 filled in, and with Rows 6 and greater also populated):

a	From the Excel top menu, click File.

b	From the side menu, click Open. The Open screen appears.

c	Locate and select the YSL report file (.xls) that you want to use as the template; for example, YSL_Balance_Sheet.xls.

d	Click Open. The selected YSL report is displayed, with Rows 1-5 populated with the required settings and Rows 6 and greater populated with entries relevant to the selected YSL report.

2	If you want to initiate a YSL report with a blank template (with only the required Rows 1 through 5 filled in):

a	From the Excel top menu, click the Add-Ins tab.

b	Click Create New YSL Workbook.

c	A new YSL report is displayed, with Rows 1-5 populated with the required settings.

3	If you want to initiate a YSL report with a blank template that includes Special Row 1 Elements (with the required Rows 1 through 5 filled in, along with Special Row 1 Elements, and with Rows 6 and greater empty):

a	From the Excel top menu, click the Add-Ins tab.

b	Click Create New YSL Workbook with Special Row 1 Elements.

c	A new YSL report is displayed, with Rows 1-5 populated with the required settings, along with the Special Row 1 Elements. For more information, see “Special Row 1 Elements”.

4	Edit the YSL report as needed.

Special Row 1 Elements

YSL.Net processes a worksheet named SpecialRow1Elements. This worksheet is not included in the generated YSL report. Only Columns A and B of this sheet are used. The following requirements must be met when creating entries that populate Row 1 of the report:

c

Column A: Specify the column in Row 1 of the YSL report that you want to set to accumulate the results of the query. YSL.Net itself reserves the first 7 columns (A through G) of Row 1 for standard filter items, such as the dates and the account tree. You can employ a numerical designation of columns Columns 8 through 254 or an alphabetic designation of Columns H through AI.

c

Column B: Specify the query which should begin with the word SELECT and thus be resolved against the database. In the case of multiple records being returned, these are written into multiple columns beginning at the column defined to the left. Only the first SQL column of any SELECT is resolved, and the limitation of Excel 2003 to 255 maximum columns applies. For example:

SELECT Convert(char,ISNULL(P.SACQUIRE, '1/1/1950'),101) AcquireDate FROM PROPERTY P

WHERE P.iType = 3 AND P.sCode IN $All Properties$

might return 10 Acquire dates as string values into successive columns of Row 1 of the YSL report, if the run-time filter was submitted with 10 properties.

c	Write your SELECTs for a property or book as an IN clause and do not include surrounding round brackets or single quotes.

c

Surround all tokens in these SELECTs with a dollar sign. You should specify the tokens in terms of the standardized tokens: such as $All Properties$, $Book2$, $Property 12$. The standardized filter tokens on a YSL report are as shown on the colored pro forma Template sheet, as shown in the following graphic.

For date manipulation, YSL.Net supports all the environmental date tokens supported by Executive Dashboard, such as $OperatingMonth$ and $FirstDayOfThisMonth$. You can use $Date1$, $Date2$, $FromDate$ or $ToDate$ to collect the filter dates. Two special tokens are also supported: $LastDayOfDate1$ and $LastDayofDate2$. Date token arithmetic is not supported. For more information, see Appendix A of the Yardi Voyager Executive Dashboard User's Guide.