Archive

Posts Tagged ‘SSRS’

SSRS 2016 New Features

June 22, 2017 Leave a comment
  • SSRS Setup

In SSRS 2016, we have new standalone installer unlike previous version  we use SQL  Server Setup to install Reporting Services.

New SSRS 2016 additional installer is look like below:-

SSRS Setup

  • Data Sources

    SSRS can connect to other database systems, if other database providers update their data connectors then Microsoft need to update its providers list as well.

Differences between SSRS 2012 and SSRS 2016 data providers given below:-

SSRS 2012                           SSRS 2016

In above screen, Hyperion Essbase database provider is shown in 2012 version, that become Oracle Essbase in 2016 version because Oracle acquired Essbase.

  • Reporting Services web portal
    • Enhanced Web portal- Report Manager is replace by Portal
    • New Reporting Export options like PowerPoint

SSRS2016ExportOption

                      Custom branding for the web portal

CustomBranding

 

  • KPI

    Key performance indicators (KPI) in the web portal, On Portal one can show the KPI’s as objects which loaded the values when the portal is opened.

    KPI

  • Power BI Reports Integration

PowerBI

  • Mobile Report Publisher

MobileReportPublisher

 

Thanks

Prasad

 

 

Advertisements
Categories: Home, SSRS Tags: ,

SSRS : Page break after certain number of group

May 29, 2016 Leave a comment

This question I got from MSDN, Click here to get the original source of this question.

In brief :-

page break after certain number of group display

how can I do a page break after displaying 10 group collapsed lines ?

i am showing collapsed groups and want to page break after displaying 10 group lines (in collapsed view)

So user want like page break should happen  after 10 groups. Unfortunately there are no function to get the group counts. So better to bring group count number in the query itself.

Let say you have a sample data query as given below:-

select ‘Fruits’ category, ‘Apple’ product, 50 cost
UNION
select ‘Fruits’ category, ‘Grapes’ product, 40 cost
UNION
select ‘Fruits’ category, ‘Peach’ product, 30 cost
UNION
select ‘Vegetables’ category, ‘Tomato’ product, 50 cost
UNION
select ‘Vegetables’ category, ‘Potato’ product, 20 cost
UNION
select ‘Vegetables’ category, ‘Capsicum’ product, 40 cost
UNION
select ‘Grocery’ category, ‘Rice’ product, 100 cost
UNION
select ‘Grocery’ category, ‘Wheet’ product, 120 cost
UNION
select ‘Grocery’ category, ‘Oil’ product, 180 cost
UNION
select ‘Cleaning’ category, ‘Detol’ product, 80 cost
UNION
select ‘Cleaning’ category, ‘Floor cleaner’ product, 50 cost
UNION
select ‘Cleaning’ category, ‘Dish washer’ product, 40 cost

Dataoutput

add the  Dense_Rank function to add one more column which will give the group number as shown below:-

select *, dense_rank() over( order by category)pagebreakrow from
(
select ‘Fruits’ category, ‘Apple’ product, 50 cost
UNION
select ‘Fruits’ category, ‘Grapes’ product, 40 cost
UNION
select ‘Fruits’ category, ‘Peach’ product, 30 cost
UNION
select ‘Vegetables’ category, ‘Tomato’ product, 50 cost
UNION
select ‘Vegetables’ category, ‘Potato’ product, 20 cost
UNION
select ‘Vegetables’ category, ‘Capsicum’ product, 40 cost
UNION
select ‘Grocery’ category, ‘Rice’ product, 100 cost
UNION
select ‘Grocery’ category, ‘Wheet’ product, 120 cost
UNION
select ‘Grocery’ category, ‘Oil’ product, 180 cost
UNION
select ‘Cleaning’ category, ‘Detol’ product, 80 cost
UNION
select ‘Cleaning’ category, ‘Floor cleaner’ product, 50 cost
UNION
select ‘Cleaning’ category, ‘Dish washer’ product, 40 cost

)T

denseRankoutput

Now go to report design and group properties as shown below:-

GroupProperties

let say I want page break after 2 groups then the expression should be like below:-

=iif(Fields!pagebreakrow.Value mod 2 = 0,False ,True )

run the report, output is like below:-

ReportOutput.png

Let say , I want page break after 3 groups then expression should be as given below:-

=iif(Fields!pagebreakrow.Value mod 3 = 0,False ,True )

run the report

 

ReportOutput

Hope you like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Categories: Home, MSDN, SSRS Tags: ,

SSRS : Dynamic ODBC Excel connection using expressions

May 29, 2016 Leave a comment

ODBC connection using Excel as data-source.

First we check the normal ODBC excel connection,  how to create Shared data source using normal connection, i.e. Static connection is given very nicely in below post.

http://hussain-msbi.blogspot.sg/2012/07/create-ssrs-report-using-excel-data.html

We will only see the connection string of normal connection then we try to modify it to dynamic.

I have created two excel with different data in it as shown below:-

sample.xlsx:-

sample

One more excel with different data.

sample_2016_05.xlsx:-

sample_2016_05

One connection is shared data source with sample.xlsx as shown below :-

`shareddatasource

Above connection string is static one which connect to sample.xlsx.

How to create above data source connection ?

Please refer the blog given below:-

http://hussain-msbi.blogspot.sg/2012/07/create-ssrs-report-using-excel-data.html

Now we will create dynamic ODBC connection with expressions.

Now second file is full path is :-

C:\Users\prasad\Desktop\2016\sample_2016_05.xlsx

To write expression in data source connection, it should be embedded data-source.

Note: In Shared data source connection we cant write expression.

Please follow below screen , how to write expression for dynamic one:-

DynamicDatasource.png

Type: ODBC above is must select option.

Expression given in above screen:-

=”Dsn=ODBC_Connection_Sample;dbq=C:\\Users\\prasad\\Desktop\\” & cstr(year(Today)) & “\\sample_” & cstr(year(Today)) & “_0” & cstr(month(Today)) & “.xlsx;defaultdir=C:\\Users\\prasad\\Desktop;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5”

Now create two dataset using each type of datasource, report design will look like below:-

ReportDesign

Run the report, check the report output.

ReportOutput

 

Hope you people like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

SSRS: Custom Start of week (Friday to Thursday )

May 15, 2016 Leave a comment

Generally we have start of week Sunday to Saturday or Monday to Sunday depends upon the system settings.

So before setting our expression to custom week as Friday to Thursday , we should know what is the default start of the week.

Can get more information on MSDN

As I am checking weekday Starting from Sunday or Monday.

CheckingWeekStart

So In my SSRS report week start is Sunday and end at Saturday.

Now when we want to modify week as Friday to Thursday then we need to identify the pattern.

Below  column F and H are the pattern :-

week PatternAnalysis

Now write expressions based on the pattern:-

Start of week Expression:-

=Format(iif(WeekDay(Fields!Date.Value)>=6,dateadd(DateInterval.Day, 6-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, -1-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

End Of week Expression:-

=Format(iif(WeekDay(Fields!Date.Value)>=6,dateadd(DateInterval.Day, 12-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, 5-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

Run the report , output as given below:-

SSRS Ouput

Now we will check how to get the previous week using again pattern analysis

Previous week  Pattern Analysis

Now write expressions based on the pattern:-

Start of Previous Week :-

=Format(iif(WeekDay(Fields!Date.Value)<=5,dateadd(DateInterval.Day, -8-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, -1-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

End Of Previous Week:-

=Format(iif(WeekDay(Fields!Date.Value)<=5,dateadd(DateInterval.Day, -2-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, 5-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

 

Run the report, output looks like below:-

ReportPreviousWeekOutput

So we can set previous week or current week based on custom requirement, only we need to identify the pattern. Pattern help to write case or IF statements. Without pattern we need to minimum 7 cases for each week day.

Hope you people like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Categories: Home, MSDN, SSRS Tags: ,

SSRS : Hide Data Point on SSRS Chart

May 6, 2016 Leave a comment

This Question is originally posted on MSDN, link click here.

In Brief:-

I am using SSRS 2008 R2. I have a chart where the values are gallons of fuel, category groups are Months, and Series Groups are Years. I want to hide the current month of the current year only when the chart is generated. So when I generate the chart in May 2016, I want the data point hidden, not the category group since there will be data points for May 2013, 2014, and 2015. Then when June 2016 roles around, same thing, etc. Below is a screen shot of my chart. If someone needs to see the SQL Query, I can provide that as well.

Lets create sample report using sample data , Sample data query as given below:-

 

select ‘Jan’ Monthsname, 1 MonthNumber, 2014 year, 2000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2014 year, 3000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2014 year, 5000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2014 year, 7000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2014 year, 8000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2014 year, 9000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2014 year, 10000 gallons
union
select ‘Jan’ Monthsname, 1 MonthNumber, 2015 year, 3000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2015 year, 5000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2015 year, 8000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2015 year, 2000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2015 year, 6000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2015 year, 1000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2015 year, 7000 gallons
UNION
select ‘Jan’ Monthsname, 1 MonthNumber, 2016 year, 9000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2016 year, 8000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2016 year, 7000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2016 year, 3000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2016 year, 2000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2016 year, 10000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2016 year, 3000 gallons

order by year,MonthNumber

Design the report dataset with above query and drag the  line char control on to the report body and set the Values, Category Groups and series groups as shown below:-

1

Run the report to check what it is showing then we modify what needed .

1

As already shown above that , what we needed we need to hide the current month current year data point.

First we show data points with circle as shown below:-

1

Lets Filter the data from dataset or chart as per your need. Better we filter at chart level , I mean filter current Month and current year data.

Go to Chart Properties , Filters Pane and set the expression as shown below:-

1

Expression :-

=cstr(Fields!year.Value) + cstr(Fields!MonthNumber.Value)

Value:-

=CSTR(year(now()))+cstr(month(now()))

Run the report, report will not have the data point but line was breaked as shown below:-

1

Now We need to set the color of the current year series, Why we need to set the color of the series will show in later stage of development.

1

=iif(Fields!year.Value=year(now()),”RED”,”Automatic”)

We will create a line for empty data values so we set color as RED as we set the same series with RED color in above screen

Follow above click on line and go to the properties and then go to EmptyPoint and set Color RED.

1

 

Now run the report

1

let say i didn’t set the series color and for empty rows  I set color green then how it look like:-

1

So better set the color for filtered series and same color used for empty data line

 

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Categories: Home, MSDN, SSRS Tags: ,

SSRS : Replace string with an image

April 30, 2016 Leave a comment

Requirement is like  write an expression in textbox such that based on the value image is replaced.

Let say I have sample data query similar to you data as given below:-

select 'P1' GroupName,'A' Value
union
select 'P1' GroupName,'B' Value
union
select 'P1' GroupName,'C' Value
union
select 'P2' GroupName,'A' Value
union
select 'P2' GroupName,'B' Value
union
select 'P2' GroupName,'C' Value

And I have three images in my desktop as shown below:-

3

Now create a sample report as shown below:-

4

Now right Click on the textbox below the Value column and insert image as shown in below screen:-

111

Once you insert image, its open the image properties as shown below, and click on the expression icon which is also shown in this image:-

222

Expression editor open, where you enter the below expression:-

=IIF(Fields!Value.Value=“A”,“image1”,iif(Fields!Value.Value=“B”,“image2”,“image3”))

333

Images should be added in the report before as shown below:-

444

Run the report, report output looks like below:-

555

So it working, if you like it please like the post below..

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Categories: Home, MSDN, SSRS Tags: ,

SSRS : Disable CSV header while SSRS report export to CSV using URL Reporting

April 28, 2016 Leave a comment

Let say we have the report with only table and its deployed on server as shown below:-

CSVReport

Now export the report as CSV and open it ,  you will see the header columns as shown in below screen:-

CSVExported

Now create report-server URL as shown below:-

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fSAMPLE%2fTest%2fCSVexportReport&rs:Command=Render&rc:NoHeader=True&rs:Format=CSV

Actually , you need to add the exiting reportserver URL with below line

&rc:NoHeader=True&rs:Format=CSV

Copy the above URL you just created and run on browser, it will ask open or save CSV file, once you open the file, there is no header.

NoHeader

 

 

So it working, if you like it please like the post below..

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Categories: Home, MSDN, SSRS Tags: , , ,