Posts Tagged ‘odbc’

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.

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



One more excel with different data.



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


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

How to create above data source connection ?

Please refer the blog given below:-

Now we will create dynamic ODBC connection with expressions.

Now second file is full path is :-


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


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


Run the report, check the report output.



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.



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