Archive

Posts Tagged ‘dynamic’

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