making Report Parameters optional

I′m currently working on a report (SSRS 2005) with parameters which I want to make optional. In other words: when I open the report in the preview or IE, I′m always forced to enter values for every single parameter. But I want to have them optional so I can decide whether to choose values (as the option "Multi-value" is enabled) or leave them blank. (ignore in selection result)

is this possible? - I hope;-)

thanks in advance

pamike

The way to make entering parameter values optional is to give them default values. You can set the default value to give you all results and thereby achieve your result.|||

hi thanks for the answer csi_hugh, but i still don′t know:"non queried" or "from query" (or NULL?) and which value (expression) has to be set as default value?

thx

pamike

|||

The answer depends on your data. If the parameter is used in a Where condition of your Dataset, you have to consider what value will return all records based on the Where condition. In the case of Multi-select parameters, if you are using SP1 you can add the value "All" to the top of the choice lists, and then add Where conditions to your Dataset that if @.param = 'All' or Field = @.param. That will give you all records if you pick 'All'.

Hope that helps.

|||

I am also seeking an answer for this question. I guess we can do it with default values and using stored procedure to make the select.

Certainly we wish RS can provide this feature. it is fairly a basic feature.

|||

What we did to achieve this is to create a parameter with a (valid) default value.

Say, the parameter @.filter of type 'integer' is allowed to have a 'null' value and has default value 'null'.

We use this same default value in the query, like (very simple example)

SELECT * FROM Employee WHERE active=1 AND company_id=CASE WHEN @.filter IS NULL THEN company_id ELSE @.filter END

This way, if @.filter has a value other than 'null', we limit our query result to Employees with a company_id equal to @.filter. If @.filter has no value (it gets it's default value of 'null') then the query result is not limited based on company_id.

It's not pretty, but it works without creating stored procedures or functions.

making Report Parameters optional

I′m currently working on a report (SSRS 2005) with parameters which I want to make optional. In other words: when I open the report in the preview or IE, I′m always forced to enter values for every single parameter. But I want to have them optional so I can decide whether to choose values (as the option "Multi-value" is enabled) or leave them blank. (ignore in selection result)

is this possible? - I hope;-)

thanks in advance

pamike

The way to make entering parameter values optional is to give them default values. You can set the default value to give you all results and thereby achieve your result.|||

hi thanks for the answer csi_hugh, but i still don′t know:"non queried" or "from query" (or NULL?) and which value (expression) has to be set as default value?

thx

pamike

|||

The answer depends on your data. If the parameter is used in a Where condition of your Dataset, you have to consider what value will return all records based on the Where condition. In the case of Multi-select parameters, if you are using SP1 you can add the value "All" to the top of the choice lists, and then add Where conditions to your Dataset that if @.param = 'All' or Field = @.param. That will give you all records if you pick 'All'.

Hope that helps.

|||

I am also seeking an answer for this question. I guess we can do it with default values and using stored procedure to make the select.

Certainly we wish RS can provide this feature. it is fairly a basic feature.

|||

What we did to achieve this is to create a parameter with a (valid) default value.

Say, the parameter @.filter of type 'integer' is allowed to have a 'null' value and has default value 'null'.

We use this same default value in the query, like (very simple example)

SELECT * FROM Employee WHERE active=1 AND company_id=CASE WHEN @.filter IS NULL THEN company_id ELSE @.filter END

This way, if @.filter has a value other than 'null', we limit our query result to Employees with a company_id equal to @.filter. If @.filter has no value (it gets it's default value of 'null') then the query result is not limited based on company_id.

It's not pretty, but it works without creating stored procedures or functions.

Making groups visible using Parameters

Hi everyone,

I am new to reporting services and really need some help..
Im trying to simply my requirement using this scenario: Lets say

I want to display Sales for the current year (i.e from the start of the year january to the month we are currently at). The next requirement is to group these sales, by Area , Region , Zone...

Top leve l = Zone , then Region then Area.

I need the user to be able to control this using a parameter (i.e User can select "View Area Level" or "View Region Level" or "View Zone level")

How can this best be achieved ?

My idea was using row groupings and controling visibility using the parameter.. The problem is.. If i want to view ONLY the area level and not see anything above, can I turn visibility off of the Zone and Region and still see the inner group (area)

Area Level (default view)

Jan Feb
Area 1 100 100
Area 2 200 200

Region Level Selected

Reagion 1
Area 1 100 100
Region 2
Area 2 200 200

Zone Level Select

Zone 1
Region 1
Area 1 100 100
Region 2
Area 2 200 200
Any help would REALLY be appreciated ..

Kind Regards,
Neil
http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||

Hello Neil,

I can think of a couple ways to do this, one way is having 2 boolean parameters. From the way you show in your example, it looks like the Area level will always show.

First, you'll need to create the 2 parameters as boolean type and set your defaults. For example: ShowRegion - False, ShowZone - False.

Click on the row handle for your Zone level and go to the Hidden setting in the Properties window.

Change the Hidden setting to an expression and enter this into it:

=Iif(Parameters!ShowZone.Value = True, False, True)

Click on the row handle for your Region level and change the Hidden setting to this expression:

=Iif(Parameters!ShowRegion.Value = True OR Parameters!ShowZone.Value = True, False, True)

Hope this helps.

Jarret

|||Hey Jarret

Thanks so much for the reply, I have found away to do this, well In theory the idea works..
With regards to your idea.. I was thinking that If the parent group is made invisible so are the children groups (invisible).. well.. I dont know if that is the case.. If you know the answer please let me know..

Thanks for taking the time to help out.. really appreciate it

Kind Regards,
Neil
|||

Hello,

If you hide a higher group (parent), the lower groups (children) will continue to show until explicitly hidden. They are independent of each other.

Jarret

|||Great !!

Thanks!!

Make Page in Report Invisible

Hi all,
I have a report which lists all transactions in a system. This report has
the parameters called From Date and To Date. Each transaction has a amount
value.
What I need is... if the transactions between the selected dates amounts sum
up to 0, then dont generate a page in the report..
What I receive at the moment is a whole heap of pages with 0 totals..
Any help would be greatly appreciated.
I have tried the hidden attribute... but as I am using the page footer this
does not get hidden.Can't you do this in the dataset by saying where total <> 0? You may want
to add a calculated field to the dataset then you can also apply a filter on
it.
"Clint" <ccpatriot12@.yahoo.com> wrote in message
news:uPDrQToNGHA.3832@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a report which lists all transactions in a system. This report has
> the parameters called From Date and To Date. Each transaction has a
> amount value.
> What I need is... if the transactions between the selected dates amounts
> sum up to 0, then dont generate a page in the report..
> What I receive at the moment is a whole heap of pages with 0 totals..
> Any help would be greatly appreciated.
> I have tried the hidden attribute... but as I am using the page footer
> this does not get hidden.
>|||As craig said, do it in query itself. If its not possible within query go to
the filters for the dataset and specify the condition there.
Bye
Sumit Pilankar
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:%23XC4DapNGHA.3924@.TK2MSFTNGP14.phx.gbl...
> Can't you do this in the dataset by saying where total <> 0? You may want
> to add a calculated field to the dataset then you can also apply a filter
> on it.
> "Clint" <ccpatriot12@.yahoo.com> wrote in message
> news:uPDrQToNGHA.3832@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report which lists all transactions in a system. This report
>> has the parameters called From Date and To Date. Each transaction has a
>> amount value.
>> What I need is... if the transactions between the selected dates amounts
>> sum up to 0, then dont generate a page in the report..
>> What I receive at the moment is a whole heap of pages with 0 totals..
>> Any help would be greatly appreciated.
>> I have tried the hidden attribute... but as I am using the page footer
>> this does not get hidden.
>|||I cant do it in the query as that would involve grouping the dataset... as
the total is a summation of all the transactions. I have many case
statements as well..
I will look at the calculated field option.
Thanks
"Sumit Pilankar" <sumit.pilankar@.gmail.com> wrote in message
news:uJTxH0pNGHA.208@.tk2msftngp13.phx.gbl...
> As craig said, do it in query itself. If its not possible within query go
> to the filters for the dataset and specify the condition there.
> Bye
> Sumit Pilankar
>
> "Craig" <craigm_richardson@.hotmail.com> wrote in message
> news:%23XC4DapNGHA.3924@.TK2MSFTNGP14.phx.gbl...
>> Can't you do this in the dataset by saying where total <> 0? You may
>> want to add a calculated field to the dataset then you can also apply a
>> filter on it.
>> "Clint" <ccpatriot12@.yahoo.com> wrote in message
>> news:uPDrQToNGHA.3832@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report which lists all transactions in a system. This report
>> has the parameters called From Date and To Date. Each transaction has a
>> amount value.
>> What I need is... if the transactions between the selected dates amounts
>> sum up to 0, then dont generate a page in the report..
>> What I receive at the moment is a whole heap of pages with 0 totals..
>> Any help would be greatly appreciated.
>> I have tried the hidden attribute... but as I am using the page footer
>> this does not get hidden.
>>
>

make manual snapshots without default parameters set - how?

It seems like it is only possible to make a snapshot if I set default
values to all parameters. What I want to do is:
- The user generates a report manually after entering some parameters
- The user can decide to save this report as a snapshot by pressing on
the "New Snapshot" button
Is it right that there seems to be no way of doing this? (I'm talking
about the standard Web UI, no self-programmed web service or anything)
Thanks for any help
UrsHi,
check the linked report feature.
Elisabeth
"Urs Eichmann" wrote:
> It seems like it is only possible to make a snapshot if I set default
> values to all parameters. What I want to do is:
> - The user generates a report manually after entering some parameters
> - The user can decide to save this report as a snapshot by pressing on
> the "New Snapshot" button
> Is it right that there seems to be no way of doing this? (I'm talking
> about the standard Web UI, no self-programmed web service or anything)
> Thanks for any help
> Urs
>
>|||That's what I already do, but if I don't set all the parameters to a
default value in the linked report, I cannot make a snapshot.
Urs
Elisabeth wrote:
> Hi,
> check the linked report feature.
> Elisabeth
> "Urs Eichmann" wrote:
>
>>It seems like it is only possible to make a snapshot if I set default
>>values to all parameters. What I want to do is:
>>- The user generates a report manually after entering some parameters
>>- The user can decide to save this report as a snapshot by pressing on
>>the "New Snapshot" button
>>Is it right that there seems to be no way of doing this? (I'm talking
>>about the standard Web UI, no self-programmed web service or anything)
>>Thanks for any help
>>Urs
>>