2. Home
  4. Studio
  6. Queries

Studio

Queries

  • Reading time: 9 minutes 5 seconds
  • 158 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.

Table of Contents

1. Overview

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.

Steps to Write, Execute, & Analyze Queries

2. Access Queries and Select Datasource

Streamline the process of formulating queries over existing Datasources in Infoveave to efficiently customize, analyze, and modify data, deriving meaningful insights from complex datasets.

  1. To write a query in Infoveave over any existing Datasources, click on Studio Queries.
    • All Queries, created by you or shared with you will be displayed under the tabs My Query and Shared Query respectively.
  2. To formulate a fresh formula, click on New Query.
  3. Enter the Query Name.
Query
  1. Select the Datasource you want to use from the drop-down list and click Continue.

2. Write, Execute & Analyze Queries

Write and execute queries over Datasources for precise data selection, filtering, and aggregation, ensuring a targeted focus on relevant information. This process facilitates informed decision-making through valuable insights into data patterns and trends.

  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 as in a tabular form.
Run Query-Data
  1. Change the result visualization from tabular form to widget by selecting the Visualize tab, and the required widgets.
    • The measures and dimension will be defined by default.
    • Change the predefined measures and dimension with simple drag and drop feature to customize the widget visualization.
    • You can also change the widget type by selecting the widget from the list of available widgets.
    • Click on Save Widget to save the visualization to be used in the Infoboard
      • Name the widget saved.
Query-Visualise
  1. View the Query Run History from the History tab
  2. After executing the query, click on Save if you wish to save the SQL query.
    • Save it either as a new query or as an update to the existing query.
  1. Retain it as a new query with Save as New option, provide a query name and click Save.
  2. To update an existing query, select Update Existing option. Enter the query name that requires updating and click Save.
Query-Visualise

3. Infoveave SQL Query Functions

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.
3.1 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.
3.2 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.
Was this article helpful to you? Yes No

How can we help?

Copyright © 2024 Noesys Software Pvt. Ltd.

Infoveave is a proprietary product of Noesys.  All Rights Reserved.