Sep

DB2 with DotNet and SQL Server Reporting Services

The article lists the facts that are incurred while connecting IBM DB2 with DotNet applications and SQL Server Reporting Services (SSRC) tool.

Connection of DB2 to .Net and SSRS

The connection of DB2 to SSRS was made in the project using the Microsoft ODBC driver. This driver is a default driver and is visible in the list of drivers while creating a new data source in SSRS.
The first step is to create an ODBC data source for DB2.This can be done by adding a new database alias under the IBM DB2 Client Configuration Assistant.
If the creation is successful the Data Source name will appear in the list of System Data Sources in SSRS.

Drawbacks of using Microsoft ODBC driver in SSRS

The Microsoft ODBC driver cannot be used to call DB2 stored procedures. So all calls to the database have to made using Select queries written text in SSRS

Passing parameters in Select queries to make calls to DB2 using ODBC driver

Parameters can be passed using parameter prompts “?” in the select queries in SSRS
Care should be taken to map the correct parameters to the prompts as all parameters are represented by “?”
The mapping is done in the Parameters tab while editing the dataset.

Using the IBM DB2 driver to call DB2 Stored Procedures

The IBM DB2 driver can be used to call DB2 Stored Procedures.
The successful implementation of the above requires installation of IBM DB2 development client version 8 and above with the same version of gateway.
This driver must be added using the Visual Studio Addins. After this is done the driver appears in the list of drivers in .Net and SSRS.

Exporting Reports with Drilldowns to excel

Reports with drilldowns can be created using the Report Wizard and enabling the dilldowns .They can be created manually by using Toggle Field Codes under the Properties of each textbox.

Drawback observed in exporting Reports with Drilldowns to excel

Reports having Sub Reports also containing drilldowns, on export to excel show drilldowns on the Report Viewer but not on the exported excel sheet. This bug was detected on connection with DB2 databases. The same is under investigation by Microsoft Support Group.

Summation of results at the Group Level in SSRS

Summation of results in the group level can be carried out by dragging and dropping the field to be summed up on each of the group headers. The 1^st group header will have summation of results of all groups under it.
Complete summation of all group headers can be obtained by dragging and dropping the field onto the table header/footer.

Using the Running Value function

Running value function can be used to sum up certain fields at run time.
If a group is filtered for a particular value using a visibility condition the Running Value will still consider the hidden row’s value

Hiding a particular value in a group

The group can be hidden for a particular value by putting a visibility condition for that value on the table group.
To filter for a particular row in the dataset the Rownumber function can be used

Connection of .Net to DB2

As the project used web services to connect to the DB2 database, the connection string was set in the web config file of the .Net application.
The same was used to create a connection instance wherever required in the .Net code.

Posted by Tech Guy DB2, DotNet, SQL Server Subscribe to RSS feed