ReportViewer reporting from a DataTable

Question

Hello!

I was wondering if there was a way to design a report for ReportViewer from a SQL query. So from the SQL query I'd get all the data I need to make a report and then design the report in the designer?

I am very new to ReportViewer and I am quite confused. Right now I can see I can populate the DataSet from a wizard, however as far as I can see there is no way to throw a SQL query in there and then design from that.

Is what I want even possible or will I have to use a DataGridView? I really want to use the reportviewer because of the printing/exporting support. Is there any literature available on the subject?

Answer 1

If you are using the LocalReport Class (reportViewer.LocalReport) and not the SSRS (everything is clickable there), a sample is in the link.

ReportDataSource Class has constructor that accepts a DataTable object.

You only need to design the report and add datasets to the RDL file.

Edit: Insert datasets as xml like this:

<DataSources>
    <DataSource Name="MyDataSource">
      <ConnectionProperties>
        <ConnectString />
        <DataProvider>SQL</DataProvider>
      </ConnectionProperties>
    </DataSource>
    ...
</DataSources>

<DataSets>
    <DataSet Name="MyDataSet">
      <Query>
        <CommandText>MyDataSet</CommandText>
        <DataSourceName>MyDataSource</DataSourceName>
      </Query>
      <Fields>
        <Field Name="Id">
          <DataField>ID</DataField>
        </Field>
        <Field Name="SomeOtherField">
          <DataField>SOME_OTHER_FIELD</DataField>
        </Field>
      </Fields>
   </DataSet>
</DataSets>

Answer 2

Firstly, it depends on whether you're using a local report or a server report (SQL Server Reporting Services). Both can be displayed within the ReportViewer control.

For a server report, things are simple. The report contains data sources and you can either choose to use an SQL statement or an existing stored procedure.

For a local report, things are a bit more difficult. You'd have to design a typed data set that contains your data. This can be filled from anything as usual (SQL command, stored procedure, etc.). You'd then assign the instance of this data set to the report's data source.

Please note that while the server reports allow you to filter the data, you'd have to write some filtering feature yourself when using local reports.