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); } }