SQL Server Reporting Services - Report Model: API (build custom report builder)

Question

I am looking into the idea of creating a custom report builder and data tool to replace the ssrs report builder.

I am wanting something more like a query builder and data export tool rather than reporting.

The thing I want to use is the report model. I want users to select field from report model, render the sql, then exec sql and return data.

How is the report model used by report builder? Does it just provide the schema and the report builder generates the actual sql? How does the sql get generated, server or client side.

How can I so the users the fields available and how can I get the sql for the fields selected by the user?

Answer 1

I really don't think this is worth doing. You'd be better off upgrading to SQL 2008 and using Report Builder 2.0.

But... you can quite easily use the web services provided with SSRS to get at the XML for a report model, and you may be able to then use this as a form of ORM to be able to get a LINQ-ish environment happening.

I just don't think it's really worth it.

Answer 2

I agree with Rob. I don't think the effort would be worth it.

Saying that, I can provide some partial information on this. When a report is created with Report Builder 2.0, there is a semantic query that is created within the dataset that can execute against the report model.

From a high-level the query in the report model looks like it does below:

<SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign"&gt;
  <Hierarchies>
    <Hierarchy>
      <BaseEntity>
      </BaseEntity>
      <Groupings>
      </Groupings>
      <Filter>
      </Filter>
    </Hierarchy>
  </Hierarchies>
</SemanticQuery>

Since a report created from a report model report can be saved to the report server, it can be assumed that the report model is processed on the report server. The SQL is generated then in the reporting services service.