Queries

Query allows the user with administrator rights to create queries that can fetch value from a table or from more than one table in company's database. This is mainly used to generate reports as required by the user. Query Designer supports only "Select" queries. For example, if the user needs to get the log information, then the query should be "SELECT usrlog.session_id, usr.user_id, usrlog.log_in, usrlog.log_out, usrlog.application, usrlog.created_at, usrlog.modified_at FROM sy_userlog usrlog, sy_usr usr where usrlog.[user]=usr.af_row_id order by usrlog.log_in".

This page  lets you quickly create, and view the query details. In addition, this page lets you edit, delete and copy the queries information. You can also sort and filter queries using standard and custom list views provided below the Title in a page.

You can click Tools  ? Queries menu item to display the "Queries" page. This page will list all the Query Name, Links to Edit, Delete, Copy option.

Adding Query

Admin user can create query details such as Query Name, Query Description and Query. You have to follow the steps given below to add new query for the Opportunities by product.

1. Click Tools ? Queries menu item. The "Queries" page opens.

2. Click on Add New button to create a new Query. The "Query for Report" page opens.

3. Type the name of the query in the Query Name text box. For example, type the query name as "Query for customer".
4. Type the description about the query in the Query Description text box. For example, type the description as "Displays Opportunities by product".
5. Select the name of the object in the Primary objects drop down. E.g. Calendar detail. Drill down feature in reports will navigate to this objects.
6. Type the Name and values of the parameter in the parameter text box. In this parameter contains default values or dynamic values (given in a run time to generate report).
7. Type the Query in the text area provided. For example, type the query as "select customer_name,customer_id,stage,amount,quantity from sy_customer where sy_customer=$Name$".
8. Click Parse to check the syntax and check whether the given query is valid. This will display the message as "Command(s) completed successfully."
9. Click Save to save the added query details or click Reset to undo the action.

Note:

• To update a query details, click Edit   icon, and then change the fields you want to update.
• To copy a query details from an existing data, click Copy   icon.
• To delete a query detail, click Delete  icon provided on the list page.
• To view the particular a query detail, click on the required Query Name link. For example, to view the query, click the query name "Query for customer".
• To sort the column alphabetically/ascending order, click on the required column header and click the column heading a second time, to reverse the sort order.