query: a!query( aggregation: a!queryAggregation( aggregationColumns: { a!queryAggregationColumn( field: "id", aggregationFunction: "COUNT" ), a!queryAggregationColumn( field: "raisedBy", isGrouping: true ) } ), pagingInfo: local!pagingInfo ),
Discussion posts and replies are publicly visible
I think your best bet is to create a view in DB which is doing the aggregation. This is more complex but also way more flexible.
Do you have any examples of the SQL that would generate a view for aggregation?
You will want to take advantage of the SQL DATEPART() function as below. One main note here is that since Appian stores datetime in GMT in the database, you will want to handle timezone conversion manually while obtaining hour/day/week/month/year as integer values from SQL, as Appian will not perform the automatic conversion in this case.
For SQL timezone conversions, with daylight savings time factored, we use the logic from this MSSQLTips.com article. Note the function call wrapping the "addedOn" field from the "Users" table below.
With this created in a view, you can now tie your data store to it and aggregate on the desired date part values.
SELECT addedOn, -- actual time DATEPART(hour,dbo.fn_ConvertUTCToLocal(addedOn)) 'hour', DATEPART(day,dbo.fn_ConvertUTCToLocal(addedOn)) 'day', DATEPART(week,dbo.fn_ConvertUTCToLocal(addedOn)) 'week', DATEPART(month,dbo.fn_ConvertUTCToLocal(addedOn)) 'month', DATEPART(year,dbo.fn_ConvertUTCToLocal(addedOn)) 'year' from Users