Passing Querystring to SQLDataSource

[aas]

I know I'm missing something simple. But we are demoing Telerik Reporting and coming from using SSRS on ASP.NET projects.
For our existing locally processed reports, we put together an rdlc, the reportviewer and a datasource such as SQLDataSource.
This datasource connects to a stored proc that has multiple parameters passed in QueryString. We simply add QuerystringParameters to the datasource and we are done - the report host aspx page runs, datasource executes and the rdlc displays results in the ReportViewer.

What I can't figure out is how this works in Telerik Reporting. Its seems Telerik's SQLDataSource is defined in the report and not the 'host page' aspx and doesn't seem to accept QueryStringParameter as a parameter. So guess I need to know, how to pass the querystrings (up to 20) to the SQLDataSource the easiest way possible. We do not need report parameters, just the datasource parameters.

Sample aspx from SSRS local report:

<asp:SqlDataSource ID="SqlDataSourceX" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnDB %>"

SelectCommand="SELECT * FROM [Jobs] WHERE ([DeptID] = @d)">

<SelectParameters>
<asp:QueryStringParameter DefaultValue="2" Name="d" QueryStringField="d" />
</SelectParameters>
</asp:SqlDataSource>
Or, how to just use the asp:SQLDataSource on the aspx page to 'feed' the trdx and the ReportViewer control?
Any help is GREATLY appreciated.

[Steve]

Hi,

We've already answered your inquiry in the other support thread you have opened. Here is our answer:

In order to pass query string values to the report SqlDataSource component our suggestion is to utilize an user function that collects and returns the query string value. You can use the user function in expressions that set the SqlDataSource parameters value. Check out the following example:

Copy Code

public static string GetQuerystringValue(string key)
{
    if (null != System.Web.HttpContext.Current)
    {
        return System.Web.HttpContext.Current.Request.QueryString["key"];
    }
    return null;
}

Greetings,
Steve
the Telerik team

[aas]

Thank you for the suggestion. So do I understand that I put this function in the 'host page' where the ReportViewer control is placed then then call it from the 'Expression' value option in the SQLDataSource control parameters configuration panel?
I should clarify that this is an existing WebSite not a project.

[Peter]

Hello,

Here are the steps required to utilize user functions in the report designer and the viewer:

• add the user function in a class library;
• reference the class library to your report viewer project;
• add the following configuration section to your report viewer project's configuration file (web.config or app.config):
  

  <configuration>
        <configSections>
            <section
                name="Telerik.Reporting"
                type="Telerik.Reporting.Configuration.ReportingConfigurationSection, Telerik.Reporting"
                allowLocation="true"
                allowDefinition="Everywhere"/>
        </configSections>
        …
         <Telerik.Reporting>
            <AssemblyReferences>
                <add name="MyCustomAssembly" version="1.0.0.0"/>
            </AssemblyReferences>
         </Telerik.Reporting>
  </configuration>

• build the class library;
• copy the compiled assembly (.dll) to the standalone report designer root directory and add the necessary config sections as you have done for the report viewer project's and as elaborated in the Extending Report Designer help article.

Regards,
Peter
the Telerik team

[aas]

Thanks for the information. I think one problem was that this was a WebSite and not a project.
We'll follow the steps to get a class library built and see how it goes.

[aas]

Looks like we are making progress but no solution yet.

• Class created and function tested. Config files Updated. Class built and referenced in project.
• Confirmed working with call from a test aspx page like this:TextBox1.Text = RptQueryStrings.RptExp.GetQuerystringValue("d")
• Added the config and .dll to the designer directory and now the finction shows in the designer as an option.
• Included the 5 parameters from our stored proc and set their value in the 'Edit Parameters' window for the SQLDataSource to
  = RptQueryStrings.RptExp.GetQuerystringValue("d") and so forth for each parameter

Report SQLDatasource XML:

<SqlDataSource ConnectionString="QualityConn" SelectCommand="dbo.usp_LineSheetRpts" SelectCommandType="StoredProcedure" Name="sqlDataSource1">
  <Parameters>
    <SqlDataSourceParameter DbType="Int32" Name="@d">
      <Value>
        <String>= RptQueryStrings.RptExp.GetQuerystringValue("d")</String>
      </Value>
    </SqlDataSourceParameter>
    <SqlDataSourceParameter DbType="AnsiString" Name="@n">
      <Value>
        <String>= RptQueryStrings.RptExp.GetQuerystringValue("n")</String>
      </Value>
    </SqlDataSourceParameter>
    <SqlDataSourceParameter DbType="Int32" Name="@pl">
      <Value>
        <String>= RptQueryStrings.RptExp.GetQuerystringValue("pl")</String>
      </Value>
    </SqlDataSourceParameter>
    <SqlDataSourceParameter DbType="AnsiString" Name="@af">
      <Value>
        <String>= RptQueryStrings.RptExp.GetQuerystringValue("af")</String>
      </Value>
    </SqlDataSourceParameter>
    <SqlDataSourceParameter DbType="AnsiString" Name="@at">
      <Value>
        <String>= RptQueryStrings.RptExp.GetQuerystringValue("at")</String>
      </Value>
    </SqlDataSourceParameter>
  </Parameters>
</SqlDataSource>

Unfortunately, when running the report on the website, all parameters passed to the SQL server are NULL, no errors thrown but the function for querystrings seems to be returning null.

 If I debug my sample page setting the textbox value, then the correct value is returned. However, if I use the same parameters and debug running the report then all parameters return Nothing.

Public Class RptExp
    Public Shared Function GetQuerystringValue(ByVal key As String) As String
        If Not HttpContext.Current Is Nothing Then
            Return HttpContext.Current.Request.QueryString(key)
        End If
        Return Nothing
    End Function

End Class

Any ideas what would be different calling this function from the report definition versus a standard .aspx page?

[aas]

For anyone monitoring - I gave up on this mess and finished my report in SSRS using a local report. 20 filters, 300 pages and ZERO coding required.

[Peter]

Hello Andrew,

I have made some tests on our end and unfortunately the suggested approach is not applicable when the report viewer is utilized because the report processing is done in an HttpHandler that works with it's own set of querystrings. In order to handle this scenario you have two options:

• Set up the SqlDataSource component in the page's code behind and bind the data source parameters to the query string value as elaborated in the Using Parameters with the SqlDataSource component help article. Additionally because you are using the XML report definition in order to set the data item's datasource you have to deserialize the report definition as elaborated in the Serializing Report Definition in XML help article;
• Another option is to add a report parameter for every required data source parameter and pass the query string value as report parameter. This is elaborated in the Using Report Parameters programmatically help article.

Please excuse us for the caused confusion.

Kind regards,
Peter
the Telerik team