Conditionaly hiding rows in a table in a single datagroup

Hello,
I have a report which takes data from a stored procedure and then reveals
some text on a row depending on whether or not a bit is true. The data coming
from teh stored procedure is a single row for a contact, and it displays a
number of rows in the report.
When we run the report into a PDF the data is all tidily on one page,
however in the web browser it is only showng 4 database rows per page (even
if there is only one additional line being displayed), this is because there
are so many rows (10), though the data isnt spaced out it is all at the top
of the report. Is there a way of getting more data to display in the report ?
Many Thanks
ChrisHi Chris,
I understood you would like to hide rows in a table, however I am not sure
in what condition you would like to hide the row? Would you please provide
us some detailed scenario examples?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Conditionally CREATE a VIEW in a script

Hi,

I would like to create a view depending on a condition check first. However, I do not seem to the able to put a 'CREATE VIEW' within an IF statement. The following example demonstates what I am trying to achieve (please excuse the triviality of the example):

IF NOT col_length('authors','city') IS NULL

BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
END
ELSE
BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
END

When I try to parse/run this I get the following syntax error:

"Incorrect syntax near the keyword 'VIEW'."

Any help would be much appreciated.

Thanks.

Try the code below.

Chris

Code Snippet

DECLARE @.sqlstring NVARCHAR(4000)

IF NOT col_length('authors', 'city') IS NULL
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address + '', '' + city) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END
ELSE
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END

|||

I think this looks misguided. Rather than changing the view that is is created dynamically, I think you need to change the view permanently so that both views can be represented by a singular view that uses CASE construct. Hang on and if I don't get you an example, I imagine someone else will.

Maybe something like this:

create view testView
as

select au_fname + ' ' + au_lname
as fullName,
address
+ case when len(rtrim(city)) = 0
then ''
else ', ' + city
end
as address
from authors

go

select * from testView

/*
fullName address
--
Johnson White 10932 Bigge Rd., Menlo Park
Marjorie Green 309 63rd St. #411, Oakland
Cheryl Carson 589 Darwin Ln., Berkeley
*/

|||

Hi Chris,

I had thought about doing that but the real view is quite large and I was trying to avoid dealing with string manipulation but I suppose its just two single quotes for ant existing single quotes.

Thanks.

Smoc

|||

Hi Kent,

Thanks for the response but that will not work if the column does not exist in the table which is the reason I want to conditionally create 1 of 2 possible views. In the simplistic example, I want to handle the situation when the column 'city' may not be in the authors table.

I realise that i could use the col_length function instead to achieve the result you have proposed. I was just wondering why I could have two 'clean' view definitions in a script contained within an IF statement.

Regards,

Smoc

|||

Just thinking out loud really, but could you programatically add the City column to the authors table if the column doesn't exist? That way, going forward, you'd only have one version of the View to maintain.

Chris

|||

Hi Chris,

We have an application that is using a database that we have no control over and no authority to change. We have discovered some differences between schemas of different clients who have this database. The differences are not critical and we hope to handle it at the view level. Other than that we would do as you suggested.

I'm just supprised that I can do a DROP command but not a Create View command in an IF statement.

Smoc

|||

You can't create view/procedure/function/trigger inside or mid of your batch.

These create scripts should be the first line of the batch.

In IF batch you can put only the Drop view/procedure/function/trigger.

The only possible way is using dynmaic sql.

|||

Thanks for the clarification.

I will probably use the dynamic sql that you have suggested and as was also suggested in an earlier thread.

Thanks.

|||There is a neat trick to achieve just what you want . Check out this example:

-- If column doesn't exists, does not create the view that use it
IF col_length('authors','city') IS NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off
go
-- If column exists, does not create the view without it
IF col_length('authors','city') IS not NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off

You only need to carefully choose your conditions because they have to be "reversed", in a way. Still, it is a proven and reliable approach.

Conditionally adding a column to my custom component

Hi,

I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.

Depending on its setting, I want to include (or not include) a column in the output.

Any advice on how to go about doing this (with some sample code) would be much appreciated!

Here's how I'm declaring the property in ProvideComponentProperties()

IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New(); IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; IncludeErrorDesc.Name = "Some Name"; IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName; IncludeErrorDesc.Value = Convert.ToBoolean(false);

Thanks in advance

-Jamie

Implement SetComponentProperty method in your component and if the property is set to true add your column, otherwise find it in the collection and remove it.

I do not have a time to build you a sample, but give it a try and let us know if it does not go well.

BTW, you do not need the following line from your sample:

IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;

Thanks.

|||Hi Bob,
I nevre replied to this. Just wanted to say thanks for this - it worked a treat!
-Jamie|||

You are welcome, Jamie. I am glad it worked out.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.sqlsql

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional WHERE clause

Hi,

[SQL 2005 Express]

I would like a DropDownList to be populated differently depending on the selected value in a FormView.

If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.

Here is the SQL for case 1:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID).

Here's the SQL for case 2:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID) AND
(TypeID = 3).

Here's my best (failed) attempt to get what I want:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
IF @.CompanyID = 2 THEN
BEGIN
(CompanyID = @.CompanyID)
END
ELSE
BEGIN
(CompanyID = @.CompanyID) AND
(TypeID = 3)
END

I've also tried:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE @.CompanyID
WHEN 2 THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND
(TypeID = 3)
END

and

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE WHEN (@.CompanyID = 2) THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND (TypeID = 3)
END

I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.

Thanks very much.

Regards

Gary

Gary,

I think you are trying to construct a select based on the values selected in some control in your web form. (correct me if i am wrong) while going through your issue, i think the following solution should work -

Generate a dynamic where clause -

declare @.SQLSelect varchar(4000),

@.SQLWhere varchar(2000)

set @.SQLSelect = 'SELECT
AdviserID,
AdviserName
FROM
Advisers '

If @.CompanyID = 2

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID

End

Else

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID + ' and TypeID = 3'
End

set @.SQLSelect = @.SQLSelect + @.SQLWhere

Execute @.SQLSelect

I think this will help you to think further, if didn't solve your problem.

Ash

|||

Thanks, Ash.

I suspect that I'm too much of a novice to know what to do with what you've posted.

I've tried executing it as a Query and as a Stored Procedure - with no success. I ended up simplifying it to the same WHERE clause, with no better results:

Here is the simplified query:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000)
SET @.SQLSelect = 'SELECT AdviserID, AdviserName FROM Advisers '
IF @.AdviserCompanyID = 2
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
ELSE
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
SET @.SQLSelect = @.SQLSelect + @.SQLWhere
EXECUTE @.SQLSelect

This generated: "Must Declare the scalar variable @.AdviserCompany"

So, I changed the first line to:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000), @.AdviserCompanyID int

This generated:"Incorrect syntax near '@.SQLWhere'"

No joy after much fiddling. I then tried to create a Stored Procedure - with similar success levels.

What am I not getting?

Thanks very much for your help.

regards

Gary

|||

Gary:

You are not the only one. It took me hours to come to this solution.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT [AdviserID], [AdviserName] FROM [Adviser]

WHERE CompanyID = CASE @.CompanyID WHEN 2 THEN 2 ELSE @.CompanyID END AND

TypeID = CASE @.CompanyID WHEN 2 THEN TypeID else 3 END"

><SelectParameters><asp:ControlParameterControlID="FormView1$companyIDtxtbox"Name="CompanyID"Type="Int32"/></SelectParameters></asp:SqlDataSource>|||

Excellent, Limno - thanks very much! Works a charm...

(Still keen to find out if I was doing something stupid to prevent the Previous idea from working...Anyone?)

Regards

Gary

|||

Hi Guys,

I would like to apply Limnon's solution to the WHERE clause in the following select statement:

SELECT
FIInvestments.InvestmentID,
Accounts.AccountName + ' - ' + CAST(Accounts.AccountNumber AS varchar(20)) + CONVERT
varchar, FIInvestments.InvestmentDate, 3) + ': ' + ' ($' + LEFT (CAST(FIInvestments.Amount AS varchar),
LEN(CAST(FIInvestments.Amount AS varchar)) - 3) + ' for ' + CAST(FIInvestments.Term AS varchar(3)) + '
months)' AS Investment
FROM
FIInvestments INNER JOIN
Accounts ON FIInvestments.AccountID = Accounts.AccountID
WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

(In other words: I want all the Investments with the selected FundID, unless commission has already been paid on them, as evidenced by payment records in the FICommPayments table. This is so I can allocate commissionpayments to them).

However, there is one Fund/FundID (FundID = 141) which pays commission in dribs and drabs, so if that Fund is selected, I wantall the investments within that Fund - not just the ones that haven't had commission paid against them yet.

So, here are my two WHERE statements:

WHERE (FIIinvestments.FundID = @.FundID)

and

WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

And here's my best attempt at a conditional WHERE statement so far:

WHERE CASE @.FundID WHEN 141 THEN (FIInvestments.FundID = @.FundID) ELSE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS (SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes FROM FICommPayments WHERE (InvestmentID = FIInvestments.InvestmentID))) END

The error messages this generates are:

Incorrect syntax near '='.
Incorrect syntax near ')'.

I also tried, with little hope:

WHERE (FIInvestments.FundID = @.FundID) CASE @.FundID WHEN NOT 141 THEN (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))END

This generated:

Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near ')'

Perhaps I should revert to Ash's solution - but I got stuck on that one, too!

Thanks for the help.

Regards

Gary

|||

Hello:

I don't know why your approaches did not work. You may post that question again if you want an answer. Instead, you can split your condition in two separate parts, then use UNION OR UNION ALL (with possible duplicates) to combine the results.

I used a simplified version of your tables to test the following script, it works as to my understanding. But you may need to tweak it for your real case.

SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments WHERE FundID<>141 AND FundID=@.FundID AND FIInvestments.FundID NOT IN (SELECT FICommPayments.FUNDID
FROM FICommPayments INNER JOIN FIInvestments ON FICommPayments.InvestmentID = FIInvestments.InvestmentID)
UNION ALL
SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments
WHERE FundID=141 ANDFundID=@.FundID

|||

Excellent, Limon!

I started off thinking that your suggestion wasn't exactly what I'm after in this case (because I need one list for case 141 and the other for every other case, so the UNION ALL didn't seem like what I was after until it dawned on me what you're doing - very sneaky!).

I've been looking for UNION / UNION ALL for other reasons, so I get a double hit out of this one. Thanks very much - you're making my day on a number of fronts at the moment (including on the other thread)!

If this keeps up much longer, I'll have to put you on a retainer. In fact, I think that the guru's amongst you should work out an easy way of allowing novices like me to secure a commercial agreement/service with you guys in addition to this freebie one. I know that it exposes the community to abuse, but I'm sure there must be a way of doing it... I'll keep thinking and ewxperiencing and come up with something over the next month or two.

Regards

Gary

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

conditional reading of data ...

I have a need to allow users select permission depending upon
a flag and userid. Is it possible to do it on the server instead of on clien
t?
Because using 'Access front end', I am unable to restrict table view to
secured records. However, I am able to restrict the view using forms.
Thanks for your help in advance!
-MeConsider creating SQL Server views with option VIEW_METADATA. This will
limit users to only data exposed by the views and Access won't try to access
the underlying tables directly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B781CADF-9100-4295-937D-87383BA4954B@.microsoft.com...
>I have a need to allow users select permission depending upon
> a flag and userid. Is it possible to do it on the server instead of on
> client?
> Because using 'Access front end', I am unable to restrict table view to
> secured records. However, I am able to restrict the view using forms.
> Thanks for your help in advance!
> -Me
>|||Dan,
Its a great idea, I will implement it in some cases. However, what do I do
when I have to add/modify the data? If I don't link the table directly, I
won't be able to save data.
Here is my situation. I have a table say 'Tally', only admin/s are allowed
to create records in this table. It has a flag indicating if a particular
record in 'Tally' is confidential. If so, all users aren't allowed to view
this record. Only users associated with particular 'Tally' can view/modify
records. Information about who can access is stored in another table which i
s
linked to 'Tally' with a key.
As far as viewing of the data is concerned your idea of creating a view will
work
fine. But if users have to modify the record, they will need access to the
table.
Any ideas?
Appreciate your help!
-Me
"Dan Guzman" wrote:

> Consider creating SQL Server views with option VIEW_METADATA. This will
> limit users to only data exposed by the views and Access won't try to acce
ss
> the underlying tables directly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:B781CADF-9100-4295-937D-87383BA4954B@.microsoft.com...
>
>|||> As far as viewing of the data is concerned your idea of creating a view
> will
> work
> fine. But if users have to modify the record, they will need access to the
> table.
> Any ideas?
You ought to be able to modify data via the view as long as only one base
table is affected. For modifications via Access, I believe you also need to
identify the unique column(s) to Access so that it can construct the needed
SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:5861EBE1-670C-4687-B8DF-13D264443643@.microsoft.com...[vbcol=seagreen]
> Dan,
> Its a great idea, I will implement it in some cases. However, what do I do
> when I have to add/modify the data? If I don't link the table directly, I
> won't be able to save data.
> Here is my situation. I have a table say 'Tally', only admin/s are
> allowed
> to create records in this table. It has a flag indicating if a particular
> record in 'Tally' is confidential. If so, all users aren't allowed to view
> this record. Only users associated with particular 'Tally' can view/modify
> records. Information about who can access is stored in another table which
> is
> linked to 'Tally' with a key.
> As far as viewing of the data is concerned your idea of creating a view
> will
> work
> fine. But if users have to modify the record, they will need access to the
> table.
> Any ideas?
> Appreciate your help!
> -Me
> "Dan Guzman" wrote:
>

Conditional Processing from a Common Table Expression (CTE)

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:

WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */

END
ELSE
BEGIN
/* Do something else here */

END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

WITH statement is a part of SELECT/INSERT/UPDATE/DELETE statement. As result you code doesn't work.

What processing do you need?

|||

What exactly you want to do on the /* Do Some processing here */. It might help you to give the rite solution.

You can end the CTE expression with INSERT/DELETE/UPDATE/SELECT. Othere than this 4 statement none of them are allowed.

|||

Thanks for the quick response.

All I need to do in each part of the IF ...ELSE..... is to perform a select, but which select statement to use depends on a field in the CTE. One select pulls data from a table in current database, whereas the other pulls data from a table on a linked server. The tables have similar but not exactly the same structures.

As I mentioned, I have what I need working using a cursor, I was just wondering if this were possible with a CTE, but based on what you and other repsondents have stated, it appears unlikely.

conditional grouping (to group or not to group)

I have 3 groups: region, district, facility.
Depending upon user input, I want to either display the data in a drilldown
such as:
region 1
district 1
facility A data data data
facility C data data data
facility F data data data
district 2
facility B
facility D
...etc
or I want to only display the facility data without the drilldown and
without displaying which region and district those facilities belong to.
Obviously, this could be done by using 2 separate reports, but I need to have
this functionality for all of my reports. So if there is a simple way to
allow for it in the same report, I would like to do that.
ThanksThe closest you can get is a conditional grouping expression like this:
=iif(Parameters!GroupOnRegion.Value = True, Fields!Region.Value, 1)
Note: Grouping on a constant value will just generate 1 group that contains
all values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stads" <Stads@.discussions.microsoft.com> wrote in message
news:CC17E391-B188-492F-980E-C8F3B4298F97@.microsoft.com...
> I have 3 groups: region, district, facility.
> Depending upon user input, I want to either display the data in a
drilldown
> such as:
> region 1
> district 1
> facility A data data data
> facility C data data data
> facility F data data data
> district 2
> facility B
> facility D
> ...etc
> or I want to only display the facility data without the drilldown and
> without displaying which region and district those facilities belong to.
> Obviously, this could be done by using 2 separate reports, but I need to
have
> this functionality for all of my reports. So if there is a simple way to
> allow for it in the same report, I would like to do that.
> Thanks
>

Conditional group by

Hi,

Can anyone help me in writing this sql query, i want to group my select statement depending on the parameter user is passing.

Say when @.group='Cell' I want to group by CellID otherwise different conditions, something like below query but it is not working. I know we can't use case directly in where but please let me know if there is any other work around.

I don't want to use dynamic query and also this is big SP so i dont want to break sp in four conditions.

declare @.group varchar(10)

set @.group='Cell'

select cellid,sum(count)

FROM CellImpressionFact

WHERE ImpressionTypeLevelId = 2

AND ImpressionTypeId = 4

group by

case when group='Cell' then GROUP BY CellId

else group by activityID

end

This is not a good idea really. I would use dynamic SQL to provide this kind of capability if you really need to. It is possible (see code) but I would be very concerned about performance.

create table test
(
grouper int,
grouper2 int,
value decimal(10,5)
)
go
insert into test
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,10
go
declare @.groupby varchar(10)
set @.groupBy = 'grouper2'

select max(grouper) as grouper,
max(grouper2) as grouper2,
sum(value) as valueSum
from test
group by case when @.groupBy = 'grouper' then grouper else grouper2 end

Note that the grouper2 column is of any value when you group by grouper, and vice versa (say it five times fast.)

Conditional Formatting on a table

Hi all,

Any ideas how to apply conditional formatting depending on the value of a particular cell in a table. Basically i want to say that if a value is less than 0, then that figure should have a red background, if the figure is = 0, then leave it white, and then if the figure is more than 0, then put a green background on it. Any ideas how to do this??

Miles

Here is an article that may help you outhttp://blogs.msdn.com/swisowaty/attachment/661446.ashx

About half way into the article it talks about conditional formating with colors.

Conditional Formating of axis labels(Urgent!)

Depending on the time interval, that is covered by the report and which will
be provided by parameters, the format of the x-axis labels should be e.g.
hh:mm:ss or dd.mm.yyyy. It seems to me, that the format can only be defined
statically. Why can't I use expressions here?
Any help is appreciated, thanks.You can certainly use expressions for the format code on x-axis labels.
However, make sure that the category grouping expressions (which get applied
to the x-axis) result in objects of type DateTime and not in strings. Format
codes won't have any effect on strings.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:FD03A48E-AA5C-400E-815D-FDE48CD07CD0@.microsoft.com...
> Depending on the time interval, that is covered by the report and which
will
> be provided by parameters, the format of the x-axis labels should be e.g.
> hh:mm:ss or dd.mm.yyyy. It seems to me, that the format can only be
defined
> statically. Why can't I use expressions here?
> Any help is appreciated, thanks.

Conditional formating in Subtotals?

I have subtotals in a matrix and I want to format the background color depending on the subtotal value - > 95 = "Green", < 95 > 90 = "Yellow", < 90 = "Red". I go to the Subtotal properties and put the iif expression in the background color, but it's not detecting the Subtotal value. How do I refer to the Subtotal value in a formating expression?

You have to put the background color expression directly on the matrix cell, similar to the approach described in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=648771&SiteID=1

-- Robert

|||That didn't work. I copied the example from the post and I set a different color for each of the three results. It set all the cells as "In Subtotal of entire matrix" color. The odd thing is, I also have a background image which shows up correctly in the preview, but the image doesn't show up when I render the report from a browser.|||

if you are doing alot of testing, make sure to refresh the report in the browser from the view to the print view. I have found that SSRS cache's data and unless a refresh is done it can still show previous view data.

Weird.

|||We need more information.

an iif statement will work, how are you computing the subtotals, is it in a group footer? if so something like

=iif( sum(fields!field.value) < Number, "Green", "Yellow")

should work...|||

Yes, I created the subtotals by right-clicking the row cell of the outer-most group and selecting 'subtotals'. The problem with the simple iif... is that it affects all the cells - not just the subtotals.

I did find out why my background image wasn't showing in the subtotals, if you set a background color in the Subtotal properties, it will overlay the background image from the cell properties.

|||I think the InScope solution may be the key to my problem, but for some reason, it isn't detecting the scope. Any ideas on what I've done wrong?|||

Robert was right. The InScope works, but I needed to add the matrix name and underscore to the row and columns group names since I had more than one matrix in the report.

Thanks Robert!

Conditional Formating in Reporting Services 2005

Hello,
I have a problem formating a line in a diagram in SSRS 2005. I want to change the color of the line depending on a value on the x-axis.

I always get an exception that the bordercolor expression for the diagram Object 'Bestandsentwicklung.DataPoint' contains an error. The entry string has the wrong format.

The function is the following:
=IIF(Parameters!CurrentWeek.Value.ToString > cdbl(Fields!DimZeit_WocheTag_Woche_MEMBER_KEY.Value.ToString),"Firebrick","Blue")
whereby the parameter currentweek should return a number and the field DimZeit_WocheTag_Woche_MEMBER_KEY, too.

Can anyone help me? How do I have to convert the values to solve the problem?

Stefoon

The error is because you are comparing a string with a double data type. Do not use ToString for CurrentWeek parameter, instead use CDbl on both sides of your expression in IIf.

Shyam

|||

Hi Shyam,

you are right. But it does not work either.

Stefoon

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!