October 1, 2015 at 3:34 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 1, 2015 at 6:02 am
Try the StrToMember function. Also make sure your parameter strings are formatted correctly. Here's a snippet of code I used that is working.
{StrToMember(@MonthYear, CONSTRAINED).lag(23):StrToMember(@MonthYear, CONSTRAINED)}
October 6, 2015 at 3:28 am
Brian hi, thanks for getting back to me I appreciate your help. I edited the code but without success; did I do it right?
The view returned is to show the measures for each month over the last n months, where n is the lag months value and the date from is the first date value
With Member [Measures].[Measure1] As [Measures].[PI002 Rent Collection Ratio RAT (%)]
Member [Measures].[Measure2] As [Measures].[Value Rent Period Debit]
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
,(StrToMember(@FirstDate,Constrained).Lag(@LagMonths):(StrToMember(@FirstDate,Constrained)) On Rows
FROM ( SELECT ( STRTOSET(@TenancyTenure, CONSTRAINED) ) ON COLUMNS
FROM [CBIHousing])
Where(
[Property V].[Reporting Rent Group].[SomeHA]
,
Iif(StrToSet(@TenancyTenure, Constrained).Count = 1, StrToSet(@TenancyTenure,Constrained), [Tenancy].[Tenancy Tenure].currentmember)
)
;
Here's the error text
TITLE: Microsoft SQL Server Report Builder
------------------------------
Query preparation failed.
------------------------------
ADDITIONAL INFORMATION:
Query (11, 18) Parser: The syntax for 'FirstDate' is incorrect. (Microsoft SQL Server 2008 R2 Analysis Services)
------------------------------
BUTTONS:
OK
------------------------------
Here's the parameter settings
LagMonths - no dimension, no hierarchy, no multiple values, Default = 12
FirstDate - no dimension, no hierarchy, no multiple values, Default = 1[Time].[Fiscal Time].[Sep. 15]
TenancyTenure- Tenancy, TenancyTenure, allow multiple values, Default = 'All Tenures'
Any help, pointers would be hugely appreciated
Thank you
Duncan
October 6, 2015 at 8:47 am
I suspect the problem is with the Lag(@LagMonths) but I'm not sure. Can you try putting a constant in there to see if that's the problem?
Another troubleshooting tip is to run a SQL Profiler trace on the SSAS instance. Then you can see how SSRS is manipulating the query which can help you see where the problem is.
October 6, 2015 at 9:31 am
Brian hi, edited the query as follows
With Member [Measures].[Measure1] As [Measures].[PI002 Rent Collection Ratio RAT (%)]
Member [Measures].[Measure2] As [Measures].[Value Rent Period Debit]
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
,(StrToMember(@FirstDate,Constrained).Lag(6):StrToMember(@FirstDate,Constrained)) On Rows
FROM ( SELECT ( STRTOSET(@TenancyTenure, CONSTRAINED) ) ON COLUMNS
FROM [CBIHousing])
Where(
[Property V].[Reporting Rent Group].[SomeHA]
,
Iif(StrToSet(@TenancyTenure, Constrained).Count = 1, StrToSet(@TenancyTenure,Constrained), [Tenancy].[Tenancy Tenure].currentmember)
)
;
With exactly the same result "Query(11,18) Parser: The syntax for 'FirstDate' is incorrect
October 6, 2015 at 4:42 pm
One more thing, are you formatting the parameters to look like the members? I would recommend a trace at this point so you can see the raw MDX.
October 7, 2015 at 7:52 am
October 7, 2015 at 5:55 pm
Good job. Glad to help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply