Skip to main content
Inspiring
May 23, 2025 4:29 PM UTC
Question

How to Create a Dynamic Grid with Month-Year Columns Based on Date Range selection in Appian?

Hi Community,

I’m working on a requirement where I need to create dynamic columns in read-only grid in Appian and would appreciate some guidance.

Problem Statement:

I have a database table with the following columns:

  • ActivityName

  • ForecastDate (Date field)

  • ForecastAmount (Decimal)

Requirement:

I want to display a grid where:

  • Each row represents an ActivityName.

  • Columns dynamically represent Month-Year values (like Jan-2025, Feb-2025, etc.), based on the date range selected from interface.

  • Each cell should show the corresponding ForecastAmount for that activity and month, if available.

So the table should “pivot” the data such that ForecastDate drives the column structure dynamically.

Note: Any Date range between 2020 to 2030 can be selected and searched.

Questions:

  1. What’s the best approach to dynamically generate these month-year columns in an Appian interface?

  2. Should I preprocess the data in an expression rule or CDT before passing it to the grid?

  3. Any examples, ideas, or plug-ins that might help achieve this?

Any help or guidance would be highly appreciated!

Thanks in advance,

3 replies

stefanhelzle0001
Brainy
May 23, 2025 5:26 PM UTC

If you plan to use a normal read only grid, you can create any number of columns dynamically. 

My approach to such tasks is, to first build a small prototype, assuring myself that I understand the challenge and I have solutions to all the tiny problems making up the whole thing.

E.g. to get the dynamic list of months, you can use code like this:

a!localVariables(
  local!start: today(),
  local!end: date(2026, 11, 15),
  local!numMonths: (12 * year(local!end) + month(local!end)) - (12 * year(local!start) + month(local!start)),
  local!months: a!forEach(
    items: enumerate(local!numMonths),
    expression: a!addDateTime(
      startDateTime: local!start,
      months: fv!item
    )
  ),
  local!months
)

Inspiring
May 23, 2025 5:33 PM UTC

Hi [mention:a11f77a729fb4db2af76b09948583328:e9ed411860ed4f2ba0265705b8793d05] Thanks for the support so far!

I’ve made good progress — I’m able to:

[emoticon:599cc6d89e2145289084ee73d7cb364b] Generate all dates between the selected date range

[emoticon:599cc6d89e2145289084ee73d7cb364b] Display dynamic Month-Year columns in a grid

[emoticon:599cc6d89e2145289084ee73d7cb364b] Populate each cell with the correct ForecastAmount for its respective ActivityName and Month-Year, using an expression rule

However, I’m facing a performance issue now.

Since the dataset contains millions of records, and each cell in the Month-Year columns is calling an expression rule to get its value, the performance has become very slow.

My Question:

Is there a better or more efficient way to display these Month-Year forecast values without making individual rule calls for each cell?

Would it be better to:

  • Preprocess and pivot the data in a single rule before binding it to the grid?
  • Use a different design approach or plug-in to optimize performance?

Any suggestions or best practices for handling large datasets with dynamic grids would be really helpful.

stefanhelzle0001
Brainy
May 24, 2025 4:45 AM UTC

You cannot display millions of anything in an Appian interface. This is not what Appian is made for.

But, as far as I understand, you do want to display only a small subset of it. Did you do an estimation of how much data you need to display?

Then, calling rule in a loop is no problem in general, while doing database queries in a loop is.

Did you consider using Process HQ to support this use case?