xlsgen > overview > Business intelligence

xlsgen has business intelligence features all meant to improve the insight on your data. Here is how it works :

automatically infer the underlying structure of your data

worksheet.Tables.InferFromWorksheet

slice and dice

The capabilities are exposed in the IXlsTableColumn interface.

worksheet.Tables.Item(1).DataResults.Columns.ItemByName("lastname").DisplayOrder = 2
worksheet.Tables.Item(1).DataResults.Columns.ItemByName("ID").Hidden = TRUE
worksheet.Tables.Item(1).DataResults.Transpose

high-level calculations

The capabilities are exposed in the IXlsTableColumns interface.

worksheet.Tables.Item(1).DataResults.Columns.AddCalculated("=[PRICE]*[QUANTITY]")

reporting capabilities

The capabilities are exposed in the IXlsTableResults interface.

worksheet.Tables.Item(1).DataResults.ApplyTemplate(wkshtsrc1)
worksheet.Tables.Item(1).DataResults.TurnToChart(xlsgen::charttype_bar3D)

advanced analysis

• sort with custom lists (IXlsTableSort)

  IXlsList list = worksheet.NewList()
  list.Add("jan")
  list.Add("feb")
  list.Add("march")
  list.Add("april")
  
  IXlsTableSort s = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("date").Sorting
  s.CustomList = list
  s.Apply
  

• nested filtering

  worksheet.TablesItem(1).DataResults.Columns.ItemByName("SALES").Filter.CustomExpression = "> 45"
  

• aggregate time series IXlsTableMap
  
  
  

  IXlsList listQ1 = worksheet.NewList()
  listQ1.Name = L"Q1"
  listQ1.Add("jan")
  listQ1.Add("fev")
  listQ1.Add("march")
  IXlsList listQ2 = worksheet.NewList()
  listQ2.Name = L"Q2"
  listQ2.Add("apr")
  listQ2.Add("may")
  listQ2.Add("jun")
  IXlsList listQ3 = worksheet.NewList()
  listQ3.Name = L"Q3"
  listQ3.Add("jul")
  listQ3.Add("aug")
  listQ3.Add("sep")
  IXlsList listQ4 = worksheet.NewList()
  listQ4.Name = L"Q4"
  listQ4.Add("oct")
  listQ4.Add("nov")
  listQ4.Add("dec")
  
  worksheet.Tables.Item(1).DataResults.Columns.ItemByName("date").Map.TextSeries(listQ1)
  worksheet.Tables.Item(1).DataResults.Columns.ItemByName("date").Map.TextSeries(listQ2)
  worksheet.Tables.Item(1).DataResults.Columns.ItemByName("date").Map.TextSeries(listQ3)
  worksheet.Tables.Item(1).DataResults.Columns.ItemByName("date").Map.TextSeries(listQ4)
  
  worksheet.Tables.Item(1).DataResults.Columns.ItemByName["date"].GroupBy(xlsgen::groupby_showmergedcells)
  

prompting

The capabilities are exposed in the IXlsTablePrompts and IXlsTablePrompt interfaces.

IXlsTablePrompt prompt1 = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("city").AddPrompt();
IXlsTablePrompt prompt2 = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("department").AddNestedPrompt(prompt1);
IXlsTablePrompt prompt3 = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("region").AddNestedPrompt(prompt2);
IXlsTablePrompt prompt4 = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("state").AddNestedPrompt(prompt3);

prompt1.UniqueValues.SelectItem(3)
prompt1.UniqueValues.UnselectItem(2)
prompt1.Apply

breaks

The capabilities are exposed in the IXlsTableBreaks interface, and covered in more details here.

worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index").Breaks
pb.Formula = "=IF([Sales index] > 80; 2; 1)"
pb.Apply()

subtotals

The capabilities are covered in more details here.