How to set a date time parameter to show first day of the current month and other date time parameter to current date (today)

Start date shows as first day of current month

 

Write an expression to Default Value using Date Serial like below.

 

End date show as today

You can write an expression using Date & Time Common functions

 

 

SSRS deployment error : Query execution failed for dataset…..(reErrorExecutionCommand)



Have you ever come across  Query execution failed for dataset…..(reErrorExecutionCommand) when you try to deploy a report in SSRS reporting server and when you run it? This error throws mainly because the dataset are not correctly set up during deployment. Best way to troubleshoot this is to edit in Report Builder and run the report. You will get the same error message but this design view help to troubleshoot problem easier. Go to dataset and check whether the connection type has been set to SQL Server and the connection string is correct. Test Connection button is helpful to test the connection. This error is common if you use more than one datasource in same report and setting up datasources correctly will resolve this error.



 

SSRS sub report does not show on main report when sub report has no values

In SSRS if you use sub report within the main report you might have come across situation like sub report does not show on main report. That is because sub report has no data to bring. There is  a trick that you can use to show sub reports on main report all the time despite sub reports are empty or not. What you can do is, in the sub report create another dataset and write an empty query like below.

 

 

 Then create a hidden textbox on the sub report and use the created dataset and call the empty field here.

 

 

SSRS: Trying to divide 0 by 0 and results end up as #Error?

Here is a trick that you can do. I need to sum two fields values and then divide it by another field value. What if the total of summation ends up with 0 and same time you are trying to divide it by 0. You can see the output as #Error.

 

What I have done here is I have checked whether the denominator is zero, if it is zero I have set it to 1 else use the normal field value. You can use this as a trick, using this trick you can customise according to your logical requirement.

 

=formatnumber(Sum(((Fields!hghra.Value + Fields!vghre.Value) / IIF(Fields!nmlhrs.Value=0,1,Fields!nmlhrs.Value))),2)

In SSRS Error : The tablix includes a table header or column with FixedHeader set to true. This is not allowed ….

 If you’ve got this error, first select the tablix and go to tablix properties. Check FixedColumnHeaders/ FixedRowHeaders property has been set to true, then you need to set it to FALSE.