Transcript
Quick Reporting Tool – User Guide
Quick Reporting Tool Copyright © 2012 Little Creations Software
Company web site: http://www.little-creations.net/
Copyright © 2012 Little Creations Software
email:
[email protected]
0
Quick Reporting Tool – User Guide
Table of Contents INTRODUCTION TO QUICK REPORTING TOOL ........................................................................................................ 3 MODULES OF QUICK REPORTING TOOL ................................................................................................................. 4 DEVELOPER MODULE ......................................................................................................................................................4 END USER MODULE........................................................................................................................................................5 SQL QUERY OF QUICK REPORTING TOOL ............................................................................................................... 6 SQL QUERY WITHOUT PARAMETER ....................................................................................................................................6 SQL QUERY WITH PARAMETER ..........................................................................................................................................7 WHAT IS PARAMETER TYPE ................................................................................................................................... 8 NUM – NUMERIC PARAMETER TYPE ..................................................................................................................................8 NLIST – NUMERIC LIST PARAMETER TYPE ...........................................................................................................................9 STR – STRING PARAMETER TYPE......................................................................................................................................10 PWD - PASSWORD STRING PARAMETER TYPE ....................................................................................................................11 SLIST – STRING LIST PARAMETER TYPE .............................................................................................................................12 TEXT – TEXT PARAMETER TYPE .......................................................................................................................................13 DATE - DATE PARAMETER TYPE ......................................................................................................................................14 TIME - TIME PARAMETER TYPE .......................................................................................................................................16 DT - DATETIME PARAMETER TYPE ....................................................................................................................................18 XSQLPICKY – SQL DROP-DOWN LIST PARAMETER TYPE .......................................................................................................20 XFIXPICKY – FIX DROP-DOWN LIST PARAMETER TYPE ..........................................................................................................20 WHAT IS PARAMETER NAME ............................................................................................................................... 29 MORE EXAMPLES OF SQL QUERY WITH PARAMETER(S)....................................................................................... 36 CONNECT TO A DATABASE ................................................................................................................................... 43 STEP 1 – OPEN DATABASE CONNECTION DIALOG BOX .........................................................................................................43 STEP 2 – CREATE A DATABASE CONNECTION ......................................................................................................................43 STEP 3 – ENTER DATABASE CONNECTION DETAILS ...............................................................................................................45 STEP 4 – CONNECT TO YOUR DATABASE ............................................................................................................................46 CREATE QUERY WITH DEVELOPER MODULE ........................................................................................................ 47 STEP 1 – CONNECT TO A DATABASE .................................................................................................................................47 STEP 2 – CREATE QUERY ................................................................................................................................................47 STEP 3 – SAVE QUERY ...................................................................................................................................................48 EXECUTE QUERY WITH END USER MODULE ......................................................................................................... 50 STEP 1 – BROWSE AND FIND QUERY .................................................................................................................................50 STEP 2 – EXECUTE QUERY ..............................................................................................................................................51 STEP 3 – ENTER PARAMETER VALUE (IF ANY)......................................................................................................................52 STEP 4 – DATA RETRIEVED .............................................................................................................................................53 EXPORT DATA TO DELIMITED TEXT FILE ............................................................................................................... 54 STEP 1 – SAVE RESULT TO FILE ........................................................................................................................................54 STEP 2 – CHOOSE DELIMITER CHARACTER(S) ......................................................................................................................55 STEP 2 – CHOOSE DELIMITER CHARACTER(S) ......................................................................................................................55 STEP 3 – ENTER FILE NAME ............................................................................................................................................56 STEP 4 – DATA EXPORTED ..............................................................................................................................................57
Copyright © 2012 Little Creations Software
1
Quick Reporting Tool – User Guide PASSING COMMAND LINE PARAMETERS ............................................................................................................. 58 QUICK REPORTING TOOL’S PARAMETERS ..........................................................................................................................58 EXAMPLES FOR PASSING THE PARAMETERS.........................................................................................................................59 CONTACT INFORMATION ..................................................................................................................................... 60
Copyright © 2012 Little Creations Software
2
Quick Reporting Tool – User Guide
Introduction to Quick Reporting Tool Quick Reporting Tool is a simple but powerful SQL Query tool built for SQL developers to create SQL query (with or without parameter) for their end users to execute and export data efficiently from database to file with minimum steps. In this tool, SQL developers are not only able to create and execute a SQL query with “SELECT” statement but they are also able to create and execute a SQL query with any SQL statement like “INSERT”, “UPDATE”, “DELETE”, “TRANCATE”, “ALTER”, etc.
Copyright © 2012 Little Creations Software
3
Quick Reporting Tool – User Guide
Modules of Quick Reporting Tool The Quick Reporting Tool software is consists of two parts, the Developer Module and End User Module.
Developer Module This module allows the SQL developer to connect to any kind of databases via OLEDB and ODBC to execute the SQL query. It also allows the SQL developer to create, edit and save the SQL query together with the connection string into an encrypted file (.qri) so that end users are able to execute it in the End User Module. Screenshot of the Developer Module:
Copyright © 2012 Little Creations Software
4
Quick Reporting Tool – User Guide
End User Module This module is for users to retrieve and export data from database by execute the SQL query that is created from the Developer Module. SQL query could not be modified from here as the End User Module does not allow end users to create or edit any SQL query. Screenshot of the End User Module:
Copyright © 2012 Little Creations Software
5
Quick Reporting Tool – User Guide
SQL query of Quick Reporting Tool SQL query can be a normal SQL statement (SQL query without parameter) or a SQL statement with parameter (SQL query with parameter).
SQL query without parameter Example of SQL query without parameter: Select top 1000 * from Trn_PR where Trn_userid = ‘0908’ and Status <> ‘C’ A Screenshot of SQL query without parameter:
Copyright © 2012 Little Creations Software
6
Quick Reporting Tool – User Guide
SQL query with parameter Example of SQL query with parameter(s): Select * from Mtn_AccountBudget where BudgetYear = ?NUM_”Budget Year”? A Screenshot of SQL query with parameter:
Result after click OK button (example):
In the example above, ?NUM_”Budget Year”? is parameter of a SQL query. For a parameter to be valid, it must begin with a question mark [ ? ] and also end with a question mark [ ? ]. Characters in between the first question mark [ ? ] and the underscore [ _ ] (i.e NUM) is referred to as the parameter type (which is not case sensitive), whereas characters in between the underscore [ _ ] and the next question mark [ ? ] (i.e “Budget Year”) is called the parameter name. If a parameter name contains space character or pipe [ | ], then it must be enclosed in double quotes [ " ]. If a SQL Query contains one or more parameters, a parameter's screen will pop up after the user clicked on the execute button, to allow the user to input the parameters' value. Copyright © 2012 Little Creations Software
7
Quick Reporting Tool – User Guide
What is Parameter Type Quick Reporting Tool is basically consists of 25 parameter types. i.e NUM, NList, Str, Pwd, SList, Text, Date, Time, DT, NSqlPickR, NSqlPickW, NSqlPickC, NFixPickR, NFixPickW, NFixPickC, SSqlPickR, SSqlPickW, SSqlPickC, SFixPickR, SFixPickW, SFixPickC, VSqlPickR, VSqlPickC, VFixPickR and NFixPickC. Each of the parameter type is illustrated below with examples and screenshots:
NUM – Numeric parameter type Single-line editor – Value is returned in decimal format. (Without single quotes [ ‘ ]) Example: SQL Query input by user: Select * from Mtn_AccountBudget where BudgetYear = ?Num_”Budget Year”? Screenshot of parameter value input screen:
Text returned by parameter: ?”NUM_Budget Year”? 2010 SQL to be executed: Select * from Mtn_AccountBudget where BudgetYear = 2010 Copyright © 2012 Little Creations Software
8
Quick Reporting Tool – User Guide
NLIST – Numeric List parameter type Multi-line editor – Value(s) are returned in decimal format and enclosed by brackets. Each value is separated by commas. (Without single quotes [ ‘ ]) Example: SQL Query input by user: Select * from Mtn_AccountBudget where BudgetYear in ?NList_”Budget Year:”? Screenshot of parameter value input screen:
Text returned by parameter: ?NList_”Budget Year:”? (2008,2009,2010,2011) SQL to be executed: Select * from Mtn_AccountBudget where BudgetYear in (2008,2009,2010,2011)
Copyright © 2012 Little Creations Software
9
Quick Reporting Tool – User Guide
STR – String parameter type Single-line editor – Value is returned in string format. (With single quotes [ ‘ ]) Example: SQL Query input by user: Select * from Mtn_AccountBudget where AccountNo = ?Str_”Account No:”? Screenshot of parameter value input screen:
Text returned by parameter: ?Str_”Account No:”? '221000.7931.100' SQL to be executed: Select * from Mtn_AccountBudget where AccountNo = '221000.7931.100'
Copyright © 2012 Little Creations Software
10
Quick Reporting Tool – User Guide
PWD - Password String parameter type Single-line mask editor – Value is returned in string format. (With single quotes [ ‘ ]) Example: SQL Query input by user: Select * from User_Profile where User_ID = ?Str_”Login ID:”? and Password = ?Pwd_Password:? Screenshot of parameter value input screen:
Text returned by parameters: ?Str_”Login ID:”? '8088' ?Pwd_Password:? '12345678' SQL to be executed: Select * from User_Profile where User_ID = '8088' and Password = '12345678'
Copyright © 2012 Little Creations Software
11
Quick Reporting Tool – User Guide
SLIST – String List parameter type Multi-line editor – Value(s) are returned in string format and enclosed by brackets. Each value is separated by commas. (With single quotes [ ‘ ]) Example: SQL Query input by user: Select * from Mtn_AccountBudget where AccountNo in ?SList_”Account No:”? Screenshot of parameter value input screen:
Text returned by parameter: ?SList_”Account No:”? (‘7931.100','7931.200','7684.100') SQL to be executed: Select * from Mtn_AccountBudget where AccountNo in ('7931.100','7931.200','7684.100')
Copyright © 2012 Little Creations Software
12
Quick Reporting Tool – User Guide
TEXT – Text parameter type Multi-line editor – Value is returned in multi-line string format. (With single quotes [ ‘ ]) Example: SQL Query input by user: Select * from Trn_PR_Detail where ItemDesc = ?Text_”Item Desc:”? Screenshot of parameter value input screen:
Text returned by parameter: ?Text_”Item Desc:”? '1) PANASONIC SINGLE LINE PHONE C/W FLASH, REDIAL BUTTON & VOLUME CONTROL 2) PANASONIC ITS DISPLAY/SPEAKERPHONE C/W 30-STATION ONE-TOUCH/SPEED DIALER, DATA PORT & DIAL LOCK' SQL to be executed: Select * from Trn_PR_Detail where ItemDesc = '1) PANASONIC SINGLE LINE PHONE C/W FLASH, REDIAL BUTTON & VOLUME CONTROL 2) PANASONIC ITS DISPLAY/SPEAKERPHONE C/W 30-STATION ONE-TOUCH/SPEED DIALER, DATA PORT & DIAL LOCK'
Copyright © 2012 Little Creations Software
13
Quick Reporting Tool – User Guide
DATE - Date parameter type Date editor with drop-down calendar – Value is returned in date [yyyy-mm-dd] format. (With single quotes [ ‘ ]) Example in SQL Server: SQL Query input by user: Select * from Mtn_AccountBudget where convert(char(10),Trn_DateTime,120) = ?Date_”Transaction Date:”? Screenshot of parameter value input screen:
Text returned by parameter: ?Date_”Transaction Date:”? '2010-03-29' SQL to be executed: Select * from Mtn_AccountBudget where convert(char(10),Trn_DateTime,120) = '2010-03-29'
Copyright © 2012 Little Creations Software
14
Quick Reporting Tool – User Guide Example in Oracle: SQL Query input by user: Select * from Prod.TransactionHistory where to_char(Trn_datetime, 'yyyy-mm-dd') = ?Date_”Transaction Date:”? Screenshot of parameter value input screen:
Text returned by parameter: ?Date_”Transaction Date:”? '2010-03-29' SQL to be executed: Select * from Prod.TransactionHistory where to_char(Trn_datetime, 'yyyy-mm-dd') = '2010-03-29'
Copyright © 2012 Little Creations Software
15
Quick Reporting Tool – User Guide
TIME - Time parameter type Time editor – Value is returned in time [hh:mm:ss] format. (With single quotes [ ‘ ]) Example in SQL Server: SQL Query input by user: Select * from Mtn_AccountBudget where convert(char(8),Trn_DateTime,108) = ?Time_”Transaction Time:”? Screenshot of parameter value input screen:
Text returned by parameter: ?Time_”Transaction Time:”? '00:00:01' SQL to be executed: Select * from Mtn_AccountBudget where convert(char(8),Trn_DateTime,108) = '00:00:01'
Copyright © 2012 Little Creations Software
16
Quick Reporting Tool – User Guide Example in Oracle: SQL Query input by user: Select * from Prod.TransactionHistory where to_char(Trn_datetime, ‘hh24-mi-ss') = ?Time_”Transaction Time:”? Screenshot of parameter value input screen:
Text returned by parameter: ?Time_”Transaction Time:”? '00:00:01' SQL to be executed: Select * from Prod.TransactionHistory where to_char(Trn_datetime, 'hh24-mi-ss') = '00:00:01'
Copyright © 2012 Little Creations Software
17
Quick Reporting Tool – User Guide
DT - Datetime parameter type Date and time editor with drop-down calendar – Value is returned in datetime [yyyy-mm-dd hh:mm:ss] format. (With single quotes [ ‘ ]) Example in SQL Server: SQL Query input by user: Select * from Mtn_AccountBudget where convert(char(19),Trn_DateTime,120) between ?DT_ “Datetime From:”? and ?DT_ “Datetime To:”? Screenshot of parameter value input screen:
Text returned by parameter: ?DT_”Datetime From:”? '2010-03-28 00:00:00' ?DT_”Datetime To:”? '2010-03-31 23:59:59' SQL to be executed: Select * from Mtn_AccountBudget where convert(char(19),Trn_DateTime,120) between '2010-03-28 00:00:00' and '2010-03-31 23:59:59'
Copyright © 2012 Little Creations Software
18
Quick Reporting Tool – User Guide Example in Oracle: SQL Query input by user: Select * from Prod.TransactionHistory where to_char(trn_datetime, 'yyyy-mm-dd hh24:mi:ss') between ?DT_”Datetime From:”? and ?DT_”Datetime To:”? Screenshot of parameter value input screen:
Text returned by parameters: ?DT_”Datetime From:”? '2011-03-28 00:00:00' ?DT_”Datetime To:”? '2011-03-31 23:59:59' SQL to be executed: Select * from Prod.TransactionHistory where to_char(trn_datetime, 'yyyy-mm-dd hh24:mi:ss') between '2011-03-28 00:00:00' and '2011-03-31 23:59:59'
Copyright © 2012 Little Creations Software
19
Quick Reporting Tool – User Guide
XSqlPickY – SQL Drop-down list parameter type Drop-down list box (The list is generated from SQL select statement)
X First character of SqlPick. Y Last character of SqlPick. X can be either N, S, or V, whereas Y can be either R, W or C as below:
NSqlPickR, NSqlPickW, NSqlPickC, SSqlPickR, SSqlPickW, SSqlPickC, VSqlPickR, VSqlPickC.
XFixPickY – Fix Drop-down list parameter type Drop-down list box (The list is hardcoded in the SQL query)
X first character of FixPick. Y Last character of FixPick. X can be either N, S, or V, whereas Y can be either R, W or C as below:
NFixPickR, NFixPickW, NFixPickC, SFixPickR, SFixPickW, SFixPickC, VFixPickR, VFixPickC.
The first and last characters are representing for: V – Value(s) are returned in string format. (Without single quote [ ‘ ]) N – Value(s) are returned in decimal format. (Without single quotes [ ‘ ]) S – Value(s) are returned in decimal format. (With single quotes [ ‘ ]) R – Single selection drop-down list box. (Not editable) W – Single selection drop-down list box. (Editable) C – Multiple selection drop-down list box. Value(s) returned are enclosed by brackets and each value is separated by commas. (Not editable) Please see the following pages for examples of how they work.
Copyright © 2012 Little Creations Software
20
Quick Reporting Tool – User Guide Example 1: SSqlPickR SQL Query input by user: Select * from Trn_PR_Cost where AccountNo = ?SSqlPickR_”Account No:”||”{select AccountNo from Mtn_Account}”? Screenshots of parameter value input screen:
Text returned by parameter: ?SSqlPickR_”Account No:”||”{select AccountNo from Mtn_Account}”? '200000.1841.100' SQL to be executed: Select * from Trn_PR_Cost where AccountNo = '200000.1841.100'
Copyright © 2012 Little Creations Software
21
Quick Reporting Tool – User Guide Example 2: NSqlPickW SQL Query input by user: Select * from Trn_PR_Cost where Trn_UserId = ?NSqlPickW_”Trn User ID”||”{select User_Id from Mtn_UserProfile}”? Screenshots of parameter value input screen:
Text returned by parameter: ?NSqlPickW_”Trn User ID”||”{select User_Id from Mtn_UserProfile}”? 0908 SQL to be executed: Select * from Trn_PR_Cost where Trn_UserId = 0908
Copyright © 2012 Little Creations Software
22
Quick Reporting Tool – User Guide Example 3: SSqlPickC SQL Query input by user: Select * from Trn_PR_Cost where Trn_UserId in ?SSqlPickC_”Trn User ID:”||”{select User_Id from Mtn_UserProfile}”? Screenshots of parameter value input screen:
Text returned by parameter: ?SSqlPickC_”Trn User ID:”||”{select User_Id from Mtn_UserProfile}”? ('0207','0356','0545') SQL to be executed: Select * from Trn_PR_Cost where Trn_UserId in ('0207','0356','0545')
Copyright © 2012 Little Creations Software
23
Quick Reporting Tool – User Guide Example 4: VFixPickR SQL Query input by user: Select * from Trn_PR_Cost where OQYear = ?VFixPickR_”OQ Year:”||{2008,2009,2010,2011,2012}? Screenshots of parameter value input screen:
Text returned by parameter: ?VFixPickR_”OQ Year:”||{2008,2009,2010,2011,2012}? 2010 SQL to be executed: Select * from Trn_PR_Cost where OQYear = 2010
Copyright © 2012 Little Creations Software
24
Quick Reporting Tool – User Guide Example 5: SFixPickW SQL Query input by user: Select * from Mtn_AccountBudget where AccountNo = ?SFixPickW_”Account No:”||”{‘7258.100’, ’7510.100’, ’7521.200’, ’7531.100’, ’7639.100’}”? Screenshots of parameter value input screen:
Text returned by parameter: ?SFixPickW_”Account No:”||”{‘7258.100’, ’7510.100’, ’7521.200’, ’7531.100’, ’7639.100’}”? ‘7510.100’ SQL to be executed: Select * from Mtn_AccountBudget where AccountNo = '7510.100'
Copyright © 2012 Little Creations Software
25
Quick Reporting Tool – User Guide Example 6: VFixPickC SQL Query input by user: Select * from Trn_PR where ?Str_”Order No:”? in (?VFixPickC_”Order Type:”||”{OQ, PR, PO}”?) Screenshots of parameter value input screen:
Text returned by parameter: ?Str_”Order No:”? ‘10310008’ ?VFixPickC_”Order Type:”||”{OQ, PR, PO}”? OQ,PR SQL to be executed: Select * from Trn_PR where '10310008' in (OQ,PR)
Copyright © 2012 Little Creations Software
26
Quick Reporting Tool – User Guide Example 7: VFixPickC SQL Query input by user: Select ?VFixPickC_”Select field(s) to display:”|| “{OQ, PR, PO, VendorName, OQDate, Remarks, Trn_Datetime, Trn_UserId, Status}”? from dbo.Trn_PR Screenshots of parameter value input screen:
Text returned by parameter: ?VFixPickC_”Select field(s) to display:”|| “{OQ, PR, PO, VendorName, OQDate, Remarks, Trn_Datetime, Trn_UserId, Status}”? OQ,PR,PO,OQDate,Remarks,Status SQL to be executed: Select OQ,PR,PO,OQDate,Remarks,Status from dbo.Trn_PR
Copyright © 2012 Little Creations Software
27
Quick Reporting Tool – User Guide Example 8: VFixPickR (in SQL Server) SQL Query input by user: Declare @Status as varchar(10) = ?SFixPickR_Status:||"{'Open PO', 'Completed PO'}"? Select * from dbo.Trn_PR where status = case @Status when 'Open PO' then 'O' else 'C' end Screenshots of parameter value input screen:
Text returned by parameter: ?SFixPickR_Status:||"{'Open PO', 'Completed PO'}"? 'Open PO' SQL to be executed: Declare @Status as varchar(10) = 'Open PO' Select * from dbo.Trn_PR where status = case @Status when 'Open PO' then 'O' else 'C' end
Copyright © 2012 Little Creations Software
28
Quick Reporting Tool – User Guide
What is Parameter Name In Quick Reporting Tool, parameter name for parameter type XSqlPickY and XFixPickY must be separated by pipe [ | ] into 3 parts but for the rest of the parameter types, their parameter name can be only 1 part or separated by pipe [ | ] into 2 parts. The first and second parts are allowed to return value to the parameter by enclosed them with curly brackets { }. The first part is often used to display or select the table’s field name but the second part is often used to display or select the logical operator. Whereas the third part is only used by parameter type SqlPick and FixPick to populate data to their dropdown list box. Examples of parameter name (highlighted in yellow) for parameter type XSqlPickY and XFixPickY: Parameter name that will not return any value to the parameter: ?SSqlPickC_”Account No:”|”in”|”{select AccountNo from Mtn_Account}”? Parameter name that will return value to the parameter: ?SSqlPickC_”{‘Account No’}”|”{‘in’}”|”{select AccountNo from Mtn_Account}”? Examples of parameter name (highlighted in yellow) for other parameter type: Parameter name that will not return any value to the parameter: ?Str_”Status:”? ?Str_”Account No:”? ?Str_”Account No”|”=”? Parameter name that will return value to the parameter: ?Str_”Account No”|”{=}”? ?Str_”{‘Account No’}”|”{‘=’,’>’,’>=’,’<=’,’<’,’like’}”? ?Str_”{‘OQ’, ‘OR’, ‘OP’}”|”{‘=’, ‘>’, ‘>=’, ‘<=’,’<’, ‘like’}”? The following pages illustrate the parameter name (highlighted in yellow) in more details with examples and screenshots:
Copyright © 2012 Little Creations Software
29
Quick Reporting Tool – User Guide Examples of parameter name that will not return any value to the parameter: Example 1: Parameter: ?Str_”Account No:”? Screenshots of parameter value input screen:
Text returned by parameter: '10310001'
Example 2: Parameter: ?Str_”Account No:”|? Screenshots of parameter value input screen:
Text returned by parameter: '10310001'
Copyright © 2012 Little Creations Software
30
Quick Reporting Tool – User Guide Example 3: Parameter: ?Str_”Account No”|”=”? Screenshots of parameter value input screen:
Text returned by parameter: '10310001'
Example 4: Parameter: ?Str_”Account No:”|”Not In”? Screenshots of parameter value input screen:
Text returned by parameter: '10310001'
Copyright © 2012 Little Creations Software
31
Quick Reporting Tool – User Guide Example 5: Parameter: ?SSqlPickC_”Account No:”||”{select AccountNo from Mtn_Account}”? Screenshots of parameter value input screen:
Text returned by parameter: ('1001','200000.1151.100','200000.1151.200')
Example 6: Parameter: ?SSqlPickC_”Account No”|”in”|”{select AccountNo from Mtn_Account}”? Screenshots of parameter value input screen:
Text returned by parameter: ('1001','200000.1151.100','200000.1151.200')
Copyright © 2012 Little Creations Software
32
Quick Reporting Tool – User Guide Examples of parameter name that will return values to the parameter: Example 1: Parameter: ?Str_”{‘OQ’}”|”=”? Screenshots of parameter value input screen:
Text returned by parameter: OQ 'OQ0001'
Example 2: Parameter: ?Str_”OQ”|”{‘=’}”? Screenshots of parameter value input screen:
Text returned by parameter: = 'OQ0001'
Copyright © 2012 Little Creations Software
33
Quick Reporting Tool – User Guide Example 3: Parameter: ?Str_”{‘OQ’}”|”{‘=’}”? Screenshots of parameter value input screen:
Text returned by parameter: OQ = 'OQ0001'
Example 4: Parameter: ?Str_”{‘OQ’, ’OR’, ’PO’}”|”{‘=’, ’>=’, ’<=’, ’>’, ’<’, ’<>’, ’like’}”? Screenshots of parameter value input screen:
1. Enter “%OQ0001%” and select “OQ”.
2. Select “like”. Text returned by parameter: OQ like '%OQ0001%'
Copyright © 2012 Little Creations Software
34
Quick Reporting Tool – User Guide Example 5: Parameter: ?SSqlPickC_”{‘AccountNo’}”|”{‘in’}”|”{select AccountNo from Mtn_Account}”? Screenshots of parameter value input screen:
Text returned by parameter: AccountNo in ('1001','200000.1151.100','200000.1838.200') Example 6: Parameter: ?SSqlPickR_”{‘UserIdCreated’, ‘UserIdModified’}”|”{‘=’}”|”{select User_Id from Mtn_UserProfile}”? Screenshots of parameter value input screen:
1. Select “UserIdCreated”.
2. Select “0908” Text returned by parameter: UserIdCreated = '0908'
Copyright © 2012 Little Creations Software
35
Quick Reporting Tool – User Guide
More examples of SQL query with parameter(s) Example 1 SQL query: select * from Mtn_AccountBudget where BudgetYear = ?Num_”Budget Year”? and AccountNo in ?SList_”Account No”?
Executed SQL: select * from Mtn_AccountBudget where BudgetYear = 2009 and AccountNo in (‘7510.100',’7531.100',’7651.100',’7675.100')
Copyright © 2012 Little Creations Software
36
Quick Reporting Tool – User Guide Example 2 SQL query: select * from Trn_PR where Trn_DateTime between ?Dt_”Date From”? And ?Dt_”Date To”? and Status = ?Str_”PO Status”? and Trn_UserId in ?SList_Creator? order by Trn_UserId, Trn_DateTime
Executed SQL: select * from Trn_PR where Trn_DateTime between '2009-07-16 00:00:01' And '2009-0916 00:00:01' and Status = 'C' and Trn_UserId in ('0908','0927') order by Trn_UserId, Trn_DateTime
Copyright © 2012 Little Creations Software
37
Quick Reporting Tool – User Guide Example 3 SQL query: Select * from Mtn_AccountBudget where AccountNo = ?SFixPickW_”Account No”|=|”{7258.100,7510.100,7521.200,7531.100,7639.100}”? and Trn_UserId = ?SSqlPickR_”Trn User ID”|=|”{select User_Id from Mtn_UserProfile}”?
Executed SQL: Select * from Mtn_AccountBudget where AccountNo = '7510.100' and Trn_UserId = '0908'
Copyright © 2012 Little Creations Software
38
Quick Reporting Tool – User Guide Example 4 SQL query: Select * from Trn_PR_Cost where OQYear = ?NFixPickR_”OQ Year”|=|”{2008,2009,2010,2011,2012}”? and AccountNo = ?SSqlPickR_”Account No”|=|”{select AccountNo from Mtn_Account}”?
Executed SQL: Select * from Trn_PR_Cost where OQYear = 2009 and AccountNo = '200000.1841.100'
Copyright © 2012 Little Creations Software
39
Quick Reporting Tool – User Guide Example 5 SQL query: update Mtn_Support set TeamName = ?Str_”Team Name”? where ITSupport_Id = ?Str_”IT Support Id”?
Executed SQL: update Mtn_Support set TeamName = 'ERP Support' where ITSupport_Id = 'ERP'
Copyright © 2012 Little Creations Software
40
Quick Reporting Tool – User Guide Example 6 SQL query: Insert into Mtn_Support (ITSupport_Id, TeamName, Trn_UserId, Trn_Datetime) values (?Str_”ITSupport Id”?, ?Str_”Team Name”?, '0908', getdate())
Executed SQL: Insert into Mtn_Support (ITSupport_Id, TeamName, Trn_UserId, Trn_Datetime) values ('HELPDESK', 'HELPDESK TEAM', '0908', getdate())
Copyright © 2012 Little Creations Software
41
Quick Reporting Tool – User Guide Example 7 SQL query: Insert into dbo.Mtn_EmailAddress (EmailAddr, EmailName) Values (?Str_"Email Address:"?, ?SSqlPickW_"Email Name:"||"{select User_Name from Mtn_UserProfile}"?)
Executed SQL: Insert into dbo.Mtn_EmailAddress (EmailAddr, EmailName) Values ('
[email protected]', 'Steve')
Copyright © 2012 Little Creations Software
42
Quick Reporting Tool – User Guide
Connect to a database Step 1 – Open Database Connection dialog box On Quick Reporting Tool’s Developer Module, click the Database Connection button to open the Database Connection’s dialog box.
Step 2 – Create a database connection On Database Connection’s dialog box, enter a connection name, example “ServerName – DatabaseName” in the Connection Name’s text box.
Copyright © 2012 Little Creations Software
43
Quick Reporting Tool – User Guide Click Add/Edit button to open the Data Link Properties’ dialog box to enter your database connection details.
Click Yes button to continue.
Copyright © 2012 Little Creations Software
44
Quick Reporting Tool – User Guide
Step 3 – Enter database connection details On the Data Link Properties’ dialog box, enter your database connection details and click OK button to continue. This will save your connection information into a universal data link (.udl) file.
Copyright © 2012 Little Creations Software
45
Quick Reporting Tool – User Guide
Step 4 – Connect to your database Select a connection (.udl) file and click Connect button to connect to your database.
Database connected.
Copyright © 2012 Little Creations Software
46
Quick Reporting Tool – User Guide
Create query with Developer Module Step 1 – Connect to a database Step 2 – Create query Enter a query in the query editor as shown below: Example: SQL query: Select * from Trn_PR_Cost where OQYear = ?SFixPickR_”OQ Year”||”{2008,2009,2010,2011,2012}”?
Copyright © 2012 Little Creations Software
47
Quick Reporting Tool – User Guide
Step 3 – Save query Click Save button to save the query.
Example: Save the query as “BTS – Get PR cost by year.qri”. Note: The query file (*.qri) is also carrying the current database connection string.
Copyright © 2012 Little Creations Software
48
Quick Reporting Tool – User Guide Query saved:
Copyright © 2012 Little Creations Software
49
Quick Reporting Tool – User Guide
Execute query with End User Module Step 1 – Browse and find query
Copyright © 2012 Little Creations Software
50
Quick Reporting Tool – User Guide
Step 2 – Execute query Select a query and click Execute button to continue. Then, click Yes button to confirm execute the query.
Copyright © 2012 Little Creations Software
51
Quick Reporting Tool – User Guide
Step 3 – Enter parameter value (if any) If the query contains parameter(s), a Query Parameters’ dialog box will pop up for you to enter your parameter value.
After enter or select parameter value, click OK button to continue.
Copyright © 2012 Little Creations Software
52
Quick Reporting Tool – User Guide
Step 4 – Data retrieved
Copyright © 2012 Little Creations Software
53
Quick Reporting Tool – User Guide
Export data to delimited text file Step 1 – Save result to file Click Save Result to File button to export the data that you retrieved to a delimited text file.
Copyright © 2012 Little Creations Software
54
Quick Reporting Tool – User Guide
Step 2 – Choose delimiter character(s) Choose your expected output file format and click OK button to continue.
Copyright © 2012 Little Creations Software
55
Quick Reporting Tool – User Guide
Step 3 – Enter file name Enter a file name and click Save button to continue.
Copyright © 2012 Little Creations Software
56
Quick Reporting Tool – User Guide
Step 4 – Data exported
Copyright © 2012 Little Creations Software
57
Quick Reporting Tool – User Guide
Passing Command Line Parameters Quick Reporting Tool’s Parameters Quick Reporting Tool’s parameter can be passed from the command line in Windows, which is useful if you wish to schedule the query to run in the Windows Task Scheduler. It has 5 parameters in total. The parameters must be in sequence and each must be separated by space. The table below will describe the parameters in detail:Parameter
Description
Example
1st Parameter
Query’s file name (.Qri)
“C:\Queries\Abc.qri”
2nd Parameter
The file name for the data to export to.
“C:\Reports\Abc.xlsx”
3th Parameter
File format to export: 1. Delimited Text File = D 2. Excel File = X
D X
4th Parameter
4th parameter must be a text delimiter if , TAB value of 3rd parameter is set to “D”. |
5th Parameter
4th parameter must be a Ms Excel file’s extension if value of 3rd parameter is set to “X”.
Xlsx Xls Xml Csv Html
1. Exclude column header = 0 2. Include column header = 1
0 1
Note:- All error occured in auto execution will be logged in Windows Application Event Log.
Copyright © 2012 Little Creations Software
58
Quick Reporting Tool – User Guide
Examples for passing the parameters C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.txt” D Tab 1 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.txt” D | 1 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.csv” D , 1 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.xml” X Xml 1 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.xlsx” X Xlsx 0 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.xls” X Xls 1 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.html” X Html 1 C:\Qrpt_Dev.exe “C:\Queries\BTS – Get PR Cost by Year.qri” “C:\Reports\Abc.csv” X Csv 1
Copyright © 2012 Little Creations Software
59
Quick Reporting Tool – User Guide
Contact Information Little Creations Software E-mail:
[email protected] Website: http://www.little-creations.net/ We would be glad to help you with any questions and problems you might have. Thank you for using Quick Reporting Tool!
Copyright © 2012 Little Creations Software
60
