MDX Query that runs perfectly in SSMS, will not show the measure value in SSRS...???
I have an MDX query that is the source of an SSRS Dataset. When I run this query in SQL Mgmt Studio pointing to SSAS, it runs just fine and returns the values I need in a grid. When I run this exact query as part of my SSRS dataset in a report, only the Dimension shows and not the Measure value. I am not hiding the field and the font is not transparent for this field and I have no filters on the tablix or grouping. Here is the query: SELECT NonEmpty({[Measures].[AllocRv]}) ON COLUMNS, Filter({[Segment].[Markets].[Segment].Members}, [Measures].[AllocRv] <> NULL) ON Rows FROM [Sales] WHERE ([Properties].[Type].[Trx], ( STRTOMEMBER("[Trans Date].[FY Year - Quarter - Month - Date].[FY Date].&[20110901]") : STRTOMEMBER("[Trans Date].[FY Year - Quarter - Month - Date].[FY Date].&[20110930]") )) I wouldn't think it's a query problem, however there's nothing I can see wrong with the report tablix either. Anyone have any clues as what may be causing this? It's all in SQL 2008 R2. Thanks! --ACG
October 28th, 2011 8:07pm

Hi ACG, To trouble shoot your issue, please confirm the following information: 1. Did you use the same datasource in dataset as the one you query in SSMS? 2. Did you exectute the query with the same User both in SSMS and BIDS? If not, please make sure that the user performing the query in Reporting Services has proper permissions and access right: To check the roles he/she belongs to: SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS The Roles column will have a comma-separated list of the roles that they belong to for that database. However, it only returns the current users??role. So, if you are an administrator, it will return all roles. Thanks, Lola TechNet Subscriber Support in forum If you have any feedback on our support, please contact Please remember to mark the replies as answers if they help.
Free Windows Admin Tool Kit Click here and download it now
November 13th, 2011 12:15am

Hi, Replace your double quotes with single quotes in strtomember and try to check the same.Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 ) Dont forget to mark it as Answered if found useful
November 13th, 2011 12:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics