Archive

Author Archive

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

 

 

Categories: Home, SSRS Tags: ,

Power BI – Introduction

March 3, 2017 Leave a comment

I have uploaded the Power BI introductory video, which is the first video in my upcoming Power BI Training videos.

Please have a look of Power BI Overview , Products and How to setup for use.

 

Thanks

Prasad

Categories: Power BI Tags:

SQL Server 2012 new Features

June 21, 2016 Leave a comment

Column store indexes

  • Non-clustered index
  • There is a limit of one columnstore index per table.
  • An index can be created before there is data in the table.
  • A table with a columnstore index cannot be updated

Column store indexesColumn store indexes_1

Contained database

  • Database with meta-data information, security information etc with in the database itself
  • Helps in migrating databases with user accounts

User Defined roles

  • Server Level Roles

User Defined roles

 

Tabular Model

Tabular Model

 

DQS Data Quality Services

DQS

DQS Flow

  • Build Rule

DQS-Build Rule

  • Transform Rule (IND to India)

DQS-Transform Rule

  • Create New DQS project

Create New DQS project

  • Select DataSource

DQS-Select DataSource

  • Process Data

DQS-Process Data

  • Result

DQS-Result

 

Always On

  • AlwaysOn =Mirroring + Clustering
  • AlwaysOn they need to be in full recovery mode and backed up (preferably to a share that’s visible from the other nodes).

Always On Feature Disable Error

Always On

 

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

 

T-SQL 2012 New Functions

June 20, 2016 Leave a comment

 

Date and time functions

  1. DATEFROMPARTS

Syntax:- DATEFROMPARTS(Year, Month, Day)

Arguments:- year Integer expression specifying a year.

                      month Integer expression specifying a month, from 1 to 12.

                      day     Integer expression specifying a day.

Return Type:– Date

DATEFROMPARTS

2. DATETIMEFROMPARTS

Syntax:- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Arguments:-

year Integer expression specifying a year.

  month  Integer expression specifying a month.

  day      Integer expression specifying a day.

  hour     Integer expression specifying hours.

  minute Integer expression specifying minutes.

  seconds Integer expression specifying seconds.

  milliseconds Integer expression specifying milliseconds.

Return Type:- datetime

DATETIMEFROMPARTS

3. DATETIME2FROMPARTS

Syntax:- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Arguments:- year Integer expression specifying a year.

  month  Integer expression specifying a month.

  day      Integer expression specifying a day.

  hour     Integer expression specifying hours.

  minute Integer expression specifying minutes.

  seconds Integer expression specifying seconds.

  milliseconds Integer expression specifying milliseconds.

Return Type:- datetime

DATETIME2FROMPARTS

4.SMALLDATETIMEFROMPARTS

Syntax:- SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Arguments:-

  Year – Integer expression specifying a year.

  Month-Integer expression specifying a month.

  Day Integer expression specifying a day.

  Hour Integer expression specifying hours.

    Minute Integer expression specifying minutes.

Return Type:- smalldatetime

SMALLDATETIMEFROMPARTS

5.DATETIMEOFFSETFROMPARTS

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

6.TIMEFROMPARTS

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

7.EOMONTH 

EOMONTH ( start_date [, month_to_add ] )

EOMONTH

  • Logical functions

    1. CHOOSE ( index, val_1, val_2 [, val_n ] ) 

      CHOOSE

    2. IIF ( boolean_expression, true_value, false_value )SELECT IIF ( 20 > 10, ‘true’, ‘false’ ) AS Result;

      SELECT IIF ( 45 > 30, NULL, NULL ) AS Result;

      DECLARE @P INT = NULL, @S INT = NULL;

      SELECT IIF ( 45 > 30, @p, @s ) AS Result;

  • String functions

    1. CONCAT ( string_value1, string_value2 [, string_valueN ] )CONCAT

    2. FORMAT ( value, format [, culture ] )FORMAT
  • Conversion functions

    1. PARSE ( string_value AS data_type [ USING culture ] )PARSE
    2. TRY_PARSE ( string_value AS data_type [ USING culture ] )TRY_PARSE
    3. TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )TRY_CONVERT
  • Sequence

    Sequence Objects

  • Offset

First mark the start of the row by using “OFFSET” command.

Second specify how many rows you want to fetch by using “FETCH” command.

offset

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

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: ,