Tag: SSRS

Axis Object – Auto Interval Error in SSRS line charts

I faced this problem because the data series in my line chart doesn’t have any data when the vertical axis’s minimum, maximum, Interval and Interval type properties are all set to “Auto”.

Reading from the net it seems that this problem can also occur when your data has values of infinity such as when you use an expression to calculate the series data values and there is a division by 0 somewhere which produces infinity.

To solve this problem when there are no data values, I set the minimum value and the maximum value of the vertical axis using the following expression

For minimum value:  =IIf(Min(Fields!Value.Value) = Nothing, 0, “Auto”)

For maximum value: =IIf(Max(Fields!Value.Value) = Nothing, 100, “Auto”)

Basically, if there are no maximum or minimum values in the dataset, set some default values. This way SSRS won’t complain and the report will show with no error messages.

Default Values for parameters not working in SSRS with SharePoint integration

I faced this problem while trying to set default values to parameters in a report that resides within the SharePoint site.

It turned out that once a report is uploaded to SharePoint, the default values for parameters are maintained in the server, so subsequent changes to the default values will have no effect after saving or overwriting the report in the server.

To solve this, delete the report from the server and upload the report again. Quite stupid, but it works.

SSRS line chart gaps are not correct

For SSRS line charts to display gaps in the data correctly, the data must explicitly tells that for certain timestamps there are no data in those timestamp.

If there is a gap between timestamp A and timestamp B, the line chart will only display a gap if there are table rows for timestamps between A and B that contain empty or nil values. Otherwise, it will connect the lines instead of showing gaps.

Creating additional series from same table column for Line graphs in SSRS

I spent quite a lot of time experimenting to be able to do this.

Say you have a table like so

KPI        Value    Date

Temp      34        …

Press      12         …

 

And you would like to have 2 lines in the line graph, one for temperature, one for pressure, the series for pressure may use the secondary y axis. The series for pressure may use a totally different chart type(column).

To do this, don’t add KPI as a series group, add Date as a category group and add Value twice as values.

for the first Value, use IIf(Fields!KPI.Value = “Temp”, sum(Fields!Value.Value, Nothing).

for the second Value, use IIf(Fields!KPI.Value = “Temp”, sum(Fields!Value.Value, Nothing).

Configure the second value to use secondary y axis and you’re done.

Consume Web API response in SSRS

Follow the article: http://jaliyaudagedara.blogspot.sg/2015/10/using-aspnet-web-api-as-data-source-for.html

Need to configure the web api service to always return XML data. When configuring the data source, add ?type=xml at the end of the URL.

Note: for SSRS/Report Builder to retrieve the fields, don’t use blank query string, use the following:

<Query>

</Query>

Find /bin folder for SSRS integrated with SharePoint

SharePoint strikes again.

Follow the article http://blogs.catapultsystems.com/aroney/archive/2013/02/11/custom-dlls-with-sql-server-reporting-services-in-sharepoint-integrated-mode/

Normally, you can find the SSRS at C:\Program Files\Microsoft SQL Server\ MSRS10_50.SQLSERVER\Reporting Services\

If you want to find the folder for SSRS integrated with SharePoint, it’s not where it’s usually located but under the /15 hive, C:\Program Files\Common Files\Microsoft Shared\Web Service Extensions\15\Webservices\reporting\bin