I have a PPS filter which shows all the top level members in a two level heirarchy. I connect this filter to "Row" of my PPS scorecard using the following filter connection formula to dynamically display selected filter dimension member and all its children in my scorecard.
HIERARCHIZE({[Product].[ProductCategory].&[<<UniqueName>>],DESCENDANTS([Product].[ProductCategory].&[<<UniqueName>>] , , AFTER)})
The scorecard returns dimension members as expected including parent and it's children but all KPI's are blank and also the scorecard loses all its interactivity\right click menu options after load. I checked in SQL profiler and it captured the following error.
Query The syntax for 's' is incorrect.
When I checked the query text it has one of the child level dimension member attribute having a single quote in its name (X and Y's Product in the query text below). This single quote causes the query to fail as the query generated by the PPS scorecard encloses measures in single quotes as shown below. Please let me know if there is any solution to this problem apart from removing the single quotes from dimension members.
Thanks in advance!
Query Text: WITH MEMBER [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_3ee31ecc-07fd-4817-82e6-953e6c98f3c2] as '([Measures].[Measure1])' MEMBER [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_49ef9a43-cc63-454b-93eb-0f21692305f7] as '([Measures].[Measure2])' MEMBER [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_1d122ea1-bbfe-4dfc-a428-23ce7f2e3de1] as '([Measures].[Measure3])' MEMBER [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_67ad66f8-bf42-4ca6-9322-938bc2524cf6] as '([Measures].[Measure4])' MEMBER [Product].[ProductCategory].[FilterAggregate] as 'AGGREGATE({[Product].[ProductCategory].&[Category1],[Product].[ProductCategory].&[ABC Product], [Product].[ProductCategory].&[X and Y's Product]})' SELECT { [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_3ee31ecc-07fd-4817-82e6-953e6c98f3c2], [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_49ef9a43-cc63-454b-93eb-0f21692305f7], [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_1d122ea1-bbfe-4dfc-a428-23ce7f2e3de1], [Measures].[018e0b9f-a50b-4ff0-43ab-8d77dda4c829_67ad66f8-bf42-4ca6-9322-938bc2524cf6]} ON AXIS(0) FROM [Model] WHERE ([Product].[ProductCategory].[FilterAggregate])