Here to Help

Write and Manage Custom Queries

Estimated reading: 7 minutes 317 views

A query is a request for data or information from a database table or combination of tables. We use SQL queries to request or collect data from the datasource. The result of these queries is the request data, such as pictorials, graphs or complex results. An example of such data is the rend analyses from data-mining tools. This section covers creating SQL queries in Infoveave for widgets, reports, jobs, and tasks.

The section details on 

Under query features, you can

  • Write and execute new queries.
  • View all the Queries,
    • You have created under StudioQueries My Queries .
    • Shared with you under StudioQueries Shared Queries .
  • Show query as a report.
  • Edit any existing queries.
  • Share queries
  • Describe any query to assist while sharing a query with the Add Description.
  • Delete any query.

Write and Execute Custom SQL Queries

Follow the steps below to create a new Query

  1. Click on StudioQueries . All Queries, that are created by you or shared with you will be displayed.

  2. If you wish to create a new query, hit on New. Select the datasource from the drop-down list.

  1. After selecting the datasource, write the required query in the editor box.
  2. Click  to execute the query.
    • The viewer box displays the query results. By default, Infoveave will present the result in a tabular form.
    • Change the result visualization from tabular form to widget by selecting the visualize tab, and the required widgets.
    • Add theses widgets to your Infoboards or reports.

  1. After executing the query, choose click save if you wish to retain the SQL query. Save it either as a new query or as an update to the existing query.

  2. Retain it as a new query with Save as New option, provide a query name and click Save.

  3. To update an existing query, select Update Existing option. Enter the query name that requires updating and click Save.

Infoveave SQL Query Functions

SQL Functions for dates

Infoveave offers a set of business and holiday or custom calendar date functions, to be embedded while writing SQL queries for reports or widgets. Infoveave SQL functions accepts three parameters; Date, Level and Lag.

    • Date: Date can be of the forms of, @Start-Date, @End-Date, and @Current-Date. The valid date syntax is: “2020-06-19”.
    • Level: Level decide the date granularity as Years, Quarters, Months, Weeks and Days. All the levels are case insensitive.
    • Lag: Normally a Integer number, and the number level must be added to the given date.

Business Dates

Infoveave supports the functions to include a @business start date and @business end date.  

@Business Start Date: Returns the business start date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax: @BusinessStart(Date, Level, Lag)
  • Examples:
    • Select @BusinessStart(“2020-06-19”, year, 0) as Date:
      • Function will return “2020-01-01” as the start date of the year, if “2020-01-01” is holiday, then function will return next date which is not in the holidays list that is “2020-01-02”.
    • Select @BusinessStart(“2020-06-19”, year, 1) as Date:
      • Function will add the lag value 1 to the date passed and will return start date of that year as “2021-01-01”.
    • Select @BusinessStart(“2020-06-19”, year, -1) as Date:
      • Function will subtract the lag value 1 to the date passed and will return start date of that year as “2019-01-01”.

@Business End Date: Returns the business end date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax: @BusinessEnd(Date, Level, Lag)
  • Examples:
    • Select @BusinessEnd(“2020-06-19”, year, 0) as Date:
      • Function will return “2020-12-31” as the end date of the year, if “2020-12-31” is a holiday, then function will return the previous date which is not in the holidays list that is: “2020-12-30”.
    • Select @BusinessEnd(“2020-06-19”, year, 1) as Date:
      • Function will add the lag value 1 to the date passed and will return the end date of that year as “2021-12-31”.
    • Select @BusinessEnd(“2020-06-19”, year, -1) as Date:
      • Function will subtract the lag value 1 to the date passed and will return the end date of that year as “2019-12-31”.
  • Business end will support additional lag “-0” that will return the previous date of the current date.
    • Select @BusinessEnd(“2020-06-19”, year, -0) as Date:
      • Function will return “2020-06-18” as the business end date.

Calendar Dates

Infoveave supports the functions to include a @calendar start date and @calendar end date.  

@Calendar Start Date: Returns the calendar start date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax: @CalendarStart(Date, Level, Lag)
  • Examples:
    • Select @CalendarStart(“2020-06-19”, year, 0) as Date:
      • Function will return “2020-01-01” as the start date of the year, if “2020-01-01” is holiday, then function will return next date which is not in the holidays list that is “2020-01-02”.
    • Select @CalendarStart(“2020-06-19”, year, 1) as Date:
      • Function will add the lag value 1 to the date passed and will return start date of that year as “2021-01-01”.
    • Select @CalendarStart(“2020-06-19”, year, -1) as Date:
      • Function will subtract the lag value 1 to the date passed and will return start date of that year as “2019-01-01”.

@Calendar End Date: Returns the calendar end date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax: @CalendarEnd(Date, Level, Lag)
  • Examples:
    • Select @CalendarEnd(“2020-06-19”, year, 0) as Date:
      • Function will return “2020-12-31” as the end date of the year, if “2020-12-31” is a holiday, then function will return the previous date which is not in the holidays list that is: “2020-12-30”.
    • Select @CalendarEnd(“2020-06-19”, year, 1) as Date:
      • Function will add the lag value 1 to the date passed and will return the end date of that year as “2021-12-31”.
    • Select @CalendarEnd(“2020-06-19”, year, -1) as Date:
      • Function will subtract the lag value 1 to the date passed and will return the end date of that year as “2019-12-31”.
  • Calendar end will support additional lag “-0” that will return the previous date of the current date.
    • Select @CalendarEnd(“2020-06-19”, year, -0) as Date:
      • Function will return “2020-06-18” as the calendar end date.

The same conditions apply when choosing other levels like Quarter, Month, Week and Day.

Features under Manage Queries

  1. View all the Queries,
    • You have created under StudioQueries My Queries .
    • Shared with you under StudioQueries Shared Queries .
  1. Present your query as a report with Show as Report .
    • The query-based reports are under StudioReports .
  1. Click the icon , to share the query with your team or with a specific job  role. A dialog box will open up with the list of your team.
    • Choose between the options
    • To share with the users, select the “share with users” tab. Pick the user(s) with whom the NGauge forms needs to be shared from the share dialogue box.
    • Choose the “share with role” tab to make a NGauge forms available to a certain work role. From the dialog box, choose a role.
    • Select the users from the presented list of users.
      • If you wish to share your NGauge forms with all users then select Share With Everyone.
    • Select the user from the available list and click  to share the NGauge forms.
    • To remove a particular user/role from the shared list, select the user/role and click  .

    • Click Save to share your NGauge forms with the selected users.

      • Shared personals receive a notification after the successful share.
    • To unshare a NGauge forms, just unselect users or uncheck the Share With Everyone.
  1. Edit any of your queries with the Edit option.
  1. To move to a folder, create a folder by clicking the New Folder option, then drag the query into the desired folder using the drag-and-drop feature.
    • You may also choose a folder from a drop-down menu by clicking the folder symbol on the query.
  1. Click “Add Description” and fill out the description details to add a description to the query.
    • Select the Editor Type.
    • Add necessary Tags (if required).
    • Add Description and Content.
    • Click Ok
  1. If you want to delete query, click on Delete .
    • Type in “delete‘ in the text box.
    • Click Yes.

Unshare a shared query to delete it from My Queries under Studio.

You cannot show as a report, make folders, add descriptions, or delete under shared queries.