• September 25, 2018

Business Intelligence Development Studio (SSRS/Dashboard) and U2 Toolkit for .NET v.2.1.0

Overview

Microsoft describes SQL Server Reporting Services (SSRS) as a full range of ready-to-use tools and services to help you create, deploy, and manage reports. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components. With Reporting Services you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. Reports can include rich data visualization, including charts, maps, and sparklines. You can publish reports, schedule report processing, or access reports on-demand. You can select from a variety of viewing formats, export reports to other applications such as Microsoft Excel, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site.

 

In this post we have adapted the tutorial provided at the Microsoft Developer Network web site titled “Create a Basic Table Report (SSRS Tutorial)” to show how to leverage these techniques along with our U2 Toolkit for .NET to implement this functionality. For convenience we have included the instructions here, modified to use U2 Toolkit and access data in Rocket UniData or Rocket UniVerse. See below for links to the original Tutorial and to Rocket Software’s Github repository for the Rocket U2 servers lab to find our sample code.

U2 Toolkit for .NET v2.1.0 supports:

  • SQL Access
  • Native Access

U2 Toolkit for .NET v2.1.0 is Shared Data Source in SSRS Project.

Note

You need to register U2 Toolkit for .NET with SSRS. See below for details.

You can find more information here “Register a Standard .NET Framework Data Provider (SSRS)”.

Requirements

U2 Version
UniVerse 10.3 or later
UniData 7.1 or later

 

Microsoft Version
Visual Studio –          2010 SP1–          2012 Update 4

 

–          2013 Update 2 RC

Business Intelligence Development Studio (BIDS)(SSIS and SSRS) –          BIDS 2010–          BIDS 2012 

 

Registering U2 Toolkit for .NET Provider on the Report Server

You must modify the following configuration files so that U2 Database can be visible in SSRS Shared Data Source. Configuration file path depends upon BIDS/SQL Server SSRS version. In this example, we have used SQL Server 2012.

Configuration File Name Add/Modify
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\ rsreportserver.config See Appendix A
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\ rssrvpolicy.config See Appendix B
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\ RSReportDesigner See Appendix C
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\ RSPreviewPolicy.config See Appendix D

 

Create a Basic Table Report

Create a basic table report based on the U2 database (UniVerse/HS.SALES Account) using the Report Designer. In this tutorial, you will create a report project, set up U2 connection information, define a query, add a Table data region, group and total some fields, and preview the report.

Step 1: Creating a Report Server Project (Reporting Services)

First you need to create a report server project where you will save your report definition (.rdl) file and any other resource files that you need for your report. Then you will create the actual report definition file, define a data source for your report, define a dataset, and define the report layout. When you run the report, the actual data is retrieved and combined with the layout, and then rendered on your screen, where you can export it, print it, or save it. In this lesson, you will learn how to create a report server project in SQL Server Data Tools (SSDT). A report server project is used to create reports that run on a report server.

To create a report server project

  1. Click Start > All Programs > Microsoft SQL Server > SQL Server Data Tools. If this is the first time you have opened SQL Server Data Tools, click Business Intelligence Settings for the default environment settings.
  2. On the File menu, click New > Project.
  3. In the Installed Templates list, click Business Intelligence.
  4. Click Report Server Project.
  5. In the Name field, enter U2_SSRS_Tutorial.
  6. Click OK to create the project.

fig1

 

The Tutorial project is displayed in Solution Explorer.

To create a new report definition file

  1. In Solution Explorer, right-click Reports and then select Add > New Item.

fig2

  1. In the Add New Item dialog box, select the Report template.
  2. In the Name field, enter Customers.rdl and then click Add.
  3. The Report Designer opens and displays the new .rdl file in Design view.

fig3

Report Designer is a Reporting Services component that runs in SQL Server Data Tools (SSDT). It has two views: Design and Preview. Click each tab to change views. You define your data in the Report Data pane. You define your report layout in Design view. You can run the report and see what it looks like in Preview view.

Step 2: Specifying Connection Information (Reporting Services)

 

Before this step, you must complete step “Registering U2 Toolkit for .NET Provider on the Report Server”.

You need to define a data source, which is connection information the report uses to access data from U2 database. You will use the U2 Database (UniVerse/HS.SALES Account) sample database as your data source.

To set up a connection

  1. In the Report Data pane, click New > Data Source
  2. In the Name field, enter CustomerDS.
  3. In the Type field, select U2.

fig4

 

  1. Click Edit. This requires you to enter the following information: User, Password, Server, Database, ServerType, and AccessMode, and to set the PersistSecurityInfo parameter to True.

fig5

  1. Click Test Connection.
  2. Press OK

fig6

  1. Click Credentials in the left pane and click Use Windows Authentication (integrated security).

fig7

Step 3: Defining a Dataset for the Table Report (Reporting Services)

 

After you define the data source, you need to define a dataset. In Reporting Services, data that you use in reports is contained in a dataset. A dataset includes a pointer to a data source and a query to be used by the report, as well as calculated fields and variables.

 

Query Designer is not supported for Custom .NET Provider.

To define a query for report data

  1. In the Report Data pane, click New > Dataset. The Dataset Properties dialog box opens.
  2. In the Name box, type CustomerDataset.
  3. Click Use a dataset embedded in my report.
  4. Make sure the name of your data source, CustomerDS, is in the Data source text box, and that the Query type is Text.
  5. Type, or copy and paste, the following query into the Query box.

SELECT C.CUSTID, C.FNAME, O.PRODID, O.BUY_DATE, O.DESCRIPTION FROM CUSTOMER AS C INNER JOIN CUSTOMER_ORDERS AS O ON C.CUSTID=O.CUSTID ORDER BY C.CUSTID ASC

fig8

  1. Test the query. Click Query Designer. Click Run.

fig9

  1. Click OK to exit the Dataset Properties dialog box.

fig10

Step 4: Adding a Table to the Report

 

After the dataset is defined, you can start designing the report. You create a report layout by dragging and dropping data regions, text boxes, images, and other items that you want to include in your report to the design surface. Items that contain repeated rows of data from underlying datasets are called data regions.

To add a Table data region and fields to a report layout

  1. In the Toolbox, click Table, and then click on the design surface and drag the mouse. Report Designer draws a table data region with three columns in the center of the design surface.

fig11

  1. In the Report Data pane, expand the CustomerDataset dataset to display the fields.

fig12

 

fig13

  1. Drag field from the Report Data pane to the first column in the table.

fig14

Step 5: Preview Your Report

Previewing a report enables you to view the rendered report without having to first publish it to a report server.

To preview a report

  • Click the Preview tab. Report Designer runs the report and displays it in Preview view.

The following diagram shows part of the report in Preview view.

fig15

 

Step 6: To Publish the Report to the Report Server (Optional)

 

  1. An optional step is to publish the completed report to the native mode report server so you can view the report from Report Manager.
  2. 2. On the toolbar click Project > U2_SSRS_tutorial Properties.
  3. In the TargetServerURL type the name of your report server, for example http://<servername>/reportserver (for example : http://localhost/reportserver
  4. 4.Click OK
  5. On the toolbar click Build > Deploy tutorial.
  6. Start Report Manager with administrator privileges, for example, right-click the icon for Internet Explorer and click Run as administrator. Browse to the Report Manager URL, for example: http://<server name>/reports.
  7. Browse to the folder that contains the report and click the name of the report Customersto view the rendered report in the browser.

fig16

fig17

Appendix A

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\ rsreportserver.config <Data><ExtensionName=”SQL”Type=”Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”SQLAZURE”Type=”Microsoft.ReportingServices.DataExtensions.SqlAzureConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”SQLPDW”Type=”Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”OLEDB”Type=”Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”OLEDB-MD”Type=”Microsoft.ReportingServices.DataExtensions.AdoMdConnection,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”ORACLE”Type=”Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”ODBC”Type=”Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”XML”Type=”Microsoft.ReportingServices.DataExtensions.XmlDPConnection,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”SHAREPOINTLIST”Type=”Microsoft.ReportingServices.DataExtensions.SharePointList.SPListConnection,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”SAPBW”Type=”Microsoft.ReportingServices.DataExtensions.SapBw.SapBwConnection,Microsoft.ReportingServices.DataExtensions.SapBw”/>

<ExtensionName=”ESSBASE”Type=”Microsoft.ReportingServices.DataExtensions.Essbase.EssbaseConnection,Microsoft.ReportingServices.DataExtensions.Essbase”/>

<ExtensionName=”TERADATA”Type=”Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/>

<ExtensionName=”U2″Type=”U2.Data.Client.U2Connection,U2.Data.Client, Version=2.1.0.0, Culture=neutral , PublicKeyToken=7afaaf959fd491a0″/>

</Data>

 

Appendix B

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\ rssrvpolicy.config <CodeGroupclass=”UnionCodeGroup”version=”1″PermissionSetName=”FullTrust”Name=”U2 Toolkit for .NET Provider”Description=”.Net Framework Data Provider for U2 Data Server”><IMembershipConditionclass=”UrlMembershipCondition”version=”1″Url=”C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\bin\.NETFramework\v4.0\U2.Data.Client.dll”/></CodeGroup>

 

Appendix C

C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\ RSReportDesigner <Data><ExtensionName=”SQL”Type=”Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions” /><ExtensionName=”SQLAZURE”Type=”Microsoft.ReportingServices.DataExtensions.SqlAzureConnectionWrapper,Microsoft.ReportingServices.DataExtensions” /><ExtensionName=”SQLPDW”Type=”Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”OLEDB”Type=”Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”OLEDB-MD”Type=”Microsoft.ReportingServices.DataExtensions.AdoMdConnection,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”ORACLE”Type=”Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”ODBC”Type=”Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”XML”Type=”Microsoft.ReportingServices.DataExtensions.XmlDPConnection,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”RS”Type=”Microsoft.ReportingServices.DataExtensions.RSDPConnection,Microsoft.ReportingServices.DataExtensions”/><ExtensionName=”SHAREPOINTLIST”Type=”Microsoft.ReportingServices.DataExtensions.SharePointList.SPRemoteConnection,Microsoft.ReportingServices.DataExtensions”/>

<ExtensionName=”SAPBW”Type=”Microsoft.ReportingServices.DataExtensions.SapBw.SapBwConnection,Microsoft.ReportingServices.DataExtensions.SapBw”/>

<ExtensionName=”ESSBASE”Type=”Microsoft.ReportingServices.DataExtensions.Essbase.EssbaseConnection,Microsoft.ReportingServices.DataExtensions.Essbase”/>

<ExtensionName=”TERADATA”Type=”Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions”/>

<ExtensionName=”U2″Type=”U2.Data.Client.U2Connection,U2.Data.Client, Version=2.1.0.0, Culture=neutral , PublicKeyToken=9ab9148678f4f448″/>

</Data>

 

Appendix D

 

C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies\ RSPreviewPolicy.config <CodeGroupclass=”UnionCodeGroup”version=”1″PermissionSetName=”FullTrust”Name=”U2 Toolkit for .NET Provider”Description=”.Net Framework Data Provider for U2 Data Server”><IMembershipConditionclass=”UrlMembershipCondition”version=”1″Url=”C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\bin\.NETFramework\v4.0\U2.Data.Client.dll”/></CodeGroup>

 

Resources

Source Code

Contact us to learn more about Rocket MultiValue Solutions.

 

Rocket Software

Rocket Software 108 Posts

Thousands of companies around the world depend on Rocket to solve their most challenging business problems by helping them run their critical infrastructure, business processes, and data, as well as extending the value of these assets to take advantage of cloud and mobile computing, advanced analytics, and other future innovations.

9 Comments

  • SleazyOtto Reply

    September 2, 2014 at 1:30 pm

    Raj, I’m a bit perplexed by this comment, from Wikipedia’s entry for BIDS:
    “BIDS is not supported with http://en.wikipedia.org/wiki/Visual_Studio_2010 and later, and has been replaced by SQL Server Data Tools – Business Intelligence”

    That doesn’t seem to make sense, given that you reference both a 2010 and 2012 version of BIDS (in the requirements section), and VS 2012 or 2013 as requirements.  Do you know any more about that?

    Thanks! – Scott Marshall, Fidelity E&P

  • Rajan Kumar Reply

    September 15, 2014 at 12:20 pm

    Hi Scott,
    Thank you for reading the article and asking this question.
    In March, 2013, BIDS is renamed to SQL Server Data
    Tools – Business Intelligence (SSDT-BI). So for VS2012 and VS2013 , you
    have to download SSDT-BI. This will allow you to  develop
    and deploy SQL Server Business intelligence project using VS2012 and VS2013. If
    you have VS2010 Project, you can open with VS2012.
    VS2010  supports BIDS. You need to install SQL Server
    2010 with BIDS features (unlike SSDT-BI). Then you can see BIDS Template
    in VS2010. See enclose screen shot VS2010 and BIDS.

    See below screen shot.

    screenshot raj 1

    Regards,

    Rajan

  • Mike Petryk Reply

    May 15, 2015 at 9:51 am

    Hi Rajan,

    I’m following along with your article “Business Intelligence Development Studio (SSRS/Dashboard) and U2 Toolkit for .NET v.2.1.0” and my *Edit…* button on the Shared Data Source Properties popup is not enabled.

    I found this resource: https://social.msdn.microsoft.com/Forums/en-US/a0441d42-4995-4c61-b54d-c1167ed111d3/edit-button-disabled-when-selecting-custom-data-provider-in-shared-data-source-properties?forum=sqlreportingservices

    which indicates the reason it is not enabled is:

    “We check to see if the data provider currently selected has a custom Connection UI dialog associated with it to determine if the Edit button is enabled or disabled. We do the check by trying to retrieve the IDataConnectionProperties collection and the IDataConnectionUIControl UI control from the custom data provider. If you implemented both interfaces for your data provider, then the button should be enabled. ”

    I’ve updated all the config files as specified in the articles appendix and have tried both the 32 and 64 bit drivers (I’m running Windows 8.1 64bit/ Visual Studio 2012 / SQL 2014).

    What do I need to do in order to get the edit button enabled?

    Regards,
    Mike

  • Rajan Kumar Reply

    May 15, 2015 at 10:38 pm

    Hi Mike,

    Thank you for asking this question. Thanks for reading this blog and trying to create SSRS Report using U2 Toolkit for .NET.

    I checked my code and I can see that I have implemented these two interfaces in “U2.Data.VSPackage.dll” DLL:
    [ IDataConnectionProperties collection and the IDataConnectionUIControl UI control ]

    I have seen this issue before (“Edit” control is not enabled).

    It looks like you did not Authorize U2 Toolkit for .NET Developer Product. Please get Authorize Code License from U2BC and run “U2AddinsAuthorization.exe”. See screen shot below.
    Once it is Authorized, “Edit” control button will be enabled.

    Please install U2 Toolkit for .NET v2.2.0 Developer. In this Release we have developed new tool called “Config File Editor” (RegisterU2Provider.exe). It helps you to modify all 4 SSRS configuration files easily. See screen shot below.

    Link for screen shot:

    http://1drv.ms/1EaC6lE

    Regards,

    Rajan

  • Kurt Reply

    May 21, 2015 at 12:09 am

    Hi Rajan,

    I am facing a similar predicament, after downloading the U2. TOOLKIT FOR .NET developer from the Rocket website. It looks like this version of the download does not have the Authorize software in it. Could you please point us towards the right direction?

    Thanks,
    Kurt

    • Rajan Kumar Reply

      May 21, 2015 at 10:40 am

      Hi Kurt,

      Thank you for downloading U2 Toolkit for .NET.

      It looks like you have downloaded U2 Toolkit for .NET Provider ( NOT U2 Toolkit for .NET Developer).

      For your information, U2 Toolkit for .NET has two components:

      – U2 Toolkit for .NET Developer (Chargeable)
      – U2 Toolkit for .NET Provider (Free)

      For SSRS development, you need U2 Toolkit for .NET Developer. So please contact u2support@rocketsoftware.com.

      They will help you to do the following:

      – Purchase U2 Toolkit for .NET Developer License
      – Download Web Link to U2 Toolkit for .NET Developer Product
      – Authorize code

      Please contact u2support@rocketsoftware.com

      Regards,

      Rajan Kumar

  • Rocket at 25: top posts of 2015 | Rocket Software Reply

    December 17, 2015 at 10:00 am

    […] 1. Business Intelligence Development Studio (SSRS/Dashboard) and U2 Toolkit for .NET v.2.1.0: Microsoft describes SQL Server Reporting Services (SSRS) as a full range of ready-to-use tools and services to help you create, deploy, and manage reports. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components. With Reporting Services you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. […]

  • Warren Wilson Reply

    April 19, 2018 at 11:39 am

    Do you have updated Report Server configuration files information for using SQL 2016? I am using the above configurations but they do not work.

  • Paul Chang Reply

    April 20, 2018 at 12:38 pm

    SQL Server 2016 SSRS with .Net Framework 4.6:
    Appendix A
    C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\ rsreportserver.config

    <Data>
    <Extension Name=”U2″ Type=”U2.Data.Client.U2Connection,U2.Data.Client, Version=2.2.2.0, Culture=neutral , PublicKeyToken=20e25e560a20d05b”/>
    </Data>

    Appendix B
    C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\ rssrvpolicy.config
    <CodeGroup
    class=”UnionCodeGroup”
    version=”1″
    PermissionSetName=”FullTrust”
    Name=”U2 Toolkit for .NET Provider”
    Description=”.Net Framework Data Provider for U2 Data Server”>
    <IMembershipCondition class=”UrlMembershipCondition”
    version=”1″
    Url=”C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\bin\.NETFramework\v4.6\U2.Data.Client.dll”/>
    </CodeGroup>

    VS 2015 with SQL Server 2016 SSRS
    Appendix C
    C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ RSReportDesigner.config

    <Data>
    <Extension Name=”U2″ Type=”U2.Data.Client.U2Connection,U2.Data.Client, Version=2.2.2.0, Culture=neutral , PublicKeyToken=20e25e560a20d05b”/>
    </Data>

    Appendix D
    C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ RSPreviewPolicy.config

    <CodeGroup
    class=”UnionCodeGroup”
    version=”1″
    PermissionSetName=”FullTrust”
    Name=”U2 Toolkit for .NET Provider”
    Description=”.Net Framework Data Provider for U2 Data Server”>
    <IMembershipCondition class=”UrlMembershipCondition”
    version=”1″
    Url=”C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\bin\.NETFramework\v4.6\U2.Data.Client.dll”/>
    </CodeGroup>

    Note: For the .Net Framework 4.5, the PublicKeyToken is “e2387a1682b4f882”.

Leave a Comment

Your email address will not be published. Required fields are marked *