I am trying to prepare a quick ROLAP report. I just want to understand if this the manner that the ROLAP works. It appears that any measure that counts any Alpha column will get an SQL convert error on execution when any Dimension is added to the columns.
This only occurs when the counted measure column is alpha and a dimension is added to the "columns" on the report. We are on SQL 2008, Build 169.
SQL and error below the counted measure Bene_ID is a member id that is a combination of alpha and digits. Since the SQL case is using 0 I guess SQL expects Bene_IDS to be numeric --
Conversion failed when converting the varchar value 'Z1325582T' to data type int.
SQL: SELECT Count(DISTINCT CASE SPD.Aid_CD WHEN '10' THEN SPD.Bene_ID ELSE 0 END) as [10], Count(DISTINCT CASE SPD.Aid_CD WHEN '14' THEN SPD.Bene_ID ELSE 0 END) as [14], Count(DISTINCT CASE SPD.Aid_CD WHEN '1E' THEN SPD.Bene_ID ELSE 0 END) as [1E], Count(DISTINCT CASE SPD.Aid_CD WHEN '1H' THEN SPD.Bene_ID ELSE 0 END) as [1H], Count(DISTINCT CASE SPD.Aid_CD WHEN '20' THEN SPD.Bene_ID ELSE 0 END) as [20] FROM SantaClaraSPD SPD
Report Portal
ROLAP Count
Posted - 07/20/2010 : 21:58:27
