<< Click to Display Table of Contents >> Navigation:  QuoteWerks Help > CHAPTER 20: Management Reports > Designing a Report

Fundamentally, a report is comprised of two components. The first component is a filter. You design a filter to return only the information you want to see on the printed report. For example, if you wanted to create a report that displayed all the orders for this month, you would create a filter that limited the results to only orders, and then further limited the results to only orders that were sold in the month you specify.

The second component is the layout. The layout will determine how the information is presented, including the sort order. For example, with the list of orders you may only want to see the SalesRep, Document Number, Quote Date, and the Grand Total. The layout is where you visually design what information will appear in the printed result and where the information will be placed. More information about layout creation and modification can be found in Printing and Customizing Document Layouts.

From the Management Reports window, click on the [New] button to create a new report, or click on the [Edit] button to edit an existing report. The report properties are displayed on three tabs; the General tab, Filter tab, and Layout tab.

General Tab

The general tab contains information about the report, such as the report name and report description. You can also change the name of the “Report Configuration File” (described earlier) for this report by clicking the button.

Filter Tab

The filter tab is where you can select which database you would like to report on, either the document database (which contains all the quotes/order/invoices) or one of the product databases (which contains the lists of products and services that you sell). It is also where you build the content of the filter expression.

The first step in creating a filter is to first choose the database that you want to report on. The “Select Database to report on” drop down is where you can select if you would like to create a report based on the document database, or one of the product databases:

Report on Documents Database

When you select to create a report based on the documents database, you will see two checkbox options.

In the documents database there are two tables, the DocumentHeaders table and the DocumentItems table. The DocumentHeaders table contains one record for each quote, and contains information such as Document Number, Document Date, Sold to Company, Terms, Ship via, etc. In other words, any fields for which there are only one per quote. There is only one Document Number per quote; there is only one Document Date per quote, etc. The DocumentItems table contains a record for each line item in each quote, and contains line item detail information such as Quantity, Description, Manufacturer Part Number, Unit Price, Extended Price, etc.

The “Force inclusion of DocumentHeaders table” and “Force inclusion of DocumentItems table” options pertain to how QuoteWerks uses the DocumentHeaders and DocumentItems tables in conjunction with the filter expression. These are advanced options that are used only in certain situations. When building the filter expression, if you only specify DocumentItems fields in the filter, QuoteWerks will omit the DocumentHeader fields in the resulting data that it returns. If you do want the DocumentHeader fields even though you are only filtering on DocumentItems field values, you will need to check the “Force inclusion of Document Headers table” checkbox.

For example, if you create a filter to only display products that you have sold by a certain manufacturer, QuoteWerks will automatically include the DocumentItems table because anything relating to the line items on the quote, such as products that you have quoted, will be in the DocumentItems table. A report using just this filter alone would be very useful. However, what if you also wanted to know what date these items were quoted on? In this case, you would need to check the “Force inclusion of DocumentHeaders table” to force QuoteWerks to include that information.

Commission Reports

A common report you may need is a commission report. In QuoteWerks, two commission reports are available by default. Users who have commission based on the sale price of the document or the profit percentage of a document can create a report in QuoteWerks, where this information is calculated for management.  

The reports can be customized to include a single rep, multiple reps, or all reps. Two default reports exist in the Management Reports that give users a starting point:

1.A report for the Administrator Sales Rep. Modify this report for individualized commission reports by cloning this report and changing the sales rep to create commission reports for the previous month. If you need a report for a different time frame, simply adjust the dates.

2.A report for all sales reps. This will calculate the commission based on all sales reps for the previous month of sales. Again, this report can be modified to adjust dates and reps on the document.

Click Save & Preview after selecting the appropriate filters to save your changes and preview the report. An example is shown below:

Report on Product Database

When you select to create a report based on a products database, you will see an option to select a customer profile.

As described in Setting Up Native Products and Services, you can set up the prices of your products to be different based on the customer you are selling the product to, using this report to create product catalogs for your customers.

If you have priced any of your products using this pricing method, you can specify the customer profile here, and when the report is printed, the product price will be calculated and printed using the selected customer profile. If you have not set up any products in your product database to use customer based pricing, then this customer profile selection will be ignored.

Click Save & Preview after selecting the appropriate filters to save your changes and preview the report. An example is shown below:

Build Filter Expression

The “Build Filter Expression” frame is where you can create the filter expression that will be used to filter the information that will be included in the report. Starting with the basics of filter building, you want to tell QuoteWerks how you would like to filter the information. You do this primarily by telling QuoteWerks that you are looking for a specific value in a specific field in the database.

For example, you might want to tell QuoteWerks that you are only looking for documents that have a document type of QUOTE. The document type is stored in a field called “DocType” and is in the DocumentHeaders table. We know that the field should be in the DocumentHeaders table, because as previously described, any information in the quote that does not pertain to the line items will be in the DocumentHeaders table. The document type is displayed on the Document Items tab of the quote workbook. Figuring out that the field name for the document type is DocType is a process of association. Generally speaking, the field names will very closely resemble the labels given to the fields and displayed in QuoteWerks. When you are selecting a field to add to the filter expression, you will be presented with a list of fields to choose from, so selecting the correct field name should be a fairly intuitive process. A list of fields you can use along with a description for each can be found under Tables and Fields.

Now that we have covered the concept behind selecting a field for use in the filter expression, you can click on the [Field] button to display a window that will let you select the table and then the field that you want to insert into the filter. You can also right click over the filter expression box to display a right click menu, which will give you the option to Add or Insert a field among other options. When you are creating a documents database report, you will be given the option to choose fields from the DocumentHeaders and DocumentItems tables. When you are creating a products database report, you will be given the option to choose fields from the Products table.

Adding a Filter Item

After selecting the [Field] button, the Add Filter Item window will be displayed allowing you to make your field selection:

Table

First select the table of the field that you would like to add to the filter from the Table drop-down. For our example, we want DocumentHeaders.

Field

Second, select the field that you would like to add to the filter from the Field drop-down. For our example, we want DocType.

Operator

Third, select the operator from the Operator drop-down. The simplest operator is the “Equal to” operator. In the concepts that we have described so far, we wanted to create a filter that only returned the documents that had a type Equal to QUOTE. The Equal to is the operator. Another simple operator is the Does Not Equal. You could use this to create a filter that returned all documents that did not have a type of QUOTE. There are many other operators that you can choose as well, with various uses, such as the Contains or Does not Contain operators. You could use these operators in a filter to pull all products with descriptions that contain or do not contain the word “Blue”, for example.

Value

Fourth, enter the value that we want to filter for. In our example, we want to filter for a value of QUOTE.

Filter Item Value Dialog

In our example above, we “hard-coded” a type value of QUOTE. Each time we run the report, it will automatically filter by the QUOTE DocType. So, each time we run the report, if we want to filter for a different type, we would have to first edit this filter item and change the value, save the report, and then run the report. This is not a very efficient process. To make this process more efficient, there is a feature that when used will ask the user for the document type value that they want to use at the time the report is being run.

You will notice that to the right of the Value box there is a button. If you click on this button, the Enter Filter Value window will be displayed:

Static Value

Using this option, you can enter a static value that will be the same every time the report is run. You can manually type in a value here, or you can click on the drop-down to see a list of all the values that are currently in the database for this field. Additionally, if this filter value is for the SalesRep field in the DocumentHeaders table, then the drop-down will also contain a list of all the User Groups. You can select a User Group like <Inside Sales> to filter on a group of users. If this field is a date field, the date selection button will be displayed and can be used to select a date from a calendar dialog.

Prompt for Value

Using this option, you can enter the text that you would like the report to prompt you with when it needs to ask for the document type. As an example you can use the text of “Enter the type of the documents that you want to run the report on:”

You can then set a Default Value (this can be left blank), and optionally specify a list of values for you to choose from at the time the report is run.

Use Macro field

You can also select a macro field from the list of available macro fields. For example, if you wanted to use today’s date as a filter value, rather than having to manually enter the date every time you pull the report, you can use the today’s date macro which is named &Sys_TodaysDateForSQL. That way, every time the report is run, the filter value will automatically populate with today’s date.

There are many macro fields available; scroll through the list to see what other options are available to you.

Lastly, click on the [Ok] button to complete the filter item selection. You will now see this filter item listed in the filter expression window. You have successfully created a simple, single criteria filter expression. “Single criteria” means that you are only filtering one field, though it is possible to filter on more than one field. Let’s say, for example, that in addition to a filter for a DocType Equal to QUOTE, you also wanted to filter for a DocDate Equal to 01/27/2004. The DocDate field is also in the DocumentHeaders table and is typically the date the document was created. We would add this filter exactly the same way that we added the filter for the DocType field.

However, when you create multiple criteria filter expressions, you need to “connect” each criteria with a logical operator such as AND or OR. In our example we want to filter for DocType Equal to QUOTE and DocDate Equal to 01/27/2004, so before we add our second filter criteria of DocDate Equal to 01/27/04, we need to select the [AND] button to add the AND operator after the existing DocType Equal to QUOTE filter:

We will then click on the [Field] button to select the DocDate field, the Equal to operator and the 01/27/2004 filter value. Once we complete those steps, the second filter selection will display below the AND operator, as shown in the screenshot to the right.

Previewing the Filter Results

On the Filter tab, you can also click on the [Preview Filter Results] button to display the Filter Results window:

On this window, you can see the actual data resulting from the filter, including how many records were returned, and what database tables were used.

Clicking on the Filter Expression tab will display the SQL statement used to return the filter results. This is for viewing purposes only, and cannot be modified from this window: