private static void DrillThroughThreadSafe(QueryClient queryClient)
{
Excel.Range rngCell = queryClient.RangeCell;
// create sheet
Excel.Sheets sheets = xlApp.Sheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add();
// show message to user we are retrieving records
Excel.Range rngHead = sheet.Range["A1"];
int maxDrillThroughRecords = ExcelHelper.GetMaxDrillthroughRecords(rngCell);
rngHead.Value2 = string.Format("Retrieving TOP {0} records",
maxDrillThroughRecords);
// set up connection
var connString = ExcelHelper.GetConnectionString(rngCell);
var commandText = queryClient.GetDAXQuery(connString);
var daxClient = new DaxClient();
var cnnStringBuilder = new TabularConnectionStringBuilder(connString);
var cnn = new ADOMD.AdomdConnection(cnnStringBuilder.StrippedConnectionString);
// retrieve result
var dtResult = daxClient.ExecuteTable(commandText, cnn);
// output result to sheet
Excel.Range rngOut = sheet.Range["A3"];
ExcelHelper.FillRange(dtResult, rngOut);
ExcelHelper.FormatRange(dtResult, rngOut);
rngHead.Value2 = string.Format("Retrieved TOP {0} records", maxDrillThroughRecords);
}
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 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);
}
