Back to all categories

Reporting

  Training Videos

  BI Report

  BI Editor

  Data

  Reports

  Advanced Reports

  Forecast

The Data module, located in the Reporting group, provides the ability to retrieve and export data from the Fishbowl database. All data can be viewed, but the database cannot be modified. The admin user will always have access to the Data module. Other users can be given access in the User Group module. A query can be run, saved, exported, and scheduled. The query results can also be accessed through the Fishbowl API.

Run a query

1. Open the Data module and click the New button located on the module toolbar.
2. Type any SQL query, for example, SELECT * FROM customer
   
3. To see the results, click the Run Query button on the toolbar to the right, or press Ctrl + Enter

The columns in the Data section can be dragged, sorted, and hidden.

Query parameters

The Data module supports several query parameters. Parameters make it easier to select data from the database. For example, instead of manually entering the ID of a customer directly into a query, a parameter allows the customer to be selected from a drop down list.

Query without parameters

Query with parameters

Parameters are available in queries by using the following format: $CODE{Label|Default Value|Tab}

For example, $PART{Select_a_part|B201|Advanced_tab} would display the following when the query runs.

These parameters are also available in the BI Editor module by clicking the Insert report setting button on the component toolbar. This provides a graphical input to customize parameters using the same format as above.

Below are some examples of queries that use parameters.

SELECT * FROM part WHERE datelastmodified BETWEEN $RANGE{Modified_Date}

SELECT * FROM location WHERE locationgroupid = $LG{Location_Group}

SELECT * FROM customer WHERE id = $CUST{Select_Customer|27|Customer_tab}

Notes

• Most of the parameters return a record ID from the database. For example, $CUST will insert the ID of the selected customer into the query, not the NAME of the customer.
• The text between the curly braces { } is the label that will be displayed next to the field.
• Make sure to use curly braces { } around the label and not parentheses ( ).
• The text in the label can be modified to provide a more informative description. Use an underscore _ to display a space in the label.
• The default value for a parameter can be included by adding a pipe character and then the value to enter in the field. For example, $PART{Select_a_part|B201} or $CUST{Customer|27}
• Most parameters cannot be left blank. If a wildcard is used and no input given, the query will not return results. For example, SELECT * FROM customer WHERE id = $CUST{Customer|%} will run but will not return any results unless the user selects a customer.
• Parameters can also be separated into tabs by including a tab name after the default value. For example, $PART{Select_a_part|B201|Advanced tab} or $PART{Part||Advanced tab}
• If you want to give a $LIST a default value, you can add the ^ character before the default value. For example $LIST{Select_From_List|Apple,^Orange,Banana}.
• Because parameters require user input, queries with parameters cannot be used in the Schedule module.

Save a query

1. To save a query for reuse, enter a name in the Name field. The description is optional.
2. Click the Save button on the module toolbar, or press Ctrl + S

Filter a query

• The data returned from a query can be further filtered by clicking on the right side of any column header.
  
• Click (All) to select or unselect all entries.
• Click an individual entry to show or hide it in the results.
• Click (Custom...) to add a custom filter to the results.
  
  Possible filters include is anything, is, doesn't equal, is in, isn't in, is empty, is not empty, begins with, ends with, contains, and doesn't contain.
• When data is exported, only the filtered data that is currently visible in the table will be exported.
• Filters are temporary. The next time the query is executed, the filters will be reset.

Export a query

1. After running the query and applying any filters, click the Export to .xls button or the Export to .csv button.
2. Choose where the file should be saved and then click Export.
3. Browse to the file and open it with a spreadsheet application or a text editor.

Copy data

The text in the Data section can also be copied and pasted into a text document or a spreadsheet.

• To copy a single cell, double-click the cell and then press Ctrl + C.
• To copy a single row, click the row and then press Ctrl + C.
• To copy all rows, press Ctrl + A and then press Ctrl + C.
• To copy consecutive rows, click and drag the mouse over the rows and then press Ctrl + C.
• To copy non-consecutive rows, hold down the Ctrl key while clicking each row and then press Ctrl + C.

Query history

• To move through the history of executed queries, click the Previous button or the Next button on the toolbar to the right.
• The history is deleted each time the module is closed.
• The number of items stored in the history can be set in the Data module options.
• The history only displays queries that have been executed. To undo a typing mistake, press Ctrl + Z

Schedule a query

1. Open the Schedule module and click the New button.
2. Select Data and then click Next.
   
3. Select a saved query, enter the required information, and then click Next.
4. Set the scheduled time and finish the wizard.

Extract a query

The Fishbowl Server can log many of the queries that are run when a user is using Fishbowl. It is often helpful to start with a query from a report and then modify it as necessary. Below are the steps for extracting a query for inventory quantities.

1. In a Fishbowl Client, run the Inventory Availability report.
2. Open the Data module and click the Server Log button.
3. Click the Reports tab.
4. Scroll to the bottom of the tab and copy the query starting right after SQL query string: until the end of the file.
5. Paste the query into the Data module.
6. Use the parameters that are listed below the query to replace the ? symbol.

Below is an example of how to modify the query. After completing the modification, press the Run Query button.

Database tables

Click the Tables tab to see a list of all of the tables, fields, and data in the Fishbowl database. Double-click a table name to see it in a separate window.

A database dictionary can also be downloaded by clicking here.

Server log

The Server Log button on the Data module toolbar shows the recent activity of the server, including any errors. The buttons in the window allow the data to be refreshed, copied, or sent to Fishbowl.

The report queries are stored in a separate file and can be viewed on the Reports tab.

MODULE OPTIONS

Some aspects of the Data module can be customized by opening the module, clicking the Tools menu, and then selecting Module Options.

• Query History Size

Sets the number of queries that are stored in the query history.

• Max Server Log Lines

Sets the maximum number of lines to display when clicking the Server Log button on the module toolbar.

• Server Logging Detail Level

Select the amount of data that is logged. The server log can be viewed by clicking the Server Log button at the top of the module.