January 3, 2018 at 7:15 am
Hello,
I'm quite new to MDX syntax and I'm trying to get to this:
Measures
Actual
Budget
Dimensions
Owner
Job
FY
Month
Status
I'd want to extract, given an Owner and a FY (which I need in the rows)
-all the actual and budget amounts by FY/Month/Job for every status in the current FY
-all the actual and budget amounts only for a certain status for the previous year
I managed to build something like that, but I can't get to insert the second condition!
SELECT NON EMPTY
{
[Measures].[Actual],
[Measures].[Budget],
} ON COLUMNS,
NON EMPTY
{
(
STRTOSET(@Owner) *
[Job].[Job].[Job].ALLMEMBERS *
STRTOSET(@FY) *
[Date].[Simple Date].[Month].ALLMEMBERS *
[Status].[Status].[Status].ALLMEMBERS
)
}
ON ROWS
From [Cube]
Can someone point me in the right direction? Thanks in advance
Renato
January 4, 2018 at 1:44 pm
try something like this :
NON EMPTY
{
(
STRTOSET(@Owner) *
[Job].[Job].[Job].ALLMEMBERS *
STRTOSET(@FY) *
[Date].[Simple Date].[year].[2018].children * --assuming month is the level under year, if you have semester/quarters you have to adjust it
[Status].[Status].[Status].ALLMEMBERS
)
+
(
STRTOSET(@Owner) *
[Job].[Job].[Job].ALLMEMBERS *
STRTOSET(@FY) *
[Date].[Simple Date].[year].[2018].children * --assuming month is the level under year, if you have semester/quarters you have to adjust it
--here put a set with all the status you want
)
}
January 5, 2018 at 1:48 am
Hi Nkl, thank you very much. Meanwhile a got around the issue by working in SSRS and filtering there the values, but your solution is cleaner.
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply