PDXpert PLM Software
Application Notes
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 theReport1 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 theTransforms collection'sSQL transformationstopic. 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.
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.
<Definition>
<dataquery xmlns="http://www.plmx.org/DataQuery.xsd">
...SQL query elements...
</dataquery>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" >
...RDLC report elements...
</Report>
<Provider>Report2</Provider>
<settings />
<context />
</Definition>
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:
- The report dataset's SQL query is contained within a CDATA[ ] element. For example:CDATA[SELECT * FROM ItemView].
- The query result name (such asDataSet1) is used during the report development within Visual Studio.
- 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 topicParameterized SQL queries.
<dataquery parameterized="true" xmlns="http://www.plmx.org/DataQuery.xsd">
<choice>
<sql name="DataSet1">
<![CDATA[
...SQL query...
]]>
</sql>
</choice>
</dataquery>
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:
- Find and save a separate copy of the RDLC file.
- Delete the <?xml version="1.0" encoding="utf-8"?> from the copied file.
- Create and name a new transform.
- 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 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).
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 theMicrosoft Report Viewer 2015 Runtime (RV2015).
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).
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]
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 theDataSet1.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>
<sqlname="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, theparameterized="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.
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:
Select File menu,New ▸ Project…
On the New Project window, selectInstalled > Visual C# > Windows, and then aWindows Forms Application. Name the project (let's say, PDXpertReportDesign), and click the OK button.
In the Solution Explorer, Add ▸ New Item… to the project.
On the Add New Item window, selectVisual C# Items > Data > DataSet. Name the dataset (say, DataSet1), and click the Add button. A newDataSet1.xsd document opens.
In the Toolbox DataSet tools list, locate the TableAdapter and drag it onto the DataSet1.xsd document.
The TableAdapter Configuration Wizard opens. Set up the data connection to the PDXpertDB database. Click Next >.
If you wish, save the connection string. ClickNext >.
Set the TableAdapter database access to Use SQL Statements. ClickNext >.
Enter your SQL query (above) into the What data should be loaded into the table? textbox. ClickFinish. Save the DataSet1.xsd content.
In the Solution Explorer, Add ▸ New Item… to the project.
On the Add New Item window, selectVisual C# Items > Reporting, and then eitherReport or Report Wizard. Name the report (for example, HomePartsList.rdlc), and click the Add button. A newHomePartsList.rdlc document opens.
Add the report's dataset to the RDLC document.
In the Dataset Properties window, select your dataset (DataSet1) from theData source: dropdown, and clickOK.
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 aReporting > ReportViewer control to Form1. In the ReportViewer Tasks control, select your report in the Choose Report dropdown list.
Start the project to test your report.
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.
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.