You software allows you to create your own custom reports. There is no limit to the number of reports you can create. Each time you create a new report, you have the option of having it added to the Reports pull-down menu. You can even create your custom reports by basing them on the built-in reports that come with your software. Creating a custom report is not difficult. However, you need to be familiar with the following two topics:
The database format. The database is where the software holds all your information. It contains a list of all your clients, products, services, sales, etc. These items are grouped together in separate lists known as tables. The tables are further subdivided into smaller categories called fields, i.e. the Clients table has a field called FirstName, which is simply a list of all the clients' first names. You can see a complete list of the database format by opening the file called, DB Schema.xml, located on the software CD or in the program's folder on your hard drive (typically located at C:\Program Files(x86)\[Program Name]\DB Schema.xml).
Structured Query Language. The Structured Query Language, or SQL, is a series of commands you use to specify what information you want in your report. The format consists of abbreviations and words similar to English.
Creating Custom Reports
Select Manage Custom Reports from the Reports pull-down menu.
The Custom Reports screen will appear.
Click the Add button.
Enter the title and description of the report.
Add the SQL statement. See the examples in the Structured Query Language Information section of this article for assistance on writing the SQL statement.
If desired, select from the following options:
Show results after running report: check this if the report is generating information that you need displayed, i.e. generating a report of all clients that came in on a specific date. If the report is being used to make changes to a specific field, then this option can be unchecked, i.e. setting all client discounts to 0
Fix first column: select this option if you do not want to be able to click the column headings to sort the data
Show subtotals row for each change in first column: check this option if the report needs to feature subtotals under each row
Show totals row at end of report: check this option if you are generating a report that is to show a total amount
Click the Run button to view your report.
Click the OK button to save it.
Creating Custom Reports Based on Built-In Reports: you can view the SQL statements for approximately 90% of the built-in reports into your software. Importing the built-in reports is a great way to see how the reports were written so you can model your own reports after them.
Select Manage Custom Reports from the Reports pull-down menu.
The Custom Reports screen will appear.
Click the Add button.
The Modify Custom Report screen will appear.
Click the Import button.
The Available Reports screen will appear.
Select the report you wish to import the SQL statements for and click OK.
Make the desired changes to the SQL statement, Title, and Description.
Click OK to return to the Custom Reports screen.
Structured Query Language Introduction
Below is a basic introduction to the SQL language. This introduction will allow you to write basic reports. SQL statements can range from very simple to extremely complex. Because SQL is one of the most popular database languages, hundreds of books have been written on it. We highly recommend purchasing a book on SQL if you would like to become more familiar with the language. After you have a firm understanding of SQL, you will be able to write your own very sophisticated reports.
The basic format of a SQL statement is as follows:
SELECT <field> FROM <table>
Where:
<field> are a list of the items, or fields, that you want to view, or * if you would like to view all fields
<table> is the name of the table containing the items
Example 1: List of All Clients: as mentioned, there is a table in the database called Clients that contains a field called FirstName. A SQL statement to obtain a list of all the first names of all your clients would be:
SELECT fldFirstName FROM tblClients
Notice that all fields are preceded by fld and all tables are preceded by tbl. This is true for all fields and tables in your software.
You could also obtain a list of all the information on each client with the following statement:
SELECT * FROM tblClients
Now let's create a custom report that shows a list of all the clients.
Select Manage Custom Reports from the Reports pull-down menu.
The Custom Reports screen will appear.
Click the Add button.
The Modify Custom Report screen will appear.
Enter the following information:
Click the Run button.
You should see a list of all your clients and all their information. Each field name (heading of each
column) is preceded by fld. We will show you how to clean this up in the next example.Click OK.
Click Close to finish.
Example 2: Better List of All Clients: Example 1 showed you how to get a basic list of all clients and all their information. The result was a list that was very complete, but also very hard to read as the column headings were all preceded with fld. You can indicate your own column heading names by using the AS command. You can also specify what fields you want displayed. In the example below, you can type anything you want in the brackets: [Type anything you want in the brackets]. Whatever is between the brackets is your new column heading.
Example 3: Show Only Certain Clients: now let's make a more useful report. We will create a report that contains a list of all clients who have a first name of Chris and have spent more than $100. We will use the WHERE command to do this. The WHERE command is placed after the name of the table and is used to select only entries matching a specific criteria.
The above examples are only an introduction to SQL. The language is very sophisticated and supports dozens of other keywords. Over 90% of the reports in Salon Iris were written using pure SQL. You can view all the SQL statements by using the Import button when editing a report, then selecting the built-in report that contains the SQL statement you wish to view.
Built-In User Prompts: there will be times when you want to ask the user for information when you are writing a report. In addition to all SQL commands, Salon Iris allows you to embed additional commands in your SQL statements. They are as follows:
GetUserInput (<prompt>)
<prompt> is the question you want to display to the user.
When this keyword is embedded in an SQL statement, the user will be prompted to enter a value. The value is inserted into the original SQL statement in place of the GetUserInput keyword.
GetUserDate (<type>, <field name>, <prompt>)
<type> is WHERE or AND
<field name> is the name of the field you want to compare the selected date to
<prompt> is the question you want to display to the user
When this keyword is embedded in the SQL statement, the user will be prompted to enter a range of dates. The range of dates selected by the user is transformed into another SQL statement using the <field name> that is passed. The result is inserted into the original SQL statement in place of the GetUserDate keyword
GetUserEmployees(<type>, <field name>, <prompt>)
<type> is WHERE or AND
<field name> is the name of the field you want to compare the selected Employee ID to
<prompt> is the question you want to display to the user
When this keyword is embedded in the SQL statement, the user will be prompted to select employees this report applies to. The employee list selected by the user is transformed into another SQL statement using the <field name> that is passed. The result is inserted into the original SQL statement in place of the GetUserEmployees keyword
Example Using GetUserInput(<prompt>)
SELECT * FROM tblClients WHERE fldFirstName='GetUserInput('Enter the client's first name.')'
When this report is run, the user will be prompted with a pop-up window asking to 'Enter the client's first name.' If the user enters 'Bill', the final SQL statement would be:
SELECT * FROM tblClients WHERE fldFirstName='Bill'
Example 1 Using GetUserDate(<type>,<field name>,<prompt>)
SELECT * FROM tblClients GetUserDate('WHERE','fldLastVisit','Enter last visit date range')
When this report is run, the user will be prompted with a pop-up window and asked to enter a date or range of dates. In this example, to select a date range from Jan 1, 2010 to Jan 1, 2012, the final SQL statement would be:
SELECT * FROM tblClients WHERE fldLastVisit >= DateSerial(2010,1,1) AND fldLastVisit <= DateSerial(2012,1,1)
Example 2 Using GetUserDate(<type>,<field name>,<prompt>)
If we wanted to specify additional search criteria, we would place it in prior to the GetUserDate keyword and then use the AND type in the GetUserDate keyword.
SELECT * FROM tblClients WHERE fldFirstName='Chris' GetUserDate ('AND','fldLastVisit','Enter last visit date range')
When this report is run, the user will be prompted with a pop-up window and asked to enter a date or range of dates. In this example, to select a date range from Jan 1, 2010 to Jan 1, 2012, the final SQL statement would be:
SELECT * FROM tblClients WHERE fldFirstName='Chris' AND fldLastVisit >= DateSerial(2010,1,1) AND fldLastVisit <= DateSerial(2012,1,1)
Example Using GetUserEmployees(<type>,<field name>,<prompt>)
SELECT fldFirstName, fldLastName, fldHomePhone FROM tblEmployees GetUserEmployees('WHERE','fldEmployeeID','Select the employees to list')
When this report is run, the user will be prompted with a pop-up window and asked to select which employees to run the report for. In this example, to select employees only with IDs 1000, 1001, and 1002, the final SQL statement would be:
SELECT fldFirstName, fldLastName, fldHomePhone FROM tblEmployees WHERE fldEmployeeID IN (1000, 1001, 1002, 0)