Home › Discussions › Questions & Answers › Using &lexical Anchors for Dynamic PIVOT Tables

Using &lexical Anchors for Dynamic PIVOT Tables

Avatar photoCustomer Questions & Answers 5 replies

I need to build a report where the column names must change dynamically—specifically, a PIVOT table where the list of periods is determined by the user’s input parameter. Which anchor type is best?

5 replies

  • Support Enginatics

    You should use the &lexical parameter reference for dynamic pivot tables. The &lexical anchor is highly versatile and is used to insert variable SQL snippets for elements like dynamic columns, ORDER BY, GROUP BY, or, in your case, the dynamic list of periods columns required by the PIVOT clause.

  • Avatar photoCustomer

    So the placeholder &gl_period_pivot would be replaced entirely with the list of period names enclosed in quotes, correct?

  • Support Enginatics

    Yes, that is correct. The &gl_period_pivot reference inside the pivot clause will be replaced at execution time by the parameter SQL text, which generates the comma-separated list of periods (e.g., ‘Jan-20’ jan_2020, ‘Feb-20’ feb_2020, …).

  • Avatar photoCustomer

    And if the parameter value is left blank?

  • Support Enginatics

    If the parameter value is left blank, the corresponding &lexical reference is completely removed before SQL execution, although for a PIVOT clause, leaving the column list blank might cause a syntax error depending on your overall SQL structure.

  • You must be logged in to reply to this post.