From Fishbowl
Revision as of 22:25, 22 April 2021 by Ncarter (talk | contribs) (→‎Installing iReport)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

iReport is a third-party application that can be used to create Fishbowl reports. iReport is not developed or supported by Fishbowl. iReport is a powerful and intuitive visual report builder/designer. This tool allows users to visually edit complex reports with charts, images, and sub reports. In addition to many included reports, Fishbowl also offers custom reports and reports from the Fishbowl Store.


Installing iReport

  1. Click here to download the iReport 5.6.0 installer. It is recommended to install iReport on the same computer as the Fishbowl Server.
  2. Double-click the iReport-5.6.0-windows-installer.exe that was downloaded and complete the installer with the default options.
    IReport installer.png
  3. Double-click the iReport-5.6.0 shortcut that was added to the Desktop.
  4. If you get a Java error, or the iReport splash screen shows but the program never loads, you need to install Java 7 as described in the following section.

Installing Java 7

Because iReport requires Java 7, a few extra steps are necessary for computers that don't already have Java 7 installed.

  1. To download Java 7, click here and scroll down to Java SE Runtime Environment 7u80.
    JRE 7 installer.png
  2. Select the appropriate JRE file for your operating system (for example jre-7u80-windows-x64.exe). You may need to create a free Oracle account to complete the download.
  3. Double-click the Java 7 installer that was downloaded and complete the installer with the default options.
  4. Open the iReport/etc directory (for example, C:\Program Files (x86)\Jaspersoft\iReport-5.6.0\etc) and copy the ireport.conf file to the Desktop.
  5. Use Notepad or another text editor to open the ireport.conf file that is located on the Desktop.
  6. Add the following line to the bottom of the file (adjusting the path as needed).
    jdkhome="C:\Program Files\Java\jre7"
  7. Save the file and paste it back into the iReport\etc directory.
  8. Double-click the iReport-5.6.0 shortcut that was added to the Desktop and verify that iReport is running.

Set up the classpath

  1. Open iReport and select the Tools menu then Options.
    1iReport Tools.Options.png
  2. Navigate to the iReport Menu, open the Classpath tab, and select the Add JAR button.
    1iReport.Classpath.Add JAR.png
  3. Browse to the C:\Program Files\Fishbowl\lib and select all of the JAR files located within that folder.
    Add JARs to Fishbowl.lib Classpath.png
  4. Click the Open button and then click OK.

Configure database service

Fishbowl includes a Icon.Data.png Data module that can be used to view tables and test queries. iReport also comes with a database service that can do the same. The database service is not required to set up or create a report, although it is recommended.

  1. Browse to C:\Program Files\Fishbowl\lib\ and locate the mysql-connector-java-5... file. If the lib folder only has a mysql-connector-java-8 file, the mysql-connector-java-5 file can be downloaded by clicking here. This file can then be unzipped and placed in the C:\Program Files\Fishbowl\lib\ folder.
  2. Open iReport and press Ctrl + 5 to open the Services pane.
  3. Expand the Databases section, right-click Drivers, and then select New Driver.
    New iReport driver.png
  4. Click the Add button, select the C:\Program Files\Fishbowl\lib\mysql-connector-java-5... file and then click Open.
  5. Enter Fishbowl MySQL in the Name field and then click OK.
    1iReport driver name.png
  6. Expand the Drivers folder, right-click Fishbowl MySQL, and then select Connect Using...
    1iReport Connect Using.png
  7. Enter the information shown below.
    Host: localhost (or the IP address of Fishbowl server)
    Port: 3305
    Database: Enter the actual database name
    User Name: gone
    Password: fishing
    Remember password: Yes
    MySQL server properties.png
  8. Click OK.

Create a datasource

  1. Click the Report datasources Icon Button.png Report datasources button at the top of the screen and then click the New button.
  2. Select NetBeans Database JDBC connection and then click Next.
    JDBC datasource connection.png
  3. Enter the database name and then click Test.
  4. Click Save.

Using iReport for custom reports

These are the conventions accepted by Fishbowl for JasperReports. Using these conventions ensures that a custom report will be handled as expected and as desired.

iReport versions

  • Fishbowl 2015.8 through 2021.9 uses version 5.6.0
  • Fishbowl 2015.0 through 2015.7 uses version 5.1.0

Report basics

  • The root report directory is Fishbowl/Server/reports. Custom reports added to this folder or any subdirectory within this folder will be used by Fishbowl.
    - The hierarchy/organization of reports in the Report Module matches the file structure of Fishbowl/Server/reports. For example, if a folder named Customer exists in the reports directory, then there will be a Customer folder in the Report Module. Any reports within that folder will appear in the matching folder of Report Module.
  • Reports are keyed on path. This means that Fishbowl recognizes a report by where it is physically stored. If a report is moved, then Fishbowl assumes that it is a new report. If a report is copied over an old report, Fishbowl assumes that it is the same report, but that it has been modified and will update all relevant settings.
  • Reports are Case Sensitive. This means that "FISHBOWL" is not the same as "Fishbowl".
  • A report must be compiled (.jasper) in order for it to be accessible within Fishbowl. Additionally, the report should be compiled using Java. Reports compiled using Groovy are not supported.
  • If there are parse errors, then the server log should be checked. Mis-configured Fishbowl parameters are a common problem. For example, it may be that one of the listed module names or additional node paths isn't correct, or an additional node path might conflict with another path. These problems will need to be corrected in the reports.
  • Deleting or moving a report from reports directory will remove any record of it from Fishbowl.

Report conventions

  • Name the report using camelback convention. - Camelback convention is joining multiple words together and capitalizing the first letter of each individual word, i.e. CamelBackConvention. Fishbowl goes through and puts in spaces before every capital letter to make it display properly, i.e. Camel Back Convention. Do this via the Project | Project Options dialog. DO NOT include the word "Report" in the name of the report. It's redundant.
  • Name the file the same name as the report name used above.
  • Don't include a date range in the query. - For reports showing date fields, don't include a date range in the query. Allow Fishbowl to apply the date range using its controls.
  • Make the report hyperlink compatible. - Make the report hyperlink compatible if you are displaying fields which can be linked.

Hyperlink Values

You can link to records within Fishbowl from a report by using a hyperlink reference. The valid options are

  • PartNum
  • ProductNum
  • SONum
  • PONum
  • WONum
  • MONum
  • TONum
  • RMANum
  • CustomerName
  • VendorName
  • Report

Fishbowl Parameters

  • To access a report's parameters in iReport, go Report Inspector | Parameters. Using the dialog that appears you can create new parameters, modify existing parameters, and delete parameters.
  • All Parameters are Optional. REPORTDESCRIPTION Parameter will be the most useful, so it is Highly Recommended.
  • Parameter Types
    • Special Parameters - Are used to tell Fishbowl to do something Special with the Report.
    • Standard Parameters - Are used to get Information from the User/Fishbowl into the Report. Used change display of report or added to the Query.
    • Custom Parameters - Are just like Standard Parameters except a specific Naming/Type Convention is used to help the User enter the Correct Type of Information into the Report.
  • Parameter Fields
    • Parameter Name - The name of the Parameter. Some specific names will produce different Results. A list of Special/Custom Parameters follows this section. Use Camelback Convention for a better display in Fishbowl.
    • Titling - Some Parameters have the option of being Titled. This means adding on to the name of a Custom Parameter. The title can be left blank. The title will help to tell the difference if you use more than one of that type of Custom Parameter. The Title is what will display when Fishbowl ask the user to enter information. EXAMPLE: parameter Name: partNumBOMPart will ask the user for a part number but will title it as "BOM Part" instead of the standard "Part Number"
    • Parameter Class Type - The Special and Custom Parameters depend on the Class Type you choose. The Parameter Default Expression also depends on the Class Type. java.lang.String is the default and mostly used.
    • For Prompting - All parameters (Except Special Parameters) are displayed to the user when the report is pulled. This field determines whether this parameter is Required or Optional. If all parameters for the Report are Optional then the report will not ask the user for anything and run the report using all defaults for Parameters. If there are some Required parameters (Is For Prompting checked true) then when the Report is run from the Report Module, Fishbowl will ask the user to enter those Parameters.
    • Parameter Description - This Field is used by a few Custom Parameters. It houses additional Information that the Parameters needs. cb, dateRangeDefault, and sort use this Field.
    • Default Value Expression
      This is the Default value for your Parameter. This is the Value that iReport uses when testing your Reports. It is also the Value that Fishbowl puts in the Parameter (uses it for Optional Parameters, it will not add the default if the Parameter is Required. It makes the User enter the Required Information.). There are 2 types of Default Values:
      • Parameter Class Type java.lang.String - This value is surrounded in Quotes. EXAMPLE: "This is a Default Value for a String".
        For many Custom Parameters, the best way for Optional Parameters is to follow this Template: [FileName] LIKE $P{Parameter} This is because an Optional Parameter will return a % to the report. EXAMPLE: part.num LIKE $P{partNum} This will allow the user to enter a Part Number or not one, and no part number is entered it will pull all parts.
      • Any other Parameter Class Type - This follows standard Java Syntax. EXAMPLE: For a Type java.util.Date with default to Today's Date: new Date(). For a Type java.lang.Integer: new Integer(1).
  • Special Parameters
    These Parameters tell Fishbowl to treat this Report a certain way. The User using Fishbowl will never see these parameters, yet they will see the effects of them.
Parameter Name Class Type Default Value About
module java.lang.Object Not Used Used to pull Information from Fishbowl. Common uses are: Company Logo and Tracking Information.
path java.lang.String Not Used Is populated with the Current Path of the Report. This is needed for any Sub-Reports that the Report uses. The Sub-Reports Require the full path to the Sub-Report, this helps to complete that task.
currentUser java.lang.Integer Not Used Used to automatically populate the user ID for the person running the report.
  • Custom Parameters
    These Parameters will Display additional Information to help the User to input the Correct Desired Information.
Parameter Name Class Type Default Value Description About
accountID[Title] java.lang.String {Default Account ID} Not Used Provides a Search Option for Accounts.
{Boolean Name} java.lang.Boolean {new Boolean(true) or new Boolean(false)} Not Used Displays a simple Check Box with the Parameter Name.
carrier java.lang.String {Default Carrier name} Not Used Displays a list of all Carriers in the system. Returns the Carrier Name.
cb[Title] java.lang.String {Default Value Return} {Comma separated choices like this: "Value Return 1, Value Show 1, Value Return 2, Value Show 2".} Allows a list of Items to be Displayed. First Value in list is Returned to Report, the Second Value is Displayed in the Combo Box. This allows a Readable Value to be Displayed while a more useful value is passed to the report.
ck[Title] java.lang.String {Default Value Return} {Comma separated choices like this: "Value Return 1, Value Return 2".} Allows a Check Box to be displayed and Return of String of One of Two Values.
customerID[Title] java.lang.String {Default Customer ID} Not Used Provides a Search Option for Customers.
customerGroupID[Title] java.lang.String {Default Customer Group ID} Not Used Provides a Search Option for Customer Groups. Caution! This parameter works differently due to security restrictions. Fishbowl will return a list of Valid Customer Group IDs instead of a '%' if no Customer Group is selected. Example for WHERE Clause: ( IN ($P!{customerGroupID}))
{Date Name} java.util.Date {Default Date Value} Not Used Displays Room to enter a Date. Also provides a Button to a Calendar Display.
dateRange1[Title] java.util.Date {Default Value for From Date} Not Used Populates 2 Parameters together to provide a Date Range. Gives a Combo Box of common Date choices - Today, This Week, Last Year, etc. Use the same [Title] for dateRange2.
dateRange2[Title] java.util.Date {Default Value for To Date} Not Used Does nothing by itself. dateRange1[Title] populates this Parameter.
{Double Name} java.lang.Double {Default Double Value} Not Used Display a standard Form Box to add a Double (Number with Decimals). Checks to makes sure Input is Valid.
{Integer Name} java.lang.Integer {Default Integer Value} Not Used Display a standard Form Box to add an Integer (Number without Decimals). Check to make sure Input is Valid.
kitNum[Title] java.lang.String {Default Product Number} Not Used Provides a search option for kit numbers.
locationGroupID[Title] java.lang.String {Default Location Group ID} Not Used Provides a Search Option for Location Groups. Caution! This parameter works differently due to security restrictions. Fishbowl will return a list of Valid Location Group IDs instead of a '%' if no Location Group is selected. Example for WHERE Clause: ( IN ($P!{locationGroupID}))
locationID[Title] java.lang.String {Default Location ID} Not Used Provides a Search Option for Locations
moNum[Title] java.lang.String {Default MO Number} Not Used Provides a search option for MO numbers.
partNum[Title] java.lang.String {Default Part Number} Not Used Provides a Search Option for Part Numbers
paymentMethod[Title] java.lang.String {Default Payment Method ID} Not Used Provides a Search Option for Payment Methods.
pickNum[Title] java.lang.String {Default Pick Number} Not Used Provides a search option for pick numbers.
poNum[Title] java.lang.String {Default PO Number} Not Used Provides a Search Option for PO Numbers.
productNum[Title] java.lang.String {Default Product Number} Not Used Provides a Search Option for Product Numbers.
productTree1[Title] java.lang.String {Default Product Tree Node ID} Not Used Provides a Product Tree that users can select a node to filter by. Returns a list of the ID of the selected node, plus the ID of its children categories.
productTree2[Title] java.lang.String Not Used Not Used Does nothing by itself. When coupled with the productTree1 parameter, it will allow users to view products that are not in the product tree. It returns either % or the selected node ID. Example for WHERE clause: AND ((COALESCE(producttreeid,0) IN ($P!{productTree1})) OR ((COALESCE(producttreeid,0) LIKE $P{productTree2})))
qbClassID[Title] java.lang.String {Default QuickBooks Class ID} Not Used Provides a Search Option for Quick Book Classes.
receiptID[Title] java.lang.String {Default Receipt ID} Not Used Provides a Search Option of r a Receipt. Displays the order number to the user, but passes the ID to the report.
rmaNum[Title] java.lang.String {Default RMA} Not Used Provides a Search Option for RMAs.
salesPerson[Title] java.lang.String {Default Sales Person Name} Not Used Provides a Drop Down of Sales Persons
shipID[Title] java.lang.String {Default Ship ID} Not Used Will Display the SO/PO Number with the Date and give the User a Search for a Ship. Passes the Ship ID to the Report.
soNum[Title] java.lang.String {Default SO Number} Not Used Provides a Search Option for SO Numbers.
{String Name} java.lang.String {Default String Value} Not Used Display a standard Form Box to add a String.
toNum[Title] java.lang.String {Default TO Number} Not Used Provides a Search Option for TO Numbers.
vendorID[Title] java.lang.String {Default Vendor ID} Not Used Provides a Search Option for Vendors.
woNum[Title] java.lang.String {Default WO Number} Not Used Provides a Search Option for WO Numbers.
colName java.lang.boolean {Default true or false} Not Used Adds a table column to the column selector tab of the reports dialog.

iReport is sometimes mistakenly referred to as iReports