Basic
Tips & Tricks
for
QueryCalc
Summit
Reports
|
GENERAL TIPS
- Use @REREADING as a low-cost way to pull data. After
a FIND, do all of the @REREADINGs from this database
before doing other calculations to maximize efficiency.
- @REREADING is a serial read of all records found with
the previous query. Very occasionally, it will be more
efficient to use multiple queries than to use @REREADING.
- You can use the Wingdings font to create useful
symbols. Some examples are:
q = open checkbox
4 = checkmark
8 = "fancy" X
- Ambiguous data item names that can't be easily
distinguished from a mathematical equation
can be clarified by
enclosing the data item name with \..\. For example,
the following query would result in an error in a
Spectrum database because NETWORK is a dataitem
by itself:
@USING ATMNET.ANET-DETAIL-FILE,
FIND WHEN NETWORK-DATE=[AD32]
To correct the error, type the query as follows:
@USING ATMNET.ANET-DETAIL-FILE,
FIND WHEN \NETWORK-DATE\=[AD32]
- A watermark print effect can be created with your logo
by placing the image on the report with a very light color
gradient.
- You can print the database schemas by:
- Open the database (@OPENDB databasename)
- Display the database format (@FORM)
- Press [F6] to direct the output to the printer
- Press [F2] to "Show All"
- Press [F7] to direct the output back to the terminal
- If you use the command /CLR to clear a range such as
A1:Z90, instead
of the page, the column widths and other settings
will be retained.
- If you want to use the LIMIT parameter in a query, put
the limit on the spreadsheet. Then use a cell reference
to point to the limit in the query. This way, you can
look at the worksheet and know what the limit is. You can
also change it easily.
- You can find accounts that meet an ADB criterion by
multiplying the desired amount by the number of days in
the month and by 100. e.g., to find accounts with an ADB
greater than $10,000 in the last month, do the following:
A2: 10000 (the dollar limit)
A3: $DAT$2(SYSDATE,-DAY(SYSDATE) (last day in last month)
A4: DAY(A3) (day associated with A3)
A5: A2*A4*100 (the ADB limit, as stored in Spectrum)
A6: @USING MEMBRS.SHARE-FILE, FIND WHEN ADB-DAYS>[A5]
- If you want to print to letterhead and you'll need to
place letterhead in the printer, using the following
commands in the macro will 1) identify the output as
"Letter" (change the name to suit your needs),
2) print to LDev 316 (change the LDev to the desired
device), and 3) defer this output to the printer.
The file "LETTER" will remain spooled to the
device until someone changes the output priority.
30 /SYSTEM FILE LETTER;DEV=316,1
40 /SYSTEM FILE QCLISTF=*LETTER
- An ASCII printer must be specified when creating a
disk file or the output will be the PostScript commands.
- Collateral codes assigned to specific G/L account
numbers can be found and placed in a storelist by using
the following equations:
A2: 701.101 (G/L number)
A3: @USING MEMBRS.LN-CONTROL-FILE,
STORE IN !L COLLATERAL
WHEN GL-LN-ACCT=[A2*1000]
- The password for databases created with the /PRINTDB
command is READER (in all caps).
- If you are using a cell reference in an equation and
the cell is on the same page, it is more efficient to use
the cell reference without the page qualifier
(e.g. D2 vs. CD2).
HELPFUL FORMULAS
- Use RJS$ to format the suffix with leading zeroes (after
you have formatted the suffix value to zero decimal places):
A2: @REREADING, VAL OF SUFFIX
A3: $RJS$("00"+A2,2)
- The formula to change the name from "DOE/JOHN M" to
"John M Doe" is:
A2: @REREADING, VAL OF NAME
A3: $NAM$(SUB$(A3,POS(A3,"/")+1,30)+" "+SUB$(A3,1,POS(A3,"/")-1))
- Calculate the end of month date using the following
formulas. These formulas assume that you are reporting
on the previous month end if the current date is less
than the 25th of the month.
A1: $DAT$1(SYSDATE)
A2: $DAT$1(A1,IF(DAY(A1)<25,0,10))
A3: $DAT$1(A2,-(DAY(A2))
- Format an SSN (after you have formatted the value to
zero decimal places):
A2: @REREADING, VAL OF SSN
A3: $RJS$("000000000"+A2,9)
A4: $SUB$(A3,1,3)+"-"+SUB$(A3,4,5)+"-"+SUB$(A3,6,9)
- ADB (Average Daily Balance) is stored as:
AMOUNT*DAYS*100
- A salutation can be created by reading the sex code of
the member from the Member-File dataset, assigning a title
based on the sex code, then changing the name as shown
above.
A3: @USING MEMBRS.MEMBER-FILE, VAL OF NAME
WHEN ACCOUNT=[A2]
A4: @REREADING, VAL OF SEX
A5: $IF$($A4=$"M","MR.",IF$($A4=$"F","MS.",""))
A6: $NAM$("Dear "+A5+" "+SUB$(A3,1,POS(A3,"/")-1)+":")
STORELIST TIPS
MACRO TIPS
- %PAG=%PAG+1 moves to the "next" page in the
worksheet.
- The current page can be referred to as %P$ (e.g. the
recalculation order of the current page can be set to row-wise by):
/ORDER %P$ R
- %COL=%COL+1 moves the active cell 1 column to the right.
- %ROW=%ROW+1 moves the active cell down 1 row.
- *+1R-1C moves the active cell down 1 row and left 1 column.
DETAIL LIST REPORT TIPS
- A second find on a detail page creates a new group.
Use one of the other keywords (e.g. VAL, NUM, SUM etc.) to
avoid creating a second group.
- The format of numbers on the detail page determines
how the output will be displayed.
- To format the output of a detail list report, edit the /LAYOUT page.
- Detail reports are printed to a PostScript form by
using the FORM parameter in the /PRINT command.
/PRINT C S FORMB
where
Page C = the detail list report
Page B = the PostScript form
- LIMIT the number of records found in a query by using
the LIMIT parameter. This is especially useful when
building a detail list report. You can limit the records
found to 1 until all of the data is formatted correctly.
Then, eliminate the LIMIT parameter and recalculate the
report.
- Use ONNEWPAGE/ENDPAGE to put headers on all pages after page 1.
- Use ## in the LAYOUT page to specify the page number.
H,"MY CREDIT UNION",C;"Page No: ##",-125
- Use @@@ in the LAYOUT page to specify the worksheet name. e.g.
H,"MY CREDIT UNION",C;"@@@",82
- When printing to an ASCII file, set the lines per page
to the number of detail lines that will be printed. In
the layout, delete the headers and footers. This will
eliminate blank rows from the output.
- The /PRINT command works differently in a macro than
it does in a session. The first print to a file replaces
it, and subsequent prints will append to the end of it as
long as you continue to print to the same device and file.
- When printing to a database, the width specified in
column D will determine the width of text items in the
KSAM file. All numbers are stored as R4.
SPECTRUM DATABASE TIPS
- Many of the GL data items are stored in data arrays,
where 1 = last month, 2 = 2 months ago, etc. Each time
the G/L is closed, the data array is updated. A data
array is notated as ItemName(i) where i = the item
number.
- GL account numbers are stored in non-CFO module
databases as an integer. The number in the database is
equal to the GL number without the decimal point. If the
GL account number is input on the worksheet and formatted
to look like Spectrum output, you can use the following
formula to retrieve information from the database:
A2: 1 (Number of months in the past to look for)
A3: 701.000 (GL account number)
A4: @USING GLDATA.ACCOUNT-FILE, VAL OF
LAST-MO-END-BAL(A2)/100 WHEN ACCOUNT=[A3*1000]
- The check number for credit union issued checks is
stored as a 10-character text item (X10). To find a
specific check, the number must be formatted with leading
zeroes. You could use the following formula to format the
check number.
A2: 123456 (Check number)
A3: $RJS$("0000000000"+A2,10)
|
