// This returns the query results based on the specified tableQuery.
public static string BuildCustomQueryText(DaxDrill.Tabular.TabularHelper tabular, PivotCellDictionary pivotCellDic, string tableQuery,
int maxRecords, IEnumerable <DetailColumn> detailColumns,
IEnumerable <string> pivotFieldNames)
{
string filterText = BuildFilterCommandText(pivotCellDic, tabular, pivotFieldNames);
// create inner clause
string commandText = string.Format("TOPN ( {1}, {0} )", tableQuery, maxRecords);
// nest into SELECTCOLUMNS function
if (detailColumns != null && detailColumns.Count() > 0)
{
commandText = string.Format("SELECTCOLUMNS ( {0}, {{0}} )", commandText);
string selectColumnsText = BuildSelectText(detailColumns);
commandText = string.Format(commandText, selectColumnsText);
}
// add filter arguments
if (!string.IsNullOrWhiteSpace(filterText))
{
commandText += string.Format(",\n{0}", filterText);
}
// nest into CALCULATETABLE function
commandText = string.Format("EVALUATE CALCULATETABLE ( {0} )", commandText);
return(commandText);
}
/// <summary>
/// Builds DAX query based on location on pivot table (specified in parameters). This returns all columns in the table.
/// </summary>
/// <param name="tabular">Tabular connection helper</param>
/// <param name="pivotCellDic">Dictionary representing Pivot Table context filters</param>
/// <param name="measureName">Name of DAX measure to be used in drillthrough</param>
/// <param name="maxRecords">Maximum records to be retrieved</param>
/// <param name="detailColumns">List of columns to be included in drill-through</param>
/// <returns></returns>
public static string BuildQueryText(DaxDrill.Tabular.TabularHelper tabular, PivotCellDictionary pivotCellDic, string measureName,
int maxRecords, IEnumerable <DetailColumn> detailColumns,
IEnumerable <string> pivotFieldNames)
{
var measure = tabular.GetMeasure(measureName);
string commandText = BuildCustomQueryText(tabular, pivotCellDic, measure.TableName, maxRecords, detailColumns, pivotFieldNames);
return(commandText);
}
public bool IsDatabaseCompatible(string connString)
{
var cnnStringBuilder = new TabularConnectionStringBuilder(connString);
bool result = false;
using (var tabular = new DaxDrill.Tabular.TabularHelper(
cnnStringBuilder.DataSource,
cnnStringBuilder.InitialCatalog))
{
tabular.Connect();
result = tabular.IsDatabaseCompatible;
tabular.Disconnect();
}
return(result);
}
private TabularItems.Measure GetMeasure(Excel.Range rngCell)
{
var cnnBuilder = new TabularConnectionStringBuilder(this.connectionString);
string measureName = GetMeasureName(rngCell);
TabularItems.Measure measure = null;
using (var tabular = new DaxDrill.Tabular.TabularHelper(cnnBuilder.DataSource, cnnBuilder.InitialCatalog))
{
tabular.Connect();
measure = tabular.GetMeasure(measureName);
tabular.Disconnect();
}
return(measure);
}
public static string BuildFilterCommandText(PivotCellDictionary excelDic, DaxDrill.Tabular.TabularHelper tabular,
IEnumerable <string> pivotFieldNames)
{
string singCmdText = BuildSingleSelectFilterCommandText(excelDic.SingleSelectDictionary, tabular,
pivotFieldNames);
string multiCmdText = BuildMultiSelectFilterCommandText(excelDic.MultiSelectDictionary, tabular);
string result = singCmdText;
if (!string.IsNullOrEmpty(multiCmdText))
{
if (!string.IsNullOrEmpty(singCmdText))
{
result += ",\n";
}
result += multiCmdText;
}
return(result);
}
public string GetDAXQuery(string connString)
{
string commandText = "";
var cnnStringBuilder = new TabularConnectionStringBuilder(connString);
int maxRecords = ExcelHelper.GetMaxDrillthroughRecords(rngCell);
var detailColumns = GetCustomDetailColumns(rngCell);
using (var tabular = new DaxDrill.Tabular.TabularHelper(
cnnStringBuilder.DataSource,
cnnStringBuilder.InitialCatalog))
{
tabular.Connect();
// use Table Query if it exists
// otherwise get the Table Name from the Measure
string tableQuery = GetCustomTableQuery(rngCell);
if (string.IsNullOrEmpty(tableQuery))
{
// if table not defined in XML metadata, retrieve entire table
string measureName = GetMeasureName(rngCell);
commandText = DaxDrillParser.BuildQueryText(tabular,
pivotCellDic,
measureName, maxRecords, detailColumns, pivotFieldNames);
}
else
{
// if table is defined in XML metadata, retrieve using DAX command
commandText = DaxDrillParser.BuildCustomQueryText(tabular,
pivotCellDic,
tableQuery, maxRecords, detailColumns, pivotFieldNames);
}
tabular.Disconnect();
}
return(commandText);
}
private static string BuildMultiSelectFilterCommandText(DaxFilterCollection daxFilterDic, DaxDrill.Tabular.TabularHelper tabular)
{
string commandText = "";
foreach (var pair in daxFilterDic)
{
List <DaxFilter> daxFilterList = pair.Value;
string tableName = daxFilterList[0].TableName;
if (commandText != "")
{
commandText += ",\n";
}
string childCommandText = "";
foreach (var item in daxFilterList)
{
if (childCommandText != "")
{
childCommandText += " || ";
}
else
{
childCommandText = "FILTER ( " + tableName + ", ";
}
var table = tabular.GetTable(item.TableName);
childCommandText += BuildColumnCommandText(table, item);
}
commandText += childCommandText + " )";
}
return(commandText);
}
public static string BuildQueryText(DaxDrill.Tabular.TabularHelper tabular, PivotCellDictionary pivotCellDic, string measureName, int maxRecords)
{
return(BuildQueryText(tabular, pivotCellDic, measureName, maxRecords));
}
private static string BuildSingleSelectFilterCommandText(Dictionary <string, string> excelDic, DaxDrill.Tabular.TabularHelper tabular, IEnumerable <string> pivotFieldNames)
{
List <DaxFilter> daxFilterList = ConvertSingleExcelDrillToDaxFilterList(excelDic, pivotFieldNames);
string commandText = "";
foreach (var item in daxFilterList)
{
if (commandText != "")
{
commandText += ",\n";
}
if (item.IsHierarchy)
{
commandText += "FILTER ( " + item.TableName + ",";
}
var table = tabular.GetTable(item.TableName);
commandText += BuildColumnCommandText(table, item);
if (item.IsHierarchy)
{
commandText += ")";
}
}
return(commandText);
}
