What is Crystal Reports?

Crystal Reports is a reporting product produced by SAP to facilitate the representation of data in an understandable & timely fashion. Crystal Reports can extract data from many sources, including Microsoft SQL Server, which is what Jiwa uses as its data store.

The advantages of using Crystal Reports as the reporting engine in Jiwa is that the Crystal Reports product is relatively easy to use, is extremely popular, and is a skill that can not only be applied to Jiwa, but to many other software packages, or even used stand-alone.

To develop your own report for use in Jiwa, or to modify an existing Jiwa report, a licensed copy of Crystal Reports is required in addition to the Jiwa software. An add-in for Microsoft Visual Studio is also available from here - NOTE: This add-in does not work with Visual Studio Express Edition.

To familiarise one’s self with a typical Jiwa report, it is suggested that a standard shipped-with-Jiwa report be opened up in the Crystal Reports Designer and examined by the reader. Often, business specific requirements can be met by making minor alterations to an existing standard shipped-with-Jiwa report.

How to Install the Crystal Reports Editor

Crystal reports can be purchased as a stand-alone product, or a free edition can be used with a free edition of Microsoft Visual Studio.

You don't need Jiwa installed to edit Crystal Reports, but if you do have Jiwa installed, the version of Microsoft Visual Studio and SAP Crystal Reports For Visual Studio, Developer Version you should install without affecting Jiwa will differ depending on the version of Jiwa that is installed. Installing the wrong version will cause Jiwa to longer be able to generate reports.

If Jiwa 7.00.175.00 or earlier is installed, Visual Studio 2015 must be used - the Community Edition is a free edition, and the paid Professional or Enterprise editions will also work.

If Jiwa 7.1 or later is installed, Visual Studio 2017 must be used - the Community Edition is a free edition, and the paid Professional or Enterprise editions will also work.

If Jiwa is not installed, then either Visual Studio 2017 or Visual Studio 2015 can be used, but note that when using Visual Studio 2017, you must use SAP Crystal Reports For Visual Studio, Developer Version SP 22 or later - earlier versions are not compatible with Visual Studio 2017 - follow the instructions for Installing When Jiwa 07.01.00.00 or Later is Installed.

Installing When Jiwa 07.00.175.00 or Earlier is Installed

1. Download Visual Studio 2015 Community Edition from https://go.microsoft.com/fwlink/?LinkID=626924&clcid=0x409
2. Run the downloaded vs_community_ENU.exe to install Visual Studio 2015 - Select the Default installation and follow the prompts in the installation wizard until the installation is complete.
3. Download SAP Crystal Reports For Visual Studio, Developer Version SP 16 from http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0_16.exe
4. Run the the downloaded CRforVS_13_0_16.exe to install the add-in for Visual Studio and follow the prompts in the installation wizard until following dialog appears - and deselect the "Yes. install 64-bit runtime" checkbox.

Installing When Jiwa 07.01.00.00 or later is Installed

1. Download Visual Studio 2017 Community Edition from https://www.visualstudio.com/thank-you-downloading-visual-studio/?sku=Community&rel=15
2. Run the downloaded vs_community_xxxxx.xxxxxxxx.exe to install Visual Studio 2017 - Select the .NET desktop development Workload and follow the prompts in the installation wizard until the installation is complete.
3. Download SAP Crystal Reports For Visual Studio, Developer Version SP 22 from http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0_22.exe
4. Run the the downloaded CRforVS_13_0_22.exe to install the add-in for Visual Studio and follow the prompts in the installation wizard until following dialog appears - and deselect the "Yes. install 64-bit runtime" checkbox.

Editing Reports

Reports can be edited with the appropriate version of Visual Studio - double-clicking a .rpt file will either open Visual Studio, or if you have not yet associated the .rpt file type with Visual Studio, Windows will prompt you to select how to open the file:

Select the Microsoft Visual Studio Version Selector application, and check the "Always use this app to open .rpt files" and press OK

You can also open files within Visual Studio using the File → Open → File menu option.

The Visual Studio Crystal Reports editor then appears as follows (Note colour schemes are user customisable and may appear different):

Where Jiwa Reports are Located

Jiwa reports are all stored in the Jiwa database and deployed to the local filesystem on demand. The location they are deployed to is in the C:\ProgramData\Jiwa Financials\Jiwa 7\%Jiwa Version%\%Windows User%\%SQL Server Name%\%Jiwa Database Name%\Reports folder.

Reports will not appear in this location until attempted to be run within Jiwa.

Best Practices

Rename Report File Names

If wishing to modify an existing standard Jiwa report, you should rename the file. Try to name the file such that it is clear that this is a custom report.

Change Report Title

The Report Title in the Summary Info should also be changed, as Jiwa uses this when importing / updating a report. Change the Report Title by select the Crystal Reports → Report → Summary Info menu option

The Summary Info Dialog will then open - change the Report Title field and press OK

Importing a New or Updated Report Into Jiwa

Once you have finished making changes to a Crystal Report, and want to add or update the report within Jiwa, make sure you have saved the changes in Visual Studio first.

Then, open the Reports form within Jiwa from the System Settings → Report Configuration menu, and press the Refresh From Files tool on the ribbon.

The Report selection dialog will then open - select the report file or files you wish to add or update

You will then be prompted with a confirmation dialog:

Then press the Save tool on the ribbon to commit the changes to the database.

Table Based vs. Stored Procedure Based Reports

This section talks about the two different techniques used in the various Jiwa reports, and why one should be used over the other.

Table Based Reports

The simplest of the report writing approaches is table based reports. Table based reports are those reports where only tables (or views) are added to the Crystal report, and linked together using the Crystal "Database Expert". Calculations and groupings are done by the report engine, and minimal programming skill is required by the creator. The Crystal "Record Selection Formula" is used to limit the data that is returned by the report.

Table based reports are quick and easy to construct, but can become awkward to work with when the reporting requirements are complex, involving lots of logic for selecting the data. Table based reports tend to be slower than their stored procedure based counterparts. For simple reports, a table based report should be used as the overhead in getting something up and working is minimal.

An example of a table based report in Jiwa is "CRINF003 - Creditors Adjustment Notes.rpt".

Stored Procedure Based Reports

Stored procedure based reports are those reports where all intelligence, including grouping information and calculations, is done by a stored procedure. Crystal Reports becomes just a dumb display engine that simply calls a stored procedure (often passing some parameter values), and then rendering the data returned. All new standard Jiwa reports are written in this manner. The advantages of stored procedure based reports are that they are fast (SQL already knows the query plan), and that the logic is separated from the display aspects. Some further advantages:

Changes/fixes often only require a modification to the stored procedure in the database, therefore we do not need to redeploy any report files.
Code re-use - the same stored procedure can be used by multiple reports, or even by the client software (i.e. Inventory Maintenance grids).
Complex report requirements are easy to design for, implement, and maintain through the use of the logic structures available in stored procedure syntax - T-SQL.

The disadvantages:

Must know T-SQL syntax.
Need to deploy the report AND a stored procedure initially.

An example of a stored procedure based report in Jiwa is "ININF000 - Inventory Maintenance Listing.rpt"

How Crystal Reports Integrates With Jiwa

Jiwa uses the "Crystal Runtime" to run a report, and uses the "Crystal Report Viewer" (a .NET Windows Forms control) to display the report on the screen. The screenshot below shows the "Crystal Report Viewer" denoted by the red box - the rest of the report screen, including the Settings and Formula tabs, are controlled by Jiwa.

A report can be accessed 1 of 2 ways in Jiwa. From the menu (menu based) or from a form (form based). If running a menu based report, a series of parameters are often presented which the user may manipulate. If running a form based report, the report usually has some parameters set automatically by the form, and the report is rendered to the screen immediately without any user input.

Menu Based Report

When Jiwa loads a report from the menu, the first thing it does is look for the existence of a formula named "JiwaRanges". If it finds such a formula, it looks inside for any comment lines - these comment lines denote formulae names. Here is the contents of the JiwaRanges formula for the ININF000 - Inventory Maintenance Listing.rpt report:

//{@Pass_SP_StartingClassification}
//{@Pass_SP_EndingClassification}
//{@Pass_SP_StartingPartNo}
//{@Pass_SP_EndingPartNo}
//{@Pass_GroupBy}
//{@Pass_ShowOtherDescriptions}
//{@Pass_ShowAlternate}
//{@Pass_ShowProductGroups}
//{@Pass_ShowSupplierInformation}
//{@Pass_ShowQuantityPriceBreaks}
//{@Pass_ShowComponents}
//{@Pass_ReportStyle}

Being comment lines Crystal ignores them, but to Jiwa these lines have meaning - these are the names of other formulae in the report that Jiwa should use to construct the grid on the "Ranges" tab of the report screen. Below is a screenshot of the ranges as presented to the user for the "Inventory Maintenance Listing" report (the filename of which is "ININF000 - Inventory Maintenance Listing.rpt"). The ranges grid is denoted by the red box.

Let's look at the "Pass_GroupBy" formula in a little more detail. The "Pass_GroupBy" range parameter gives the user a combo-box from which to pick a single value. Below is a screen shot that shows the expanded combo-box after the user has clicked on it.

In the case of this particular report, the "Pass_GroupBy" formula controls whether the inventory items are simply output one after the other in part number order, or whether they are "grouped" by classification - i.e. list all the inventory items with a classification of "Building Accessories" first, then leave a little gap, then list all the inventory items with a classification of "Cleaning" second, and so-on. Here is the content of the "Pass_GroupBy" formula:

//Type = NumberCombo
//Caption = Select Grouping
//ADD=1,Classification
//ADD=2,None
//Default=2
1

Note that much of the formula, as far as Crystal Reports is concerned, are comments (denoted by the //). Crystal Reports, of course, ignores such lines - they mean nothing to Crystal Reports and have no bearing on the report’s output. The last line is not a comment line. Therefore, Crystal Reports sees the formula "Pass_GroupBy" as having a value of 1. If you were to run this report within the Crystal Reports Designer, Crystal Reports would deem the value of this formula to be 1.

The comment lines do mean something to Jiwa, however. Jiwa "parses" the comment lines, and in the case of a range parameter, uses them in the construction of the ranges grid on the ranges tab of the report screen.

The above formula indicates it’s "Type" to be "NumberCombo" as per the first line (see the "Type" keyword Range Parameter Formulae section for a full list of possible "Types"). Therefore, Jiwa sets up a combo box in the ranges grid. Line 2 defines a "Caption" - Jiwa places this to the left of the combo box on the ranges grid. The "Caption" can be whatever you like. Lines 3 and 4 are used by Jiwa to populate the combo box with values that the user may pick from. If more than 2 pick values are required, additional "ADD" lines are inserted into the formula thus:

//Type = NumberCombo
//Caption = Select Grouping
//ADD=1,Classification
//ADD=2,None
//ADD=3,Category 1
//ADD=4,Category 2
//Default=2
1

Finally, the 7^th line (the 5^th line before we inserted the extra ADDs) sets the combo box’s initial value.

The section titled "Sample Range Parameter Formulae" shows examples of all the different types of range parameters that are possible, from simple number boxes through to parameters that use the Jiwa search screen for value selection.

Range parameters are the most important component of writing reports for Jiwa. These are the real glue between Jiwa and Crystal Reports. These are what allow the user to choose ranges or options that affect the set of data that the report will show, and how it will present that data. See the section below titled "Range Parameter Formulae" for information about the syntax that can be used in Crystal formulae for Jiwa ranges.

After the report has been loaded, and the ranges grid constructed and presented, the user can change the values of the range formulae, and then click on the print button. At this point Jiwa will replace the report's record selection criteria formulae references with the actual range values as chosen on the ranges grid. This is how "Table Based Reports" are able to limit the data they return based on the range values chosen by the user.

Further, Jiwa will look at each range formula name, drop the "Pass_" prefix if one exists, and then look for a stored procedure parameter in the report with that name - if one is found then the range value is used as the value for that stored procedure parameter. This is how "Stored Procedure Based Reports" are able to control which data is returned by the stored procedure and ultimately used by the Crystal report.

Form Based Reports

Form based reports are those which are run from a specific screen in Jiwa, such as Sales Order Entry, or Purchase Orders. Such reports display information that is limited to just the record that the form is currently displaying, and as such, no report ranges are involved. For example, in the case of purchase orders the user may be looking at order no. 100517 on the screen, and by clicking on the print icon on that screen a crystal report is launched, showing only the information that is pertinent to order no. 100517.

When a report is launched from a form, the Jiwa code may do one of two things, or both:

Look for a particularly named formula and set it's value to be the RecID of the record currently on screen
Set the report's record selection formula so that the report is limited to whatever record is currently on-screen, i.e. PO_Main.OrderID = '00000000080000000014'

The "particularly named formula" and the value for the record selection formula varies from form to form in Jiwa. Whilst purchase orders only sets the record selection formula to PO_Main.OrderID = '<CurrentOnScreenOrderID>', sales orders actually looks for a crystal formula in its report called "Pass_SP_InvoiceHistoryID" and sets its value to <CurrentInvoiceHistoryIDOfTheOnScreenSalesOrder>. The easiest way to determine what is being set is to open the standard Jiwa report and take a look. A table based report will always be getting it's record selection formula set - the main table of the report (i.e. PO_Main) will be limited to the appropriate RecID ('00000000080000000014'). A stored procedure based report will have it's "Pass_SP_..." parameters set to the appropriate RecID.

Order of Events

When a report is run from Jiwa, the following tasks are performed by the Jiwa code:

If the report is being launched from a form, the form may (it depends on the form) inject some values into the "Record Selection Formula" of the report. The form may also set some other specific formula values (see the section "Form Based Reports" for more information on this behaviour).
LoadReport - Any formulae that are named starting with "Pass_" and that appear as comments in the a formula named "JiwaRanges" are parsed. Based on this parsing, the ranges grid on the ranges tab of the report screen is set up. See the section "Range Parameter Formulae" for further information.
UpdateReport - When the user clicks on the "Print" button on the report screen (below), the following occurs:
1. Tables, views, store procedures, etc. that are in the report have their "locations" changed from the server/database they were developed against to the server/database that the report is currently being executed in.
2. Formulae in the "Record Selection Formula" that are named starting with "Pass_" are replaced with the value that the user selected for them in the ranges grid of the report screen.
3. If the Crystal report has any parameter formulae (these appear if the report is referencing a stored procedure that accepts parameters), Jiwa performs some matching to "normal" Crystal report formulae based on naming:
  1. If the name of a Crystal formula minus the leading "Pass_" is equal to the name of a Crystal stored procedure parameter formula, then the value of the Crystal formula is pushed into the Crystal stored procedure parameter formula.
  This allows integration with reports that call stored procedures that require parameters to be set.
4. The auto-populated formulae have their values set by Jiwa.
5. The underlying Crystal report engine is told to execute the report.

Once the report has finished executing (i.e. received all of the required data from the SQL Server and rendered all of the report pages), focus moves from the "Ranges" tab on the report screen to the "Report" tab such that the result is shown to the user (via the embedded Crystal Reports Viewer .Net control). Note that there is a 3^rd tab than can be manually changed to, the "Formula" tab - only the "admin" Jiwa user can access this tab. Some useful debugging information can be found here.

Range Parameter Formulae

Range parameter formulae contain a number of "Keyword=Value" lines. These lines are entered into the formula as comments so that Crystal Reports ignores them (but Jiwa knows to act upon them). Therefore, a typical line inside a range parameter formula would look like "//Keyword=Value" (without the surrounding quotes).

Below is a list of all of the keywords that Jiwa will recognize and act upon, if found inside a range parameter formula.

Keywords

Caption

The value (remember the format is Keyword=Value) is used as the description for the range parameter, and is presented to the user alongside the range parameter when the report is loaded.

Possible Values:

Any text

Example:

//Caption=Select Starting Part No.

Type

This defines the basic type of the range parameter, such as string or number. This affects the values accepted by the formula (i.e. "a" is not valid for a "Type=Number" formula), and also the way the parameter is presented to the user in the ranges grid of the report screen.

A full description of possible "Type" values is given below.

Possible Values:

String - A simple text box.

Number - A simple number box. Formatting such as the number of decimal places to display is controlled by other keywords such as "Decimal".

NumberCombo - A dropdown combo box from which the user may select 1 item.

Date - A date of the format dd/mm/yyyy.

CheckBox - A two state clickable box (tick or no tick).

Example:

//Type=String

Search

The value may be "Yes" (case insensitive) or non-yes, or the keyword may not be used in the formula at all (which implies non-yes). If the value is the string "Yes", a clickable button is enabled alongside the range parameter value on the screen. When this button is clicked the Jiwa search screen is launched. The contents of the search screen and how the results of the search screen will be used are determined by the inclusion/values of other keywords (i.e."Select", "Order", and "Default").

Possible Values:

Yes

No

Any text (this implies that this range parameter value is NOT Jiwa searchable)

Example:

//Search=Yes

Select

If the range parameter has been defined as a searchable one (i.e. "//Search=Yes"), then the value for this keyword is combined with the value for the "Order" keyword (if one exists) to build the T-SQL Query string to be used by the search screen. The first item in the SELECT clause is what is returned to the ranges grid when a selection is made from the search screen.

Possible Values:

Any valid T-SQL query that excludes the ORDER BY clause (Ordering is handled by a separate keyword, "Order").

Example:

//Select=SELECT PartNo, Description FROM IN_Main

Order

If the range parameter has been defined as a searchable one (i.e. "//Search=Yes"), then the value for this keyword is combined with the value for the "Select" keyword to build the T-SQL Query string used by the search screen.

Possible Values:

Any valid T-SQL ORDER BY clause (including the "ORDER BY" syntax itself).

Example:

//Order=ORDER BY IN_Main.Partno

Min

If the range parameter type is numeric (i.e. Type=Number), then the value for the "Min" keyword dictates the smallest number that the user is allowed to input.

Possible Values:

Any numeric value.

Example:

//Min=0

Max

If the range parameter type is numeric (i.e. Type=Number), then the value for the "Max" keyword dictates the largest number that the user is allowed to input.

Possible Values:

Any numeric value.

Example:

//Max=100

Decimal

Controls the number of decimal places to allow in the range parameter.

Possible Values:

Any positive numeric value.

Example:

//Decimal=2

Add

This keyword is used to add values to a combo box type range parameter (i.e. //Type=NumberCombo). A formula may contain several "Add" lines.

Possible Values:

Of the form X, Y Where X is a positive integer, and Y is a string.

Example:

//ADD=1,Classification

//ADD=2,None

Default

The default value that should be used as the formula value. Depending on the "Type" of the formula, this value may be text, a number, or a T-SQL statement. There are some special keywords that can be used as the value for the "Default" keyword to represent a varying value (i.e. the current date). These special keywords are:

MachineDate:
DateTime.Now.ToString("dd/MM/yyyy")
LoginDate:
Manager.Database.SysDateTime.ToString("dd/MM/yyyy")
StartOfCurrentMonth:
"01/" & DateTime.Now.Month.ToString & "/" & DateTime.Now.Year.ToString
EndOfCurrentMonth:
Dim currentDateTime = Me.Manager.Database.SysDateTime
Dim endOfCurrentMonth = New DateTime(currentDateTime.Year, currentDateTime.Month, DateTime.DaysInMonth(currentDateTime.Year, currentDateTime.Month))
formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("ENDOFCURRENTMONTH ", endOfCurrentMonth.ToString("yyyy-MM-dd"))
StartOfCurrentCalendarYear:
"01/01" & DateTime.Now.Year.ToString
EndOfCurrentCalendarYear:
"31/12" & DateTime.Now.Year.ToString
StartOfFinancialYear:
Dim currentStartDate As Date
Manager.GeneralLedgerConfiguration.GetCurrentYearStartingDate(currentStartDate)
currentStartDate.ToString("dd/MM/yyyy")
EndOfFinancialYear:
Dim currentStartDate As Date
Manager.GeneralLedgerConfiguration.GetCurrentYearStartingDate(currentStartDate)
DateAdd(Microsoft.VisualBasic.DateInterval.Second, -1, currentStartDate.AddYears(1)).ToString("dd/MM/yyyy")
CurrentYearNo:
Dim currentStartDate As Date
Manager.GeneralLedgerConfiguration.GetCurrentYearStartingDate(currentStartDate)
Dim yearType As GeneralLedger.Configuration.YearTypes
Dim yearNo As Integer
Dim yearStartDate As Date
Dim yearEndDate As Date
Manager.GeneralLedgerConfiguration.GetYearDetails(currentStartDate, yearType, yearNo, yearStartDate, yearEndDate)
yearNo.ToString
Today:
Manager.Database.SysDateTime.ToString("dd/MM/yyyy")
NextYearStartDate:
Dim lastPeriodStartingDate As Date = Manager.GeneralLedgerConfiguration.GetLastPeriodStartingDate()
formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("LASTPERIODSTARTDATE", lastPeriodStartingDate.ToString("yyyy-MM-dd"))
LastYearStartDate:
Dim lastYearStartingDate As Date
Manager.GeneralLedgerConfiguration.GetLastYearStartingDate(lastYearStartingDate)
formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("LASTYEARSTARTDATE", lastYearStartingDate.ToString("yyyy-MM-dd"))
NextPeriodStartDate
Dim nextPeriodStartingDate As Date = Manager.GeneralLedgerConfiguration.GetNextPeriodStartingDate()
formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("NEXTPERIODSTARTDATE", nextPeriodStartingDate.ToString("yyyy-MM-dd"))
LastPeriodStartDate:
Dim lastPeriodStartingDate As Date = Manager.GeneralLedgerConfiguration.GetLastPeriodStartingDate()
formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("LASTPERIODSTARTDATE", lastPeriodStartingDate.ToString("yyyy-MM-dd"))
UTCOffset
Dim UTCOffset As TimeSpan = TimeZoneInfo.Local.GetUtcOffset(DateTime.UtcNow)
Dim UTCOffsetInMinutes = UTCOffset.TotalMinutes
formulaDefaultValue = formulaDefaultValue.ToUpper().Replace("UTCOFFSET", UTCOffsetInMinutes)

There is also a special function that can be used for date calculations:

DateAdd - Resolves to the result of DateAdd(DateInterval, DateNumber, DateBase). This works the same way as the DateAdd function in Visual Basic. Possible values for the DateInterval are:

yyyy - Year

q - Quarter

m - Month

y - Day of year

d - Day

w - Weekday

ww - Week of year

h - Hour

n - Minute

s - Second

Possible Values:

Any text

LoginDate

MachineDate

Today

DateAdd(X,Y,Z) (Where X is a valid VB DateAdd "interval", Y is amount to add, and Z is the date which the DateAdd operation is being performed on).

Examples:

//Default=DateAdd("m",1,Today)

//Default=SELECT PartNo FROM IN_Main ORDER BY IN_Main.PartNo ASC

//Default=Today

//Default=1

//Default=Enter some text here...

Hidden

If this Keyword exists and has a value of True (case insensitive), then the range parameter will not be presented to the user for input.

Possible Values:

True

False

Example:

//Hidden=True

Sample Range Parameter Formulae

Below are a series of sample range parameter formulae - these samples cover each of the different types. For further examples, open up the standard reports that ship with Jiwa and browse the report source code.

String

//Type=String
//Caption=Enter your name:
//Default=

//Type= String
//Caption=Select Starting Part No.
//Search=YES
//Select=SELECT PartNo, Description FROM IN_Main
//Order=ORDER BY IN_Main.Partno
//Default=select PartNo FROM IN_Main ORDER BY IN_Main.PartNo ASC
''

//Type= String
//Caption=Select Ending Part No.
//Search=YES
//Select=SELECT PartNo, Description FROM IN_Main
//Order=ORDER BY IN_Main.Partno
//Default=select PartNo FROM IN_Main ORDER BY IN_Main.PartNo DESC
''

Number

//Type=Number
//Caption=Please enter number of days
//Default=5

//Type=Number
//Caption=Please enter number of hours
//Default=5.5
//Decimal=2

NumberCombo

//Type = NumberCombo
//Caption = Select Grouping
//ADD=1,Classification
//ADD=2,None
//Default=1
1

Date

//Type=Date
//Caption=Enter Start Date
//Default=Today

//Type=Date
//Caption=Enter End Date
//Default=DateAdd("m",1,Today)

//Type=Date
//Caption=Enter transaction date
//Default=01/01/2009

//Type=Date
//Caption=Enter transaction date

CheckBox

//Type = CheckBox
//Caption = Show 0 Value Rows
//Default=0
0

Auto-populated Formulae

Auto populated formulae are simply Crystal formulae of a particular name that Jiwa will look for and populate. For example, if a formula exists in the report with a name of "Pass_User" (case insensitive), the contents, or value, of the formula will be set to the username of the Jiwa user who is running the report.

Below is a list of all of the keywords that Jiwa will recognize and act upon, if found inside a range parameter formula.

Pass_User

The username of the user executing the report.

Code:

"'" & Manager.Database.JiwaLoginUserName & "'"

Pass_CompanyName

The contents of the "CompanyName" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyName", "'Jiwa Financials'").ToString().Replace("'", "''") & "'"

The contents of the "CompanyPhone" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyPhone", "''") & "'"

Pass_CompanyFax

The contents of the "CompanyFax" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyFax", "''") & "'"

Pass_CompanyAddress1

The contents of the "CompanyAddress1" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyAddress1", "''") & "'"

Pass_CompanyAddress2

The contents of the "CompanyAddress2" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyAddress2", "''") & "'"

Pass_CompanyAddress3

The contents of the "CompanyAddress3" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyAddress3", "''") & "'"

Pass_CompanyAddress4

The contents of the "CompanyAddress4" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyAddress4", "''") & "'"

Pass_CompanyEmail

The contents of the "CompanyEmail" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyEmail", "''") & "'"

Pass_CompanyWebsite

The contents of the "CompanyWebsite" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyWebSite", "''") & "'"

Pass_CompanyACN

The contents of the "CompanyACN" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyACN", "''") & "'"

Pass_CompanyABN

The contents of the "CompanyABN" system setting.

Code:

"'" & Manager.Database.ReadSysData("System", "CompanyABN", "''") & "'"

Pass_ApplicationDate

The current date as per the Jiwa (i.e. A user is able to login under a different date - it is this date that is used). This is the date that appears on the bottom right of the Jiwa status bar, and can be changed by the user.

Code:

Trim("Date(" & Year(Manager.Database.SysDateTime) & ", " & Month(Manager.Database.SysDateTime) & ", " & Day(Manager.Database.SysDateTime) & ")")

Pass_ApplicationTime

The current time as per the Jiwa.

Code:

Trim("Date(" & Year(Manager.Database.SysDateTime) & ", " & Month(Manager.Database.SysDateTime) & ", " & Day(Manager.Database.SysDateTime) & ")")

Pass_SystemDate

The current date as per the workstation that the report is being run on.

Code:

Trim("Date(" & Year(Today) & ", " & Month(Today) & ", " & Day(Today) & ")")

Pass_SystemDateTime

The current date and time as per the workstation that the report is being run on.

Code:

"'" & Today.ToLongDateString & "'"

Pass_KitRoundingInventoryID

The InventoryID that is configured in Jiwa to represent the rounding amounts for kits. Sometimes it is desired to hide such inventory items from reports such as invoices, picking slips, etc.

Code:

"'" & Manager.Database.ReadSysData("InvoicingParams", "KitRoundingPart", "''") & "'"

SalesOrder_CreateEnd

Browser not supported