Null Filters?

Hi all,
My report consists of a matrix and displays results from a stored
procedure.
One of the groups is called country. The matrix displays countries
with all the products in that country.
By default, all the countries are shown, but I want to be able to
filter by country from a parameter.
I created a parameter and set it to allow nulls as I also want to be
able to display all countries.
Then I set the country field to be equal to the parameter. The filter
works if i suppoly a value for the filter (from parameter), but if i
leave the parameter (for the filter) blank, then it does not return
any rows at all. If i leave the parameter blank, then the report
should ignore the filter. Is this possible?
Thanks in advance,
KSFrom the description I assume your current filter expression is similar to
this:
FilterExpr: =Fields!Country.Value
Operator: =FilterValue: =Parameters!Country.Value
Change it to the following:
FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
Fields!Country.Value=Parameters!Country.Value)
Operator: =FilterValue: =true
This assumes that the user sets the parameter to Null. Consequently, the
filter expression will then evaluate to true for all countries and they will
be shown in the report.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"saleek" <saleem75k@.hotmail.com> wrote in message
news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> Hi all,
> My report consists of a matrix and displays results from a stored
> procedure.
> One of the groups is called country. The matrix displays countries
> with all the products in that country.
> By default, all the countries are shown, but I want to be able to
> filter by country from a parameter.
> I created a parameter and set it to allow nulls as I also want to be
> able to display all countries.
> Then I set the country field to be equal to the parameter. The filter
> works if i suppoly a value for the filter (from parameter), but if i
> leave the parameter (for the filter) blank, then it does not return
> any rows at all. If i leave the parameter blank, then the report
> should ignore the filter. Is this possible?
> Thanks in advance,
>
> KS|||Hi Robert,
Thanks for getting back to me on this. I have tried the expression you
showed, but it is giving run-time error. The error is below:
"An error has ocurred during the report processing.
The processing of filter expression for the matrix 'Stats' cannot be
performed. The comparison failed. Please check the data type reutrned by
filter expression."
I assume that the "true" part of the expression is cusing problems'
thanks,
KS
"Robert Bruckner [MSFT]" wrote:
> From the description I assume your current filter expression is similar to
> this:
> FilterExpr: =Fields!Country.Value
> Operator: => FilterValue: =Parameters!Country.Value
> Change it to the following:
> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> Fields!Country.Value=Parameters!Country.Value)
> Operator: => FilterValue: =true
> This assumes that the user sets the parameter to Null. Consequently, the
> filter expression will then evaluate to true for all countries and they will
> be shown in the report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saleek" <saleem75k@.hotmail.com> wrote in message
> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> > Hi all,
> >
> > My report consists of a matrix and displays results from a stored
> > procedure.
> >
> > One of the groups is called country. The matrix displays countries
> > with all the products in that country.
> >
> > By default, all the countries are shown, but I want to be able to
> > filter by country from a parameter.
> >
> > I created a parameter and set it to allow nulls as I also want to be
> > able to display all countries.
> >
> > Then I set the country field to be equal to the parameter. The filter
> > works if i suppoly a value for the filter (from parameter), but if i
> > leave the parameter (for the filter) blank, then it does not return
> > any rows at all. If i leave the parameter blank, then the report
> > should ignore the filter. Is this possible?
> >
> > Thanks in advance,
> >
> >
> > KS
>
>|||Sorry Robert,
It did actually work, I had left out the equals sign before the "true" for
the value of the filter. I have noticed that it is case sensitive tho.
thanks for your help,
KS
"Robert Bruckner [MSFT]" wrote:
> From the description I assume your current filter expression is similar to
> this:
> FilterExpr: =Fields!Country.Value
> Operator: => FilterValue: =Parameters!Country.Value
> Change it to the following:
> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> Fields!Country.Value=Parameters!Country.Value)
> Operator: => FilterValue: =true
> This assumes that the user sets the parameter to Null. Consequently, the
> filter expression will then evaluate to true for all countries and they will
> be shown in the report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saleek" <saleem75k@.hotmail.com> wrote in message
> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> > Hi all,
> >
> > My report consists of a matrix and displays results from a stored
> > procedure.
> >
> > One of the groups is called country. The matrix displays countries
> > with all the products in that country.
> >
> > By default, all the countries are shown, but I want to be able to
> > filter by country from a parameter.
> >
> > I created a parameter and set it to allow nulls as I also want to be
> > able to display all countries.
> >
> > Then I set the country field to be equal to the parameter. The filter
> > works if i suppoly a value for the filter (from parameter), but if i
> > leave the parameter (for the filter) blank, then it does not return
> > any rows at all. If i leave the parameter blank, then the report
> > should ignore the filter. Is this possible?
> >
> > Thanks in advance,
> >
> >
> > KS
>
>|||Another question if you dont mind!
I am working with the ReportViewer component and can pass parameter from
asp.net page to it, but it only allows me to bind once - if I click the
button to bind again with different parameter it does not work. It just shows
the component default "To render a report, enter the ServerUrl and
ReportPath."
Below is some code I use:
ReportViewer1.ServerUrl = "http://www.doneitfor.com/reportserver"
ReportViewer1.ReportPath = "/testing/Stats"
ReportViewer1.SetQueryParameter("theTitle", prm1)
ReportViewer1.DataBind()
The above code works once only, if I click to run the above code again, the
component does not reload or rebind it sits there as if the code above hasnt
even run.
Any help appreciated.
regards,
KS
"Robert Bruckner [MSFT]" wrote:
> From the description I assume your current filter expression is similar to
> this:
> FilterExpr: =Fields!Country.Value
> Operator: => FilterValue: =Parameters!Country.Value
> Change it to the following:
> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> Fields!Country.Value=Parameters!Country.Value)
> Operator: => FilterValue: =true
> This assumes that the user sets the parameter to Null. Consequently, the
> filter expression will then evaluate to true for all countries and they will
> be shown in the report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saleek" <saleem75k@.hotmail.com> wrote in message
> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> > Hi all,
> >
> > My report consists of a matrix and displays results from a stored
> > procedure.
> >
> > One of the groups is called country. The matrix displays countries
> > with all the products in that country.
> >
> > By default, all the countries are shown, but I want to be able to
> > filter by country from a parameter.
> >
> > I created a parameter and set it to allow nulls as I also want to be
> > able to display all countries.
> >
> > Then I set the country field to be equal to the parameter. The filter
> > works if i suppoly a value for the filter (from parameter), but if i
> > leave the parameter (for the filter) blank, then it does not return
> > any rows at all. If i leave the parameter blank, then the report
> > should ignore the filter. Is this possible?
> >
> > Thanks in advance,
> >
> >
> > KS
>
>|||Where does the code live? In a button click event handler or page load
or elsewhere?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 20 Oct 2004 02:43:03 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Another question if you dont mind!
>I am working with the ReportViewer component and can pass parameter from
>asp.net page to it, but it only allows me to bind once - if I click the
>button to bind again with different parameter it does not work. It just shows
>the component default "To render a report, enter the ServerUrl and
>ReportPath."
>Below is some code I use:
> ReportViewer1.ServerUrl = "http://www.doneitfor.com/reportserver"
> ReportViewer1.ReportPath = "/testing/Stats"
> ReportViewer1.SetQueryParameter("theTitle", prm1)
> ReportViewer1.DataBind()
>The above code works once only, if I click to run the above code again, the
>component does not reload or rebind it sits there as if the code above hasnt
>even run.
>Any help appreciated.
>regards,
>KS
>"Robert Bruckner [MSFT]" wrote:
>> From the description I assume your current filter expression is similar to
>> this:
>> FilterExpr: =Fields!Country.Value
>> Operator: =>> FilterValue: =Parameters!Country.Value
>> Change it to the following:
>> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
>> Fields!Country.Value=Parameters!Country.Value)
>> Operator: =>> FilterValue: =true
>> This assumes that the user sets the parameter to Null. Consequently, the
>> filter expression will then evaluate to true for all countries and they will
>> be shown in the report.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>> "saleek" <saleem75k@.hotmail.com> wrote in message
>> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
>> > Hi all,
>> >
>> > My report consists of a matrix and displays results from a stored
>> > procedure.
>> >
>> > One of the groups is called country. The matrix displays countries
>> > with all the products in that country.
>> >
>> > By default, all the countries are shown, but I want to be able to
>> > filter by country from a parameter.
>> >
>> > I created a parameter and set it to allow nulls as I also want to be
>> > able to display all countries.
>> >
>> > Then I set the country field to be equal to the parameter. The filter
>> > works if i suppoly a value for the filter (from parameter), but if i
>> > leave the parameter (for the filter) blank, then it does not return
>> > any rows at all. If i leave the parameter blank, then the report
>> > should ignore the filter. Is this possible?
>> >
>> > Thanks in advance,
>> >
>> >
>> > KS
>>|||Hi Scott,
Yes the code lives in the button click event handler.
thx,
KS
"Scott Allen" wrote:
> Where does the code live? In a button click event handler or page load
> or elsewhere?
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Wed, 20 Oct 2004 02:43:03 -0700, saleek
> <saleek@.discussions.microsoft.com> wrote:
> >Another question if you dont mind!
> >
> >I am working with the ReportViewer component and can pass parameter from
> >asp.net page to it, but it only allows me to bind once - if I click the
> >button to bind again with different parameter it does not work. It just shows
> >the component default "To render a report, enter the ServerUrl and
> >ReportPath."
> >
> >Below is some code I use:
> >
> > ReportViewer1.ServerUrl = "http://www.doneitfor.com/reportserver"
> > ReportViewer1.ReportPath = "/testing/Stats"
> > ReportViewer1.SetQueryParameter("theTitle", prm1)
> > ReportViewer1.DataBind()
> >
> >The above code works once only, if I click to run the above code again, the
> >component does not reload or rebind it sits there as if the code above hasnt
> >even run.
> >
> >Any help appreciated.
> >
> >regards,
> >
> >KS
> >
> >"Robert Bruckner [MSFT]" wrote:
> >
> >> From the description I assume your current filter expression is similar to
> >> this:
> >>
> >> FilterExpr: =Fields!Country.Value
> >> Operator: => >> FilterValue: =Parameters!Country.Value
> >>
> >> Change it to the following:
> >>
> >> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> >> Fields!Country.Value=Parameters!Country.Value)
> >> Operator: => >> FilterValue: =true
> >>
> >> This assumes that the user sets the parameter to Null. Consequently, the
> >> filter expression will then evaluate to true for all countries and they will
> >> be shown in the report.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
> >> "saleek" <saleem75k@.hotmail.com> wrote in message
> >> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> >> > Hi all,
> >> >
> >> > My report consists of a matrix and displays results from a stored
> >> > procedure.
> >> >
> >> > One of the groups is called country. The matrix displays countries
> >> > with all the products in that country.
> >> >
> >> > By default, all the countries are shown, but I want to be able to
> >> > filter by country from a parameter.
> >> >
> >> > I created a parameter and set it to allow nulls as I also want to be
> >> > able to display all countries.
> >> >
> >> > Then I set the country field to be equal to the parameter. The filter
> >> > works if i suppoly a value for the filter (from parameter), but if i
> >> > leave the parameter (for the filter) blank, then it does not return
> >> > any rows at all. If i leave the parameter blank, then the report
> >> > should ignore the filter. Is this possible?
> >> >
> >> > Thanks in advance,
> >> >
> >> >
> >> > KS
> >>
> >>
> >>
>|||Hi saleek:
I'm not sure what could be going wrong, but I would remove the call to
DataBind as this isn't needed with the ReportViewer component.
Here is a simple form that has been working for me. I can enter a new
parameter for a reports (say /SampleReports/Sales Order Detail) and
the report viewer rerenders the report.
<form id="Form1" method="post" runat="server">
<cc1:ReportViewer id="ReportViewer1"
runat="server" Width="600px" Height="400px"/>
<asp:Button id="Button1" runat="server" Text="Button"/>
<asp:TextBox id="TextBox1" runat="server"/>
<asp:TextBox id="TextBox2" runat="server"/>
</form>
And in code behind:
private void Button1_Click(object sender, System.EventArgs e)
{
ReportViewer1.ServerUrl = "http://localhost/reportserver";
ReportViewer1.ReportPath = TextBox1.Text;
if(TextBox2.Text.Length > 0)
{
ReportViewer1.SetQueryParameter(
"SalesOrderNumber",
TextBox2.Text
);
}
}
HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Thu, 21 Oct 2004 01:49:03 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Hi Scott,
>Yes the code lives in the button click event handler.
>thx,
>KS
>"Scott Allen" wrote:
>> Where does the code live? In a button click event handler or page load
>> or elsewhere?
>> --|||Hi Scott,
Having looked at my code I realised that I had some incorrect logic going on!
What I would like to know is this...
When the page is posted back by another button control, the reportviewer
seems to render to nothing and displays the caption about server url and path
etc. I would like the reportviewer to maintain its state when the page is
posted back by another button.
What I am trying to do is:
-User has a drop down list of options
-User selects an option then clicks an "ADD" button.
-the "ADD" button runs a sub to add the value of the drop down list to a
listbox
-Next to the listbox control is a "REMOVE" button
The idea is that the user can add and remove values as he/she desires into
the listbox and when they are ready they can click a "SUMMARY" button to run
the report.
What is happening now is that when i initially run the report it is fine.
When I add or remove and item from the listbox control, the viewer seems to
reset.
How can I stop this from happening?
Thanks for your time.
regards,
KS
"Scott Allen" wrote:
> Hi saleek:
> I'm not sure what could be going wrong, but I would remove the call to
> DataBind as this isn't needed with the ReportViewer component.
> Here is a simple form that has been working for me. I can enter a new
> parameter for a reports (say /SampleReports/Sales Order Detail) and
> the report viewer rerenders the report.
> <form id="Form1" method="post" runat="server">
> <cc1:ReportViewer id="ReportViewer1"
> runat="server" Width="600px" Height="400px"/>
> <asp:Button id="Button1" runat="server" Text="Button"/>
> <asp:TextBox id="TextBox1" runat="server"/>
> <asp:TextBox id="TextBox2" runat="server"/>
> </form>
> And in code behind:
> private void Button1_Click(object sender, System.EventArgs e)
> {
> ReportViewer1.ServerUrl = "http://localhost/reportserver";
> ReportViewer1.ReportPath = TextBox1.Text;
> if(TextBox2.Text.Length > 0)
> {
> ReportViewer1.SetQueryParameter(
> "SalesOrderNumber",
> TextBox2.Text
> );
> }
> }
> HTH,
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Thu, 21 Oct 2004 01:49:03 -0700, saleek
> <saleek@.discussions.microsoft.com> wrote:
> >Hi Scott,
> >
> >Yes the code lives in the button click event handler.
> >
> >thx,
> >
> >KS
> >
> >"Scott Allen" wrote:
> >
> >> Where does the code live? In a button click event handler or page load
> >> or elsewhere?
> >>
> >> --
>|||Hi saleek:
Do you have ViewState enabled? Is there any logic in the Page Load
event handler that is touching the reportviewer on every postback?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 22 Oct 2004 02:01:03 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Hi Scott,
>Having looked at my code I realised that I had some incorrect logic going on!
>What I would like to know is this...
>When the page is posted back by another button control, the reportviewer
>seems to render to nothing and displays the caption about server url and path
>etc. I would like the reportviewer to maintain its state when the page is
>posted back by another button.
>What I am trying to do is:
>-User has a drop down list of options
>-User selects an option then clicks an "ADD" button.
>-the "ADD" button runs a sub to add the value of the drop down list to a
>listbox
>-Next to the listbox control is a "REMOVE" button
>The idea is that the user can add and remove values as he/she desires into
>the listbox and when they are ready they can click a "SUMMARY" button to run
>the report.
>What is happening now is that when i initially run the report it is fine.
>When I add or remove and item from the listbox control, the viewer seems to
>reset.
>How can I stop this from happening?
>Thanks for your time.
>regards,
>KS
>"Scott Allen" wrote:
>> Hi saleek:
>> I'm not sure what could be going wrong, but I would remove the call to
>> DataBind as this isn't needed with the ReportViewer component.
>> Here is a simple form that has been working for me. I can enter a new
>> parameter for a reports (say /SampleReports/Sales Order Detail) and
>> the report viewer rerenders the report.
>> <form id="Form1" method="post" runat="server">
>> <cc1:ReportViewer id="ReportViewer1"
>> runat="server" Width="600px" Height="400px"/>
>> <asp:Button id="Button1" runat="server" Text="Button"/>
>> <asp:TextBox id="TextBox1" runat="server"/>
>> <asp:TextBox id="TextBox2" runat="server"/>
>> </form>
>> And in code behind:
>> private void Button1_Click(object sender, System.EventArgs e)
>> {
>> ReportViewer1.ServerUrl = "http://localhost/reportserver";
>> ReportViewer1.ReportPath = TextBox1.Text;
>> if(TextBox2.Text.Length > 0)
>> {
>> ReportViewer1.SetQueryParameter(
>> "SalesOrderNumber",
>> TextBox2.Text
>> );
>> }
>> }
>> HTH,
>> --
>> Scott
>> http://www.OdeToCode.com/blogs/scott/
>> On Thu, 21 Oct 2004 01:49:03 -0700, saleek
>> <saleek@.discussions.microsoft.com> wrote:
>> >Hi Scott,
>> >
>> >Yes the code lives in the button click event handler.
>> >
>> >thx,
>> >
>> >KS
>> >
>> >"Scott Allen" wrote:
>> >
>> >> Where does the code live? In a button click event handler or page load
>> >> or elsewhere?
>> >>
>> >> --
>>|||Hi Scott,
Viewstate IS enabled on the reportviewer component and my page_load event is:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
ReportViewer1.Visible = False
'LastLoggedIn()
PassDLLsToBind()
End If
End Sub
Thanks,
KS
"Scott Allen" wrote:
> Hi saleek:
> Do you have ViewState enabled? Is there any logic in the Page Load
> event handler that is touching the reportviewer on every postback?
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
>
> On Fri, 22 Oct 2004 02:01:03 -0700, saleek
> <saleek@.discussions.microsoft.com> wrote:
> >Hi Scott,
> >
> >Having looked at my code I realised that I had some incorrect logic going on!
> >
> >What I would like to know is this...
> >
> >When the page is posted back by another button control, the reportviewer
> >seems to render to nothing and displays the caption about server url and path
> >etc. I would like the reportviewer to maintain its state when the page is
> >posted back by another button.
> >
> >What I am trying to do is:
> >
> >-User has a drop down list of options
> >-User selects an option then clicks an "ADD" button.
> >-the "ADD" button runs a sub to add the value of the drop down list to a
> >listbox
> >-Next to the listbox control is a "REMOVE" button
> >
> >The idea is that the user can add and remove values as he/she desires into
> >the listbox and when they are ready they can click a "SUMMARY" button to run
> >the report.
> >
> >What is happening now is that when i initially run the report it is fine.
> >When I add or remove and item from the listbox control, the viewer seems to
> >reset.
> >
> >How can I stop this from happening?
> >
> >Thanks for your time.
> >
> >regards,
> >
> >KS
> >
> >"Scott Allen" wrote:
> >
> >> Hi saleek:
> >>
> >> I'm not sure what could be going wrong, but I would remove the call to
> >> DataBind as this isn't needed with the ReportViewer component.
> >>
> >> Here is a simple form that has been working for me. I can enter a new
> >> parameter for a reports (say /SampleReports/Sales Order Detail) and
> >> the report viewer rerenders the report.
> >>
> >> <form id="Form1" method="post" runat="server">
> >> <cc1:ReportViewer id="ReportViewer1"
> >> runat="server" Width="600px" Height="400px"/>
> >> <asp:Button id="Button1" runat="server" Text="Button"/>
> >> <asp:TextBox id="TextBox1" runat="server"/>
> >> <asp:TextBox id="TextBox2" runat="server"/>
> >> </form>
> >>
> >> And in code behind:
> >>
> >> private void Button1_Click(object sender, System.EventArgs e)
> >> {
> >> ReportViewer1.ServerUrl = "http://localhost/reportserver";
> >> ReportViewer1.ReportPath = TextBox1.Text;
> >>
> >> if(TextBox2.Text.Length > 0)
> >> {
> >> ReportViewer1.SetQueryParameter(
> >> "SalesOrderNumber",
> >> TextBox2.Text
> >> );
> >> }
> >> }
> >>
> >> HTH,
> >>
> >> --
> >> Scott
> >> http://www.OdeToCode.com/blogs/scott/
> >>
> >> On Thu, 21 Oct 2004 01:49:03 -0700, saleek
> >> <saleek@.discussions.microsoft.com> wrote:
> >>
> >> >Hi Scott,
> >> >
> >> >Yes the code lives in the button click event handler.
> >> >
> >> >thx,
> >> >
> >> >KS
> >> >
> >> >"Scott Allen" wrote:
> >> >
> >> >> Where does the code live? In a button click event handler or page load
> >> >> or elsewhere?
> >> >>
> >> >> --
> >>
> >>
>|||Hi saleek:
The Page_Load event looks fine. I assume during other event handlers
you'll set the visibility to true and set the server and item path,
right?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 27 Oct 2004 06:21:05 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Hi Scott,
>Viewstate IS enabled on the reportviewer component and my page_load event is:
>Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles MyBase.Load
> 'Put user code to initialize the page here
> If Not Page.IsPostBack Then
> ReportViewer1.Visible = False
> 'LastLoggedIn()
> PassDLLsToBind()
> End If
> End Sub
>Thanks,
>KS
>