E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 1 Last Updated: 5/27/2008
Monthly Formulas
Order Reports by Last Name Issue No.1 Jan 2007
There are three fields that make a distinct record for each employee. Rather than
create three separate groups for each one, you can combine them into one field and
create one group. With this approach you can group and order your report by last
name.
• {pr_employee_mast.a_name_last} & ', ' &
{pr_employee_mast.a_name_first} & ' ' &
{pr_employee_mast.a_employee_number}
A similar approach can be used with the GL Account. If your chart of accounts
accommodates this approach you can use the following formula to create a group for
each account rather than needing to set up groups for both Org and Object.
• {gl_master.a_org} & ' - ' &
{gl_master.a_object} & ' ' &
{gl_master.a_project}
Last year, this year & next year Issue No.2 Feb 2007
The gl_master view holds the "current year" from the GL Parameter table. With this
information you can set your reports to display the correct fiscal year without having
to update the report.
To display "2007-2008":
totext ({gl_master.gp_curr_year},0,'','') & ' - ' &
totext ({gl_master.gp_curr_year}+1,0,'','')
To display "FY06 - 07":
'FY' &
totext ({gl_master.gp_curr_year}-1,0,'','')[3 to 4] & ' - ' &
totext ({gl_master.gp_curr_year},0,'','')[3 to 4]
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 2 Last Updated: 5/27/2008
Using Propercase to Remove All Caps Issue No.3 March 2007
In many cases, your software uses all caps when entering or saving data. This makes
it easy for entering and finding data but doesn't present well on paper. Use the
'Propercase' command in the Formula Editor to remove all caps. It's easy to use and
will quickly become one of your favorite tricks!
For Org using the gl_master view:
ProperCase ({gl_master.a_org_description})
For employee name using the pr_employee_mast view:
propercase ({pr_employee_mast.a_name_first}) & ' ' &
propercase ({pr_employee_mast.a_name_last})
If there are acronyms such as "CRS" or codes that use Roman numerals such as
"Analyst III," you will need to create an if/then/else statement in the formula to
account for those exceptions.
if {gl_budget_det.p_job_class_code} = '1320' then
'Accountant II' else
if {gl_budget_det.p_job_class_code} = '1335' then
'Account Technician III' else
if {gl_budget_det.p_job_class_code} = '1340' then
'Account Technician II' else
if {gl_budget_det.p_job_class_code} = '1430' then
'Housing Technician II' else
if {gl_budget_det.p_job_class_code} = '3020' then
'Administrative Analyst II' else
if {gl_budget_det.p_job_class_code} = '4070' then
'Maintenance III' else
if {gl_budget_det.p_job_class_code} = '4080' then
'Maintenance II' else
if {gl_budget_det.p_job_class_code} = '5100' then
'CRS Technition' else
ProperCase ({prjobcls.a_job_class_desc})
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 3 Last Updated: 5/27/2008
Calculating Percentages Issue No.4 April 2007
Whether you are looking for a percent remaining or percent used, use the following
format wherever needed.
Note that anytime you evaluate a percent, you must account for those lines where
the denominator is zero. Otherwise you will get an error in Crystal stating "Division
by zero."
To calc % used at the object level:
if Sum ({@CY Revised budget}, {gl_master.a_object}) = 0 then 0 else
(Sum ({@CY Revised budget}, {gl_master.a_object}) -
Sum ({@Available balance}, {gl_master.a_object})) %
Sum ({@CY Revised budget}, {gl_master.a_object})
To calc % available at the department level:
if Sum ({@CY Revised budget}, {@Department}) = 0 then 0 else
Sum ({@Available balance}, {@Department}) %
Sum ({@CY Revised budget}, {@Department})
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 4 Last Updated: 5/27/2008
Shared Variable Formulas Issue No.5 May 2007
Crystal Reports allows you to pass 'variables' from the subreport to the main report,
and visa versa. The most common use is to get a number from the subreport and
add it to numbers in the main report. Here's how it goes.
Place a formula, "SV Amount," in the subreport that establishes the shared variable:
whileprintingrecords:
shared numbervar amount := {field or summary}:
amount
Place a formula, "SV Amount2," in the main report that retrieves the shared variable:
whileprintingrecords;
numbervar amount2 := shared numbervar amount: amount2
Add the shared variable to existing numbers in the main report:
{SV Amount2} + Sum{field or summary}
You can also add the shared variables to create a summary of those numbers:
whileprintingrecords;
numbervar amount3 := amount3 + numbervar amount2;
amount3
The placement of these formulas can be tricky and usually takes some work to get
them right. You will also need to create a formula to reset the variables back to zero
after the subtotals are generated.
The following pages are from my Advanced Training Document
The report uses a parameter to determine if the period being reported is open or
closed.
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 5 Last Updated: 5/27/2008
A) Summary Examples – Subreports & Shared Variables
25 Medicaid Expenses – Design
(Fig. 4-8a)
25 Medicaid Expenses – Subreport Design
(Fig. 4-8b)
25 Medicaid Expenses – Preview
(Fig. 4-8c)
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 6 Last Updated: 5/27/2008
ORDER OF PROCESSING EVENTS
A.
1. Open or Closed = Closed
2. Generates date from SQL
3. Formats data on page
a. Header
b. Detail
c. Footer
d. Summaries
e. Whileprintingrecords
4. Skips subreport on each pass due to suppression formula
5. Formulas generate totals without input from shared variables
B.
1. Open or Closed = Open
2. Generates date from SQL
3. Formats data on page
4. Processes subreport on each pass
5. Shared variable formula passes data to main report
6. Formulas in main report generate totals with input from shared variables
Examples of shared variable formulas from the above report.
SV Object Amount - Subreport shared variable
SV Obj 1 - Main report picks up shared variable for use with Object total
SV Obj 1a - Main report defines variable for use in later formula
Object Total - Generates a number that includes or exclude the variable.
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 7 Last Updated: 5/27/2008
SV Org 1 - Main report picks up shared variable for use with Org total
SV Org 1a - Main report defines variable for use in later formula
Org Total - Generates a number that includes or exclude the variable.
As variables get to next object and org, a “reset formula” in the appropriate group
header sets the number back to 0. In the case of a grand total, no reset formula is
needed.
SV Reset Obj – Sets variable numbers back to 0 for the next object
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 8 Last Updated: 5/27/2008
Getting Correct Amounts from GLJEHOLD Issue No.6 July 2007
All journal entries are held in two MUNIS tables, glhistry and gljehold. All entries to
closed periods are in glhistry. All entries to open periods are in gljehold. Three things
to note here... 1) Rolling the period doesn't move the entries from gljehold to
glhistry, you need to close the period. 2) All entries for your new fiscal year are in
gljehold until last year is closed and the periods in the new year are closed. 3) 'glje'
is the prefix for gljehold and 'glhi' is the prefix for glhistry. The fields and formulas
below are interchangeable by using the correct prefix.
There are a few key fields involved in these formulas.
glje_year needs to be defined for the year you want to report.
glje_period needs to be defined. You can do your year- to-date balance with a 'less
than or equal to' statement in the formula. You can define the current period with an
'equal to' statement.
glje_tran_type is used to define actual entries (1), encumbrances (4) and budget
(5).
glje_bud_type is used to define current year (1) or carry forward (2).
glje_dr_cr identifies an entry as Debit or Credit.
The following formula will return encumbrance balances for all of 2007. Notice that
the period is not defined in the formula, which will return a balance as of the
moment the report is run. Lastly, the view 'gl_journal_hold' is used in the formula
rather than the table gljehold.
if {gl_journal_hold.je_transact_type} = "4" and
{gl_journal_hold.je_transact_year} = 2007 and
{gl_journal_hold.je_debit_or_credit} = "C" then
{gl_journal_hold.je_gross_amount}*-1 else
if {gl_journal_hold.je_transact_type} = "4" and
{gl_journal_hold.je_transact_year} = 2007 and
{gl_journal_hold.je_debit_or_credit} = "D" then
{gl_journal_hold.je_gross_amount} else
0
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 9 Last Updated: 5/27/2008
The following formula is a bit more complex. I show it here to give examples of how
you can customize formulas to meet your business needs.
This client needed to get POs for the fiscal year being reported. The “ref2” field holds
the PO number and the first four characters are always the fiscal year. Thus, the
formula calls those records where the first four characters of “ref2” are equal to the
fiscal year prompt (turned into text, of course).
Second, this report needed to get a quarter’s worth of expenses. The user would
enter period 1, 4, 7, or 10 and the formula gets the three periods in that quarter.
The last quarter, however, can also include period 13, so, we added a line specifically
to get four periods instead of three.
if {gl_history.h_ref2_po_no}[1 to 4] = totext ({?Fiscal Year},0,'','') and
(if {?Period} = 10 then
{gl_history.h_transact_period} in
[{?Period},{?Period}+1,{?Period}+2,{?Period}+3] else
{gl_history.h_transact_period} in [{?Period},{?Period}+1,{?Period}+2]) and
{gl_history.h_transact_type} = '1' and
{gl_history.h_budget_type} = '1' and
{gl_history.h_debit_or_credit} = 'D' then
{gl_history.h_gross_amount} else
if {gl_history.h_ref2_po_no}[1 to 4] = totext ({?Fiscal Year},0,'','') and
(if {?Period} = 10 then
{gl_history.h_transact_period} in
[{?Period},{?Period}+1,{?Period}+2,{?Period}+3] else
{gl_history.h_transact_period} in [{?Period},{?Period}+1,{?Period}+2]) and
{gl_history.h_transact_type} = '1' and
{gl_history.h_budget_type} = '1' and
{gl_history.h_debit_or_credit} = 'D' then
{gl_history.h_gross_amount}*-1 else
0
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 10 Last Updated: 5/27/2008
Suppressing Sections Issue No.7 January 2008
There are two ways to suppress sections of data in Crystal Reports and each requires
that you enter a formula.
To suppress individual sections - for instance, suppressing lines that have all zero
values, click on the Section Expert icon or click the Report menu > Selection
Formulas > Record. In the left window, click on the section you want to address. In
the example below we are suppressing the section for the "a_object" group. Click on
the “X+2” formula icon to the right of “Suppress (No Drill-Down)” and the Format
Formula Editor appears.
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 11 Last Updated: 5/27/2008
In the blank area in the bottom right you will enter a formula that makes a
statement that is either True or False.
With this formula, any line that has zero for Available Balance, Revised Budget and
YTD Expended will be suppressed. Notice that this is not an "If/Then/Else" formula, it
is a Boolean formula. The program knows that if the line in question is True, the line
will be suppressed.
To suppress an entire group - for instance, if all the values for all objects within an
Org are zero and you want to suppress the entire Org, you can use the Group
Suppression Formula Editor. To do this click the Report menu > Selection Formulas
> Group.
In the example below we are suppressing any "Org" group that has zeros in Available
Balance, Revised Budget and YTD Expended.
This will eliminate all group headers, detail lines and group footers for Orgs where
the above statement is True.
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 12 Last Updated: 5/27/2008
Subreport Linking Formulas Issue No.8 March 2008
One of the reasons for using Subreports is that the data you need to add to the report isn't
stored with fields that match up to ones in the table you are using.
A good example of this is text or comment fields stored in the tables sptexthd and
sptextdt. There are two key fields for linking in sptexthd, spth_prog and spth_idxval. You
will need to create a formula in the main report that matches the 'idxval' entry. Here are a
few examples.
As an FYI, both of these solutions were provided to a client recently using procedures
established working with Randy Mercier. Together we helped produce Permits and
Business License forms that have been in use for several months now.
For linking Business License license text.
totext({blmaster.blms_id}) & ' ' &
totext({bldetail.bldt_license_no},0,'','')
For linking to Permits Text.
{piappprm.piap_type} & {piapphdr.piah_ref}
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 13 Last Updated: 5/27/2008
Use the Subreport Links tool to link these fields to spth_idxval. Once you have done that
and inserted the subreport into the main report, use the Select Expert inside the subreport
to specify the spth_prog used for the entry you need. In the examples above, the BL text
program for license comments is blliccm1 and blliccm2. The program for permits text is
piapphdP.
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 14 Last Updated: 5/27/2008
E&S Bryan Inc. • Crystal Reporting Solutions • 142 High St #523 • PO Box 5038 • Portland, ME 04101
207.879.7500 • www.crystalreportingsolutions.com • [email protected]
Page 15 Last Updated: 5/27/2008
A Few Random Formulas Issue No.9 May 2008
To suppress the page header on the last page of a report, insert this formula into the
condition formula for Suppress(No Drill-Down):
pagenumber = totalpagecount
To stop a report from printing one extra blank page at the end of the report, insert
this formula into the condition formula for "New Page After":
not onlastrecord
To identify a date range of first and last day in a month, first set a parameter to
enter the first day of the month:
{?Begin Date} to date(year({?Begin Date}),month({? Begin Date})+1,1)-1
