private static void ConfigureFormating(IGenericObject theObject)
{
var theHyperCube = theObject.Properties.Get <HyperCubeDef>("qHyperCubeDef");
// List Sales per year (1 Dimension, 1 Measure)
theHyperCube.Dimensions = Enumerable.Empty <NxDimension>();
theHyperCube.Measures = Enumerable.Empty <NxMeasure>();
AddInlineDimension(theHyperCube, "Year");
AddInlineDimension(theHyperCube, "Month");
AddInlineMeasure(theHyperCube, "Sum([Sales Amount])");
SetHyperCube(theObject, theHyperCube);
// Use string literal for month by using the Text representation instead of the numeric representation
// for the month column.
Console.WriteLine("*** String literal for months");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Num);
}
var hyperCube = theObject.Properties.Get <HyperCubeDef>("qHyperCubeDef");
// Format sales as USD
var measure = hyperCube.Measures.Single();
measure.Def.NumFormat = new FieldAttributes {
Type = FieldAttrType.MONEY, nDec = 2, Dec = ".", UseThou = 1, Thou = ","
};
SetHyperCube(theObject, hyperCube);
Console.WriteLine("*** Use USD as currency");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Text);
}
}
private static void BasicDimensionAndMeasureUsage(IApp theApp, IGenericObject theObject)
{
var theHyperCube = theObject.Properties.Get <HyperCubeDef>("qHyperCubeDef");
// List Sales per year (1 Dimension, 1 Measure)
AddInlineDimension(theHyperCube, "Year");
AddInlineMeasure(theHyperCube, "Sum([Sales Amount])");
SetHyperCube(theObject, theHyperCube);
// Columns of data appear in the order they appear in the dimension and measure definition lists
// for the hypercube. Dimensions first, then measures. Column 0 will therefore contain the years,
// and column 1 will contain the sum of sales.
Console.WriteLine("*** Sales per year");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Sales: {1}", row[0].Num, row[1].Num);
}
// List sales per year and month
AddInlineDimension(theHyperCube, "Month");
SetHyperCube(theObject, theHyperCube);
// Adding a dimension increases the number of columns in the returned data. The new dimension
// will appear on position 1 (after the year) as AddInlineDimension appends dimensions to the
// end of the dimension list.
Console.WriteLine("*** Sales per year and month");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Num, row[2].Num);
}
// Use predefined library version of the measure.
// Get the ID of the measure with the title "Sales" by looking up the measures in the measure list.
theHyperCube.Measures = Enumerable.Empty <NxMeasure>();
var theMeasureList = theApp.GetMeasureList();
var measureId = theMeasureList.Layout.MeasureList.Items.First(item => item.Data.Title == "Sales").Info.Id;
AddLibraryMeasure(theHyperCube, measureId);
SetHyperCube(theObject, theHyperCube);
Console.WriteLine("*** Sales per year and month using library measure for sales");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Num, row[2].Num);
}
// Use calculated dimension.
// Calculated dimensions are added just like fields, except that they require a leading '='.
theHyperCube.Dimensions = Enumerable.Empty <NxDimension>();
theHyperCube.Measures = Enumerable.Empty <NxMeasure>();
AddInlineDimension(theHyperCube, "=Year(TimeStamp)&'-'&Month(TimeStamp)");
AddInlineDimension(theHyperCube, "YearMonth");
AddInlineMeasure(theHyperCube, "Sum([Sales Amount])");
SetHyperCube(theObject, theHyperCube);
Console.WriteLine("*** Sales per year and Month (calculated dimension)");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("=Year(TimeStamp)&'-'&Month(TimeStamp): {0}, YearMonth: {1}, Sales: {2}", row[0].Text, row[1].Text, row[2].Num);
}
}
private static void ConfigureSorting(IGenericObject theObject)
{
var hyperCube = theObject.Properties.Get <HyperCubeDef>("qHyperCubeDef");
// List sales per year and month, Sort on Year and Month, both ascending
{
var dimensions = hyperCube.Dimensions;
foreach (var dimension in dimensions)
{
// Sort both dimensions ascending.
dimension.Def.SortCriterias = new[] { new SortCriteria {
SortByNumeric = SortDirection.Ascending
} };
}
SetHyperCube(theObject, hyperCube);
}
Console.WriteLine("*** Sales per year and month, sorted");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Text);
}
// Sort by month first, then year, then sales.
hyperCube.InterColumnSortOrder = new[] { 1, 0, 2 };
SetHyperCube(theObject, hyperCube);
Console.WriteLine("*** Sales per year and month, sorted by month then year");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Num, row[2].Text);
}
// Sort by sales (descending), then year, then month.
hyperCube.InterColumnSortOrder = new[] { 2, 0, 1 };
hyperCube.Measures.First().SortBy = new SortCriteria {
SortByNumeric = SortDirection.Descending
};
SetHyperCube(theObject, hyperCube);
Console.WriteLine("*** Sales per year and month, sorted by sales (descending)");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Num, row[2].Text);
}
// Revert to sort by year first.
hyperCube.InterColumnSortOrder = new[] { 0, 1, 2 };
SetHyperCube(theObject, hyperCube);
}
private static void BasicSelection(IApp theApp, IGenericObject theObject)
{
// Print data for year 2016 only.
theApp.GetField("Year").Select("2016");
Console.WriteLine("*** Sales for year 2016 only");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Text);
}
var salesRepField = theApp.GetField("Sales Rep Name");
// Print data for year 2016 only, and for sales rep "Amalia Craig" only.
salesRepField.Select("Amalia Craig");
Console.WriteLine("*** Sales for year 2016 and sales rep Amalia Craig");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Text);
}
// Switch to sales rep "Amanda Honda"
salesRepField.Clear();
salesRepField.Select("Amanda Honda");
// Print data for year 2016 only, and for sales rep "Amanda Honda" only.
Console.WriteLine("*** Sales for year 2016 and sales rep Amanda Honda");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Text);
}
// Clear selections and print data for all years and sales reps.
theApp.ClearAll();
Console.WriteLine("*** Sales for all years");
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine("Year: {0}, Month: {1}, Sales: {2}", row[0].Num, row[1].Text, row[2].Text);
}
}
private static void PrintGroupedData(string header, IGenericObject theObject)
{
var hyperCubeLayout = theObject.GetLayout().Get <HyperCube>("qHyperCube");
// The hypercube has only a single dimension. Which dimension is active depends on the state
// of the hypercube.
var dimensionInfo = hyperCubeLayout.DimensionInfo.Single();
var template = dimensionInfo.FallbackTitle + ": {0}, Sales: {1}";
var isYear = dimensionInfo.GroupPos == 0;
// If the current group position is 0, then the dimension represents a year, if it is 1, then it
// represents a month. Use Num property of cell for Year and Text for month.
Console.WriteLine(header);
foreach (var row in GetAllRows(theObject.GetHyperCubePager("/qHyperCubeDef")))
{
Console.WriteLine(template, isYear ? row[0].Num.ToString() : row[0].Text, row[1].Text);
}
}
