Group By column functionality for a Page type

Shweta S asked on August 17, 2016 00:32


I have a page type (News) with a field called year and I want to display the list of items of that particular page type grouped by month column. I am looking at the documentation but have no clue as to how that can be done.

Example :

2016 ( column value of a page type 'News')
- All news items having Year as 2016
- All news items having Year as 2016
- All news items having Year as 2016

2015 ( column value of a page type 'News')
- All news items having Year as 2015
- All news items having Year as 2015
- All news items having Year as 2015

Correct Answer

Richard Sustek answered on August 17, 2016 10:12

I'm afraid that neither Group By (because it is an aggregate function) or Hierarchical transformations (because your items don't depend on item level, class name and differ only in a value in a single column) will do what you need in this case.

To get this working you can use Repeater web part along with Order by condition on the Year column.

Once your items are ordered, you then want to display the Year ONLY if the year is different then it was in previous items. This is the tricky part because there is no easy way to access previous/next or any other item inside a transformation. Luckily though it is possible in both ASCX and TEXT/XML transformations via some tricks.

With Text/XML transformation you will need to create a Custom macro method that will be able to store the value in throughout the life of current request for next item.

I've successfully used following macro for this purpose:

using System;
using CMS.MacroEngine;
using CMS.Helpers;
using CMS;

// Makes all methods in the 'CustomMacroMethods' container class available for string objects
[assembly: RegisterExtension(typeof(CustomMacroMethods), typeof(string))]
// Registers methods from the 'CustomMacroMethods' container into the "String" macro namespace
[assembly: RegisterExtension(typeof(CustomMacroMethods), typeof(StringNamespace))]
public class CustomMacroMethods : MacroMethodContainer
    [MacroMethod(typeof(string), "Saves given value in memory and returns previously saved value", 1)]
    [MacroMethodParam(1, "value", typeof(string), "Value to be saved")]
    public static object SaveValueInMemory(EvaluationContext context, params object[] parameters)
        switch (parameters.Length)
            case 1:
                string stockName = "SaveValueInMemory";

                // get current value
                string currentValue = ValidationHelper.GetString(RequestStockHelper.GetItem(stockName), null);

                // set new value
            RequestStockHelper.Add(stockName, ValidationHelper.GetString(parameters[0], ""));

            // return current value
            return currentValue;

            // No other overloads are supported
            throw new NotSupportedException();


Then inside the Text/XML transformation you need to store and get the value from the Request storage and compare it. The transformation could look like:

{% if(SaveValueInMemory(Year) != Year){ %}
<h1>Year: {% Year%} </h1>
{% } %}

<p>News article: {% DocumentName %}</p>

On my end the result looks like:

Year: 2016
News article: Apple iPad 2 In Stock 

Year: 2015
News article: Company Growth Exceeds Expectations 
News article: Community Website Section 

If you are using ASCX transformation you can use following code to get the Year column from previous item and generate same output using:

<%# DataItemIndex == 0 ? "<h1>" + Eval("Month") + "</h1>" : string.Empty %>
<%# ((DataItemIndex > 0) ? ((System.Data.DataView)((BasicRepeater)Parent.Parent).DataSource)[DataItemIndex-1]["Month"].ToString() != Eval("Month").ToString() ? "<h1>" + Eval("Month") + "</h1>" : string.Empty : string.Empty) %>

<p>News article: <%# Eval("DocumentName") %></p>

All you need to do with this is again to compare it with current value and display your headline only if it differs from previous value. That is why the order by is extremely important in this scenario.

Another option that I haven't mentioned is by using the Nested repeaters but due to performance reasons I would strongly advised not to do that.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on August 17, 2016 04:02

Check this article out on how to display data hierarchically. I believe this will get what you're looking for. If not, you can always create a custom query which returns the values you want using a group by query.

1 votesVote for this answer Mark as a Correct answer

Jan Hermann answered on August 17, 2016 09:06

You can always use Repeater with custom query where you can define any SQL query with GROUP BY.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.