By: Scott Murray | Updated: 2012-05-25 | Comments (6) | Related: > Reporting Services Data Sources
Problem
Although you would prefer to use SQL Server only, your organization has data in a large list of different databases including TeraData. Can TeraData be used as a SQL Server Reporting Services (SSRS) data source and if yes, how do I set it up?
Solution
Microsoft provides a rich set of data sources which can be used in Reporting Services or Report Builder; TeraData data sources are included in this list. For specific details on the exact versions of Teradata which can be used, please see http://msdn.microsoft.com/en-us/library/ms159219.aspx#Teradata. Specifically, Microsoft supports several versions of the .Net TeraData Data Provider including versions 12.00 and 13.00.
Setting up a TeraData Data Source in SSRS
First, start Business Intelligence Design Studio (BIDS), create a new SSRS Project; give the solution and project any name.
With the new project created, add a new data source by Right-Clicking on the Shared Data Sources folders and selecting Add New Data Source.
In the Name text box, Add a Name for the Data Source. Next, select the data source Type of Teradata. You will need to have the correct Teradata drivers installed on the server in order for the connection to be successful. Specially, .NET Framework Data Provider for Teradata version 12.00 or higher is required. Once, you select the Type, click on the Edit button.
On the connection properties screen, Enter the Teradata server name, the Teradata User name, and the Teradata password. Next, click on the Plus Sign to the left of Optional. On this expanded window, enter the default Teradata database. Finally, click Test Connection to verify the connection is setup correctly. If the connection is not successful, click the Advanced button to validate your connection; one possible site to compare your actual connection string against is http://msdn.microsoft.com/en-us/library/dd220525.aspx which includes several example connection strings. Also, the previously mentioned site, http://msdn.microsoft.com/en-us/library/ms159219.aspx#Teradata, is a good starting point to troubleshoot connection issues.
You are now ready to create your query, however several key points need to be mentioned. First, if you Select the Query Designer, you will notice that no Graphical Query designer is available. You will need to create the query in the Teradata SQL tools and then either use the Import option on the Dataset Properties windows or Query Designer window (see next two figures), or just copy and paste the query from the Teradata SQL tool set. Also, the Refresh Fields will not work until the query is entered correctly.
Finally, once your Teradata Data Source is setup correctly, and your Teradata query tested, you are ready to create your report. Several excellent tutorials and tips exist on MSSQLTips which guide you through the rest of the report creation process. I would suggest the following tutorials to get you started:
- SQL Server Reporting Services (SSRS) Tutorial--http://www.mssqltips.com/sqlservertutorial/222/sql-server-reporting-services-ssrs/
- Getting Started with Reporting Services -- http://www.mssqltips.com/sqlservertip/1347/getting-started-with-reporting-services/
In a similar fashion, we will review a similar process for creating a Teradata Source in Report Builder 3.0.
Creating a Teradata Report Builder Data Source
To create a report builder report based on a Teradata data source, we follow a similar process to what we learned for SSRS reports. The process in Report Builder is streamlined. First, Open up Report Builder either via an SSRS site or from the start menu. The new report Wizard opens.
Next, click on the Create a dataset radio button and then Next.
Now we are ready to create our data source by clicking on the New Button.
From this point, the setup is very similar to the SSRS data source create screen. However, the Build button is grayed for the TERADATA connection type, Microsoft says by design (http://connect.microsoft.com/SQLServer/feedback/details/724293/teradata-driver-connection-string-in-report-builder-3-0-is-greyed-out); unfortunately, the function button (fx) only opens an expressions builder window with no assistance for building a full connection string. To assist you in creating your connection string, an example of a Teradata connection string can again be found at: http://msdn.microsoft.com/en-us/library/dd220525.aspx.
At this point, you must manually complete the connection string. You could create the connection string in SSRS and then copy and paste it into the connection string text box or use the examples provided previously.
In addition to creating the connection string manually, care must be taken to determine the correct credentials to use for the report.
Finally, after creating the connection string, you can create a simple report based on our newly created data source as described in the next few steps.
Using a Teradata Data Source in a Report Builder Report
Creating a query is the first step to be completed after you have setup your data source. Similar to the BIDS process described above, the query must be input manually. No GUI or Query Designer exists.
After the query is input and complete, you will continue through the tablix wizard by first adding the appropriate columns to the Row Groups, Column Groups, and Values areas. Unfortunately due to the proprietary nature of the query, I am unable to show the exact field names.
The next step in the Wizard allows the report designer to select the report layout and subtotal options.
At this point we are ready to review our new Teradata report in the design area of Report Builder.
Finally, here is a copy of our example report. Again, I had to hide some of the proprietary data.
In this tutorial, we reviewed the process of creating a Report Services and Report Builder data source based on Teradata back end database. We learned that we must have the appropriate Teradata .Net drivers installed on the machine (whether the server or local desktop) which is running the report. Furthermore, the actual setup of the data sources varies depending on whether the data source is being designed in BIDS or Report Builder. BIDS does have a method of "building" the data source via setup windows whereas Report Builder requires the report designer to hand code the data source. Subsequently, no graphical query designer exist either in BIDS or Report Builder, which requires the designer to hand code the SQL statements. Finally when the Teradata query is complete, the report creation process can continue in a fashion similar to any other BIDS or Report Builder report.
Next Steps
- Review the tutorial Daniel Calbimonte created for creating an SSRS report based on a My SQL data source
- Check out these related resources:
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-05-25
