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, Documents (which contains all the quotes/order/invoices), one of the Product databases (which contains the lists of products and services that you sell), Purchase Orders (purchasing), Payments, Contacts, Vendors, Bundles, Configurations, Required Items, and Optional Items. 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 from the “Select Database to report on” drop down.

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. 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 Bundles

When you select to create a report based on Bundles you will see two checkbox options.  Bundles use data from two tables - the BundleHeaders table and the BundleItems table. The BundleHeaders table contains one record for each Bundle and contains information such as Type (simple or grouped), Description, Customtext values, etc. In other words, any fields for which there are only one per Bundle. The BundleItems table contains a record for each item in each Bundle, and contains item detail information such as Quantity for the Bundle, Manufacturer Part Number, Attributes, Source Database, etc.

The “Force inclusion of BundleHeaders table” and “Force inclusion of BundleItems table” options pertain to how QuoteWerks uses the tables in conjunction with the filter expression. When building the filter expression, if you only specify BundleItems fields in the filter, QuoteWerks will omit the BundleHeader fields in the resulting data that it returns. If you do want the BundleHeader fields even though you are only filtering on BundleItems field values, you will need to check the “Force inclusion of Bundle Headers table” checkbox.  You can create reports without any filters as well; for example if you wished to create a report on all of your Bundles.

Report on Configurations

When you select to create a report based on Configurations you will see three checkbox options.  Configurations use data from three tables - the ConfigurationHeaders table, the ConfigurationContainers table, and the ConfigurationItems table. The ConfigurationHeaders table contains one record for each Configuration and contains information such as Type (simple or grouped), Description, Customtext values, Notes, etc. In other words, fields for which there are only one per Configuration.  The ConfigurationContainers table contains a record for each Container in each Configuration, and contains information such as Max and Min selections, Prompt, Next container ID, Attributes, etc. The ConfigurationItems table contains a record for each item in each Configuration, and contains item detail information such as Quantity for the container, Manufacturer Part Number, Attributes, Source Database, etc.

The “Force inclusion of ConfigurationHeaders table”, “Force inclusion of ConfigurationContainers table” and “Force inclusion of ConfigurationItems table” options pertain to how QuoteWerks uses the tables in conjunction with the filter expression. When building the filter expression, if you only specify ConfigurationItems fields in the filter, QuoteWerks will omit the ConfigurationHeader and ConfigurationContainers fields in the resulting data that it returns. If you do want the ConfigurationHeader or ConfigurationContainers fields even though you are only filtering on ConfigurationItems field values, you will need to check the “ appropriate checkbox. You can create reports without any filters as well; for example if you wished to create a report on all of your Configurations.

 

Report on Contacts

When you select to create a report based on Contacts you will be able to easily create a printable or exportable list of your QuoteWerks Contacts.  This can be useful for marketing email lists or address book reviews.  You can also easily filter on specific Company names or other Contact fields to ensure you get the data you are looking for.  This report is ONLY for the QuoteWerks Contacts list and does not apply to external CRM systems.

Report on Optional Items

When you select to create a report based on Optional Items you will see two checkbox options.  Optional Items use data from two tables - the OptionalHeaders table and the OptionaItems table. The OptionalHeaders table contains one record for each set of Optional Items and contains information such as the main Part Number that drives the options, Source Database, etc. The OptionsItems table contains a record for each item that is an option of the main item, and contains item detail information such as Quantity for the option, Manufacturer Part Number, Attributes, Source Database, etc.

The “Force inclusion of OptionalHeaders table” and “Force inclusion of OptionalItems table” check boxes pertain to how QuoteWerks uses the tables in conjunction with the filter expression. When building the filter expression, if you only specify OptionalItems fields in the filter, QuoteWerks will omit the OptionalHeader fields in the resulting data that it returns. If you do want the OptionalHeader fields even though you are only filtering on OptionalItems field values, you will need to check the “Force inclusion of Bundle Headers table” checkbox.  You can create reports without any filters as well; for example if you wished to create a report on all of your Optional Items.

Report on Payments

If you have QuoteValet and accept payments through QuoteValet payments, you can create a report to show all the payments received - whether online or by proxy - that have been retrieved from QuoteValet.  The Payments table is auto-joined with the DocumentHeaders table so you can even show what the remaining balance on a document is.

 

 

 

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: