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
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