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