While working on reports, we usually get “#ERROR”,”N/A” or “error: Input string was not in a correct format”error.
The major problem behind these issues is data type mismatches and some additional reasons that cause these issues.
Let’s talk about how to resolve these issues and why we get these issues.
· Start your sql server profiler (Tools menu-> Sql Server Profiler) with Analysis services as service type. Execute/Preview your report and extract/copy the MDX from the profile. Now execute this MDX on the Analysis Services query browser. Make sure that your report’s aggregate column is available in the output. If you see your column unavailable in the output then you need to play a workaround to get include your missing column in the output.
The reason behind this missing column is if the resultant of your entire column is NULL and you are using Non empty/Nonempty then query engine exclude the column from the output to increase the query response time.
· Sometime due to mishandling the expression in the MDX query/Calculated measure, we get “Infinity”,”N/A” or scientific numeric format like 1.00E+12 in the MDX output.
To see the expected result at report level, we need to handle these problems at MDX level by using appropriate expression/fields/methods.
I was using COALESCE EMPTY and changing the value to 0, then the SUM would return an error in the SSRS report. the VAL function did the trick for me. Thanks.