All parameters as default

Using SSRS 2000, I want to be able to have all the results displayed as
default rather than a specific value set for default before the user clicks
on choosing a specific parameter value.
Is this possible?
Thanks for any suggestions!If what you want to do is use default parameter values and hide their
values from the user then open the Report Parameters dialogue box in
report designer and remove any values from the Prompt window. This
will cause the parameters to not display.|||Thank you for your reponse!
I actually want the user to be able to see and choose the parameters, but I
want the default to be all possible values and not one value for the cases
where they don't want to limit the results.
"toolman" wrote:
> If what you want to do is use default parameter values and hide their
> values from the user then open the Report Parameters dialogue box in
> report designer and remove any values from the Prompt window. This
> will cause the parameters to not display.
>|||I have on occasion provided an "ALL" option to my parameter and set the
default parameter to "ALL".
In SQL, the parameter list is loaded from a dataset as
SELECT <FieldName> AS OutName FROM <TableName>
UNION ALL
SELECT "All" AS OutName FROM <TableName>
In my data dataset the code becomes:
SELECT...
FROM ...
WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
"SK" wrote:
> Thank you for your reponse!
> I actually want the user to be able to see and choose the parameters, but I
> want the default to be all possible values and not one value for the cases
> where they don't want to limit the results.
> "toolman" wrote:
> > If what you want to do is use default parameter values and hide their
> > values from the user then open the Report Parameters dialogue box in
> > report designer and remove any values from the Prompt window. This
> > will cause the parameters to not display.
> >
> >|||Thank you very much William, for responding!
I think this is exactly what I was looking for!
I created a stored procedure in SQL Server and it works fine in the data
tab. When I run it the "Define Query Parameters" dialog box comes up and I
put 'All' and it works fine, giving me all the values. The problem is when I
preview it, it gives me only one value. I've put 'All' as the default under
the report parameter so I'm not sure what's wrong exactly!
"William" wrote:
> I have on occasion provided an "ALL" option to my parameter and set the
> default parameter to "ALL".
> In SQL, the parameter list is loaded from a dataset as
> SELECT <FieldName> AS OutName FROM <TableName>
> UNION ALL
> SELECT "All" AS OutName FROM <TableName>
> In my data dataset the code becomes:
> SELECT...
> FROM ...
> WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
>
> "SK" wrote:
> > Thank you for your reponse!
> >
> > I actually want the user to be able to see and choose the parameters, but I
> > want the default to be all possible values and not one value for the cases
> > where they don't want to limit the results.
> >
> > "toolman" wrote:
> >
> > > If what you want to do is use default parameter values and hide their
> > > values from the user then open the Report Parameters dialogue box in
> > > report designer and remove any values from the Prompt window. This
> > > will cause the parameters to not display.
> > >
> > >|||Have you altered your detail dataset to test the parameter for the "All'
option?
SELECT...
FROM ...
WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
AND ....
"SK" wrote:
> Thank you very much William, for responding!
> I think this is exactly what I was looking for!
> I created a stored procedure in SQL Server and it works fine in the data
> tab. When I run it the "Define Query Parameters" dialog box comes up and I
> put 'All' and it works fine, giving me all the values. The problem is when I
> preview it, it gives me only one value. I've put 'All' as the default under
> the report parameter so I'm not sure what's wrong exactly!
>
> "William" wrote:
> > I have on occasion provided an "ALL" option to my parameter and set the
> > default parameter to "ALL".
> >
> > In SQL, the parameter list is loaded from a dataset as
> >
> > SELECT <FieldName> AS OutName FROM <TableName>
> > UNION ALL
> > SELECT "All" AS OutName FROM <TableName>
> >
> > In my data dataset the code becomes:
> >
> > SELECT...
> > FROM ...
> > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >
> >
> >
> > "SK" wrote:
> >
> > > Thank you for your reponse!
> > >
> > > I actually want the user to be able to see and choose the parameters, but I
> > > want the default to be all possible values and not one value for the cases
> > > where they don't want to limit the results.
> > >
> > > "toolman" wrote:
> > >
> > > > If what you want to do is use default parameter values and hide their
> > > > values from the user then open the Report Parameters dialogue box in
> > > > report designer and remove any values from the Prompt window. This
> > > > will cause the parameters to not display.
> > > >
> > > >|||Yes, I tried it again and it gives me the correct number of records, but they
all have the same values when "All" is chosen. The data tab gives perfect
result, but as I found it often to be the case, the preview does not mirror
the result in the data tab!
"William" wrote:
> Have you altered your detail dataset to test the parameter for the "All'
> option?
> SELECT...
> FROM ...
> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> AND ....
>
> "SK" wrote:
> > Thank you very much William, for responding!
> >
> > I think this is exactly what I was looking for!
> > I created a stored procedure in SQL Server and it works fine in the data
> > tab. When I run it the "Define Query Parameters" dialog box comes up and I
> > put 'All' and it works fine, giving me all the values. The problem is when I
> > preview it, it gives me only one value. I've put 'All' as the default under
> > the report parameter so I'm not sure what's wrong exactly!
> >
> >
> >
> > "William" wrote:
> >
> > > I have on occasion provided an "ALL" option to my parameter and set the
> > > default parameter to "ALL".
> > >
> > > In SQL, the parameter list is loaded from a dataset as
> > >
> > > SELECT <FieldName> AS OutName FROM <TableName>
> > > UNION ALL
> > > SELECT "All" AS OutName FROM <TableName>
> > >
> > > In my data dataset the code becomes:
> > >
> > > SELECT...
> > > FROM ...
> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> > >
> > >
> > >
> > > "SK" wrote:
> > >
> > > > Thank you for your reponse!
> > > >
> > > > I actually want the user to be able to see and choose the parameters, but I
> > > > want the default to be all possible values and not one value for the cases
> > > > where they don't want to limit the results.
> > > >
> > > > "toolman" wrote:
> > > >
> > > > > If what you want to do is use default parameter values and hide their
> > > > > values from the user then open the Report Parameters dialogue box in
> > > > > report designer and remove any values from the Prompt window. This
> > > > > will cause the parameters to not display.
> > > > >
> > > > >|||The reason it doesn't match is that the preview tab uses cached results
unless the value of the parameters you pick change. So if no parameters or
the parameters don't change then it will use the cached value. Look where
you rdl files are stored. You will see files called reportname.rdl.data,
these files have the cached data used by the preview tab. Delete the file
and it will requery the database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SK" <SK@.discussions.microsoft.com> wrote in message
news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> Yes, I tried it again and it gives me the correct number of records, but
> they
> all have the same values when "All" is chosen. The data tab gives perfect
> result, but as I found it often to be the case, the preview does not
> mirror
> the result in the data tab!
>
> "William" wrote:
>> Have you altered your detail dataset to test the parameter for the "All'
>> option?
>> SELECT...
>> FROM ...
>> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
>> AND ....
>>
>> "SK" wrote:
>> > Thank you very much William, for responding!
>> >
>> > I think this is exactly what I was looking for!
>> > I created a stored procedure in SQL Server and it works fine in the
>> > data
>> > tab. When I run it the "Define Query Parameters" dialog box comes up
>> > and I
>> > put 'All' and it works fine, giving me all the values. The problem is
>> > when I
>> > preview it, it gives me only one value. I've put 'All' as the default
>> > under
>> > the report parameter so I'm not sure what's wrong exactly!
>> >
>> >
>> >
>> > "William" wrote:
>> >
>> > > I have on occasion provided an "ALL" option to my parameter and set
>> > > the
>> > > default parameter to "ALL".
>> > >
>> > > In SQL, the parameter list is loaded from a dataset as
>> > >
>> > > SELECT <FieldName> AS OutName FROM <TableName>
>> > > UNION ALL
>> > > SELECT "All" AS OutName FROM <TableName>
>> > >
>> > > In my data dataset the code becomes:
>> > >
>> > > SELECT...
>> > > FROM ...
>> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
>> > >
>> > >
>> > >
>> > > "SK" wrote:
>> > >
>> > > > Thank you for your reponse!
>> > > >
>> > > > I actually want the user to be able to see and choose the
>> > > > parameters, but I
>> > > > want the default to be all possible values and not one value for
>> > > > the cases
>> > > > where they don't want to limit the results.
>> > > >
>> > > > "toolman" wrote:
>> > > >
>> > > > > If what you want to do is use default parameter values and hide
>> > > > > their
>> > > > > values from the user then open the Report Parameters dialogue box
>> > > > > in
>> > > > > report designer and remove any values from the Prompt window.
>> > > > > This
>> > > > > will cause the parameters to not display.
>> > > > >
>> > > > >|||That makes sense, thank you!
But, it still didn't seem to work in this case!
If I use the below in the dataset, I get the all the parameters right, excep
the "All" and
SELECT...
FROM ...
WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
AND ....
And if I call a stored procedure in the dataset, I get all the values and
not just the parameter value that I choose.
"Bruce L-C [MVP]" wrote:
> The reason it doesn't match is that the preview tab uses cached results
> unless the value of the parameters you pick change. So if no parameters or
> the parameters don't change then it will use the cached value. Look where
> you rdl files are stored. You will see files called reportname.rdl.data,
> these files have the cached data used by the preview tab. Delete the file
> and it will requery the database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> > Yes, I tried it again and it gives me the correct number of records, but
> > they
> > all have the same values when "All" is chosen. The data tab gives perfect
> > result, but as I found it often to be the case, the preview does not
> > mirror
> > the result in the data tab!
> >
> >
> >
> > "William" wrote:
> >
> >> Have you altered your detail dataset to test the parameter for the "All'
> >> option?
> >> SELECT...
> >> FROM ...
> >> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> >> AND ....
> >>
> >>
> >> "SK" wrote:
> >>
> >> > Thank you very much William, for responding!
> >> >
> >> > I think this is exactly what I was looking for!
> >> > I created a stored procedure in SQL Server and it works fine in the
> >> > data
> >> > tab. When I run it the "Define Query Parameters" dialog box comes up
> >> > and I
> >> > put 'All' and it works fine, giving me all the values. The problem is
> >> > when I
> >> > preview it, it gives me only one value. I've put 'All' as the default
> >> > under
> >> > the report parameter so I'm not sure what's wrong exactly!
> >> >
> >> >
> >> >
> >> > "William" wrote:
> >> >
> >> > > I have on occasion provided an "ALL" option to my parameter and set
> >> > > the
> >> > > default parameter to "ALL".
> >> > >
> >> > > In SQL, the parameter list is loaded from a dataset as
> >> > >
> >> > > SELECT <FieldName> AS OutName FROM <TableName>
> >> > > UNION ALL
> >> > > SELECT "All" AS OutName FROM <TableName>
> >> > >
> >> > > In my data dataset the code becomes:
> >> > >
> >> > > SELECT...
> >> > > FROM ...
> >> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >> > >
> >> > >
> >> > >
> >> > > "SK" wrote:
> >> > >
> >> > > > Thank you for your reponse!
> >> > > >
> >> > > > I actually want the user to be able to see and choose the
> >> > > > parameters, but I
> >> > > > want the default to be all possible values and not one value for
> >> > > > the cases
> >> > > > where they don't want to limit the results.
> >> > > >
> >> > > > "toolman" wrote:
> >> > > >
> >> > > > > If what you want to do is use default parameter values and hide
> >> > > > > their
> >> > > > > values from the user then open the Report Parameters dialogue box
> >> > > > > in
> >> > > > > report designer and remove any values from the Prompt window.
> >> > > > > This
> >> > > > > will cause the parameters to not display.
> >> > > > >
> >> > > > >
>
>|||I am trying the same thing the problem is that I need to do this for
several different fields. If I select "all", all the records show. But
when I change one to something other then "All" I get zero records
shown..
Randy
--
Bruce L-C [MVP] wrote:
> The reason it doesn't match is that the preview tab uses cached results
> unless the value of the parameters you pick change. So if no parameters or
> the parameters don't change then it will use the cached value. Look where
> you rdl files are stored. You will see files called reportname.rdl.data,
> these files have the cached data used by the preview tab. Delete the file
> and it will requery the database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> > Yes, I tried it again and it gives me the correct number of records, but
> > they
> > all have the same values when "All" is chosen. The data tab gives perfect
> > result, but as I found it often to be the case, the preview does not
> > mirror
> > the result in the data tab!
> >
> >
> >
> > "William" wrote:
> >
> >> Have you altered your detail dataset to test the parameter for the "All'
> >> option?
> >> SELECT...
> >> FROM ...
> >> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> >> AND ....
> >>
> >>
> >> "SK" wrote:
> >>
> >> > Thank you very much William, for responding!
> >> >
> >> > I think this is exactly what I was looking for!
> >> > I created a stored procedure in SQL Server and it works fine in the
> >> > data
> >> > tab. When I run it the "Define Query Parameters" dialog box comes up
> >> > and I
> >> > put 'All' and it works fine, giving me all the values. The problem is
> >> > when I
> >> > preview it, it gives me only one value. I've put 'All' as the default
> >> > under
> >> > the report parameter so I'm not sure what's wrong exactly!
> >> >
> >> >
> >> >
> >> > "William" wrote:
> >> >
> >> > > I have on occasion provided an "ALL" option to my parameter and set
> >> > > the
> >> > > default parameter to "ALL".
> >> > >
> >> > > In SQL, the parameter list is loaded from a dataset as
> >> > >
> >> > > SELECT <FieldName> AS OutName FROM <TableName>
> >> > > UNION ALL
> >> > > SELECT "All" AS OutName FROM <TableName>
> >> > >
> >> > > In my data dataset the code becomes:
> >> > >
> >> > > SELECT...
> >> > > FROM ...
> >> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >> > >
> >> > >
> >> > >
> >> > > "SK" wrote:
> >> > >
> >> > > > Thank you for your reponse!
> >> > > >
> >> > > > I actually want the user to be able to see and choose the
> >> > > > parameters, but I
> >> > > > want the default to be all possible values and not one value for
> >> > > > the cases
> >> > > > where they don't want to limit the results.
> >> > > >
> >> > > > "toolman" wrote:
> >> > > >
> >> > > > > If what you want to do is use default parameter values and hide
> >> > > > > their
> >> > > > > values from the user then open the Report Parameters dialogue box
> >> > > > > in
> >> > > > > report designer and remove any values from the Prompt window.
> >> > > > > This
> >> > > > > will cause the parameters to not display.
> >> > > > >
> >> > > > >