Element Name ReportItems in Visual C#.NET

Printer PDF417 in Visual C#.NET Element Name ReportItems

Let s now see how the Ad Hoc Reporter generates the actual report. Creating the report table region To simplify authoring the actual report, I don t generate the report definition file from scratch. Instead, I use a template in the form of a pre-generated RDL file, Schema.xml, located in the AWReporterWin/bin/debug folder. This file originated from the report definition of a very basic tabular report that I authored using the Report Designer, as shown in listing 2.1. 58

<Table Name="table1"> Defines a table with <Height>0.25in</Height> the name table1 <Details> <TableRows> <TableRow> <Height>0.25in</Height> <TableCells> Defines the <TableCell> table cells <ReportItems> <Textbox Name="textbox1"> <Style /> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> </TableRow> </TableRows> </Details> <DataSetName>AWReporter</DataSetName> Defines the <Top>0.375in</Top> table dataset <Width>1.66667in</Width> <Style /> <TableColumns> <TableColumn> <Width>1.66667in</Width> Defines the </TableColumn> table columns </TableColumns> </Table>

As you can see, the predefined schema has a table region with one column and one cell only. For the first column that the user drags and drops, I have to update only the name of the cell. For any subsequent column, I generate a new column and cell in the table region. Generating RDL Let s now put on our developers hats and write some .NET code to generate the report definition. The bulk of the report-generation logic is encapsulated in the CreateRDL function, as shown in listing 2.2.

Listing 2.2 Creating the report definition programmatically by loading the report schema in the XML DOM

private void CreateRDL() { XmlDocument xmlDoc = new XmlDocument();

xmlDoc.Load (System.IO.Path.Combine(Application.StartupPath, "Schema.xml")); XmlNamespaceManager xmlnsManager = new XmlNamespaceManager(xmlDoc.NameTable); xmlnsManager.AddNamespace("rs","http://schemas.microsoft.com _ & /sqlserver/reporting/2003/10/reportdefinition"); xmlnsManager.AddNamespace("rd", http://schemas.microsoft.com _ & /sqlserver/reporting/reportdesigner"); GenerateColumns(xmlDoc, xmlnsManager); GenerateCells (xmlDoc, xmlnsManager); UpdateDataSource(xmlDoc, xmlnsManager); xmlDoc.Save(txtRDLPath.Text); }

The application loads the schema using the XML DOM. Because the schema defines XML namespaces, we use the XmlNamespaceManager to add the namespaces to the XML document. Then, we generate the table region columns and cells. For each cell, we set the field name to be the same as the column name. After that, we embed the data source information into the report definition, which includes the connection string and dataset schema. Finally, we save the RDL file to a location specified by the user in the Path to RDL text box. Once the report definition is generated, we can test the report by loading the file in a BI project and previewing the report. There will be cases when the AW Ad Hoc Reporter will not be enough (OK, I am also modest, am I not ). Currently, Microsoft doesn t provide an ad hoc reporting tool. To fill the void, you can use third-party offerings, as you see in the next section.

Microsoft is partnering with a number of independent software vendors (ISVs) to create add-ons to RS to extend its capabilities. I would like to finish this chapter by mentioning two third-party reporting tools, Cizer s Quick Query for Microsoft Reporting Services and Hitachi s RDL Generator. You may find these products interesting because they address two popular needs: ad hoc reporting and converting Crystal Reports. For a full list of the Microsoft partners for RS visit http://www.microsoft.com/sql/ reporting/partners/default.asp.

Cizer s Quick Query As of time of this writing, Microsoft doesn t offer a tool for ad hoc reporting that integrates with RS. To respond to this need, Cizer has developed the Quick Query as a web-based tool that allows users to craft simple ad hoc reports in a fast and easy way. No knowledge of report authoring with the Report Designer is required. Many organizations don t have dedicated IT resources or expertise to create reports using the

information workers to generate easily their own reports. Cizer s Quick Query embraces the Microsoft RDL schema as a report storage medium and integrates seamlessly with RS. The report administrator can define which data sources users can report off, such as tables, stored procedures, or SQL statements. Once the report data sources are set up, users can navigate to the Quick Query portal. Figure 2.12 shows the Quick Query web portal. Users can select a data source from the Select Data Source drop-down list. Then, they can pick which fields from the data source they want to see on the report. Quick Query supports data grouping, filtering, and parameterized queries. Once the fields are selected, users can define the report layout and request to see the report. At this point, behind the scenes Quick Query uploads the report definition to RS using the Report Server Web service API. Then, Quick Query requests the report from Report Server by URL and displays it to the user. Users also have an option to save the ad hoc report definition as an RDL file. In this case, the tool calls down to Report Server Web services by invoking the GetReportDefinition() method. This downloads the report definition file to the user machine.

Figure 2.12 Cizer s Quick Query has a web-based front end that allows end users to create ad hoc reports.