Afternoon all.
I'm building a web app and I'm attempting to pull through some data from an SSAS cube.
I have the following MDX I would like to replicate in c# but with adding a few parameters i.e. two parameteres, one for company 123 and another for location 1:
@"SELECT NON EMPTY([Dim Unit].[All Units].[Category Group Name]) ON COLUMNS
FROM [Info]
WHERE ([Dim Company].[All Companies].&[123],
[Dim Location].[All Locations].&[123]&[1])";
Now, I can get this up and running with one parameter:
AdomdCommand cmdPDC = conPDC.CreateCommand();
cmdPDC.CommandText = "SELECT [Dim Unit].[All Units].[Category Group Name].Members ON 0 FROM [Info] WHERE (StrToMember(@P1)";
string companyid = "123";
string sP1 = "[Dim Company].&" + company;
cmdPDC.Parameters.Add(new AdomdParameter("P1", sP1));
But how do I then implement a second parameter, for example, if I wanted to stick a parameter in for location? I was thinking along the lines ofbelow but I can't get the little sod to work:
AdomdCommand cmdPDC = conPDC.CreateCommand();
cmdPDC.CommandText = "SELECT [Dim Unit].[All Units].[Category Group Name].Members ON 0 FROM [Info] WHERE (StrToMember(@P1)," + "(StrToMember(@P2))";
string companyid = "123";
string locationid = "1";
string sP1 = "[Dim Company].&" + company;
string sP2 = "[Dim Location].&" + company + "&" + location;
cmdPDC.Parameters.Add(new AdomdParameter("P1", sP1));
cmdPDC.Parameters.Add(new AdomdParameter("P2", sP2));
Any help or advice gratefully received.
A solution!
And a few little pointers.
You may receive this error:
"X parameter could not be resolved because it was referenced in an inner subexpression"
Resolution: When inserting the
@CompanyId
in the quoted string, make sure the parameter is not enclosed in apostrophes (') - c# is clever and will convert the data type and add these apostrophes in for you. However, you won't be able to see it in a watch window and all would appear to be fine.All items are returned and it would appear that your parameters are being ignored.
Resolution: Check the very first
SELECT
and make sure it isSELECT NON EMPTY
.