This is a migrated thread and some comments may be shown as answers.

Passing paramaeters to a Stored Procedure

5 Answers 344 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Dan
Top achievements
Rank 1
Dan asked on 21 Sep 2007, 10:48 AM
Hi,

Is there an easy / codeless way to pass Report Parameters to a stored Procedure or DataSet, rather than filtering the data?

Using filters seems very inefficent, as all data needs to be read from the database, passed to the report, and then filtered. This is fine for small tables but not on those with tens of thousands of records plus.

I cannot see any documentation on filters / parameters on the site, I assume these are forthcoming?

Thanks,
Dan.

5 Answers, 1 is accepted

Sort by
0
Dan
Top achievements
Rank 1
answered on 21 Sep 2007, 03:50 PM
Any update would be appreciated as this seems to be the main obstacle in incorporating this reporting system.
0
Hrisi
Telerik team
answered on 24 Sep 2007, 08:11 AM
Hi Dan,

You are right, in the current implementation Report Parameters are applied after all data are fed from the DataSource. We are considering these features for the next release. 

In mean time it is possible to pass query parameters as explained in this video. As you can see it is not so difficult to add such functionality.

 
Kind regards,
Hrisi
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center
0
Dan
Top achievements
Rank 1
answered on 25 Sep 2007, 09:23 AM
PLESAE IGNORE THIS POST: I got the required functionality by using 
Report.Filters.RemoveAt();

_________________________________________

Thanks, the video helped greatly.

I have now managed to reduce the amount of data coming into the report, and would further like to reduce the fields displayed by using the Report Filters.

The one issue i'm having is that I would to provide users with optional filters, e.g. they can filter if they want, but they don't have to. I have a boolean column that a user can filter using a dropdown list with the following options:

No Filter
True
False

The true/false options work fine, but I can't seem to get a way of making the filter irrelevant by selecting the 'No Filter' option. Is there a way to turn off the filter if a certain value is selected?

Thanks,
Dan.
0
Joel
Top achievements
Rank 2
answered on 25 Sep 2007, 02:06 PM
I did it this way and it seems to work pretty well.  I think this is what you are asking for.


.aspx.vb just has this in it
    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init, ReportViewer1.DataBinding 
        Dim _qsval As Int32 
        _qsval = Request.QueryString("qsval") 
        Dim myreport As New Reports_ReportByID(_qsval) 
        ReportViewer1.Report = myreport 
    End Sub 


the report class just does this then.
  Public Sub New(ByVal _myval As Int32) 
        InitializeComponent() 
        Dim ds As DataSet 
        ds = GetMyData(_myval) 
        Me.DataSource = ds 
 
        txtPageCount.Value = "='Page ' + PageNumber + ' of ' + PageCount" '; '"Page " + PageNumber 
    End Sub 
 
    Public Function GetMyData(ByVal _myval As Int32) As DataSet 
        Dim mysql As String 
        mysql = "exec sp_MySP " & _myval 
        Return objdo.daGetDataSet(mysql) 
    End Function 


0
Svetoslav
Telerik team
answered on 25 Sep 2007, 05:37 PM
Hi Guys,

It's great that you've managed to solve this very interesting task.

We would like to share with you two more approaches that we consider very interesting. Both of them rely on the new functionality that Telerik Reporting Q2 2007 introduces - filters, report parameters and user defined functions.

Lets assume that we've defined one report parameter named UserSelection (referenced later as Parameters.UserSelection) that holds the chosen filter (No Filter, True, False) and the field that we'll filter by is named TrueFalse (referenced later as Fields.TrueFalse).

The 1st approach uses only one filter defined for the report:

new Filter("=IIF(Parameters.UserSelection = "No Filter", True, Parameters.UserSelection)", FilterOperator.Equal, "=IIF(Parameters.UserSelection = "No Filter", True, Fields.TrueFalse)");


The 2nd approach instruments one user defined function:

public static bool IsRowSelected(string userSelection, bool field)
{
    switch (userSelection)
    {
        case "No Filter":               
            return true;

        case "True":
            return (field == true);

        case "False":
            return (field == false);
    }

    return false;
}


and then uses it in the next report filter:

new Filter("=IsRowSelected(Parameters.UserSelection, Fields.TrueFalse)", FilterOperator.Equal, "=True");

We personally prefer the 2nd option as using custom functions is much more descriptive, easy to read and maintain, and the report filter is simpler.

The above examples illustrate the flexibility of the new reporting engine and the endless capabilities that it offers.

I hope this helps.
 

Sincerely yours,
Svetoslav
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center
Tags
General Discussions
Asked by
Dan
Top achievements
Rank 1
Answers by
Dan
Top achievements
Rank 1
Hrisi
Telerik team
Joel
Top achievements
Rank 2
Svetoslav
Telerik team
Share this question
or