"Select All" option visible in Designer but gone on Report Manager

Hello,

I have several reports that use parameters that I have defined as multi-value. When I am designing the reports I see the "(Select All)" option listed first in each parm control that has the multi-value property set true. However, when the same reports are deployed to our server and viewed through Report Manager, the "(Select All)" option is missing. I need the "(Select All)" to be present.

I've seen some discussion of a service pack. Could this be the problem? What do we need to do to get our "(Select All)" back?

Thanks,

BCB

Hello BCB,

You'll need to apply SP2 to the server with Reporting Services on it. It seems that your local machine already has it applied.

Jarret

|||

Hello Jarret,

Thanks for your response. Can you tell me how to identify the service packs that have been applied to our SQL Server, if any? I checked the SQL Server properties and found a Version property that reads 9.00.2047.00. However, the number doesn't mean anything to me.

How can I tell if SP2 has been applied?

Thanks,

BCB

|||

BCB,

RTM - 2005.90.1399
SP1 - 2005.90.2047
SP2 - 2005.90.3042

This link will tell you which version number corresponds to which service pack:

http://support.microsoft.com/default.aspx/kb/321185

You can also go to the properties of the EXE file to get the version number. For example:

{Drive}:\Program Files\Microsoft SQL Server\MSSQL.{InstanceID_of_RS}\Reporting Services\ReportServer\bin\ReportingServicesService.exe

Jarret

|||

I have the same scenarios (but it's not a problem)

I applied SP2 (AND SP2a) on my local client, so my VS2005 has <Select All> in the Designer, and local Preview

however, our SSRS servers haven't been applied SP2 but only SP1, so no <Select All> there

My local .exe version is 3050 (ie. anything > 3042 is SP2) since SP2a is .3050 :)

|||Thanks to all for the very helpful responses...

"Override Default" button in Properties->Parameters section

I wrote a report in Visual Studio with some parameters that I gave default values to in the report definition. I deployed it out to the report server and when I look at the report Properties, Parameters section to see the default values. Instead of seeing all of the default values, I see a bunch of "Override Default" buttons. When you click the buttons, you get a blank text box in which to enter a new value.

Someone before me created a report with default values and the parameters section of that report has text boxes with the default values in them.

Does anyone know how to have the default values show up rather than those "Override Default" buttons?

Thanks.

Somebody must know what I am talking about...|||

I have a hard time believing no one knows about this...

Is my problem not clear? Somebody please ask a question if you don't understand!

|||

I know what you are talking about, but I don't think there is a setting to have it show the values.

Jarret

|||

That makes it seem like random behavior then, which is not really an acceptable explanation.

I'm looking at one report that was written a little while back that will show you the default values in text boxes you can edit, and then there's this other report that I just wrote that will only show you the "Override Default" buttons. When you click them, then you get editable text boxes, but it won't show you what the original default value was.

Is there anyone else out there that can take a stab at it please? Thank you.

|||

Hmmmm...

I just created a simple report with a default parameter and it showed the correct default value instead of the 'Override Default' button. Maybe it has something to do with which version of RS you created the report under (RS2000, RS2005 RTM, SP1, SP2) versus which version you're trying to view it under.

Like you, I have seen this before.

Jarret

|||

I think that it is not "random behavior". As I know the behavior is:

1. If default value isn't query-based and not an expression - you will see original default value

2. If default value isn't query-based but an expression - you will see "Override Default" button instead of expression result

3. If default value is query-based - you will see text "Query Based"

|||

Alright, now we're talking. This is the kind of answer I was looking for, someone who knows the behavior!

So in the past I have had the default values be expressions. For this particular report, I just plugged in numbers. However, I put them in the following format: =2.0. Would that be considered an expression instead of just a value?

|||

miguelito928 wrote:

Alright, now we're talking. This is the kind of answer I was looking for, someone who knows the behavior!

So in the past I have had the default values be expressions. For this particular report, I just plugged in numbers. However, I put them in the following format: =2.0. Would that be considered an expression instead of just a value?

I'm pretty sure you just have to select a default value in the parameters option. I can check when I get to work if you don't confirm this to be true until then

Here's how I believe it works:

-> Add a new parameter called CategoryId, for example;

-> In the values list I'm guessing you'll retrieve them from a query that returns at least an Id and a Label;

-> In the option below the values list you have the option to add a default value, which can also be returned by a query.

Basicly what this 3 item list means is that let's imagin you have a query that returns these values:

1 - Meat

2 - Fish

3 - Fruit

It's a perfect query for your values list! Now, for the default value selection you would want it to be something like:

SELECT C.Id, C.Alias FROM Categories C WHERE C.Default = 1;

This query would return you the default category to use in your report parameter. Something like this:

1 - Meat

Best regards,

Ahkaru

|||

No, your post wasn't helpful at all. I'm not getting my default values from queries.

I am setting the default value of my parameter to "=2.0". This seems to be a non-queried expression, which would explain why I am getting an "Override Default" button instead of a text box with the value in it. The type of my parameters are floats by the way. I just noticed that the report written by someone else has strings and the default values are entered in without the equals sign in front.

So I decided to try and remove the equals signs and now it is showing that there are no default values. Is there no way to enter non-expression non-queried values from the Visual Studio report builder?

|||Ok, I get it to work if you leave all the default values blank and then fill them in through the Report Manager console after you deploy the report. The only way to deploy the report with default values is using expressions and putting equals signs in front, which causes "Override Default" buttons. Thank you to Andrey for his knowledge of the behavior.

"Override Default" button in Properties->Parameters section

I wrote a report in Visual Studio with some parameters that I gave default values to in the report definition. I deployed it out to the report server and when I look at the report Properties, Parameters section to see the default values. Instead of seeing all of the default values, I see a bunch of "Override Default" buttons. When you click the buttons, you get a blank text box in which to enter a new value.

Someone before me created a report with default values and the parameters section of that report has text boxes with the default values in them.

Does anyone know how to have the default values show up rather than those "Override Default" buttons?

Thanks.

Somebody must know what I am talking about...|||

I have a hard time believing no one knows about this...

Is my problem not clear? Somebody please ask a question if you don't understand!

|||

I know what you are talking about, but I don't think there is a setting to have it show the values.

Jarret

|||

That makes it seem like random behavior then, which is not really an acceptable explanation.

I'm looking at one report that was written a little while back that will show you the default values in text boxes you can edit, and then there's this other report that I just wrote that will only show you the "Override Default" buttons. When you click them, then you get editable text boxes, but it won't show you what the original default value was.

Is there anyone else out there that can take a stab at it please? Thank you.

|||

Hmmmm...

I just created a simple report with a default parameter and it showed the correct default value instead of the 'Override Default' button. Maybe it has something to do with which version of RS you created the report under (RS2000, RS2005 RTM, SP1, SP2) versus which version you're trying to view it under.

Like you, I have seen this before.

Jarret

|||

I think that it is not "random behavior". As I know the behavior is:

1. If default value isn't query-based and not an expression - you will see original default value

2. If default value isn't query-based but an expression - you will see "Override Default" button instead of expression result

3. If default value is query-based - you will see text "Query Based"

|||

Alright, now we're talking. This is the kind of answer I was looking for, someone who knows the behavior!

So in the past I have had the default values be expressions. For this particular report, I just plugged in numbers. However, I put them in the following format: =2.0. Would that be considered an expression instead of just a value?

|||

miguelito928 wrote:

Alright, now we're talking. This is the kind of answer I was looking for, someone who knows the behavior!

So in the past I have had the default values be expressions. For this particular report, I just plugged in numbers. However, I put them in the following format: =2.0. Would that be considered an expression instead of just a value?

I'm pretty sure you just have to select a default value in the parameters option. I can check when I get to work if you don't confirm this to be true until then

Here's how I believe it works:

-> Add a new parameter called CategoryId, for example;

-> In the values list I'm guessing you'll retrieve them from a query that returns at least an Id and a Label;

-> In the option below the values list you have the option to add a default value, which can also be returned by a query.

Basicly what this 3 item list means is that let's imagin you have a query that returns these values:

1 - Meat

2 - Fish

3 - Fruit

It's a perfect query for your values list! Now, for the default value selection you would want it to be something like:

SELECT C.Id, C.Alias FROM Categories C WHERE C.Default = 1;

This query would return you the default category to use in your report parameter. Something like this:

1 - Meat

Best regards,

Ahkaru

|||

No, your post wasn't helpful at all. I'm not getting my default values from queries.

I am setting the default value of my parameter to "=2.0". This seems to be a non-queried expression, which would explain why I am getting an "Override Default" button instead of a text box with the value in it. The type of my parameters are floats by the way. I just noticed that the report written by someone else has strings and the default values are entered in without the equals sign in front.

So I decided to try and remove the equals signs and now it is showing that there are no default values. Is there no way to enter non-expression non-queried values from the Visual Studio report builder?

|||Ok, I get it to work if you leave all the default values blank and then fill them in through the Report Manager console after you deploy the report. The only way to deploy the report with default values is using expressions and putting equals signs in front, which causes "Override Default" buttons. Thank you to Andrey for his knowledge of the behavior.

"Override Default" button in Properties->Parameters section

I wrote a report in Visual Studio with some parameters that I gave default values to in the report definition. I deployed it out to the report server and when I look at the report Properties, Parameters section to see the default values. Instead of seeing all of the default values, I see a bunch of "Override Default" buttons. When you click the buttons, you get a blank text box in which to enter a new value.

Someone before me created a report with default values and the parameters section of that report has text boxes with the default values in them.

Does anyone know how to have the default values show up rather than those "Override Default" buttons?

Thanks.

Somebody must know what I am talking about...|||

I have a hard time believing no one knows about this...

Is my problem not clear? Somebody please ask a question if you don't understand!

|||

I know what you are talking about, but I don't think there is a setting to have it show the values.

Jarret

|||

That makes it seem like random behavior then, which is not really an acceptable explanation.

I'm looking at one report that was written a little while back that will show you the default values in text boxes you can edit, and then there's this other report that I just wrote that will only show you the "Override Default" buttons. When you click them, then you get editable text boxes, but it won't show you what the original default value was.

Is there anyone else out there that can take a stab at it please? Thank you.

|||

Hmmmm...

I just created a simple report with a default parameter and it showed the correct default value instead of the 'Override Default' button. Maybe it has something to do with which version of RS you created the report under (RS2000, RS2005 RTM, SP1, SP2) versus which version you're trying to view it under.

Like you, I have seen this before.

Jarret

|||

I think that it is not "random behavior". As I know the behavior is:

1. If default value isn't query-based and not an expression - you will see original default value

2. If default value isn't query-based but an expression - you will see "Override Default" button instead of expression result

3. If default value is query-based - you will see text "Query Based"

|||

Alright, now we're talking. This is the kind of answer I was looking for, someone who knows the behavior!

So in the past I have had the default values be expressions. For this particular report, I just plugged in numbers. However, I put them in the following format: =2.0. Would that be considered an expression instead of just a value?

|||

miguelito928 wrote:

Alright, now we're talking. This is the kind of answer I was looking for, someone who knows the behavior!

So in the past I have had the default values be expressions. For this particular report, I just plugged in numbers. However, I put them in the following format: =2.0. Would that be considered an expression instead of just a value?

I'm pretty sure you just have to select a default value in the parameters option. I can check when I get to work if you don't confirm this to be true until then

Here's how I believe it works:

-> Add a new parameter called CategoryId, for example;

-> In the values list I'm guessing you'll retrieve them from a query that returns at least an Id and a Label;

-> In the option below the values list you have the option to add a default value, which can also be returned by a query.

Basicly what this 3 item list means is that let's imagin you have a query that returns these values:

1 - Meat

2 - Fish

3 - Fruit

It's a perfect query for your values list! Now, for the default value selection you would want it to be something like:

SELECT C.Id, C.Alias FROM Categories C WHERE C.Default = 1;

This query would return you the default category to use in your report parameter. Something like this:

1 - Meat

Best regards,

Ahkaru

|||

No, your post wasn't helpful at all. I'm not getting my default values from queries.

I am setting the default value of my parameter to "=2.0". This seems to be a non-queried expression, which would explain why I am getting an "Override Default" button instead of a text box with the value in it. The type of my parameters are floats by the way. I just noticed that the report written by someone else has strings and the default values are entered in without the equals sign in front.

So I decided to try and remove the equals signs and now it is showing that there are no default values. Is there no way to enter non-expression non-queried values from the Visual Studio report builder?

|||Ok, I get it to work if you leave all the default values blank and then fill them in through the Report Manager console after you deploy the report. The only way to deploy the report with default values is using expressions and putting equals signs in front, which causes "Override Default" buttons. Thank you to Andrey for his knowledge of the behavior.

"Must declare the scalar variable" in table-valued function

Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @.arTemp.Insurer AND
Pims.NABP = @.arTemp.NABP AND
Pims.NumRx = @.arTemp.NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@.arTemp".

I don't understand why SQL thinks @.arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

Not sure of the why, but aliasing the table variable seems to satisfy the compiler.

Code Snippet

UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = t1.Insurer AND
Pims.NABP = t1.NABP AND
Pims.NumRx = 1.NumRx
)

FROM @.arTemp t1;

|||Immediately after posting that I found the workaround -- enclose @.arTemp in brackets where it is used to qualify a column.

So
ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = [@.arTemp].Insurer AND
Pims.NABP = [@.arTemp].NABP AND
Pims.NumRx = [@.arTemp].NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

works -- at least it passes the syntax check.
I think this is a SQL bug.

& in report parameter

Hi,
Using URL parameters, I pass in the following department parameter "Sales & Marketing". The problem is the "&", this causing reporting services to display an error. I assume reporting services thinks it the parameter delimiter.
I have tried URL encloding the string before I pass it in but I still get an error.
Any ideas?
ThanksWhat is the error. Passing an & is fine, and yes it must be encoded on the
URL.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"MikeMcD" <MikeMcD@.discussions.microsoft.com> wrote in message
news:B84B6DAA-93FA-44CF-9CEC-C062E1DED325@.microsoft.com...
> Hi,
> Using URL parameters, I pass in the following department parameter "Sales
& Marketing". The problem is the "&", this causing reporting services to
display an error. I assume reporting services thinks it the parameter
delimiter.
> I have tried URL encloding the string before I pass it in but I still get
an error.
> Any ideas?
> Thanks|||Yes, we need to encode the & when we pass it in the URL.
just search ur string for an & and replace it with %26.
This should work fine.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.