Data Exchange Formats | MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

The two remaining export or rendering formats provided by Reporting Services are data exchange formats. They are intended to take the data in a report and put it into a format that can be used by another computer program. In this section, we look at the basic structure of each of these formats. We also discuss the customization that can be done with each format.

We continue to use the RenderingTest report from the Chapter09 report project to examine the data output by each format.

Comma-Separated Values (CSV) Data Exchange Format

The Comma-Separated Values (CSV) format has been around for a number of years. The CSV format is used to represent tabular data. Each line in the file represents one row in the table. Each value between two commas represents a column in the table. If a column value contains a comma (for example, “Bolimite, Mfg”), the value is enclosed in quotation marks.

CSV exports include the data contained within tables, matrixes, and lists in your report. All the data from the table, matrix, or list is included in the CSV export, even if a column or a row is hidden. CSV exports do not contain values from charts or text boxes that are not within a table, matrix, or list.

Reports to be exported using the CSV format should be kept simple. Only one table, matrix, or list should be placed on the report. When reports with more than one table, matrix, or list are exported using the CSV format, the resulting file can be complex and confusing.

If you open the CSV file that results from the RenderingTest report in Notepad, it appears as follows. (A CSV file opens in Excel by default.) This represents the values from the matrix near the top of the report.

 "Bolimite, Mfg",Previous Day Delivery,0,0,2,1,... "Bolimite, Mfg",Next Day Delivery,0,0,0,2,... "Bolimite, Mfg",Same Day Delivery,0,0,0,0,... "Custer, Inc.",Next Day Delivery,1,0,1,0,... "Custer, Inc.",Previous Day Delivery,0,0,1,0,... "Custer, Inc.",Same Day Delivery,0,0,0,0,...

XML Data Exchange Format

In Chapter 7, we discussed XML and the fact that reports are stored in an XML format called Report Definition Language (RDL). Here, we are looking at XML as a means of exchanging data between programs. In both cases, the XML files are simply text files with information organized between XML tags.

By default, XML exports include the data contained within tables, matrixes, lists, and charts in your report. All the data from the table, matrix, list, or chart is included in the XML export, even if a column or a row is hidden. XML exports do not contain values from text boxes that are not with a table, matrix, or list.

Because each item in the XML export is labeled with an XML tag, reports to be exported using the XML format can be more complex than those exported using the CSV format. Because of this, reports to be exported using the XML format may have more than one table, matrix, list, or chart.

The following is a section of the XML file that results from the RenderingTest report:

 <Report xmlns="RenderingTest" ...> <matrix1>   <matrix1_CustomerName_Collection>     <matrix1_CustomerName CustomerName="Bolimite, Mfg">       <matrix1_RowGroup2_Collection>         <matrix1_RowGroup2textbox6="Previous Day Delivery">                 <matrix1_Year_Collection>            <matrix1_Year Year="2002">                        <matrix1_ColumnGroup2_Collection>                          <matrixl_ColumnGroup2textbox5="Sep">                 <Cell DeliveryNumber="0"/>                </matrixl_ColumnGroup2>                            <matrixl_ColumnGroup2textbox5="Oct">                  <Cell DeliveryNumber="0"/>                </matrixl_ColumnGroup2>

You can quickly see how the XML structure follows the report layout. The Report tag provides information about the report as a whole. After that tag is a series of tags containing the data in the matrix near the top of the report. Again, note, by default, the text boxes at the top of the report are not included in the XML export.

Customizing the XML Data Exchange Format

You can customize the XML export to fit your needs. Let’s change the XML export to include the contents of the text box that reads CENTERED TEXT IN A FANCY FONT. We can also change the matrix1_RowGroup2_Collection tag to DeliveryTypes and the matrix 1_RowGroup2 tag to Delivery Type. Finally, we remove the DeliveryNumber altogether.

If you have downloaded the Chapter09 project, open the project and try this procedure:

Open the RenderingTest report.
Select the Layout tab.
Right-click the text box containing CENTERED TEXT IN A FANCY FONT. Select Properties from the Context menu. The Textbox Properties dialog box appears.
Select the Font tab.
Look in the Family drop-down list for Juice ITC. If it is not there, select a font present in the list. (You are unable to save the changes made in this dialog box unless a valid font is selected.)
Select the Data Output tab.
Type FancyFont for Element Name. (This specifies the name to use for this element.)
Select Yes for Output. (This forces this item to be output in the XML.)
Select Element for Render As. (This causes the item to be output as an element rather than as an attribute.)
Click OK to exit the Textbox Properties dialog box.
Select matrix 1 from the drop-down list at the top of the Properties window.
Click the Property Pages button in the Properties window to display the Matrix Properties dialog box.
Select the Groups tab.
Select matrix 1_RowGroup2 and click Edit next to the Rows area. The Grouping and Sorting Properties dialog box appears.
Select the Data Output tab.
Enter DeliveryType for Element name.
Enter DeliveryTypes for Collection.
Click OK to exit the Grouping and Sorting Properties dialog box.
Click OK to exit the Matrix Properties dialog box.
Right-click the text box in the lower-right corner of the matrix. Select Properties from the Context menu. The Textbox Properties dialog box appears.
Select the Data Output tab.
Select No for Output. (This causes this item not to be output in the XML.)
Click OK to exit the Textbox Properties dialog box.
Click the Preview tab.
Select XML File with Report Data from the Export drop-down list.
Select a location to store this export, enter a filename, and then click Save.
Use the Windows Explorer to find the file you just created, and then double-click the file to open it.

The first few lines of the XML file appear similar to the following:

 <Report xmlns="RenderingTest"... >   <FancyFont>CENTERED TEXT IN A FANCY FONT</FancyFont>   <matrix1>     <matrix1_CustomerName_Collection>       <matrix1_CustomerName CustomerName="Bolimite, Mfg">         <De1iveryTypes>           <DeliveryType textbox6="Previous Day Delivery">           <matrix1_Year_Collection>             <matrix1_Year Year="2002">               <matrix1_ColumnGroup2_Collection>                 <matrixl_ColumnGroup2 textbox5="Sep">                   <Cell />                 </matrixl_ColumnGroup2>                 <matrixl_ColumnGroup2 textbox5="Oct">                   <Cell />                 </matrixl_ColumnGroup2>

The values on the Data Output tab for each item in your report can be used in this way to completely customize the XML output generated by the report.