Group by field used in aggregation

[Perzival]

Hi all!

So, I am using a Search and Report DataPage  and I'm trying to group by my data using "Callback" field.

So that part works fine, however I am also trying to perform aggregations which will include the said field.

And the problem is, when I select "Callback" in the "Selected fields" in aggregation, the "enable data grouping by this field" is being disabled.

How can I do this?

 

Thanks,

Z

[Harbinger]

Hi @Perzival

 

That is correct, I happen to came upon this issue few days ago.

You may try to include other field as the selected field in the aggregations. In that way I was able to get my desired output

 So basically we can utilize the Caspio features to achieve this, Kindly check for the instructions below:

Go to the Aggregate field > Choose Name (or whichever field that is not going to use as your grouping criteria) as "Selected Field" > Select "Function" Radio button > Choose "Count" > Go to Advanced > Choose "Result set" and "Below data" > and under group options > Select "All groups" and "Above data"

You may check this article for reference: https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/

I hope this helps.

Cheers,

H

[Harbinger]

I also have another work around to recommend.

I found a way to display in one line all the total count for each distinct values I have by using SQL.

If this works for you, kindly check the following code below:

SELECT STUFF((SELECT ', '+ Callback+ '('+CAST(COUNT(Callback) AS VARCHAR) + ')' + ' ' FROM Your_table GROUP BY Callback FOR XML PATH('')) , 1, 1, '')

Please note that we have few limitations with SQL, however in my case this is the solution that works best for me. i am happy to share it with you in case you find it more suitable to what you're trying to achieve.

Cheers,

H