At least one invalid member reference was encountered in the query
When encountering this error message “At least one invalid member reference was encountered in the query…”, when reporting against SQL Server Analysis Services with IBM Cognos Report Studio 10.1 using a crosstab, the problem may be caused by having no data in a row and column intersection.
A potential solution is to go to the query properties and, under Query Hints, change Suppression to Nulls. This can also improve performance as it can reduce the data returned from the cube. Note that this is a different setting than the Zero Suppression that suppress rows and/or columns. It’s on the query, not the reporting object.
There could be many reasons that a missing member reference was encountered, but this simple change often fixes the problem.
Nice post. Quick question if I may – in Cognos 10, if new members are added to MSAS, do they automatically appear in the DMR?
New members will automatically appear in the package. It’s not literally a DMR, which is dimensionally modeled relational, but an natural OLAP datasource. You do not have to republish the package.
I’ve sometimes found that I have to log off of Cognos and log back in before cube changes show.
I have an update to this error message in entry https://willthrash.com/2012/06/21/msc-err-0008-an-error-has-occured-at-the-transport-layer-and-op-err-0181-at-least-one-invalid-member-cognos-and-ssas-errors-7/
There is a SQL Server hotfix that can remedy the error.