Adding Filters from a Form

[Mike Simpkins]

I am attempting to add a filter programmatically from a form. Nothing appears to happen. I am using the following code on an event on a form. The OK button on the form sends a Dailogue Result OK and then the report renders.

Code: Select all

Data1.Filters.Add(new StiFilter());
Data1.Filters[0].Item = StiFilterItem.Expression;
Data1.Filters[0].Expression = "(ItemsSorted.Quantity < 0)";
Data1.FilterOn = true;

ItemsSorted.Quantity is a field in my data source. Data1 is the data band.

I can add the same filter from the Report Designer using the filter dialogue and get the results I want. However, when I try and give the user some control over filter values, the filters do not work at all. My goals are as follows:

1) The user will enter values in a form -- I will do the error checking,etc.
2) Based on the user values, I will constructed a String which I intend to use as a filter expression.
3) I want to be able to create the filter with the String as the filter expression (i.e. "Sales<100"), make it active and then allow the report to be viewed.

Do I need to set my filters in the BeforePrint event? Am I improperly setting filters? Is there a trick I am (most likely) unaware of?


Any thoughts?

[Alex K.]

Hello,

As variant, you can use variable in filters.
Please see the sample report in attachment.

Thank you.

[Mike Simpkins]

I downloaded the file, but I was unable to get it to run in my version of Reports.Net (it will not connect to a data source).

In any event, you state:

As variant, you can use variable in filters.

I understand this concept and I have gotten it to work before. Essentially, one sets the filter expression in the filter dialogue to be (MyData==MyVariable).

However, I am trying to get the Filters.Add feature to work, where I can programmatically add a filter expression.

I am attempting to create a string (say "Sales > 100", or "Sales>100 && CustID==3") based on user responses to my form and then make the report run using those filters. As it is, I cannot even get the following to work:

Code: Select all

Data1.Filters.Add(new StiFilter());
Data1.Filters[0].Item = StiFilterItem.Expression;
Data1.Filters[0].Expression = "false";
Data1.FilterOn = true;

The above code should display an empty DataBand.


What am I doing wrong?

[Mike Simpkins]

Okay -- I did more poking around in the forums and I think I got a little farther. I looked at the following thread:Filter in SQL REPORT and found a little more information. Edward of the Stimulsoft Team replied to sector7g's post as follows:

Yes, it is possible to do.

Please see the Demo.exe sample application. This application available after installing full trial version of the Stimulsoft Reports.Net with setup program.
In that application go to SQL group of Reports, then into 'Parameters from Dialog Form' example report. Press the Design button.
Here is some tips about work with Sql DataSources.

1) The Sql DataSource must have ConnectOnStart property set in false.
2) Detail query in a Master-Detail report with parameters must have ReconnectEachRow parameter set in true.
3) You can connect all the Dictionary at once via this.Dictionary.Connect(); command. Also each StiSqlDataSource has its own Connect() method which can be called individually for each DataSource.

So, I placed the following code on an event in my dialog box:

Code: Select all

MyDataSource.Disconnect(); // Just in case it was connected.
if (MyDataSource.Filters.Count == 0) MyDataSource.Filters.Add(new StiFilter()); //add a filter
MyDataSource.Filters[0].Item = StiFilterItem.Expression; //Create a filter expression
MyDataSource.Filters[0].Expression = "false"; // assign the expression
MyDataSource.Connect(); //Connect data source

I then went to my DataSource properties and set "Connect on Start" to false and "Reconnect on Each Row" to true.

This code should have resulted in a blank report; instead, the report contains every record.

Still no luck.

Please advise.

[Jan]

Hello Mike,

I need to ask you. You use dialog in report or in your application? Also which database you use?

Thank you.

[Mike Simpkins]

Hello Mike,

I need to ask you. You use dialog in report or in your application? Also which database you use?

Thank you.

I use a dialog as a form in my report. I am using a SQL database. The filtering I would like to do is based on the results of a SQL SUM in the query. Here is my query:

Code: Select all

SELECT 	
		pnd_invno as InvNumber,
		pnd_Barcode as Barcode,
		pnd_DESCRIPTION as Description,
		Count(PND_INVNO) as CountSales,
		SUM(PND_QUAN) as Quantity,
		Sum(PND_ACOST) as ExtCost,
		SUM((PND_OPRICE-(PND_OPRICE*PND_DISC/100))*PND_QUAN) as ExtPrice,
		Case WHEN {Display} = (-1)
			Then
				Case WHEN (SUM((PND_OPRICE-(PND_OPRICE*PND_DISC/100))*PND_DQUAN) = 0)
				Then -9999
				Else ((SUM((PND_OPRICE-(PND_OPRICE*PND_DISC/100))*PND_QUAN)-Sum(PND_ACOST)) / SUM((PND_OPRICE-(PND_OPRICE*PND_DISC/100))*PND_QUAN))
				End
			Else
				Case WHEN (SUM(PND_ACOST)=0)
				Then 9999
				Else (((SUM((PND_OPRICE-(PND_OPRICE*PND_DISC/100))*PND_QUAN)-Sum(PND_ACOST))/Sum(PND_ACOST))+1)
				End
			End as Result

FROM
		pinvdet
 
WHERE
	 (	PND_invno !=0 and
		PND_Barcode not like '%Note:%' and
		PND_DATE >= {StartDate} and 
		PND_DATE , >=,=,  >     RESULT , >=,=,  >

Can this be done?

[Jan]

Hello Mike,

Main problem in your case that report already compiled. If you swtich to code of report you can see that all filters transfered to c# code. So when you try to change filter in report runtime from report it does not have sense. In you case you can change sql query for apply filter. For example you can use sql query in following way:

Code: Select all

select * from customers 
where {myvariable}

So you can change myvariable (string variable) before report running in report. After then you can use Connect Disconnect methods to reopen datasource.

Thank you.

[Mike Simpkins]

Main problem in your case that report already compiled. If you swtich to code of report you can see that all filters transfered to c# code. So when you try to change filter in report runtime from report it does not have sense. In you case you can change sql query for apply filter.

Code: Select all

select * from customers 
where {myvariable}

...
So you can change myvariable (string variable) before report running in report.

Okay, so here is the summary of the answer then:

1) Report Filter Expressions (StFilters) can only be changed from the code of the report prior to compilation. This has nothing to do with Stimulsoft, this is due to the fact that c# does not have run-time expression evaluation as in Java or some other languages.

2) One can make filters which reference variables in the report. One can hard code a filter such as "MyColumn <= {MyVariable}" and then change the value of MyVariable dynamically either through a report form or as the result of a calculation.

3) If one needs to use more complex, runtime expressions, one needs to create dynamic SQL WHERE clauses as opposed to a report filter.


I think that about sums it all up.

Thank you for your assistance.

[Alex K.]

Hello,

Ok.
Let us know if you need any additional help.

Thank you.