2.Define A Report

Edit

2.1General Overview

Define A Report is a variable report generator that allows access to the POSIM database and helps generate customized report in the desired formats. Define A Report can be a valuable tool in managing your business if used as designed.

Figure 2-1Screen Shot 2014-06-10 at 10.50.31 AM

Categories and Data Sources:

Define A Report breaks down the various areas of the POSIM Database into "Categories". When you select a Category you will see that the list of Data Sources will shrink to the available Data Sources for that category. Each Data Source has a set of columns that you can use to build your report.

In this screenshot below you will see that when the "Cards" category is selected it will result in the following Data Sources being available; Categories, Customers, Employees, Events, Items, Serial Numbers, and Vendors. So as you click between the various categories you will see the relevant Data Sources. To see a list of all the data sources with a description see Data Sources below.

Figure 2-2Screen Shot 2014-06-10 at 10.51.08 AM

At this point you will need to choose which Data Source you want to build your report with. For this example I am going to build a report based on Items. When you click on items you will see the Columns will now show the fields on the item cards. The column list will show which fields will be available to use in your report. Some of the data source selections are linked to other data sources. This means when a base data source is selected, the fields for the selected data source and the fields from any linked data source will be available for use. Linked data source fields are designated by a prefix that is the first few letters from the data source the field is actually found in. For example, the Items data source is linked to the Vendor and Category data sources. The fields from the Vendor data source are preceded by the letter VP. The fields from the Category data source are preceded by the letters CATG.

NOTE:

You can click the "Edit List" button to hide columns that you don't want to see.

To add a columns to the report you would Double-Click the column and it will add that column to the report in the order they are selected. For this example I want the following; I SKU, I Description, I On Hand and I Current Price.

You will see as those columns are selected they populate the Report Results panel with the live item card data.

Data Sources

Cards

Categories

Contains information from the fields on the Category cards (Inventory>Items>Category Card)

Customers

Contains information from the fields on the Customer cards (Sales>Customer>Customer Card)

Employees

Contains information from the fields on the Employee cards (Administration>Employee Administration)

Events

Contains information from the fields on the Event cards (Inventory>Items>Event Card)

Items

Contains information from the fields on the Item cards (Inventory>Items>Item Card)

Serial Numbers

Contains information from the fields on the Serial Number cards (Inventory>Items>Serial No. Card)

Vendors

Contains information from the fields on the Vendor cards (Inventory>Purchasing>Vendor Card)

Closed Trans

Physical Inventory

Contains information from header fields and line items of the Closed Transfer Cards created by Physical Inventory.

Purchase Orders

Contains information from header fields and line items of the Closed Purchase Orders.

Receiving

Contains information from header fields and line items of the Closed Receiving Logs.

Sales

Contains information from header fields and line items of the Closed Invoices.

Sales - Tills

Contains information from header fields of the Closed Invoices with the total Trans Type and Amounts.

Sales Orders

Contains information from header fields and line items of the Working Invoices.

Sales Tax

Contains information from Sales Tax fields of the Closed Invoices.

Transfers

Contains information from header fields and line items of the Closed Transfers.

Doc Headers

Closed Invoices

Contains information from header fields of Closed Invoice Cards, and a recap of the Invoice total amount and quantities. Does not include line item detail.

Closed RL

Contains information from the header fields of the Closed Receiving Logs, and a recap of the RL total amount and quantities. Does not include line item detail.

Closed Transfers

Contains information from the header fields of the Closed Transfers, and a recap of the Transfer total amount and quantities. Does not include line item detail.

Open Invoices

Contains information from header fields of Open Invoice Cards, and a recap of the Invoice total amount and quantities. Does not include line item detail.

Open PO

Contains information from header fields of Open Purchase Order Cards, and a recap of the PO total amount and quantities. Does not include line item detail.

Open RL

Contains information from header fields of Open Receiving Log Cards, and a recap of the RL total amount and quantities. Does not include line item detail.

Open Transfers

Contains information from header fields of Open Transfer Cards, and a recap of the Transfer total amount and quantities. Does not include line item detail.

Working Invoices

Contains information from header fields of Working Invoice Cards, and a recap of the Invoice total amount and quantities. Does not include line item detail.

Working PO

Contains information from header fields of Working Purchase Order Cards, and a recap of the PO total amount and quantities. Does not include line item detail.

Functions

Date-to-Date Pricing Header

Contains information from header fields of Date-to-Date Pricing Cards. Does not include line item detail.

Date-to-Date Pricing Lines

Contains information from header fields and line items of the Date-to-Date Pricing Cards.

Ending Tills

Contains information from all the fields in Ending Tills.

Open Invoice Lines

Contains information from header fields and line items of the Open Invoice Cards.

Physical - Actual Count

Contains information from the current Actual Count report in Physical Inventory.

Physical - Missing Items

Contains information from the current Missing Items report in Physical Inventory.

Physical - Serial Numbers

Contains information from the current Serial Numbers report in Physical Inventory.

Physical - Unidentified Items

Contains information from the current Unidentified Items report in Physical Inventory.

Physical - Variance

Contains information from the current Variance report in Physical Inventory.

Tax Districts

Contains information from Tax Districts set up in General Preferences

Open Trans

Open Invoice Lines

Contains information from header fields and line items of the Open Invoices.

Open PO Lines

Contains information from header fields and line items of the Open Purchase Orders.

Open RL Lines

Contains information from header fields and line items of the Open Receiving Logs.

Open Transfer Lines

Contains information from header fields and line items of the Open Transfers.

2.2Formatting Features

In Define A Report we offer several ways to customize the way your report looks. You can adjust the column widths, remove and rearrange columns, total and subtotal, group, select paper orientation, and adjust the font type and size. Click the links below for more specific information.

Adjusting the column widths

By default the report columns will show about 12 characters. You can adjust the column widths by pointing your mouse between two column headers then click with your mouse and drag left or right to increase or decrease the column width.

You will see in this screenshot I have increased the width of the Description Column. You will also see the new column width when you run the report. See Run / Export to Excel for more information.

Printed Examples

This screenshot shows what the report looks like BEFORE the Description column width is adjusted

This screenshot shows what the report looks like AFTER the Description column width is adjusted

Remove and Rearrange Columns

Remove Columns

If you no longer need a column on a report you can remove the column by righting-clicking on the column header and choosing "Remove Column"

When you select "Remove Column" that column will no longer be on the report.

Rearrange Columns

If you have the report columns in the wrong order you can click on the column header you want to move and drag it to your desired location.

Total and Subtotal Columns

If you have a column that you would like to be totaled you can right-click the column header and choose "Total Column" and/or "Subtotal Column".

The totals/subtotals will show when the report is ran to screen or exported to excel.

NOTE:

Subtotals only work when grouping the report.

Printed Examples

This screenshot shows what the report looks like BEFORE the On Hand column is totalled.

This screenshot shows what the report looks like AFTER the On Hand column is totalled.

Grouping

If you would like the report to be grouped you can choose from the "Group By" drop down list and choose the column you want the report grouped on.

In this example it is grouped by the Item Card Department. You will see the grouping happen when the report is ran to screen or exported to excel.

Showing Detail

There is a check box that says "Show Detail." This check box should be selected when you are grouping a report and you want to see all the detail lines. If you just want to see the grouped totals then you would uncheck this box.

Printed Examples

This screenshot shows what the report looks like BEFORE the report is grouped on Department with Detail ON.

This screenshot shows what the report looks like AFTER the report is grouped on Department with Detail ON.

This screenshot shows what the report looks like AFTER the report is grouped on Department with Detail OFF.

Paper Orientation

By default the the paper orientation will be portrait, but you can check the "Landscape" box to have the format changed.

You will see the paper orientation changed when the report is ran to screen.

Printed Examples

This screenshot shows what the report looks like in PORTRAIT mode.

This screenshot shows what the report looks like in LANDSCAPE mode.

Font Type and Size

If you need to adjust the font type and/or size you can right-click on the column header and choose "Edit Font"

When you select edit font you will see the following:

Using the font drop-down list you can select the font and then type the size value in the size box and press OK. You will see the font changed ONLY when you run the report to screen.

Printed Examples

This screenshot shows what the report looks like in DEFAULT font type and size.

This screenshot shows what the report looks like in CUSTOM font type and size on the Description column.

2.3Custom Formulas

In Define A Report we offer several different types of custom formulas to create your own report columns based on different functions, operators, and equations.

Formula Overview

The formula button is used to add your own custom formulas to the report.

When you click the formula button you will see the following window:

Formula Name: This is where you can type the custom name for the formula. This will become the header of the report column

Formula Window: The Formula window on the left side is used to create the formulas using SQL like syntax. The fields listed in the window on the right side can be selected and copied into this window by double clicking on one. When a field is copied it will appear in brackets. These brackets must remain for the formula to function. You can place operators between the bracketed field titles to create a formula.

OK Button: Click to exit the screen and use the formula in the Report Results window.

Cancel Button: Click to exit the formula screen and go back with no changes made

IF Button: Click to build the template for "IF-THEN-ELSE" statements.

Data Types

There are six general field types you can enter into a formula. A definition of each type is given below.

1. Boolean: Data items that can have “true” or “false” as their only possible values.

2. Integer: Whole numbers (positive or negative but with no decimal point).

3. Real or Numeric: Decimal or floating point numbers.

4. String: Alphanumeric character sequence.

5. Date: Calendar dates in the format 'YYYY-MM-DD'

6. Datetime: Calendar dates in the format 'YYYY-MM-DD HH:MM:SS'

Functions

Several functions can be used for creating your formulas. Click the links for an explanation of the Date and Time, String, and Mathematical functions.

Date and Time Functions

Date and Time Functions allow you to manipulate time units and formatting.

1. DAY('date') - This function returns a number from 1 to 31 corresponding to the day of the given date.

2. DAYNAME('date') - This function returns the name of the day from the supplied date expression.

3. DAYOFWEEK('date') - This function returns a number from 1 to 7 representing the day of the week of the given date, with Sunday=1, Monday =2, and so on.

4. HOUR('datetime') - This function returns a number from 0 to 23 corresponding to the hour component of the given date.

5. MINUTE('datetime') - This function returns a number from 0 to 59 corresponding to the minute component of the given date/time.

6. MONTH('date') - This function returns a number from 1 to 12 corresponding to the month of the given date.

7. MONTHNAME('date') - This function returns the name of the month from the supplied date expression.

8. QUARTER('date') - This function returns the quarter from the supplied date expression. For example, 1 = first of January to the end of March, 2 = first of April to the end of June etc.

9. SECOND('datetime') - This function returns a number from 0 to 59 corresponding to the second component of the given date.

10. YEAR('date') - This function returns a 4 digit number corresponding to the year of the given date.

11. DATE('datetime') - This function converts the expression into a date, and removes any hours, minutes, or seconds.

12. DATE_FORMAT('date','%M %D %Y') - This function returns a string representing the date expression ('date') in the format specified by the string expression ('%M %D %Y'). For example, DATE_FORMAT('2011-08-09','%M %D %Y') would return 'August 9th 2011'

13. NOW() - This function returns the current date and time.

NOTE:

1. date variable means 'YYYY-MM-DD'

2. datetime variable means 'YYYY-MM-DD HH:MM:SS'

3. For all the examples above you would substitute 'date' or 'datetime' with the actual date.

4. You can also use linked date fields using the Available Column selector in the formula window. For example DAYNAME([I Last Sold On]) would tell you the name of day when an item last sold.

String Functions

String Functions allow you to convert the string representation of a number to its numerical representation.

1. LENGTH('string') - This function returns the number of characters in the string.

2. LOCATE('string1', 'string2', numeric)- This function returns the character offset into the string string1 of the first occurrence of the string string2. If numeric is specified, the search will start at that offset into the string.

For example, you have several Item Cards that use the Misc 2 field to define the color, waist size, and inseam for pants. In reviewing some of the cards, you have discovered that some of the information has been listed in the order of color, waist size, and inseam (Red-34-36). However some of the cards have the information listed as waist size, inseam, color (34-36-Red). You want to run a report that tells you which cards have the information in which spaces so you can make all the cards the same without having to look at every card. You select Items data source and double click the Item SKU field from the columns to the Report Results fields window. Click the Formula button and create the following formula.

LOCATE('34', [I Misc 2])

When you click OK the report the column for this formula will have a 5 for all the Item Cards with the 34 waist size listed second in the Misc 2 field. A number 1 will be listed for those that have the 34 waist size listed first in the Misc 2 field. You can then go to all the Item Cards with a number 1 in the Locate column and correct the information in the Misc 2 field.

3. SUBSTR('string', start, length) - This function returns the substring of a string starting at the given start position (start). If the length is specified, the substring is restricted to that length. The starting position can be positive or negative. A positive starting position specifies a number of characters from the start of the string, while a negative starting position specifies a number of characters from the end of the string. The length specifies the number of characters that will be returned in the substring. Using appropriate combinations of negative and positive numbers, you can easily get a substring from either the beginning or end of the string.

For example, you have several Item Cards that use the Misc 2 field to define the color, waist size, and inseam for pants (Red-34-36). You want to list the waist size entered on each Item Card on a report. You select Items data source and double click the Item SKU field from the columns to the Report Results fields window. Click on the Formula button and create the following formula.

SUBSTR([I Misc 2],5,2)

When you preview the report, this column will show the characters that are in the fifth and sixth spaces of the Misc 2 field of each Item Card. This would be 34 for an Item Card that had Red-34-36 entered in the Misc 2 field.

Mathematical Functions

Mathematical functions allow you to manipulate number and value units.

1. FLOOR(numeric) - This function returns the floor (largest integer not greater than) of numeric.

2. ROUND(numeric, integer) - Rounds numeric to integer places after the decimal point. A positive integer determines the number of significant digits to the right of the decimal point; a negative integer, the number of significant digits to the left of the decimal point.

Multiple Functions

It is possible to combine different functions when creating a formula. For example, you have several Item Cards that use the Misc 2 field to define the color, waist size, and inseam for pants. When the cards were created, the information was not entered in a consistant manner. Following are examples of the various ways the information is shown.

blu-34-36

34-red-36

34-36-yel

You want to create a formula that will create a column that shows the color listed in the Misc 2 field on each Item Card. Such a formula can be created using LOCATE, SUBSTR, and IF-THEN-ELSE functions.

IF ((LOCATE('3',[I Misc 2],3)=8), SUBSTR([I Misc 2],4,3),

IF((LOCATE('3',[I Misc 2],3)=5), SUBSTR([I Misc 2],1,3),

IF ((LOCATE('3',[I Misc 2],3)=4), SUBSTR([I Misc 2],7,3), '')))

When you preview the report, this column will show strings with characters that are in the first, fourth, or seventh position of the Misc 2 field of each Item Card. This would be blu, red, or yel for the example Misc 2 fields given above.

Operators and Equations

Operators and Equations are used to define the relationship between the variables. Following is a list of common operators you might use while creating formulas and filters.

1. Arithmetic Operators

variable + variable Addition

variable – variable Subtraction

-variable Negation

variable * variable Multiplication

variable / variable Division

2. Comparison Operators

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

!= Not equal to

!> Not greater than

!< Not less than

3. IF-THEN-ELSE Operator. The IF-THEN-ELSE operator allows you to specify that IF certain criteria is met, THEN apply expression 1, ELSE (if the criteria are not met) apply expression 2.

The syntax for IF-THEN-ELSE is:

IF ( TRUE , 'a', 'b')

This means IF the variable matches TRUE then apply else apply .

NOTE: EVO will build this template syntax when you press the "IF" button on the filter screen.

a. Example 1.

IF ([I Catg Code]='Book' , ([I On Hand]*[I Curr Price]) , '')

This formula will look at Category Code on the Item Card. If the information in the Category Code field equals ‘Book’, then the sum of the On Hand multiplied by the Current Price will be placed in the report column. If the Category Code field does not equal ‘Book’, then the report column be left blank.

b. Example 2.

IF ([I SKU]>='N' , 'Advanced', 'Intermediate')

This formula will look at the Item SKU on the Item Card. If the information in the Item SKU field is greater than or equal to ‘N’, then the word "Advanced" will be placed in the report column. If the Item SKU field is less than ‘N’, then the word "Intermediate" will be placed in the report column.

4. Other Symbols

'' Use single quotes to identify a string field type.

[ ] Use brackets to identify a field name in a data source.

( ) Use parentheses to group operations.

Editing Existing Formulas

If you need to edit an existing formula you can right-click the report column header and choose "Edit Column Formula." This will bring up the formula box allowing you to make any changes. See Custom Formulas for more information on formulas.

2.4Custom Filters

Often times there is more information on the report than is needed or wanted. In Define A Report we offer a couple ways to filter, or narrow down, your report.

Graphical Interface Filter Method

To filter your report with the Graphical Interface Method you will right-click on the column header for the column you want filtered and choose "Filter Column."

When you click on Filter Column you will see the following window open.

This window allows you to use two levels of filtering separated with AND/OR radio selection.

Examples:

In these examples I am going to filter on the Item SKU.

Example 1 - If you are looking for one specific item you would type the item SKU in both the "From" and "To" boxes on the left side.

When you click OK on this window it would narrow down the report to just that one SKU.

Example 2 - If you are looking for a range of items, you would type the first SKU in the "From" box and the last SKU in the "To" box on the left side.

When you click OK on this window it would narrow down the report to just that range of SKUs.

Example 3 - If you are looking for two separate ranges of SKUs you would use the "From"/"To" on both sides to put your SKU ranges and choose the "OR" radio button.

When you click OK on this window would narrow down the report so show the items in both ranges.

NOTE:

1. You must have the report columns already added before you can filter.

2. You can apply multiple filters on separate columns at the same time if you need to further filter the report.

Traditional Filter Method

To filter your report with the Traditional Method you would use the Filter Criteria section of the Define A Report window.

This Filter box allows you to use SQL like syntax to filter your report. The syntax for this box is the same as the Custom Formulas and uses Operators to narrow down the report result.

Examples:

In these examples I am going to filter on the Item SKU.

Example 1 - If you are looking for one specific item you would click the "Add Field-->" button and choose I SKU from the list and press OK. This will add [I SKU] to the filter box allowing you to type operators and then the specific Item SKU. The filter would look like this:

[I SKU]=1015

This means you are looking for a SKU that equals '1015'. At this point you would press "Refresh Report Data" for the filter to activate. The report would then narrow down to just that one SKU.

Example 2 - If you are looking for range of items you would click the "Add Field-->" button and choose I SKU from the list and press OK. This will add [I SKU] to the filter box allowing you to type operators and then the range of Item SKUs. The filter would look like this:

([I SKU]>=1015) AND ([I SKU]<=1019)

This means you are looking for range of SKUs that are greater than or equal to '1015' AND less than or equal to '1019'. At this point you would press "Refresh Report Data" for the filter to activate. The report would then narrow down to just that range of SKUs.

Example 3 - If you are looking for two separate ranges of SKUs you would click the "Add Field-->" button and choose I SKU from the list and press OK. This will add [I SKU] to the filter box allowing you to type operators and then the ranges of Item SKUs. The filter would look like this:

(([I SKU]>=1015) AND ([I SKU]<=1019)) OR (([I SKU]>=1050) AND ([I SKU]<=1099))

This means you are looking for two ranges of SKUs. One that is greater than or equal to '1015' AND less than or equal to '1019' OR greater than or equal to '1050' AND less than or equal to '1099'. At this point you would press "Refresh Report Data" for the filter to activate. The report would then narrow down to so show the items in both ranges.

NOTE:

1. You must have the report columns already added before you can filter.

2. You can have multiple filters used at the same time, but each unique filter must be placed between parenthesis () with an AND or OR between the unique filter sets.

2.5Run / Export to Excel

This section will show you how to Run the created reports to screen and export them to Excel.

Run Report

This button will show a print preview of how the report would look on a piece of paper.

When you click "Run Report" you will see the following:

This window will auto-populate the report name with the current Data Source and the current date. When you click OK you will see the following:

From here you can print the report or if you need to make changes you can close this print preview and you be taken back to the Define A Report window.

Export to Excel

This button will export the report data to an .xls file that can be opened in Excel or any compatible spreadsheet application.

When you click "Export to Excel" you will see the following:

This window will auto-populate with "DAR_" and the current date. When you click SAVE the file will be saved to your selected destination. At this point you could open the file in Excel or compatible spreadsheet application. Below is an example of the file opened in Excel.

2.6Saved Reports

Often times you will make a report that you want to continue to run in the future. In Define A Report we offer a great way to keep your saved reports organized and centralized.

Save to Database

This button will save the report definitions to the database and it will be accessible from any workstation connected to the POSIM database.

When you click "Save to Database" you will see the following:

This window will allow you to give this report a custom name. When you click OK the report definition will be saved in the database. At this point you could view a list of your saved reports.

You can now double click on the report you would like to run and it will populate the Report Results window. When you double click on a report it will warn you that it will load the new report.

If you want to make changes to an existing saved report you can double click the report so it shows in Report Results. Then make any changes you would like to make then click "Save to Database" again and give the report the same name as before and it will overwrite the previous saved report definitions.

Your clerks can also access a non-editable version of the report in the regular reports window by clicking on the "User Defined" tab.

When your clerks double-click on the custom report they will be able to print it or export it to Excel.

2.7Export & Load Report Definitions

In Define A Report we offer a great way to export and load your saved report definitions. Often your VAR or a member of tech support will make a report on their machine and with this feature they can send you a report definition for your database. You can also use this if you have multiple store locations that use POSIM. You can make the report at one location and send the report definition to your other location(s).

Export Report Definitions

This button will export the report definitions to be loaded onto another database.

When you click "Export Report Definitions" you will see the following:

This window will allow you to give the report definition a custom name and there must be an .xml extension. When you click SAVE the file will be saved to your selected destination. At this point you would transfer the file to the location that would load this report.

Load Report Definitions

This button will load the report definitions from the exported file to create a new report in your database.

When you click "Load Report Definitions" you will see the following:

This window will allow you to locate the exported file saved on your computer. When you click OPEN the Report Results window will show the report data.

2.8Auto Exporting

This is considered an advanced feature. Please contact our support department if you need assistance.

This feature allows users who export frequently to automate the process. You will create a saved report in Define A Report and then use this tool to automate an export of the data to an Excel or CSV file.

The first step, or task, is to create the actual report in Define A Report. If you are not familiar with the process of creating a report in Define A Report, please review that documentation here. In this example I have created a simple "Items" report to export Item data. You can see below that the report is saved with the name "Items".

Figure 2-3Screen Shot 2016-01-12 at 8.26.52 AM

Command Line Usage

The filename extension can be either .xls or .csv and that will determine the filetype. The examples below will save the exported file to your desktop. The following commands are to be ran using the Terminal or Command Prompt application on your Mac or Windows computer.

Mac:

cd "/Applications/POSIM EVO.app/Contents/Java/" && ./java -jar Juniper.jar --darexport "<Saved_DAR_Report_Name>" "~/Desktop/<filename>.xls"

Windows:

cd "\Program Files\POSIM EVO" & "java\bin\java.exe" -jar Juniper.jar --darexport "<Saved_DAR_Report_Name>" "%HOMEPATH%\Desktop\<filename>.csv"

Command Line Usage Examples

This example will export the "Items" Define A Report to the Desktop as an Excel file on Mac and a CSV file on Windows.

Mac:

cd "/Applications/POSIM EVO.app/Contents/Java/" && ./java -jar Juniper.jar --darexport "Items" "~/Desktop/Items.xls"

Windows:

cd "\Program Files\POSIM EVO" & "java\bin\java.exe" -jar Juniper.jar --darexport "Items" "%HOMEPATH%\Desktop\Items.csv"

It is also possible to run this periodically using the scheduled tasks, or cron, feature on your Windows or Mac computer. Please contact our support department if you need assistance.
Count of comments [0]