Filter component for IN keyword

[jamesk]

I am in the process of evaluating the Stimul Reports, and so far I absolutely love them! I have some questions though.

I need to be able to allow the user to make selections on a form within my application that would dynamically add filter components. I have figured out how to dynamically add a filter, but it appears that Stimul Reports doesn't handle an 'IN'(ex. WHERE Fieldname IN (1,2,3,5,7)) or at least I haven't figured out how to do this.

For example:
User is viewing a grid in our application.
User selects the print preview button to print a report that is tied to the grid.
Prior to previewing, the application displays a form that allows the user to select the criteria to filter the report.
Once the user selects the criteria, the different filter components are then added in a loop.

It's possible that the user makes a selection that would be the equivalent to the following SQL statement:
SELECT *
FROM Products
WHERE ProductID IN ( 1,2,3)

This Select statement will return 3 records.

Rather than adding a filter for each element in the 'IN' section. (i.e. 1, 2, 3) I would prefer to create just one filter that would handle all three.
I have found that I can add three filters and use the databand.FilterMode property set to 'OR' to handle this, but if the user selects a large number of elements(for example: 5416 elements) then it will have to create the same number of filters(5416 filter components). I would prefer to create only one filter component that can handle this type of situation.

Is there a way to do this using the filter component? I would prefer to use the filter component over altering the SQL Statement that is tied to the databand.

-----

Another alternative that I could think of would be to change the datasource SQL. I tried to do this, but it doesn't seem to work for me, can someone let me know what I'm doing wrong?

Code: Select all

            StiReport report = new StiReport();
            //Load report
            report.Load("C:\\Documents and Settings\\James\\Desktop\\report.mrt");

            //Create dataset, connection and fill data adapter
            System.Data.DataSet ds_WO = new System.Data.DataSet();
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = "Data Source=nas;Initial Catalog=TestDB;Integrated Security=True";

            System.Data.SqlClient.SqlDataAdapter da_WO = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM WorkOrders WHERE UID IN (1, 2, 3, 4)", conn.ConnectionString);
            da_WO.Fill(ds_WO);
            ds_WO.DataSetName = "ds_WorkOrders";

            //Populate and display report
            report.RegData("ds_WorkOrders", ds_WO);
            report.Dictionary.Synchronize();

            report.Compile();
            report.Render(true);
            report.Show();

This code doesn't seem to do anything. The report displays fine, but it uses the datasource's SQL that was created at designtime.
Does anyone see what I am missing?

[Brendan]

Hi there,
There's 2 approaches you could use to the 2nd method you described.

I'm guessing your Datasource in the actual report is coming from an SqlConnection within the report.
The reason it's not using your newly created select statement is because you need to edit the one within the report.

to do this you can try the following (assuming the datasource name is "WorkOrders"):

Code: Select all

StiReport report = new StiReport();
//Load report
report.Load("C:\\Documents and Settings\\James\\Desktop\\report.mrt");

Stimulsoft.Report.Dictionary.StiSqlSource tableSource = report.DataSources["WorkOrders"] as Stimulsoft.Report.Dictionary.StiSqlSource;
if(tableSource != null)
{
	tableSource.SqlCommand = "SELECT * FROM WorkOrders WHERE UID IN (1, 2, 3, 4)";
}

report.Show();

A second way to do this would be to add a Variable to your report Dictionary, call it SqlFilter for example. In the report Designer you can edit the Sql Query for your DataSource (Select Datasource, right click, Edit..) so that it looks something like the following:

Code: Select all

Select * FROM WorkOrders {SqlFilter}

Now you could then load your report as follows:

Code: Select all

StiReport report = new StiReport();
//Load report
report.Load("C:\\Documents and Settings\\James\\Desktop\\report.mrt");

report.Compile();
report["SqlFilter"] = "WHERE UID IN (1, 2, 3, 4)";

report.Show();

I hope this helps.

[jamesk]

Thanks for the reply. I like the first suggestion. Does anyone know if Stimulsoft is considering adding the ability to include an IN as part of the filter component?

[Vital]

You can use more then filters with operator "OR", but when you are use Filters report engine load all data.

Thank you.