October 1, 2015 at 3:32 am
Hi, I have an MDX data-set query as follows: -
Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts]
Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit])
Select {[Measures].[Measure1]
,[Measures].[Measure2]
,[Measures].[Measure3]
,[Measures].[Measure4]
} On Columns
,Non Empty LastPeriods(@LagMonths,[Time].[Fiscal Time].currentMember) On Rows
FROM ( SELECT ( STRTOSET(@TenancyTenure, CONSTRAINED) ) ON COLUMNS
FROM [CBIHousing])
Where(
Iif(StrToSet(@TenancyTenure, Constrained).Count = 1, StrToSet(@TenancyTenure,Constrained), [Tenancy].[Tenancy Tenure].currentmember)
)
;
There are two parameters; the value of lag passed to the LastPeriods function, and tenancy types passed in the where clause
I have a third parameter I would like to use called FirstDate and this I would like to pass to the LastPeriods function as the second argument
FirstDate is a value from my Time dimension, something like [Time].[Fiscal Time].[Sep. 15]
When editing the query to: -
Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts]
Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit])
Select {[Measures].[Measure1]
,[Measures].[Measure2]
,[Measures].[Measure3]
,[Measures].[Measure4]
} On Columns
,Non Empty LastPeriods(@LagMonths,@FirstDate) On Rows
FROM ( SELECT ( STRTOSET(@TenancyTenure, CONSTRAINED) ) ON COLUMNS
FROM [CBIHousing])
Where(
Iif(StrToSet(@TenancyTenure, Constrained).Count = 1, StrToSet(@TenancyTenure,Constrained), [Tenancy].[Tenancy Tenure].currentmember)
)
;
The code errors. I have tried all sorts of formats such as LastPeriods(@LagMonths,[@FirstDate]) and LastPeriods(@LagMonths,[Time].[Fiscal Time].[@FirstDate]), but nothing works
Has anyone any ideas?
Thank you for your attention
Regards
Duncan
October 16, 2015 at 9:15 am
Cracked it
Here is an example of the code used. It is for a different query to the original although it does pretty much the same. The original query is on another machine
With
Member[Measures].[Measure01] As [Measures].[Repair Order Average Hours to Completion Ratio]
SELECT
NON EMPTY{
[Measures].[Measure01]
} ON COLUMNS
,LastPeriods(@LagMonths,StrToMember(@TimeRentTime)) On Rows
FROM(
SELECT ( STRTOSET(@AssetProfileAssetClassificationType, CONSTRAINED)) ON COLUMNS
FROM(
SELECT ( STRTOSET(@AssetProfileAssetCategoryType, CONSTRAINED)) ON COLUMNS
FROM(
SELECT ( { [Repair Order Profile].[Repair Order Priority].[Emergency] } ) ON COLUMNS
FROM [CxBI]
)
)
)
WHERE(
[Repair Order Profile].[Repair Order Priority].[Emergency]
,IIF( STRTOSET(@AssetProfileAssetCategoryType, CONSTRAINED).Count = 1, STRTOSET(@AssetProfileAssetCategoryType, CONSTRAINED), [Asset Profile].[Asset Category Type].currentmember )
,IIF( STRTOSET(@AssetProfileAssetClassificationType, CONSTRAINED).Count = 1, STRTOSET(@AssetProfileAssetClassificationType, CONSTRAINED), [Asset Profile].[Asset Classification Type].currentmember )
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy