Adding a custom SQL Server report to PDXpert

Last update 2017-04-17

Applies to Report2 transformations supported by PDXpert PLM server software release 12.0 and later. For PDXpert releases before 12.0, see the Report1 version instructions.

This is an advanced topic. You must be familiar with Microsoft's SQL Server query language, and know how to use Microsoft development tools like Visual Studio and SQL Server Management Studio.

Other reporting options include SQL Server Reporting Services (SSRS) and Microsoft Access, as well as third-party tools like Altova, BusinessObjects, or Domo. If you're familiar with SQL query language, you can export data in a CSV file, as discussed in the Transforms collection's SQL transformations topic. Of course, we can also create custom reports for you.

The Report/Export Wizard tool runs a custom report defined by a transform. The transform contains a SQL Server database query and a standard SQL Server Report. This application note describes how to add the SQL query and report RDLC code to a new transform.

Transform "Report2" for SQL/RDLC reports§

Within a transform's <Definition> element, the <dataquery> creates the report data and the <Report> defines its layout.

The <Provider>Report2</Provider> element identifies the Report2 version transform. This format differs from earlier report formats by the ability to fully customize page headers and footers. If you prefer using PDXpert's standard page headers and footers, use the older Report1 format.

XML names are case-sensitive. Use element names exactly as shown.

SQL <dataquery> element§

The dataquery element is usually created using SQL Server Management Studio (SSMS). After you develop the report's SQL code in SSMS, copy it into the transform's XML.

The dataquery contains three objects:

1. The report dataset's SQL query is contained within a CDATA[ ] element. For example: CDATA[SELECT * FROM ItemView].
2. The query result name (such as DataSet1) is used during the report development within Visual Studio.
3. An optional parameterized="true" attribute indicates that one or more items are selected by the user. Excluding this attribute runs the report against the complete database. For details, see the help file topic Parameterized SQL queries.

RDLC <Report> element§

The Report element is a slightly-modified version of the XML .rdlc file created using Visual Studio.

After you develop the report's RDLC code in Visual Studio:

1. Find and save a separate copy of the RDLC file.
2. Delete the <?xml version="1.0" encoding="utf-8"?> from the copied file.
3. Create and name a new transform.
4. Copy the RDLC code into the transform's XML text area.

Visual Studio adds Report Designer <rd:tag /> namespace elements that PDXpert doesn't need, like this: <rd:TypeName>System.String</rd:TypeName>. You can retain or remove these as you wish.

The RDLC <DataSources> and <DataSet> elements§

The same name is used within the <DataSources> and <DataSet> elements. Assign this name to the query <sql name="{dataset name}"> within the <dataquery> section (above).

Example transform: Home Parts List report§

This example uses SQL Server Management Studio (SSMS), Visual Studio 2015 Community Edition (VS2015) installed with the Microsoft SQL Server Data Tools (SSDT) option, and the Microsoft Report Viewer 2015 Runtime (RV2015).

Creating the SQL query§

In SSMS, develop and validate the SQL query.

This example SQL query lists all home organization parts' release status, as well as their released or canceled iteration (if none, then it lists the pending iteration).

In SQL Server Management Studio, the query result is:

This SQL query is used for two purposes:

• To design the RDLC report. The query is used for the RDLC file's DataSet1, and is saved within the DataSet1.xsd file.

• As the new Transform text file's <sql name="DataSet1"> CDATA element:

  <Definition>

    <dataquery xmlns="http://www.plmx.org/DataQuery.xsd">

      <choice>

        <sqlquery>

          <sql name="DataSet1"><![CDATA[

  SELECT

    [Number]

    ,[Type]

    ,[Description]

    ,'Rev ' + Coalesce(Revision,PendingRevision) + ' @ ' + Coalesce(Lifecycle,PendingLifecycle) AS [Iteration]

    ,CASE

      WHEN IsCanceled=1 THEN

       CASE

        WHEN PendingRevID IS NULL THEN 'Canceled'

        ELSE 'Canceled+Pending'

        END

      WHEN PendingRevID IS NULL THEN 'Released'

      WHEN RevID IS NULL THEN 'Pending'

      ELSE 'Released+Pending'

      END AS [Status]

  FROM

    [viewer].[ItemView]

  WHERE

    [Class]=1 AND [IsHomeItem]=1

  ORDER BY

    [Number], [Type]

          ]]></sql>

        </sqlquery>

      </choice>

    </dataquery>

    <Report>...RDLC report elements will be added here...</Report>

    <Provider>Report2</Provider>

    <settings />

    <context />

  </Definition>

Since this SQL statement queries the entire database, the parameterized="true" attribute is not included. Also, it's OK if your file's XML whitespace (spaces and tabs) is different.

PDXpert's transforms allow SQL DECLARE statements, but Visual Studio Report Designer doesn't support them. If your query includes a DECLARE statement, then create a duplicate query that replaces the variables with hard-coded example values. Create the report design with the example results, then use the actual query in the transform's <sql>...</sql> element.

Creating the RDLC element§

Visual Studio requires the Report Designer component, which must be installed before creating your report. Search the web for Microsoft SQL Server Data Tools (SSDT).

Design the report in Visual Studio:

1. Select File menu, New ▸ Project…

2. On the New Project window, select Installed > Visual C# > Windows, and then a Windows Forms Application. Name the project (let's say, PDXpertReportDesign), and click the OK button.

3. In the Solution Explorer, Add ▸ New Item… to the project.

   

4. On the Add New Item window, select Visual C# Items > Data > DataSet. Name the dataset (say, DataSet1), and click the Add button. A new DataSet1.xsd document opens.

5. In the Toolbox DataSet tools list, locate the TableAdapter and drag it onto the DataSet1.xsd document.

   

6. The TableAdapter Configuration Wizard opens. Set up the data connection to the PDXpertDB database. Click Next >.

7. If you wish, save the connection string. Click Next >.

8. Set the TableAdapter database access to Use SQL Statements. Click Next >.

9. Enter your SQL query (above) into the What data should be loaded into the table? textbox. Click Finish. Save the DataSet1.xsd content.

10. In the Solution Explorer, Add ▸ New Item… to the project.

11. On the Add New Item window, select Visual C# Items > Reporting, and then either Report or Report Wizard. Name the report (for example, HomePartsList.rdlc), and click the Add button. A new HomePartsList.rdlc document opens.

12. Add the report's dataset to the RDLC document.

    

    In the Dataset Properties window, select your dataset (DataSet1) from the Data source: dropdown, and click OK.

13. Designing a report is beyond the scope of this application note. Refer to Microsoft's documentation for details.

    After designing the report, expand the new Form1.cs window to allow the full width of your designed report. From the Toolbox, add and dock a Reporting > ReportViewer control to Form1. In the ReportViewer Tasks control, select your report in the Choose Report dropdown list.

    

14. Start the project to test your report.

    

15. In your PDXpert transform file, update the <Report> section using the RDLC file created by Visual Studio (in our example, HomePartsList.rdlc).

    Do not include the <?xml version="1.0" encoding="utf-8"?> from the first line of the RDLC file.

Final comments§

If your report's SQL query refers to any custom attributes, changing a custom attribute name within the Collection Explorer requires a similar update to the related Transform(s).

Microsoft offers many different tools and methods for creating reports. Refer to Microsoft's documentation for more instructions and options.

• Report Designer (Visual Studio)
• Creating Client Report Definition (.rdlc) Files
• SQL Server RDL Specification

For more information on querying the PDXpert database and creating a transform, see these PDXpert help topics

• How to report, import & export > View & export via ODBC > View database objects (on line)
• Collections reference > General > Data transformations (on line)

 

This application note was relevant to the PDXpert software release that was current at time of publication. Product changes since that time may affect its utility. We'd be happy to assist you in assessing the applicability of this note to your situation.