This project has moved and is read-only. For the latest updates, please go here.

Distinct Result with ASSP.GetCustomDrillthroughMDX

Topics: Developer Forum
Jan 27 at 7:59 PM
Hi,
I have been trying a lot to get the distinct results (No repeated values) while drilling down the measures using ASSP and I don't know why it is not returning distinct values .
Here is my function

ASSP.GetCustomDrillthroughMDX("Name([$Dim Faculty].[Faculty Name])")

Can anyone please help me . I have been stuck in this issue for a week.

Thank!
Jan 30 at 1:43 AM
Drillthrough always returns the lowest level stored in the cube.

So you will have to construct an MDX query that returns the columns you want. That will return the data summarized up without 'duplicate' rows. See the last example on this page:
https://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home
Marked as answer by codeplexraz on 3/8/2017 at 12:13 PM
Mar 8 at 8:14 PM
Thanks furmangg for your help. It really works now .
Mar 14 at 7:21 PM
Hi furmangg,

I used the MDX query to do the distinct drillthrough actions but the columns names are so annoying. Can you please help me to have customized column names .I found an article about it but still not clear about it .

Thanks!
Mar 14 at 10:34 PM
Since it is an MDX query instead of a drillthrough query then I think you can't rename columns unless you use ExecuteDrillthroughAndFixColumns
http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

See documentation here. Just wrap your MDX query in that.
Mar 15 at 7:50 AM
Furmangg,

I did Used the ExecuteDrillThroughAndFixColumns too. Here is my Rowset Action Expression .

'call ASSP.ExecuteDrillthroughAndFixColumns("'+
"select {[Measures].CurrentMember} On 0,
Non Empty (
[Dim Faculty].[First Name].[First Name].Members,
[Dim Faculty].[Last Name].[Last Name].Members
)
Having [Measures].[Enrollment Sum Count]>0 on 1
from (select (" + ASSP.CurrentCellAttributes([Measures].CurrentMember) + ") on 0 from [Test Cube])"+
'")'.

When I browse the cube in excel I get the First Column Header as [Member_Caption] and the next as[Dim Faculty].[Last Name].[Last Name].[Member_Caption]. Did I do something wrong or any thing missing so that my column headings are not as expected .Please help me to correct this .

Thanks!
Mar 16 at 9:42 PM
Oh that's unfortunate. It looks like the ExecuteDrillthroughAndFixColumns function may need to be enhanced to deal with MDX column names better. I will open an issue for that. But it is going to be in our backlog. If you proceed and fix the code first let us know.
Mar 16 at 9:43 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Mar 17 at 5:12 PM
Hi Furmangg,

For that Issue I found one of the developer has provided the code to fix the column names. So When I tried to add that functions on the dll file I received errors during the project build process . The errors occurred because of invalid references . If you could help me to edit the dll file then may be we can make it work.Let me know . I can provide you the code for that specific function.
Mar 18 at 12:55 PM
Sure. Either post the code inline here or click Furmangg and email me
Mar 20 at 11:36 PM
public static DataTable DrillthroughAndFixColumns(string sDrillthroughMDX, string colsToRemove = "", string colNames = "", string colOrder = "")
{
    Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSPFix");
    try
    {
        Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
        cmd.Connection = conn;
        cmd.CommandText = sDrillthroughMDX;
        DataTable tbl = new DataTable();
        Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
        TimeoutUtility.FillAdomdDataAdapter(adp, tbl);
        List<string> colsToRem = new List<string>();

        foreach (DataColumn col in tbl.Columns)
        {
            if ((colsToRemove != "") && (colsToRemove.IndexOf(col.ColumnName, 0) >= 0))
            {
                colsToRem.Add(col.ColumnName);
            }
        }

        foreach (string c in colsToRem)
        {
            tbl.Columns.Remove(c);
        }

        foreach (DataColumn col in tbl.Columns)
        {
            string sNewColumnName = col.ColumnName.Replace(".[MEMBER_CAPTION]", "");
            sNewColumnName = sNewColumnName.Replace("[Measures].", "");
            sNewColumnName = sNewColumnName.Replace("[", "").Replace("]", "").Replace("&", "").Replace("$", "").Replace("-", "").Replace(".Members", "").Replace(".AllMembers", "").Replace(".Children", "");
            col.ColumnName = sNewColumnName;
        }

        if (colNames != "")
        {
            string[][] cols = colNames.Split(';').Select(t => t.Split(':')).ToArray();
            if (cols.Length > 0)
            {
                foreach (string[] col in cols)
                {
                    Int32 i = -1;
                    bool isParsed = Int32.TryParse(col[0], out i);
                    if (isParsed && (tbl.Columns.Count >= i + 1))
                    {
                        tbl.Columns[i].ColumnName = col[1];
                    }
                }
            }
        }

        if (colOrder != "")
        {
            string[][] cols = colOrder.Split(';').Select(t => t.Split(':')).ToArray();
            if (cols.Length > 0)
            {
                foreach (string[] col in cols)
                {
                    Int32 i = -1;
                    bool isParsed = Int32.TryParse(col[1], out i);
                    if (isParsed && (tbl.Columns.Count >= i + 1) && (tbl.Columns.Contains(col[0])))
                    {
                        tbl.Columns[col[0]].SetOrdinal(i);
                    }
                }
            }
        }

        return tbl;
    }
    finally
    {
        conn.Close();
    }
}