Create custom reports in Sitecore

In a previous article found here, I’ve discussed how to make a basic custom item editor. Be sure to read that post if you are unfamiliar with how to make a custom item editor. I’m assuming you know the basics in this post.

In this post, I’ll show just one of several use cases for a custom item editor, a custom report. I’ve often gotten asked to build custom reporting options in Sitecore. Those take various forms, however, this one will be made via a custom item editor.

The use case is simple. Sometimes authors have to configure larger amounts of items in one folder. If non-bucketed or bucketed the problem remains; it’s not easy for an author to view the contents of a folder wholistically. In this case, we make them a report.

I’ve configured the Region folder template to display the Summary of Configured Regions tab. When that tab shows, our report is generated.

Let’s create the .aspx page that will serve as our report. The location of the .aspx page can vary but I generally add it in this location near the default location and place them in a Custom Editors / [Feature Name] folder.

The example code below shows how the details of the outlined .aspx page. Pay special attention to these lines:

this.database = Database.GetDatabase(this.databaseName);
Item currentItem = database.GetItem(new ID(this.currentItemId));

Remember that Sitecore passes the Item Id and the Database via querystring parameter of the clicked item to the custom editor. Here we are getting the Item Id of the folder that was clicked and the database.

this.currentItemId = Request.QueryString["id"];
this.databaseName = Request.QueryString["db"];

Next we get the database object and the current item object. Now we have what we need to iterate through the children to create the report.

<%@ Page Language="C#" %>
<%@ Import Namespace="Sitecore.Data" %>
<%@ Import Namespace="Sitecore.Data.Items" %>

<script runat="server">
    protected string currentItemId = null;
    protected string databaseName = null;
    protected Database database = null;
    protected string selectedCounties = null;
    protected string NotAssociatedCountries = null;
    protected bool checkFlag = false;

    protected string Process(string fieldName, Item childItem)
    {
        string fieldValue = GetFieldValue(fieldName, childItem);
        if (string.IsNullOrEmpty(fieldValue)) return String.Empty;
        if (!fieldValue.Contains("|")) return GetItemName(fieldValue);
        string result = FormatData(fieldValue);
        checkFlag = true;
        return result;
    }

    protected string GetFieldValue(string fieldName, Item childItem)
    {
        if (childItem.Fields[fieldName] == null || string.IsNullOrEmpty(childItem.Fields[fieldName].Value)) return string.Empty;
        return childItem.Fields[fieldName].Value;
    }

    protected string GetBoolValue(string columnName, Item childItem)
    {
        return childItem.Fields[columnName].Value == "1" ? "Y" : "N";
    }

    protected string FormatData(string fieldValue)
    {
        string[] allSelectedItemId = fieldValue.Split('|');
        string finalResult = string.Empty;
        foreach (string itemId in allSelectedItemId)
        {
            finalResult = string.Concat(finalResult, "<br/>", GetItemName(itemId));
            this.selectedCounties = string.Concat(this.selectedCounties, "<br/>", GetItemName(itemId));
        }
        return finalResult.Remove(0, 5);
    }

    protected string GetItemName(string id)
    {
        return this.database.GetItem(new ID(id)).Name;
    }

</script>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Summary Of Configured Regions</title>
    <script src="//code.jquery.com/jquery-3.3.1.min.js"></script>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css" integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
</head>
<body>
    <div class="container-fluid" id="summaryOfConfiguredRegions">

        <h2 style="text-align: left; margin-top: 20px; margin-left: 10px;">Summary Of Configured Regions</h2>
        <table id="tableRegion" class="table table-striped table-bordered" style="text-align: left; width: auto">
            <thead>
                <tr>
                    <th>Regions</th>
                    <th>Associated Countries</th>
                    <th>Excluded Brands</th>
                    <th>Has Json</th>
                </tr>
            </thead>
            <tbody>
                <%
                    this.currentItemId = Request.QueryString["id"];
                    this.databaseName = Request.QueryString["db"];
                    this.database = Database.GetDatabase(this.databaseName);
                    Item currentItem = database.GetItem(new ID(this.currentItemId));
                    if (currentItem != null && currentItem.Children.Count > 0)
                    {
                        foreach (Item childItem in currentItem.Children)
                        {
                %>

                <tr>
                    <td><% = childItem.Name %></td>
                    <td><%  = Process("Countries", childItem) %></td>
                    <td><%  = Process("Excluded Brands", childItem) %></td>
                    <td><%  = string.IsNullOrEmpty(GetFieldValue("Categories Json", childItem)) ? "N" : "Y"  %></td>
                </tr>
                <%
                        }
                    } %>
            </tbody>
        </table>
    </div>
</body>
</html>

Related Posts

CORS issue for fonts in XM Cloud Local Experience Editor

Use Visual Studio to view your Docker containers and view XM Cloud CM files in the container.

In XM Cloud disable password requirement for Powershell

About Phil Paris