Report Explorer

The Report Explorer renders results of a Data Query as a table and provides filter, search and linking functionality for said table.

Widget properties

• Widget version - Version of the Widget used in this Dashboard. If a new version of this Widget is available and you want to use it, you have to manually update the version here. When a new Widget is added to the Dashboard, automatically the latest version is placed.

• Data query - Data Query delivering the data this Widget will show. The Data Query has to follow the SQL query template Report Explorer List provided in the Data Query editor since this template:

  • Calculates the total number of rows in the result
  • Filters the results but the input of the users
  • Paginates the results
  • Retrieves the current page
  • Orders the results according to the users preferences

  To do this, the query gets the several parameters from the Report Explorer Widget and needs to handle these parameters accordingly:

  • @Page - Current page to show show
  • @PageSize - Number of rows per page
  • @Query - Search string the Users entered or empty if the User did not enter anything
  • @Filter - Filtering configuration for the columns in JSON format or empty if the User did not specify any filters
  • @OrderBy - Name of the column defining the order of the results
  • @Direction - Ascending or descending order of the results

  An example query is shown below. This query handles the columns MyName and MyValue of the Table MyTable.

  DECLARE @QueryStr NVARCHAR(200) = '%' + @Query + '%'
  DECLARE @FilterTable TABLE ([Key] NVARCHAR(200), [Value] NVARCHAR(200))
    
  INSERT INTO @FilterTable
      SELECT JSON_VALUE(x.value, '$.Key') AS [Key], y.value
          FROM OPENJSON(JSON_QUERY(@Filter, '$')) AS x
          CROSS APPLY OPENJSON(JSON_QUERY(x.value, '$.Value')) AS y
  INSERT INTO @FilterTable
      SELECT JSON_VALUE(value, '$.key') AS [Key],
         JSON_VALUE(value, '$.value') AS [Value]
              FROM OPENJSON(JSON_QUERY(@Filter, '$'))
    
  --calculate the total number of records
  SELECT COUNT(Id) AS ItemCount,
      @Page AS CurrentPage,
      CEILING(CAST(COUNT(Id) AS DECIMAL) / CAST(@PageSize AS DECIMAL)) AS TotalPageCount
      FROM [MyTable]
      WHERE ([MyName] LIKE @QueryStr OR [MyValue] LIKE @QueryStr)
      AND CASE 
          WHEN (SELECT COUNT(*) FROM @FilterTable WHERE [Key] = 'MyName') = 0 THEN 1
          WHEN [MyName] IN (SELECT [Value] FROM @FilterTable WHERE [Key] = 'MyName') THEN 1
          ELSE 0
      END = 1
      AND CASE 
          WHEN (SELECT COUNT(*) FROM @FilterTable WHERE [Key] = 'MyValue') = 0 THEN 1
          WHEN [MyValue] IN (SELECT [Value] FROM @FilterTable WHERE [Key] = 'MyValue') THEN 1
          ELSE 0
      END = 1
    
  --return paged result
  SELECT Id, MyName, MyValue 
      FROM [MyTable]
      WHERE ([MyName] LIKE @QueryStr OR [MyValue] LIKE @QueryStr)
      AND CASE 
          WHEN (SELECT COUNT(*) FROM @FilterTable WHERE [Key] = 'MyName') = 0 THEN 1
          WHEN [MyName] IN (SELECT [Value] FROM @FilterTable WHERE [Key] = 'MyName') THEN 1
          ELSE 0
      END = 1
      AND CASE 
          WHEN (SELECT COUNT(*) FROM @FilterTable WHERE [Key] = 'MyValue') = 0 THEN 1
          WHEN [MyValue] IN (SELECT [Value] FROM @FilterTable WHERE [Key] = 'MyValue') THEN 1
          ELSE 0
      END = 1
        
      ORDER BY
      CASE WHEN ISNULL(@OrderBy, '') = '' THEN [MyName] END ASC, 
      CASE WHEN ISNULL(@OrderBy, '') = 'MyName' AND ISNULL(@Direction, 'asc') = 'asc' THEN [MyName] END ASC,
      CASE WHEN ISNULL(@OrderBy, '') = 'MyName' AND ISNULL(@Direction, 'asc') = 'desc' THEN [MyName] END DESC,
      CASE WHEN ISNULL(@OrderBy, '') = 'MyValue' AND ISNULL(@Direction, 'asc') = 'asc' THEN [MyValue] END ASC,
      CASE WHEN ISNULL(@OrderBy, '') = 'MyValue' AND ISNULL(@Direction, 'asc') = 'desc' THEN [MyValue] END DESC
  
      OFFSET((CAST(@Page AS INT) - 1) * CAST(@PageSize AS INT)) ROWS
      FETCH NEXT CAST(@pageSize AS INT) ROWS ONLY
  

• Data query parameters (Key: data query parameter, value: fixed value) - Maps fixed values to the parameters of the Data Query specified in Data query:

  • Add - Adds a new parameter mapping
  • Key - Name of the Data Query parameter, i.e, target of the mapping
  • Value - Value of the Data Query parameter entered directly here, i.e., source of the mapping
  • - Removes the Data Query parameter mapping

• Data query for column filters - Data Query delivering the values used as filter options. The Data Query has to follow the SQL query template Report Explorer Filter provided in the Data Query editor since this template determines the unique values for each filter column.

  To do this, the query gets the several parameters from the Report Explorer Widget and needs to handle these parameters accordingly:

  • @Page - Current page to show show
  • @PageSize - Number of rows per page
  • @AttributeName - Name of the column that is used for the filtering
  • @Filter - Filtering configuration for the columns in JSON format or empty if the User did not specify any filters. This parameter is not used in the example query below, so that it delivers all possible values as filter options to the Users. If desired, the content of @Filter can be integrated in the example query below, just as it is used with the @FilterTable in the example query shown for the Data query configuration to limit the filtering options.

  An example query is shown below. This query handles the columns MyName and MyValue of the Table MyTable.

  -- Filter for first column
  IF @AttributeName = 'MyName'
  BEGIN
      SELECT COUNT(DISTINCT [MyName]) AS ItemCount,
          @page AS CurrentPage,
          CEILING(CAST(COUNT(DISTINCT [MyName]) AS DECIMAL) / CAST(@pageSize AS DECIMAL)) AS TotalPageCount
      FROM [MyTable] 
    
      SELECT DISTINCT [MyName] AS [Key], [MyName] AS Value
          FROM [MyTable]
          ORDER BY [MyName]
                
      OFFSET((CAST(@page AS INT) - 1) * CAST(@pageSize AS INT)) ROWS
      FETCH NEXT CAST(@pageSize AS INT) ROWS ONLY
  END
  -- Filter for second column
  ELSE IF @AttributeName = 'MyValue'
  BEGIN
      SELECT COUNT(DISTINCT MyValue) AS ItemCount,
          @page AS CurrentPage,
          CEILING(CAST(COUNT(DISTINCT MyValue) AS DECIMAL) / CAST(@pageSize AS DECIMAL)) AS TotalPageCount
      FROM [MyTable] 
    
      SELECT DISTINCT [MyValue] AS [Key], [MyValue] AS Value
          FROM [MyTable]
          ORDER BY [MyValue]
                
      OFFSET((CAST(@page AS INT) - 1) * CAST(@pageSize AS INT)) ROWS
      FETCH NEXT CAST(128 AS INT) ROWS ONLY
  END
  --ELSE IF @AttributeName = 'NextColumn'...
  

• Data query parameters for column filters (Key: data query parameter, value: fixed value) - Maps fixed values to the parameters of the Data Query specified in Data query for column filters:

  • Add - Adds a new parameter mapping
  • Key - Name of the Data Query parameter, i.e, target of the mapping
  • Value - Value of the Data Query parameter entered directly here, i.e., source of the mapping
  • - Removes the Data Query parameter mapping

• Filter by fixed value (key: column, value: fixed value) - If no filter is set, all results of the Data Query configured in Report for Query are shown. With a filter, only results with values matching a filter are shown. If multiple filters are configured, results fulfilling any of the filters are shown. The filters defined here are unified with the filters provided via the Dashboard parameters in Filter by dashboard parameters (Key: attribute, value: dashboard parameter) by the SQL query template shown in Report for Query.

  • Add - Adds a new filter
  • Key - Column name to filter the results
  • Value - Value the cell needs to have so that the row is shown in the Widget.
  • - Deletes the filter

• Hyperlinks - Creates hyperlinks of entries in columns. Such hyperlinks are used so that Users can navigate to other Dashboards or set Dashboard Parameter by a simple click.

  • Add - Adds a new hyperlink configuration.
  • Display attribute - Name of the column which entries should be made into a hyperlink.
  • Value attribute - Value associated with the hyperlink. This can be the same as Display attribute but it can also be different. E.g., often Display attribute is the name of an entry, while Value attribute is the ID.
  • Dashboard - Target of the hyperlink, i.e., this Dashboard is opened when the hyperlink is clicked by the Users. This can be the another Dashboard if a new Dashboard should be opened, or it can be the same Dashboard as this Widget is placed on, if the link should set a Dashboard Parameter.
  • Parameter name - Name of the Dashboard Parameter where the value set in Value attribute is put in.
  • Popup - Sets if the target Dashboard should be opened in a popup windows. This option is mutually exclusive to New tab.
  • New tab - Sets if the target Dashboard should be opened in a new browser tap. This option is mutually exclusive to Popup.
  • - Deletes the hyperlink configuration

• Page size - Number of rows displayed in the Widget at once.

• Filter by dashboard parameters (Key: column, value: dashboard parameter) - Maps Dashboard Parameter to the parameters of the SQL data query specified in Data query so that they are used like the filters configured at Filter by fixed value (key: column, value: fixed value). Hence, if no filter is set, all results of the Data Query configured in Data query are shown. With a filter, only results with values matching a filter are shown. If multiple filters are configured, results fulfilling any of the filters are shown. The filters defined here are unified with the filters provided via the Dashboard parameters in Filter by fixed value (key: column, value: fixed value) by the SQL query template shown in Data query.

  • Add - Adds a new Dashboard Parameter to SQL parameter mapping
  • Key - Name of the column in the SQL data query the filter should be applied on parameter, i.e, target of the mapping
  • Value - Name of the Dashboard Parameters parameter holding the filter value, i.e., source of the mapping
  • - Removes the Dashboard Parameters parameter mapping

• Translation table - Selected which column headers should be translated by the Localization settings:

  • Add - Adds a new translation configuration
  • Key - Name of the column, i.e., untranslated column header, that should be translated
  • Value - Default translation if not translation is found in the Localization settings. If this field is left empty, the column name becomes the default translation.
  • - Deletes the translation configuration