Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Report with Teradata Data Sources


By:   |   Read Comments (6)   |   Related Tips: > 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.  

New Project

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.

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.

TeraData Connection String

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.

Teradata Detail Datasource Connection Details DB Setup

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.  

Teradata Query
Teradata Query Step 2

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:

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.

Report Builder

Next, click on the Create a dataset radio button and then Next.

Report Builder

Now we are ready to create our data source by clicking on the New Button.

Report Builder DataSource Setup

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.

Report Builder Query

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.   

Report Builder Final Query

In addition to creating the connection string manually, care must be taken to determine the correct credentials to use for the report.

Report Builder Security

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.

Report Builder Query

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.

Report Matrix

The next step in the Wizard allows the report designer to select the report layout and subtotal options.

Report Builder Matrix Final Layout

At this point we are ready to review our new Teradata report in the design area of Report Builder.

Report Builder Design Tab

Finally, here is a copy of our example report.  Again, I had to hide some of the proprietary data.  

Report Builder Final

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


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, April 29, 2013 - 10:03:00 AM - Scott Murray Back To Top

Yes... straigt ETL Scripts.  using the data reader....dot net I believe, but do not remember exactly and we no longer have that environment.  I do remember we had to install the Teradata tools on the server.


Monday, April 29, 2013 - 9:45:03 AM - Janie.Carlisle Back To Top

Hmmmm how does that work? Is it efficient. Are you doing straight ETL scripts within the package? Details. . . Thanks!


Friday, April 26, 2013 - 12:21:37 PM - Scott Murray Back To Top

We were actually doing SSIS packages to load the data from Teradata into SQL for 2005, via ODBC. 


Friday, April 26, 2013 - 11:55:02 AM - Janie.Carlisle Back To Top

Yeah that's what I figured. I was trying it out and got nothing. Oh well. I expect to be upgraded to 2012 so have opted to not install 2008 but will definitely give it a shot at that time. We're working a lot with Teradata and it works fine with other tools but I hadn't tried it with my SQL tools because I thought it didn't work with my version. Thanks.


Friday, April 26, 2013 - 10:19:26 AM - Scott Murray Back To Top

I do not believe so.


Friday, April 26, 2013 - 9:34:34 AM - Janie.Carlisle Back To Top

Hi,

I am guessing this doesn't work in SQL 2005, correct?


Learn more about SQL Server tools