Display cumulative data on Appian Reports

I added a separate column to DB with a date which represents the first day of the month for that date. So if my event was on 20.12.2015 my date would be 01.12.2015. Then I can use a simple aggregation in my queryentity expression.

Thanks for replying Stefan. Yes i am comfortable with grouping and using aggregation but i think aggregation will not yield cumulative data. The only option what i have till now is making a view to result the report and trying selection on that but i am having very huge set of data about 27K records and still growing... It is taking very huge time so i am looking for alternatives.

I would recommend using a view. Databases are designed to handle large data operations efficiently, and the view would let you customize the data that comes back so you can run an aggregation on it as you wish. You can make one column return the day and the other return the month for each row in the DB, and then run a queryAggregation on those columns (or just have the view return the data that you want in your chart).

If I understand the requirement correctly you should be able to do that by filtering on validatedwithin1day = true, performing grouping on the date field and using a count aggregation function on validatedwithin1day. Something like:

a!queryEntity(
entity: cons!ENTITY_DATA,
query:
          a!query(
                    aggregation: a!queryAggregation(aggregationColumns: {
                     a!queryAggregationColumn(field: "validationdate", isGrouping: true),
                     a!queryAggregationColumn(field: "validatedwithin1day", aggregationFunction: "COUNT")
                    }),
                    filter: a!queryFilter(
                     field: "validatedwithin1day",
                     operator: "=",
                     value: true
                    )
          )
)

@raviteja You would have already known this but I would like to suggest to strictly stick with database approach. Further I would like to suggest to achieve the use case completely in the view as suggested by @brettf.

Do attach the view here if you are expecting any suggestions from the performance perspective post development of the view.

@csteward To the best of knowledge, I guess the approach suggested by you might not work as per my knowledge because if we observe the requirement carefully two things can be understood:
1. Requirement is demanding the grouping on the month-year combination and a count against it whereas your idea performs a grouping on the day-month-year.
2. Requirement demands a cumulative count operation which is at not at all possible with the queryEntity and requires a special coding in the view to calculate it.

After re-reviewing the requirements, that is correct my solution above will not work without manipulating the data to remove day, via a view. At that time, data could also be aggregated on the SQL side - I definitely agree with others here suggesting a SQL view.

Thank you everyone.. i am making this out by using view. Hoping some functionality to support cumulative reporting will come in future.