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:
http://msdn.microsoft.com/en-us/library/ms174786.aspx
http://technet.microsoft.com/en-us/library/ms174840.aspx
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
tnmff@microsoft.com.
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
http://myspeakonbi.blogspot.com/
November 13th, 2011 12:55am