Dynamically edit DataSet

Hi,

I have a web application, where the user specifies the criteria for filtering data (with like, In, between... operators and data retrieved from the db). So, the selection criteria query (SQL) is generated. This is somthing similar to Query Wizard. It builds the SQL for WHERE clause only. This needs to be attached with the query (the developer specified during report-design, which is under dataset->commandtext in RDL file).

To clarify, do not want to use parameters. The idea is, the report design need not be modified each time any filters to be added or removed. The user can simply, add/remove the fields and corresponding sql statements for each field from the db (e.g. QueryWizard table).

The functionality to attach the where clause (from web page) to query (specified in reprot-design) is ready. I need to know, is there anyway I can retrieve the query from the RDL programmatically and set it back before the report is rendered (generated).

I heard something about QueryDefinition.Query, which returns the query, but not sure.

Anybody, any suggestion, will be a great help !!

Have you tried Report Builder? What you want sounds very close to what Report Builder is doing (RS 2005)|||

I have already explored the option of using the Report Builder. I do not want the end-user to design the report. The end-user should be able to specify the criteria (easy-steps thru Query Wizard) and view the report.

However, in case of Report Builder also, if I want to add new fields for selection criteria, the design of the report requires modification, which is what I dont want to do.

Is there any option? Cannt I edit the dataset programmatically ?

|||

Well, DataSet.Query.CommandText can be an expression.
You can try something like this: ="select * from mytable " + ReportParameters!Filter.Value

Dynamically Change FilterExpression

I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.

Something like this example...http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx

Here is some of my code..

PrivateSub ApplyFilter()

Dim _filterExpressionAsString =""

If (Not DropDownList1.SelectedIndex = 0)And (DropDownList2.SelectedIndex = 0)And (DropDownList3.SelectedIndex = 0)Then

_filterExpression ="CategoryName = '{0}'"

EndIf

Me.SqlDataSource1.FilterExpression = _filterExpression

EndSub

ProtectedSub DropDownList1_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles DropDownList1.SelectedIndexChanged

ApplyFilter()

EndSub

But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.

Hi nfalk,

a) :It seems to me that you have specified an filterexpression to use parameter while didn't add any values to the FilterParameters collection. As to how to use filter expression, see the code example below:

msdn:

 <asp:DropDownList id="DropDownList1" runat="server" AutoPostBack="True"> <asp:ListItem Selected>Sales Representative</asp:ListItem> <asp:ListItem>Sales Manager</asp:ListItem> <asp:ListItem>Vice President, Sales</asp:ListItem> </asp:DropDownList></p> <asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" FilterExpression="Title='{0}'"> <FilterParameters> <asp:ControlParameter Name="Title" ControlId="DropDownList1" PropertyName="SelectedValue"/> </FilterParameters> </asp:SqlDataSource>

b): The filter expression only take affect when datasource.select() method is called. Thus only modifying the filter expression won't change your gridview content. You need to call gridview.databind() again

in your DropDownList1_SelectedIndexChanged method.

Hope my suggestion helps

|||

Hello Bo Chen,

a) I did have the filter parameters set I just forgot to mention it in the posting.

b) I was forgetting to call the DataBind() in the event handler. After I added it everything works fine.

Thanks for the reply.

|||

I am trying to do something similar but can't get it to work. I have the following code running every time the web page reloads:

ReportDataSource.FilterExpression ="SurveyID=" & SurveyList.SelectedValue.ToString

SurveyReportViewer.DataBind()

I have a ReportViewer (SurveyReportViewer) based on an ObjectDataSource (ReportDataSource). Every time the page reloads it checks the SurveyList drop-down to get the new ID. This works fine the first time the page is loaded, but the filter doesn't change after that. I know it's not supposed to work until the Select is called again, but I thought that's what the DataBind method did (I had tried using DataBind on the ObjectDataSource as well as the ReportViewer but it didn't make any difference). Since it does work the first time through, I'm assuming most everything is set up correctly and that I'm just missing something that's required to refresh the data source. Any suggestions?

|||

What I did was create a ApplyFilter() method with my logic to change the FilterExpression. And in the DropDownList SelectedIndexChanged event call the applyfilter and rebind to data source...

ProtectedSub DropDownList2_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles DropDownList2.SelectedIndexChanged

ApplyFilter()

Me.SqlDataSource1.DataBind()

EndSub

Also I don't know if it makes a difference but I used Filter parameters...

<asp:ControlParameterControlID="DropDownList2"DefaultValue="*"Name="Filter"PropertyName="SelectedValue"/>

In the ApplyFilter()...

Me.SqlDataSource1.FilterExpression="CategoryName = '{0}' "

Hope this helps

|||

Well I gave it a try but had the same results. I may have to try using a SqlDataSource instead of ObjectDataSource and see if there's a difference there. This is probably what I get for using wizards instead of coding it myself...