Search

Advertisements

Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts

SSRS 2016 Mobile reports

In this post, I have provided quick walk-through on Integration of Mobile reports with SSRS 2016 


Below are the pre-requisites to play with mobile reports, Inside Azure VM – Install below software’s:

1.       Microsoft Visual C++ 2012 Redistributable (x86) - 11.0.61030
2.       Microsoft SQL Server Mobile Report Publisher
3.       Microsoft® SQL Server® 2016 Report Builder
4.       Configured a Firewall for Report Server Access
5.       AdventureWorks Databases and Scripts for SQL Server 2016 CTP3

SSRS Exporting reports to different file formats

Till SQL Server 2012 below are the supported file formats for exporting SSRS reports :




From 2016,

SSRS 2016 - Treemap and Sunburst - New Shape Chart types

In SSRS 2016, two new Shape Chart types are included
i)  Treemap 
ii) Sunburst 

Tree Map and sunburst visualizations are great for visually representing hierarchal data.

We can add this new chart types like same way we add other charts.
Design -> Right-click on report body -> Insert -> Chart -> Shape -> Tree map / Sunburst.


Microsoft Azure - Getting started with SSRS on VM

In this article, let us see steps to get started with SQL Server Reporting Services (Report Builder 2016) on Microsoft Azure SQL Server Virtual machine.

To create & connect to SQL Server VM, refer my previous post.


SSRS - Make common change in multiple reports in one click

Consider a scenario, say if there are more than 100 reports developed already. Each & every report has company logo on header as embedded image. Suddenly, if the company has decided to make minor change in the logo and all the reports needs to be modified with new logo image.

To make common change in multiple reports in one click, we need to create 
  1. Stored procedure 
  2. SSIS package

 Worked Example:

 I have created six sample reports with "SQL Server 2014" image in the header as shown below:

SSRS Delete Dataset Cache Files

After designing the report and when the report is Previewed , you will notice DATA file getting created in the project folder of report .

To know more about DATA file : DATAFILE (rdl.data)

To delete dataset cache files :

SSRS - PIE chart

In this article , let us see an example for creating SSRS PIE chart .

Let us try to create Pie chart like in MSDN forum users profile .

Drag & drop , Pie chart on to the report body :

From Toolbox - > Report Items - > Chart - > Shape - > Pie
or Under Design pane - > right click on report body - > Insert - >  Chart - > Shape - > Pie



SSRS - How to remove space (because of no data) between Bars in Column Chart

In SSRS , because of no data there can be space between bars in Column Chart .

Try this MSDN SSRS forum discussion for clear explanation on how to remove space (because of no data) between bars in Column Chart .

SSRS - How to make copy of RDL file

In some cases , we might need copy of RDL file , say .,

For example : you had completed developing your report and only alignment & formatting changes are pending and you don't want your final version of the report to be disturbed because of these changes .

Remember we do have the undo/redo option with CTRL+Z .

But still if you need to make a copy of RDL file , it is simple and easy

SSRS - Divide by Zero error - Myth of IIF

In SSRS , IIF evaluates both True and False part irrespective of condition imposed .

Because of that , expressions using IIF will lead us to error in some cases .

Two common scenarios where IIF causes problem :

i. ) Divide by zero .
ii ) Argument 'Month' is not a valid value .

i . )  Divide by zero :


Refer below two MSDN blogs for explanations on how IIF causes divide by zero error and workaround for the same .

http://blogs.msdn.com/b/sqlforum/archive/2011/02/28/faq-why-does-the-attempted-to-divide-by-zero-error-still-happen.aspx

http://blogs.msdn.com/b/bwelcker/archive/2006/09/26/end-of-amnesia-_2800_avoiding-divide-by-zero-errors_2900_.aspx

ii ) Argument 'Month' is not a valid value :


Refer this link for example and explanations on how IIF causes "Argument Month is not a valid value" error and workaround for the same .

Here is the Microsoft Connect for SSRS IIF - Divide by zero error -
http://connect.microsoft.com/SQLServer/feedback/details/729872/ssrs-iif-divide-by-zero-error 

SSRS - SUM based on condition

In this article , let us see an example on how to SUM based on condition in SSRS tablix report .

Consider below query is my report dataset :

DECLARE @tmp TABLE (Code VARCHAR(10),Level1 INT,Level2 INT)
INSERT @tmp SELECT 'CHN',5,10
INSERT @tmp SELECT 'KOL',5,15
INSERT @tmp SELECT 'IND',10,25
INSERT @tmp SELECT 'NY',5,10
INSERT @tmp SELECT 'TX',5,15
INSERT @tmp SELECT 'US',10,25
SELECT * FROM @tmp


Consider , I have tablix report as shown in below image :



In the above shown report , If we are required to SUM values in the Columns Level1 and Level2
, only for Code = "IND" and Code = "US" , then we need to write expression as shown below :

In the Total data row , under Level1 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level1.Value,0))

In the Total data row , under Level2 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level2.Value,0))




Reference : 
http://technet.microsoft.com/en-us/library/ms159134(v=sql.100).aspx

SSRS - No data message for report items

This article is about handling display of report items like chart , table or matrix when there is no data
returned from the dataset .

For example :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth
WHERE MonthNo = @MonthNo


I have table & column chart on the report body , when I preview the report and enter MonthNo =12
and click on View report




Again if I enter MonthNo =13 and click on View report



So to handle display of report items like chart , table or matrix when there is no data
returned from the dataset:

Go back to Design pane , click on table , press F4 - > Tablix properties - > NoRowsMessage - > write this expression : ="No data for Month :"+Parameters!MonthNo.Value 





Click on chart , press F4 -> Properties - > NoDataMessage -> (Caption) ->
write this expression : ="No data for Month :"+Parameters!MonthNo.Value



Now again when I preview the report and enter MonthNo =13  and click on View report 








Reference links :
http://technet.microsoft.com/en-us/library/dd220407.aspx
http://technet.microsoft.com/en-in/library/cc645968(v=sql.100).aspx

SSRS - How to display table after clicking on chart

If we have a requirement to display table only after clicking on chart then follow below steps :

Consider below query is my dataset :

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth


I have Column chart and table on the report body as shown below :




To display all labels on the X-axis (Horizontal axis) 

To sort labels on X - axis (Horizontal axis) 

Parameter settings is as shown in below images :

Advertisements