#ERROR/NAN / error: Input string was not in a correct format issue in SSRS

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.

1.    If you are performing any aggregate operation like SUM, AVG etc  on the report and also you know that your input field might have string value then never use an expression like

=sum(iif(isnumeric(Fields!YourFieldName.Value), cdbl(Fields!YourFieldName.Value),0))

OR

=sum(iif(isnumeric(Fields!YourFieldName.Value), cint(Fields!YourFieldName.Value),0))

OR

=sum(iif(isnumeric(Fields!YourFieldName.Value), iif(isnumeric(Fields!YourFieldName.Value),cdbl(Fields!YourFieldName.Value),0),0))

The reason behind this is ISNUMERIC() method works in a mysterious way. If we will try to explorer this method a bit more in detail then we will find that is ISNUMERIC()returns true for any expression if there will be any possibility to convert the expression in numeric. For example, if you test ISNumeric("1,234.55") will return true whereas SUM("1,234.55")will give you #Error. To resolve this sitution

Always use expression like =SUM(VAL(Fields!YourFieldName.Value))

2.    If you are using cube/MDX report and also using your aggregate expression like above but still getting an error or an unexpected result then you need to check these points

·         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.

To resolve this problem, you need to add a calculate Member in your MDX query and append the expression by adding +0 at the end like :

With

Member CalculatedMember as [Measures].[Measure Name]+0

·         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.

Exclude Week End Days From Calendar

Today I found a post at MSDN to exclude Sunday using Datediff(). I worked around the query and found a solution

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9b3efb7f-2015-432f-8624-c78a5c535f1c

declare @start datetime,

              @end datetime

set @start = '2006-01-01'

set @end =   '2006-01-08'

;

with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as

(

select @start ,

case when datepart(dw,@start) in (1,7) then 0 else 1 end,

year(@start),

datepart(qq,@start),

datepart(mm,@start),

datepart(dd,@start),

datepart(dw,@start),

datename(month, @start),

datename(dw, @start),

datepart(wk, @start)

union all

select date + 1,

case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,

year(date + 1),

datepart(qq,date + 1),

datepart(mm,date + 1),

datepart(dd,date + 1),

datepart(dw,date + 1),

datename(month, date + 1),

datename(dw, date + 1),

datepart(wk, date + 1) from calendar where  date + 1<= @end

)

select * into #test from calendar option(maxrecursion 10000)

Select  DATEDIFF(DD,@start,@end)+1-Count(case when #test.isweekday=0 then #test.isweekday end )

from #test where #test.date between @start and @end

You can also find the solution in this way

SELECT startDate,EndDate,DATEDIFF(DD,startDate,EndDate) FROM Production.WorkOrder WHERE ((DATEPART(dw, StartDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

How to restrict/set number of records per page in SSRS report

Sometimes we came into a situation where we need to show only a specific number of records on per page.

Let’s see how we can restrict the number of records on per page. I am assuming that you are good with your report design. So I will focus only on the required expressions/steps that will need us to achieve required page break in the report.

Step 1: Add a Parent Group under Row Group section. Now on popup window under “Group by:” dropdown, use this expression:

Image -1

=Int((RowNumber(Nothing)-1)/10)

In this expression, you need to change 10 to

any desire number as you wish to see number of records on per page

Image-1 will help you regarding this step.

Step 2: Open the group properties window(Right Click on group name-> select Group Properties) of this newly added group.             

Image -2

           Go to Page Breaks and checked “Between each instance of the group”.

           Now go to Sorting and delete column/sorting expression (if any).

           You can also delete the 1^st column of your table that has been created automatically on action of add new group by choosing “Delete Columns only” option from the prompt.

           Image-2 will help you regarding this step.

Now you are done. Preview your report and check report output.

You can download the sample report from following link.

Number of records per page Sample Report