Report with a variable number of columns

AFAIK, Appian will not allow variable number of fields for Datatypes.
But you can achieve variable number of columns in the grid by having visibility condition I.e having if condition on the columns to conditionally show or hide

For a similar kind of requirement in past we build a query to generate dynamic pivot table and exported it to excel.
To my best knowledge it is not possible to create grid showing true dynamic pivot table as you are expecting.
A work around implementation for this is
1.          creating a display cdt with N (Maximum number of columns possible )values,
2.          Map values to these columns(A little tricky part but can be done by doing row by row)
3.          Displaying columns only if they have values. (using apply components)

Please let me know if you need help in implementing with Maximum of N columns approach. But I recommend going for exporting dynamic pivot table to excel if possible.

We have been able to implement a report (SAIL Interface) with a variable number of columns. Our use case is that there are any unknown number of vendors submitting. We need to see each vendor as a column to compare the details. We do this with a combination of gridcolumn and applycomponents.
I will see if some of my teammates can reply with more direction.

Like Christine mentioned, we have used a variable number of columns to display a variable number of vendors. Each vendor had multiple rows in the database for different categories, but all vendors that were to be displayed together had the same categories. This is similar to “Project Divisions” in your example. a!gridField was used to create a grid. The “columns” parameter had two values. The first value was a!gridTextColum, which displayed all of the categories. The second value was a!applyComponents, which was used to create a column for each index in an array of CDTs. These columns were our own rule that displayed each value from the CDT in their own row. Because of the way our data was set up, the rule that called a!gridField queried for the relevant vendor names and categories only. The rule used to display each column (the one called by a!applyComponents) had its own query to populate all the category data for that specific vendor. It is important that all of your queries sort by the same field (Project Divisions in your case), otherwise the values in each column will not match up to the correct row.

I might be oversimplifying your request, but nonetheless: take the SAIL recipe for conditionally showing a column as a basis: http://ap.pn/2iNTKc9 Then you define an array of false and true per vendor that you can then apply as the condition to show or hide a column.

We do this frequently in reporting, standard use case is to show users a checkbox list of which data they would like to view in the report. The a!gridField's columns parameter is defined as below, utilizing a!applyComponents() to dynamically create the requested columns:

a!applyComponents(
function: a!gridTextColumn(
alignment: "CENTER",
label: _,
field: _,
data: _
),
array: merge(
apply(fn!displayValue,local!columnSelection,local!columnOptionsValue,local!columnOptionsName,null),
local!columnSelection,
apply(rule!reportGetColumnData,local!columnSelection,local!gridData.data)
)
)

2 arrays are used on the report for columns, one for column name (local!columnOptionsName) and one for column field (local!columnOptionsValue). In the merge() above, displayValue is used to obtain the label since the physical selection of field values is stored in local!columnSelection.

For the data parameter, rule!reportGetColumnData is iterated through to return an array of data arrays, rule definition is:

=index(ri!data,ri!column,null)

For more flexibility you can replace the gridTextColumn function under applyComponents with a new expression rule that returns differently depending on field values. Here we may hard code in certain behavior based on the field - users can select any field they like, but if the field being displayed is one with a special case, format it accordingly:

a!gridTextColumn(
alignment: "CENTER",
label: ri!label,
field: ri!field,
data:
if(
or(
ri!field="value",
ri!field="freightInboundTotal",
ri!field="freightOutboundTotal",
ri!field="totalOrder",
ri!field="totalExtendedCost"
),apply(rule!displayPrice,ri!data),
if(ri!field="step",apply(rule!getStep,ri!data),
if(ri!field="priority",left(ri!data,1),
ri!data
)))
)

Thanks for all the feedback. The issue I am having is not around conditionally displaying fields, but rather allowing the interface to be able to handle additional customer accounts being added without requiring modifications. If a new customer is added to the customer table then the report should have an additional column added without any designer update to the interface.
@ravitejavj The users would prefer to have this functionality within Appian reports rather than having a xlsx file download. The max column approach sounds like it could work; how do you map the CDT to the underlying view in that case?

Even we can apply components dynamically, they all are meant to work on datasubset, to my best knowledge it is not possible to create datasubset for dynamic pivot table. If anybody knows please correct me and let everyone know.

so even if you manage to display different fields which are not there in data subset we cannot make it fully featured as sort,dynamic links will not work. I am not sure about possibilites of making this in editable grid but for this requirement i belive the best way of implementing is only paging grid.

You can use following steps. I just made it so abstract please make improvements where ever needed.

1.create a cdt with below strucutre and create a view and query rule to return data in this strucutre without duplicates
for example lets name the cdt as cdt_view
id |division | costumer | value
- you can take id as text combination of division+costumer

2.create a display cdt with following strucutre

divsion -Text
costumer1 - number (integer)
---------
costumern - number (integer)

3.here is the rule which transpose your view to displayCDT
with(

local!data:queryRuleToReturnDataInStrucutreMentionedInStep1(any parameters),
local!divisions:fn!index(local!data,"divison",null),
local!costumers:fn!index(local!data,"costumer",null),
/*you can restrict local!costumers to max limit just to avoid it breaking*/

local!displayCDT:apply(
rule!createDataForSingleRow(_,local!costumers,local!data),local!divisions

))
/* use display cdt to display in editable grid*/

/*inputs ri!division,listOfCostumers,data*/
rule!createDataForSingleRow(
with(
local!divisionData:fn!index(
ri!data,
wherecontains(fn!tostring(ri!divison),fn!touniformstring(fn!index(ri!data,"divison",null))),null),
type!displayCdt(
division:ri!division,
costumer1:fn!displayvalue(
fn!index(ri!costumers,1,null),
fn!index(local!divisionData,"costumer",null),
fn!index(local!divisionData,"value",null)
null),
.......
costumern:fn!displayvalue(
fn!index(ri!costumers,n,null),
fn!index(local!divisionData,"costumer",null),
fn!index(local!divisionData,"value",null)
null),
)
)
)


Let me know if you need any help.

small correction use
local!divisions:union(fn!index(local!data,"divison",null),fn!index(local!data,"divison",null)),
local!costumers:(fn!index(local!data,"costumer",null),fn!index(local!data,"costumer",null)),
instead of
local!divisions:fn!index(local!data,"divison",null),
local!costumers:fn!index(local!data,"costumer",null),