Using WITH in mdx to retrieve row names
I am executing the following MDX query in SSMS, which I have got from Profiler:
SELECT {
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
This produces the following results (snippet only):
The rows are months, and because of the method of retrieval, I need the months to also be displayed in a column.
So I tried this:
WITH
MEMBER [TheDate] AS [Retail Sales Date].[Month].CurrentMember.Name
SELECT {
[TheDate],
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
which would be fine, except that it also shows rows where there are null values, as follows:
I know there is NONEMPTY, but I don't know where I would put it. How can I get rid of the empty rows?
mdx
asked Nov 13 '18 at 23:45
tonetone
467520
add a comment |
I am executing the following MDX query in SSMS, which I have got from Profiler:
SELECT {
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
This produces the following results (snippet only):
The rows are months, and because of the method of retrieval, I need the months to also be displayed in a column.
So I tried this:
WITH
MEMBER [TheDate] AS [Retail Sales Date].[Month].CurrentMember.Name
SELECT {
[TheDate],
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
which would be fine, except that it also shows rows where there are null values, as follows:
I know there is NONEMPTY, but I don't know where I would put it. How can I get rid of the empty rows?
mdx
asked Nov 13 '18 at 23:45
tonetone
467520
add a comment |
I am executing the following MDX query in SSMS, which I have got from Profiler:
SELECT {
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
This produces the following results (snippet only):
The rows are months, and because of the method of retrieval, I need the months to also be displayed in a column.
So I tried this:
WITH
MEMBER [TheDate] AS [Retail Sales Date].[Month].CurrentMember.Name
SELECT {
[TheDate],
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
which would be fine, except that it also shows rows where there are null values, as follows:
I know there is NONEMPTY, but I don't know where I would put it. How can I get rid of the empty rows?
mdx
asked Nov 13 '18 at 23:45
tonetone
467520
I am executing the following MDX query in SSMS, which I have got from Profiler:
SELECT {
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
This produces the following results (snippet only):
The rows are months, and because of the method of retrieval, I need the months to also be displayed in a column.
So I tried this:
WITH
MEMBER [TheDate] AS [Retail Sales Date].[Month].CurrentMember.Name
SELECT {
[TheDate],
[Measures].[Dollar Amount],
[Measures].[Transaction Count]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Retail Sales Date].[Month].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Retail Sales Cube]
WHERE ([Geography].[Retail Sales Location].[Country Name].&[Australia],[Retail Category].[Retail Category].[All])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
which would be fine, except that it also shows rows where there are null values, as follows:
I know there is NONEMPTY, but I don't know where I would put it. How can I get rid of the empty rows?
mdx
mdx
asked Nov 13 '18 at 23:45
tonetone
467520
asked Nov 13 '18 at 23:45
tonetone
467520
asked Nov 13 '18 at 23:45
tonetone
467520
asked Nov 13 '18 at 23:45
tonetone
467520
asked Nov 13 '18 at 23:45
tonetone
467520
467520
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Perhaps the following expression would do the trick:
WITH
MEMBER [TheDate] AS
IIF( !isEmpty( [Dollar Amount] )
[Retail Sales Date].[Month].CurrentMember.Name,
null
)
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291112%2fusing-with-in-mdx-to-retrieve-row-names%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Perhaps the following expression would do the trick:
WITH
MEMBER [TheDate] AS
IIF( !isEmpty( [Dollar Amount] )
[Retail Sales Date].[Month].CurrentMember.Name,
null
)
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
add a comment |
Perhaps the following expression would do the trick:
WITH
MEMBER [TheDate] AS
IIF( !isEmpty( [Dollar Amount] )
[Retail Sales Date].[Month].CurrentMember.Name,
null
)
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
add a comment |
Perhaps the following expression would do the trick:
WITH
MEMBER [TheDate] AS
IIF( !isEmpty( [Dollar Amount] )
[Retail Sales Date].[Month].CurrentMember.Name,
null
)
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
Perhaps the following expression would do the trick:
WITH
MEMBER [TheDate] AS
IIF( !isEmpty( [Dollar Amount] )
[Retail Sales Date].[Month].CurrentMember.Name,
null
)
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
answered Nov 14 '18 at 6:38
Marc PolizziMarc Polizzi
7,04232648
7,04232648
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291112%2fusing-with-in-mdx-to-retrieve-row-names%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
