Subscribe to feed

 

Creating a Cross Table

Code example explaining how to set up cross tables.

Refer to What is a Cross Table? for a cross table presentation.

Create and Configure a Cross Table

public static void CreateCrossTable(AnalysisApplication application)
{
    // Add a cross table to the page
    CrossTablePlot crossTable = application.Document.ActivePageReference.Visuals.AddNew<CrossTablePlot>();
    crossTable.Title = "Basic Cross Table";

    // Connect the cross table to data
    DataManager dataManager = application.Document.Data;
    crossTable.Data.DataTableReference = dataManager.Tables.DefaultTableReference;

    // Set filtering for the visualization
    crossTable.Data.UseActiveFiltering = true;

    // Set marking for the visualization
    crossTable.Data.MarkingReference = dataManager.Markings.DefaultMarkingReference;

    // Set rows by Type
    crossTable.RowAxis.Expression = "<Type>";

    // Set columns by Category
    crossTable.ColumnAxis.Expression = "<Category>";

    // Set the values to Sum(Sales)
    crossTable.MeasureAxis.Expression = "Sum(Sales)";

    // Show Continuous Color
    crossTable.ShowContinuousColor = true;

    // Show Grandtotals for both rows and columns
    crossTable.ShowRowGrandTotal = true;
    crossTable.ShowColumnGrandTotal = true;

    // Sort rows by sum of sales for Fruit
    crossTable.SortRowsCategory = new CategoryKey("Fruit");
    crossTable.SortRowsOrder = Spotfire.Dxp.Data.SortOrder.Ascending;

    // Sort columns by sum of sales for apples
    crossTable.SortColumnsCategory = new CategoryKey("Apples");
    crossTable.SortColumnsOrder = Spotfire.Dxp.Data.SortOrder.Ascending;
}

Create and Configure a Cross table with Formatting for Different Columns

public static void CreateCrossTableWithFormatting(AnalysisApplication application)
{
    // Add a cross table to the page
    CrossTablePlot crossTable = application.Document.ActivePageReference.Visuals.AddNew<CrossTablePlot>();
    crossTable.Title = "Cross Table";

    // Connect the cross table to data
    DataManager dataManager = application.Document.Data;
    crossTable.Data.DataTableReference = dataManager.Tables.DefaultTableReference;

    // Set filtering for the visualization
    crossTable.Data.UseActiveFiltering = true;

    // Set marking for the visualization
    crossTable.Data.MarkingReference = dataManager.Markings.DefaultMarkingReference;

    // Set rows by Type
    crossTable.RowAxis.Expression = "<Type>";

    // Set columns by Category
    crossTable.ColumnAxis.Expression = "<[Category] NEST [Axis.Default.Names]>";

    // Set the values to Sum(Sales) and Count()
    crossTable.MeasureAxis.Expression = "Sum(Sales), Count()";

    // Hide grandtotals for both rows and columns
    crossTable.ShowRowGrandTotal = false;
    crossTable.ShowColumnGrandTotal = false;

    // Create a currency formatter
    NumberFormatter formatter = (NumberFormatter)DataType.Real.CreateLocalizedFormatter();

    // Set the category to currency
    formatter.Category = NumberFormatCategory.Currency;

    // Set the number of decimals to 2 and use $ as currency symbol.
    formatter.DecimalDigits = 2;
    formatter.CurrencyCulture = new System.Globalization.CultureInfo("en-US");

    // Set the formatter for the Sum(Sales) columns
    crossTable.Formatting.IndexedRealFormatter["Sum(Sales)"] = formatter;

    // Create a number formatter
    NumberFormatter nf = (NumberFormatter)DataType.Integer.CreateLocalizedFormatter();

    // Set the category to general
    nf.Category = NumberFormatCategory.General;

    // Set the formatter for the Count columns
    crossTable.Formatting.IndexedIntegerFormatter["Count()"] = nf;

}