Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.

Filters

A common question is on reports with parameters - is there some way to also
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>

filters

does anyone know if it is possible to add a filter to a report that is
passed as a user-defined parameter? i know i can do this by modifying the
sql that generates the report, but i'd like to do this inside of the report
so as to not hit the database each time the user selects a different filter.On Sep 24, 2:18 pm, "JTL" <j...@.clickstreamtech.com> wrote:
> does anyone know if it is possible to add a filter to a report that is
> passed as a user-defined parameter? i know i can do this by modifying the
> sql that generates the report, but i'd like to do this inside of the report
> so as to not hit the database each time the user selects a different filter.
If I'm understanding you correctly, you should be able to pass a
report parameter value to a table/matrix control via: right-clicking
the table/matrix control -> select Properties -> select the Filters
tab -> below Expression, enter an expression similar to the following:
=Parameters!ParamName.Value
And then compare it to the Value accordingly.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Filtering the parameter content

Hello,

I have built a report using a Cube (and not a relational database).

I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.

Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.

Thank you in advance !

Alexis

If you always want it to start from 2006( not change to 2007 to 2090 when it is 2008) it′s easy in MDX:

SELECT {[Time].[Year].&[2006] : NULL } on rows, bla bla bla.

This will make it take every member from the time dimension from 2006 up til the end, in your case 2090.|||

Thank you, it works perfectly !

It would be interesting to make dynamic date range according to the actual year but.... next time !

Filtering Table name with Parameter

<br><br>I obtain table names from a database and pass them to a dropdownlist. Based on user selection, I want to pass each table name to a query.Here is an extract from my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="select * from @.dDTable"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="dDTable" PropertyName="SelectedValue" DefaultValue="product" Direction="InputOutput" Size="15" Type="String" /> </SelectParameters> </asp:SqlDataSource>I keep getting this error: Must declare the table variable "@.dDTable".Please does anyone knows the best way to go about this?

Hi,

thats not working. Dynamic table name are allowed in here. You could use a stored procedure instead which then uses dynamic sql to execute the query that was first concatenated (with the table name).

But you should be aware of the curse of dynamic SQL :-)

http://www.sommarskog.se/dynamic_sql.html

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks.

Filtering results after execution and multivalue parameter questions

Hi. 2 questions:

1.
I'm trying to execute a report, then allow the user filter the results using a value list from the report dataset in a dropdown list. When I create a filtering parameter, i can't figure out how to make it work on results after the report is executed.

2.
How can I make a multivalue parameter default to (Select All)?

thanks!
#1: You can simulate the "user-driven filtering" by using drillthrough actions to the report itself and setting the filter parameter accordingly. In any case, it will require the report to be reprocessed because filtering data may impact many other things (such as pagination).

#2: Make the valid values list and the default value both query-based and use
the same dataset column.

-- Robert