Use Local Datalayer as Dynamic SQL

We had a unique requirement in one of our reports which we solved using a combination of Local Datalayers, a SQL PIVOT, and a Repeat Element in our resulting Datatable.

Our customer users are able to create any number of "Product Number Types" in our parent app and we have a Logi Info table where we present each "Product" with its various "Product Number Types" (PNT) as searchable, actionable columns along with other Product-related data (cost, qty on hand, vendor, manufacturer...). We researched the option of using a Crosstab table, but we would have lost features in the table and elsewhere in the report by not having each PNT as its own distinct entity. For example, the PNT need to be ordered with one marked as Primary (from a DB value) and we need checkboxes to show/hide/sortby each of the PNTs.

The solution was to create a Local Datalayer where we gather the customer's PNT values (for use throughout the report), then use Calculated Columns to craft the results into a format for use in the Datatable's SQL PIVOT. The Local DL output is as follows (in psuedo-SQL):

id
,<db PNT values with primary prefixed> as [PNType]
,CONCAT("MAX([","<unique pnt name>","]) as ["<unique pnt name>","]") as [AsPNType]

I drop a DataLayerLink here (for use in my Repeat Element) named "dllProdNumType".

Then I add the following 3 columns... 1 to concatenate PNType with "],[" as the separator, 2 to add "[" and "]" to the beginning and end of that column, 3 to concatenate AsPNType with comma-separation:

<AggregateColumn
				AggregateColumn="PNType"
				AggregateFunction="Concat"
				ConcatSeparator="],["
				DataType="Text"
				ID="concatPNType"
			/>
			<CalculatedColumn
				Formula="&apos;[&apos;+&apos;@Data.concatPNType~&apos;+&apos;]&apos;"
				ID="strPNTypes"
			/>
			<AggregateColumn
				AggregateColumn="AsPNType"
				AggregateFunction="Concat"
				ConcatSeparator=","
				DataType="Text"
				ID="concatMAXPNType"
			/>

Now I have strPNType to use in my SELECT and concatMAXPNType to use after my PIVOT... essentially dynamic SQL in Logi. My datalayer is as follows:

SELECT ...
,@Local.concatMAXPNType~
FROM (SELECT...) as t1
PIVOT (MAX(number)
FOR PrimaryType IN (@Local.strPNTypes~)) AS pvt

Now that I have my PNTs enumerated and dynamically pivoted, I need to display them as columns in my Datatable. For this, I'm using a Repeat Element using the dllProdNumType DataLayer Link from my Local DL. The column header is the ProductNumber's 'name', and the column's label is a nested Data/Repeat Token - @Data.@Repeat.PNType~~. I'm also able to allow header-click sorting by using the PNType token as the Sort.

<RepeatElements ID="rptProductNumType">
  <DataLayer ID="linkProdNumTypeCols" LinkedDataLayerID="dllProdNumType" Type="Linked" />
  <DataTableColumn Condition="InStr('@Request.inpSHColumns~','@Repeat.id~')&lt;&gt;0" Header="@Repeat.name~" ID="dt@Repeat.pntId~">
    <Label Caption="@Data.@Repeat.PNType~~" />
    <DataColumnSort DataColumn="@Repeat.PNType~" />
    <ExcelColumnFormat ExcelColumnWidth="Auto" />
  </DataTableColumn>
</RepeatElements>

As a bonus, you'll notice the Condition in the column references a Request token for "inpSHColumns". This is where I placed checkboxes, using the Local Datalayer Link to allow the user to show/hide columns. So I'm checking the string of columns the user wants to include (InStr) for each Repeat.id and excluding the PNT if the user didn't select it to appear.

There was a lot of error-handling along the way (using REPLACE in SQL to remove extraneous characters, using some fun SQL tricks to order the PNTs by Primary, then others in sequence, etc.) but the solution now allows our customer users to see an actionable table showing them their customer-configurable PNTs, all Product Numbers, values, and inventory.

Add a Comment if you need help crafting a similar solution... I'm happy to show some more detail and/or provide some guidance on the process.