Reporting

Acctivate includes pre-built reports that can be previewed in Acctivate as well as printed and exported to various file types. In addition to the reports distributed with Acctivate, external reporting options, such as Microsoft Excel Queries, can be used by connecting third party programs to Microsoft SQL Server, the database engine used by Acctivate.

Reports

Acctivate contains a report catalog of over 140 reports. Acctivate has the Crystal Reports Viewer embedded within it, allowing you to preview, print and export these reports. Reports are accessible in the Reports window, which can be accessed by clicking Reports on the toolbar. The Reports window is categorized into top-level folders which have their accessibility controlled by user permissions. For example, users with the "Print Order Reports" permission set to "No" would not have access to any reports in the "OrderProcessing" folder.

You can expand these top-level folders to view subfolders which further categorize reports for easier navigation.

Alternatively, you can use the different menu options in Acctivate to view reports for a particular top-level folder. For example, at the top of Acctivate use the following menu options:

Activities → Business Activity Reports
Customer → Customer Information Reports
Inventory → Inventory Management Reports
Purchasing → Purchasing Reports
Sales → Sales Order Reports
Packaging → Packaging/Shipping Reports

These menu options open a report navigation window that only shows the subfolders for the selected top-level folder.

Expanding the subfolders will display the list of reports included in that section. Click on a report to view a note at the bottom of the Reports window describing the information contained in the report.

Generate a Report

To generate a report, do the following:

Click on the Reports icon on the toolbar or use one of the Acctivate menus to open the Reports window.
Expand the folders until you find the report you are looking for.
Click Select to open the Report Selection Criteria window.
Click Preview to view the report.

Report Selection Criteria

In the Report Selection Criteria window you can filter the report to select (or exclude) certain records. From this window you can choose to preview or print the report.

Filtering

Use the Filter options to include or exclude report records.

Field
The Field column contains pre-defined report fields and parameters from the Crystal Report file. These fields are also known as Selection Prompts and are set up in the Report Catalog. Crystal Reports Parameter fields are also added to this list. Parameters are added within the Crystal Report file.

Filter
A relational operator (i.e., Filter type) must be provided to define how to filter the report on the given values. The most common operator is "Equal To", which will only include records where a field equals a single value. There are different Filters available based on the data type (i.e., Text, Numeric or Date).

Text and Numeric field filters	Include records...
All	all records, no filters.
Between	between Value 1 and Value 2, including both endpoints.
Equal To	with an exact match for Value 1.
Greater Than	greater than Value 1.
Greater than or Equal To	greater than or equal to Value 1.
Less Than	less than Value 1.
Less Than or Equal To	less than or equal to Value 1.
Like	containing the text from Value 1. Use * for a wildcard. For example, A*C would match both ABC and ADC.
In	for a comma-separated list in Value 1.
Not In	that are not in a comma-separated list in Value 1.

Date field filters	Include records for...
All	any date.
Today	today's date.
Week-to-date	last Sunday through today.
This Week	last Sunday through this Saturday.
Month-to-date	1st of this month through today.
This Month	1st through last day of this month.
Last Month	1st through last day of last month.
Year-to-date	Jan. 1st through today.
Before Today	all dates through yesterday.
Last year	Jan. 1st through Dec. 31st of last year.
Last year-to-date	Jan 1st through today's date of last year.
Between	between Value 1 and Value 2, including both end dates.
Equal To	exact match of Value 1.
Not Equal To	all dates except Value 1.
Greater Than	dates after Value 1.
Greater Than or Equal To	dates on or after Value 1.
Less Than	dates before Value 1.
Less Than or Equal To	dates on or before Value 1.

Value 1 and Value 2
You will provide only Value 1 to filter a report on a single value (e.g., Order Number Equal To 12345). Value 2 is only required when using the Between operator (e.g., Order Number Between 12345 and 12350).

Printer Setup

The Printer Setup button on the Report Selection Criteria window can be used to define the printer and other print properties before clicking Preview or Print

Preview

The Preview button generates the report on screen for you to preview.

Print

The Print button will send the report to the printer selected in the Printer Setup, if defined, otherwise it will use default printer of the workstation.

Preview Window

When you click Preview in the Report Selection Criteria field, the Crystal Reports Viewer window will open. This window allows you to preview the report and Print, Export, Email or Open the report in another application. You can also view additional pages, search the report and change the zoom.

Print
Clicking the Print icon will open the Print window. In this window, you can change the printer (it will default to the default printer for the workstation unless you've used the Printer Setup button on the Report Selection Criteria window), Print range, Number of copies and more.

Export
Clicking the Export button will open the Export Report window. IN this window, you can choose the location you want to save the report, the File name of the report and you can change the Save as type to select the file type this report should be saved as. The File type will default to the last file type used.

Email
Clicking the Email button will open the email Message window. The report will be attached as a .pdf copy to this email. You will need to add a To email address and compose the rest of the email, or you can use an existing Message Template. Emails sent from a report preview are not saved in Acctivate.

Open
When clicking the Open button, you will be presented with a menu of programs that you can open this report in. After selecting an application, the report will open in that Application. From the application can you choose to print or save.

Refresh
The refresh button is uses to refresh the report with its current filters without closing it and re-generating the report. For example, if you are viewing the Open Orders report, you may want to edit a sales order, then refresh the report so that it's current before saving it.

Toggle Group Tree
The preview window displays the report grouping tree in the left pane by default. This allows you to click on a group to quickly jump to the start of that group in the report. Click the Toggle Group Tree button to hide/show it.

Page Navigation
If a report contains more than one page, you can use the page navigation buttons:

Go to First Page
Go to Previous Page
Go to Next Page
Go to Last Page

You can also see the number you are on as well as the total pages. To jump to a particular page number, change the number in the Go to Page field and press Enter on your keyboard. The current page number and total page number are also displayed at the bottom of the preview window.

Find Text
The Find Text button (binocular icon) allows you to search for text in the report. Clicking the button opens the Find Text search box. Enter the text to search for and click Next. The report will search from the current page forward, so you may want to start at the beginning of the report.

Zoom
The Zoom function can be used to zoom in or out on the report. Reports will open with a zoom factor of Page Width. Clicking the icon will display a list of predefined zoom options. You can also choose Customize..., which will allow you to enter a zoom factor from 25-400. The currently selected zoom factor displays at the bottom of the preview window.

Drill Down
Some fields in the report can be double-clicked to open the related Acctivate window. These fields can be identified by moving the mouse over the field. If the mouse cursor displays a box and arrow icon, that means that double clicking the field will open the related Acctivate window. For example, if the cursor changes when moving over the a sales order number, then double clicking that field will open the Enter Sales Order window for that order number.

The report will also "Drill Down" to show only that record or group in the report. This will create additional tabs in the preview window. The Main Report tab is the entire report, and each numeric tab represents a drilled down views. To close these drilled down view, click on the tab and then click the small "X" in the upper right hand corner next to the "SAP Crystal Reports" text.

Dashboard

The Dashboard allows for displaying predefined reports that provide summary data. Each user can have the Dashboard configured to display reports relevant to them and the Dashboard can automatically launch upon their login.

To open the Dashboard, click on the Dashboard icon on the toolbar or select it from the Alerts menu. To set up a report:

Click Edit in the Dashboard window.
Click Add Report.
In the report window that appears, select the report you wish to add (only reports that are configured with the Document Type of Dashboard Panel are available to include in the dashboard).
After selecting the report, you can enter in the report selection criteria and click Done.
The report will be added to the Dashboard. Multiple reports can be added. If your Dashboard contains multiple reports, you can click on the report title (while in edit mode) and drag the report to a quadrant of the Dashboard or on top of another report to make a tabbed report interface.
Click Save.

Optionally, you can define the Autorefresh rate at the top of the Dashboard. If left at None, you will need to click the refresh button the to see up to date information. You can use the Pop out button to pop the dashboard window out of Acctivate. To return the Dashboard to the main Acctivate window, click Pop in.

Tip

Dashboards are meant to provide single page summary data. If you need to view multi-page or data intense reports, you should use the Reports window.

Microsoft Excel Query

An Excel spreadsheet can be an easy way to query and display information dynamically linked from the Acctivate database. A Microsoft Query allows you to view information in an easy to update spreadsheet that can be used to update records and re-import into Acctivate or can be saved and refreshed to view real-time information from Acctivate.

The basic steps to set up a Microsoft Query or Microsoft Excel Query are below, these may vary slightly depending on your version of Microsoft Excel.

Before performing the steps below you must set up an ODBC Database Connection

Note

There are many reasons Acctivate uses Microsoft SQL Server as its database engine; it's stable, fast, extremely popular and affordable. One added benefit is that Microsoft Excel has built in support for SQL Server. Excel can be connected directly to your Acctivate database allowing you to query data quickly and efficiently.

The steps below cover the basic steps to connecting Microsoft Excel to your Acctivate database, however due to the wide variety of Excel options, advanced Excel features, and an unknown level of the reader's proficiency of database concepts, these steps cannot be comprehensive in nature. Fortunately, there is an abundance of information available related to using Microsoft Excel to query data from Microsoft SQL Sever.

Initial Steps

Open Excel.
Select the Data tab.
Choose **Get Data → From Other Sources → From Microsoft Query.
In the Choose Data Source window, select your ODBC Datasource and click OK.
In the SQL Server Login window, enter the Acctivate database login credentials:
- Login ID: sa
- Password: (Contact Acctivate Support)
Optionally, click Options to view additional connection options, such as which Database you want to connect to (it will default to the database defined in the initial ODBC setup).
Click OK.
In the Query Wizard, you can select the Tables or Views to include in the query results. It is strongly suggested that you use database Views:
Click Options... in this window.
Check only Views in the Show section.
Check List Tables and Columns in alphabetical order.
Set the Owner field to dbo.
Click OK.
Click Next
- If you have selected fields from more than one database View you will be prompted with the message "The Query Wizard cannot continue because it cannot join the tables in your query. You must join the tables manually in Microsoft Query by dragging the fields to join between the tables.". Clicking OK will open the Microsoft Query window. Continue following the steps under Microsoft query.
- If you have selected fields from a single database view, continue with the remaining steps.
In the Query Wizard - Filter Data screen, optionally select a column(s) to filter. Click Next.
In the Query Wizard - Sort by screen, optionally select a column(s) to sort by. Click Next.
In the Query Wizard - Finish window, select Return Data to Microsoft Excel and click Finish. Alternatively, you can choose View data or edit query in Microsoft Query for more advanced data transformations.

The data will be returned to the Excel Sheet.

Microsoft Query

The Microsoft Query window allows for joining database Views as well as more complex data transformations.

In the Microsoft Query window, you must join the database Views together. Do this by dragging a field from one View to the field you want to link it to in the corresponding View.
To add a new field to the query results, drag a field from the database Views into the grid below.
Additional data transforming (filtering sorting, etc.) can be performed here.
When ready to return the results to Excel, click the Exit Microsoft Query button on the toolbar.

Edit Excel Query

If you've created a spreadsheet with an Excel Query in the past and would like to change what data is being returned on the report, you can follow the steps below to update the query:

Open a spreadsheet in Excel that contains a working Microsoft Query
Highlight cell A1 (or any cell associated with the query), then the click the Data tab and select Properties.
This will open the External Data Properties window. Here, click the button next to the Name field to open the Connection Properties window.
This will open the Connection Properties window. Click the Definition tab, then click the Edit Query button.
From here, you should see the full Microsoft Query editor. This is where you can modify the query being returned to the spreadsheet.
- If you are familiar with SQL and want to run a different query, click the SQL button and replace the existing query with your new query.
- If you would rather use the User Interface tools instead of writing a SQL Query, click and drag field names into the table below or go to Table > Add tables… to add a new table to the report.
When finished, click the Return Data button to return the information to your Excel spreadsheet.
You can save the new spreadsheet to a different file with a new name (File > Save As) or replace the existing spreadsheet by clicking Save.