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

Other than the typical aggregate functions such as SUM, COUNT, AVERAGE, ... subtotals can also compute year over year differences. The capabilities are covered in more details here.

visual components

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

worksheet.Tables.InferFromWorksheet()

IXlsTableVisualComponent vc = worksheet.Tables.Item(1).DataResults.TurnToVisualComponent("Geomaps.ARsTdesign.1")

vc.Formula["SetTitle"]   = "=\"Sales (millions)\""
vc.Formula["SetRegions"] = "=[Country]"
vc.Formula["SetLabels"]  = "=[Sales (millions)]"
vc.Formula["SetColors"]  = "=IF([Sales (millions)]>=10;\"00FF00\";\"FF0000\")"
vc.Formula["SetLegends"] = "=IF([Sales (millions)]>=10;\"> 10 millions\";\"< 10 millions\")"
vc.Apply