September 15, 2014 at 12:41 am
i have an ssrs report which i created by using cube,,,,,i have to find total cost of cost head name which iam getting...but i also want to filter some cost head names total amount which contains allocation in their name i want to find the sum of the cost head names amount which contains allocation in their name........that means LIKE function in sql but when iam trying in MDX iam unable to filter it.... i wrote below query
can any please help me how to get this.......
with set [myData] as Filter( [Cost Center].[Tree Indicator] .members, [Cost Center].[Tree Indicator].currentMember.name = 'N' )
member [Measures].[Total] as Sum( [myData],[Measures].[Amount] )
MEMBER [Measures].[YTD] AS ( SUM( [Date].[Fin Year Month Name].&[01April]:[Date].[Fin Year Month Name].&[08November], [Measures].[Amount]) )
SET Product AS FILTER( [Parent Cost Head].[Cost Head Name].CHILDREN, vbamdx!INSTR([Parent Cost Head].[Cost Head Name].CURRENTMEMBER.Name,'Allocation',1 >= 1 ))
SELECT NON EMPTY {[Measures].[Amount],[Measures].[YTD],[Measures].[Total],[Measures].[AllocationCost]} ON COLUMNS, NON EMPTY {([Parent Cost Head].[Cost Head Name].[Cost Head Name].ALLMEMBERS*[Date].[Month Name].[Month Name].ALLMEMBERS*[Date].[Financial Quarter].[Financial Quarter].ALLMEMBERS* [Finance Indicator].[Fin Indicator Desc].[Fin Indicator Desc].ALLMEMBERS)} ON ROWS FROM (SELECT STRTOSET(@CostCenter , CONSTRAINED) ON 0 FROM (SELECT STRTOSET(@Account_Name , CONSTRAINED) ON 0 FROM (SELECT STRTOSET(@FinancialYear , CONSTRAINED) ON 0 FROM [iFin])))
September 15, 2014 at 3:18 am
You can achieve this easily by using the Like function in the ASSP project:
http://asstoredprocedures.codeplex.com/wikipage?title=StringFilters&referringTitle=Home
September 15, 2014 at 4:42 am
sry iam not yet getting can u tell me how can i use according to my requirement
September 15, 2014 at 4:50 am
You need to download and add the ASSP assembly to your SSAS instance (instructions here: http://asstoredprocedures.codeplex.com/wikipage?title=Installation%20Instructions&referringTitle=Home) and then use the example provided in the previous link to use the Like function in your query.
Also in your query you are only defining the "Product" set and not using it in the rows so it will show everything.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply