Epicor Ideas - Vote request - https://epicor-manufacturing.ideas.aha.io/ideas/KIN-I-4016

Kinetic ERP
1

I have summarized the following Epicor Ideas suggestion: KIN-I-4016 - BAQ - enhanced support for pivoting and unpivoting BAQ subqueries I would appreciate your votes if other would like to see this additional functionality.

I would like to see enhanced support for pivoting and unpivoting BAQ subqueries.

In the Pivot SubQuery For Clause grid, we would have three columns:

Pivot Aggregate               For Column                      Operation
<Aggregate function>      <Column selection>         <Operation Selection>

The from above would be a calculated field edit box.

The from above would be a drop-down list of columns in the subquery.

The would include the following:

      IN <Specified> constant list

      This would pop up the current slide-out dialog.

      IN DISTINCT column value(s). 

      This would generate a dynamic list of values in a specific column

      IN DISTINCT value(s) of the field from the specified subquery

      This would generate a dynamic list of values using the distinct results of another subquery.

Additionally, it would be exceedingly helpful if we could add multiple lines to “the Pivot SubQuery For Clause grid” to pivot on multiple columns which would automatically unpivot and isolate certain data based on the aggregate function of each specific row.

2

Did you try existing PIVOT/UNPIVOT functionality in BAQ?

1 Like
3

The current Pivot is limited to 'in constant list.

Unpivot does not seem available when creating a BAQ, and there does not currently appear to be any way to create columns dynamically while pivoting, or nativally pivoting on multiple columns.

4

Unpivot is in browser designer, did you check it?
You probably should mention the difference with exiting functionality in your idea as well.

1 Like
5

This is a limitation of SQL you have to have constant list to pivot on it doesn’t allow dynamic values. Unless you use dynamic sql it isn’t supported by native SQL queries.

1 Like
6

I understand that the query has to be dynamic as it would require certain variables to be able to generate the columns names labels etc, I give the following example in the Epicor Idea (this post was a summary):

DECLARE @columns NVARCHAR(MAX);

DECLARE @query NVARCHAR(MAX);



SELECT @columns = STRING_AGG(DISTINCT QUOTENAME(column2), ',')

FROM your_table;



SET @query = '

SELECT *

FROM (

  SELECT column1, column2, column3

  FROM your_table

) AS source_table

PIVOT (

  aggregate_function(column3)

  FOR column2 IN (' + @columns + ')

) AS pivot_table;';



EXEC sp_executesql @query;

In the following idea I have requested Enhanced BAQ Functionality for Improved Data Retrieval I have suggested the inclusion of more SQL-like features. It is incredibly difficult and in certain cases ‘impossible’ to do certain things with BAQs because of the lack of certain features, or lack of documentation regarding available features.

For Instance, if I wanted a report of sales by rep by month over the past 12 months with each month’s sales in a separate column progressing from left to right with the most recent month in the left-most column I can do this with SQL with something like:

-- Create a temporary table to hold the dynamic column names
CREATE TEMPORARY TABLE IF NOT EXISTS temp_months (month_year VARCHAR(7));

-- Generate the column names for the past 12 months dynamically
-- Start with the current date and go back 12 months
SET @date := CURDATE();
SET @i := 0;
WHILE @i < 12 DO
  INSERT INTO temp_months (month_year) VALUES (DATE_FORMAT(@date, '%Y-%m'));
  SET @date := DATE_SUB(@date, INTERVAL 1 MONTH);
  SET @i := @i + 1;
END WHILE;

-- Generate the dynamic SQL statement to pivot the data
SET @sql := CONCAT(
  'SELECT Rep,',
  ' SUM(SO_TotalSales) AS Total_Sales,',
  ' ', GROUP_CONCAT('SUM(CASE WHEN DATE_FORMAT(SO_DATE, ''%Y-%m'') = ''', month_year, ''' THEN SO_TotalSales ELSE 0 END) AS `', month_year, '`' SEPARATOR ', '),
  ' FROM SO',
  ' WHERE SO_DATE >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)',
  ' GROUP BY Rep'
);
SET @sql := CONCAT('SELECT * FROM (', @sql, ') AS t ORDER BY Rep');

-- Execute the dynamic SQL statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Drop the temporary table
DROP TABLE IF EXISTS temp_months;
7

AFAIR, now constant list in BAQ with PIVOT is shown in the field list and can be added into the display fieds of the top subquery. BAQ cosumers always expect to know which columns will be returned at the end from that Display Fields list.
How do you think it should work with dynamic list, for example, in dashboard?

9

Some thoughts on the integration of dynamic BAQs and the display of blended static and dynamic columns in Application Studio. As Epicor is transitioning from Epicor’s classic interface to Kinetic, and moving from dashboards to custom layers in Application Studio. I believe that targeting dynamic BAQ functionality is better suited for Application Studio rather than the classic dashboard interface.

To facilitate this, I propose the creation of view rules that determine the display of specific columns in the grid. Currently, each DataView includes attributes such as Column, Caption, Data Type, Format, Custom Context Menu, Additional Column, and more. To adapt the existing ‘column personalization’ features, I suggest adding attributes such as Dynamic and Display to enable control of dynamic columns in grid views.

Moreover, introducing a grid model property of ‘display unassigned columns’ would provide valuable control over the behavior of unmapped columns. Then by leveraging regular expressions, we can effectively rename and organize lists of static and dynamic columns within the existing grid model. The columns in a grid model are defined by attributes such as Field, Title, Ep Binding, and ID. Utilizing regular expressions in the ID and Title attributes can potentially streamline the extraction and pairing of dynamic column names from the data table, thereby enhancing the visualization and analysis of the data.

In line with these discussions, I propose the following user story:

User Story: Enhancing Application Studio for Blended Static and Dynamic Columns

As an Epicor Kinetic customization layer developer,

I want to blend static columns with dynamic columns retrieved from Application Studio’s BAQ REST endpoint and display them in a grid,

So that I can utilize regular expressions to rename and organize lists of static and dynamic columns for improved visualization and analysis in Kinetic Grids,

Ideally, I should be able to use logical expressions to determine the inclusion or exclusion of certain columns.

Acceptance Criteria:strong text

  • As a developer, I should be able to blend static columns with dynamic columns retrieved from Application Studio’s BAQ REST endpoint.
  • The columns should be organized and renamed using regular expressions to ensure clarity and relevance in the grid display.
  • The grid should support the display of dynamic columns based on user-defined view rules, allowing for personalized column visibility.
  • The grid model properties should include “Dynamic” and “Display” attributes to enable personalized column visibility and adaptability.
  • Regular expressions should be integrated the attributes of the grid model such as “ID” and “Title” to facilitate the extraction and pairing of dynamic column names, and column IDs for improved display.

Additional Considerations:

  • The grid should incorporate a “display unassigned columns” property to determine the behavior of unmapped columns, providing flexibility in column visibility.
  • The DataView attributes for each column should be extended to include “Dynamic” and any other necessary properties, enabling personalized column visibility and adaptability.

I believe that these enhancements will significantly improve the capabilities of Application Studio, allowing for seamless blending of static and dynamic columns while leveraging regular expressions for efficient column organization and display logic within grid views.