Data Exchange Formats


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 will look at the basic structure of each of these formats. We will also discuss the customization that can be done with each.

We will continue to use the RenderingTest report from the Chapter09 report project to examine the data that is 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 that are to be exported using the CSV format should be kept very 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 very complex and confusing.

If you open the CSV file that results from the RenderingTest report in Notepad, it will appear as follows. (A CSV file will open 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 talked about 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 that are to be exported using the XML format can be more complex than those exported using the CSV format. Due to this fact, reports that are 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_RowGroup2 textbox6="Previous Day Delivery">                 <matrix1_Year_Collection>            <matrix1_Year Year="2002">                        <matrix1_ColumnGroup2_Collection>                          <matrix1_ColumnGroup2 textbox5="Sep">                 <Cell DeliveryNumber="0" />               </matrix1_ColumnGroup2>                           <matrix1_ColumnGroup2 textbox5="Oct">                 <Cell DeliveryNumber="0" />               </matrix1_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 that contain the data in the matrix near the top of the report. Again, note that, 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 will also change the “matrix1_RowGroup2_Collection” tag to “DeliveryTypes” and the “matrix1_RowGroup2” tag to “DeliveryType.” Finally, we will remove the DeliveryNumber altogether.

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

  1. Open the RenderingTest report.

  2. Select the Layout tab.

  3. Right-click the text box containing “CENTERED TEXT IN A FANCY FONT.” Select Properties from the context menu. The Textbox Properties dialog box will appear.

  4. Click the Advanced button. The Advanced Textbox Properties dialog box will replace the Textbox Properties dialog box.

  5. Select the Font tab.

  6. Look in the Family drop-down list for “Juice ITC.” If it is not there, select a font that is present in the list. (You will not be able to save the changes made in this dialog box unless there is a valid font selected.)

  7. Select the Data Output tab.

  8. Type FancyFont for ElementName. (This will specify the name to use for this element.)

  9. Select Yes for Output. (This will force this item to be output in the XML.)

  10. Select Element for Render As. (This will cause the item to be output as an element rather than as an attribute.)

  11. Click OK to exit the Advanced Textbox Properties dialog box.

  12. Select “matrix1” from the drop-down list at the top of the Properties window.

  13. Click the Property Pages button in the Properties window to display the Matrix Properties dialog box.

  14. Select the Groups tab.

  15. Select “matrix1_RowGroup2” and click Edit next to the Rows area. The Grouping and Sorting Properties dialog box will appear.

  16. Select the Data Output tab.

  17. Enter DeliveryType for Element name.

  18. Enter DeliveryTypes for Collection.

  19. Click OK to exit the Grouping and Sorting Properties dialog box.

  20. Click OK to exit the Matrix Properties dialog box.

  21. Right-click the text box in the lower-right corner of the matrix. Select Properties from the context menu. The Textbox Properties dialog box will appear.

  22. Click the Advanced button. The Advanced Textbox Properties dialog box will replace the Textbox Properties dialog box.

  23. Select the Data Output tab.

  24. Select No for Output. (This will cause this item not to be output in the XML.)

  25. Click OK to exit the Advanced Textbox Properties dialog box.

  26. Click the Preview tab.

  27. Select XML File with Report Data from the Export drop-down list.

  28. Select a location to store this export, enter a filename, then click Save.

  29. Use the Windows Explorer to find the file you just created and then double-click the file to open it.

  30. The first few lines of the XML file will 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">         <DeliveryTypes>           <DeliveryType textbox6="Previous Day Delivery">           <matrix1_Year_Collection>             <matrix1_Year Year="2002">               <matrix1_ColumnGroup2_Collection>                 <matrix1_ColumnGroup2 textbox5="Sep">                   <Cell />                 </matrix1_ColumnGroup2>                 <matrix1_ColumnGroup2 textbox5="Oct">                   <Cell />                 </matrix1_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.




Microsoft SQL Server 2000 Reporting Services
Microsoft SQL Server 2000 Reporting Services Step by Step (Pro-Step by Step Developer)
ISBN: 0735621063
EAN: 2147483647
Year: 2003
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net