Apply grouping over group

Question

Hi

I'm not entirely sure whether this is possible.

This is our dataset:

This is what our resulting report needs to look like:

We've been able to apply grouping via the farm_area_name field (see following image), but want it to be grouped further by applying grouping of product_name + reg_dosage + symbol over the grouped farm_area_name.

Any input or guidance would be greatly appreciated.

Thank you

Answer 1

Hi @ZoeHui-MSFT

Thank you for your input.

We were actually able to come up with a solution to this. I did not mention that we could modify the dataset using SQL - which is how we've been able to find a solution.

We changed the dataset to include a combined group hash of the 3 columns (product_name + reg_dosage + symbol) per farm area by making use of FOR XML PATH('') to join the hashes together to form a unique group hash. This then allowed us to group on a combination of the grouped hash as well as the 3 columns. It is a rather convoluted solution, but it'll have to do for now.

This is what our new dataset looks like:

We then further refined the dataset by grouping on the product_grouped_by_farm_area_combined_hash, product_name, symbol & reg_dosage before pulling it into SSRS (again using FOR XML PATH('') in order to group the farm_area names together):

Then within SSRS using the refined dataset we just group on the combined farm_area column (farm_areas) & our result is achieved:

Answer 2

Hi @Malcolm van Staden ,

I don't think it's possible to meet your requirement from SSRS side.

We couldn't apply grouping of product_name + reg_dosage + symbol over the grouped farm_area_name.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.