Showing posts with label filter. Show all posts
Showing posts with label filter. Show all posts

Friday, March 9, 2012

Dynamically change report grouping

Currently our users can filter data but they cannot manually group data
differently without us having to create them a new report with their
required group options.
Is there a way perhaps to hard code maybe 1 or 2 group levels in a reports
and then turn them off or turn them on dynamically depending on how the user
wanted to view the results.
Any help is appreciated.We use a 'Group By' parameter that the stored proc uses to do the
grouping. The results come back in the same format each time of
course, they're just grouped differently. This can sometimes lead to
some icky VB.NET conditionals inlined into the RDL, but in general it
has worked very well.|||Alternatively, group on an expression that is based on the value of a
parameter.
In our case, the client's requirements needed 6 reports but this
technique allowed us to give them one.
The stored proc must return a result set at a grouping level just above
the two alternative groupings.
BrianK
www.bolign.com

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...

Wednesday, March 7, 2012

Dynamically adding Select Parameters (Filter)

how do i add parameters like this dynamically? do i need to change the select command? to add the @.ID part?

Although this is for delete you get the idea. This is using a sqldatasource with stored procedures.

protected void SqlPages_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{

// Wipe out the auto params and replace with the correct one.
DbParameterCollection CmdParams = e.Command.Parameters;
DbParameter oParam = null;
foreach (DbParameter cp in CmdParams)
{
//Trace.Warn(cp.ParameterName, Convert.ToString(cp.Value));
if (cp.ParameterName == "@.PageID") {
oParam = cp;
}

}
CmdParams.Clear();
CmdParams.Add(oParam);

//e.Cancel = true;
}

HTH,

|||

You can see the select i am using has a filter as well. Here is the aspx code:

<asp:SqlDataSource ID="SqlPages" runat="server"
ConnectionString="<%$ ConnectionStrings:MonkeyCon %>"
SelectCommand="Pages_SelectPagesBySite"
SelectCommandType="StoredProcedure"
...

<SelectParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:ControlParameter ControlID="ddlSiteFilter" Name="SiteID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

can i like have an option to display back all? unfiltered? like first i filter by Item Name = "Something", then i want it to be <All> now, how do i do that? something like Select * From SomeTable. no more where...

|||

You could add a branch in your SPROC where if the ID = 0 you return all. And just add an option of <Show All> with a value of 0 to your dropdownlist ..