Conditional Page Breaks

Hi -
I'm trying to generate invoice reports where there are two types of
line items (with a different number of columns). I am basically looking
to have two sets of tables as follows:
Case A - at least one of each item
Table Items 1
<PAGE BREAK>
Table Items 2
Case B - items 2 exist, but no items 1
Table Items 2
Case C - items 1 exist, but no items 2
Table Items 1
I've spent hours trying to figure out how to accomplish this - I have
played with putting Table 1 in an object with conditional visibility,
but that doesn't seem to suppress a page break. I've tried a "hidden
group" but no luck there either. I've also tried having Table items 1
in a subreport.
I'm open to suggestion if you don't think I am going about this the
right way - I would really appreciate any help!
Thanks
BNo conditional page breaks. I've been asking for them since the first
release a couple years ago. It is the main reason we have not migrated. If
you can't control the layout of the report it's pointless. I've tried many
workarounds but it seems like the developers went out of their way to not let
you programatically control them. Be it if you want to remove them as in
HTML reports or insert them manually.
Good luck on your quest. If you find something let us know.
"bigbrorpi@.gmail.com" wrote:
> Hi -
> I'm trying to generate invoice reports where there are two types of
> line items (with a different number of columns). I am basically looking
> to have two sets of tables as follows:
> Case A - at least one of each item
> Table Items 1
> <PAGE BREAK>
> Table Items 2
> Case B - items 2 exist, but no items 1
> Table Items 2
> Case C - items 1 exist, but no items 2
> Table Items 1
>
> I've spent hours trying to figure out how to accomplish this - I have
> played with putting Table 1 in an object with conditional visibility,
> but that doesn't seem to suppress a page break. I've tried a "hidden
> group" but no luck there either. I've also tried having Table items 1
> in a subreport.
> I'm open to suggestion if you don't think I am going about this the
> right way - I would really appreciate any help!
> Thanks
> B
>|||See if this can help :
You can insert a rectangle into a rectangle, and set a page break in the
inner rectangle and visibility in the outer and paste this empty nested
rectangles
in between subreports or tables . If Visible expression is True, the page
break will
work. if Visible expression is False , it will ignore a Page Break. So,
this nested construction will work as a conditional page break ( for RSS
2000) .

Conditional Order By Stored Procedure

I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
The approach I typically take is this:

SELECT
someColumns
FROM
someTable
WHERE
CASE WHEN @.sortValue = 1 THEN NEWID() END,
CASE WHEN @.sortValue = 2 THEN someTextColumn END,
CASE WHEN @.sortValue = 3 THEN someDateTimeColumn END,
defaultSortColumn -- just in case the @.sortValue is not 1, 2, or 3, Iknow the results will be sorted by *something*

|||My full stored procedure is listed below but i assume i have to specify the order
by clause somewhere it keeps returning a incorrect syntax near CASE error message
The sproc
CREATE PROCEDURE [dbo].[sp_call_accomSearch]
(
@.accomType As Int,
@.sgleroom As Int,
@.dbleroom As Int,
@.twinroom As Int,
@.tripleroom As Int,
@.Garage As Int,
@.Phone As Int,
@.Altitude As Int,
@.CarPark As Int,
@.Tv As Int,
@.TownCentre As Int,
@.SwimPool As Int,
@.Radio As Int,
@.NearSlopes As Int,
@.DgsAdmit As Int,
@.Safe As Int,
@.CrossCtry As Int,
@.SuitDisable As Int,
@.Balcony As Int,
@.OnTLake As Int,
@.Solarium As Int,
@.Suite As Int,
@.QutZone As Int,
@.BeautyCb As Int,
@.Minibar As Int,
@.Tennis As Int,
@.WhirlPl As Int,
@.Elevator As Int,
@.Sauna As Int,
@.PriceRgLow As Int,
@.PriceRgHigh As Int,
@.DateFromTotal As DateTime,
@.DateToTotal As DateTime,
@.selfcatering As Int,
@.halfboard As Int,
@.fullboard As Int,
@.roomphone As Int,
@.Suitdisableroom As Int,
@.CountryID As Int,
@.OrderBy As Int
)
AS
SELECT
tblaccommodation.accommodationID,
tblaccommodation.[name],
tblaccommodation.address1,
tblaccommodation.address2,
tblaccommodation.town,
tblaccommodation.postcode,
tblaccommodation.country,
tblaccommodation.email,
tblaccommodation.contact,
tblaccommodation.editorial,
(SELECT [name] FROM tblresort WHERE resortID = resortname) As ResortName,
(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As SgleRoomTotal,

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As dbleRoomTotal,
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As twinRoomTotal,
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As tripleRoomTotal
FROM
tblaccommodation
WHERE

tblaccommodation.accomType = case
when @.accomType = 1 then @.accomType
else tblaccommodation.accomType
end AND

(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.Sgleroom AND

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.dbleroom AND
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.twinroom AND
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.tripleroom AND

tblaccommodation.Garage = case
when @.Garage <> 0 then @.Garage
else tblaccommodation.Garage
end AND
tblaccommodation.Phone = case
when @.Phone <> 0 then @.Phone
else tblaccommodation.Phone
end AND
tblaccommodation.Altitude = case
when @.Altitude <> 0 then @.Altitude
else tblaccommodation.Altitude
end AND
tblaccommodation.Carpark = case
when @.Carpark <> 0 then @.Carpark
else tblaccommodation.Carpark
end AND

tblaccommodation.TownCentre = case
when @.TownCentre <> 0 then @.TownCentre
else tblaccommodation.TownCentre
end AND
tblaccommodation.SwimPool = case
when @.SwimPool <> 0 then @.SwimPool
else tblaccommodation.SwimPool
end AND
tblaccommodation.NearSlopes = case
when @.NearSlopes <> 0 then @.NearSlopes
else tblaccommodation.NearSlopes
end AND
tblaccommodation.DgsAdmit = case
when @.DgsAdmit <> 0 then @.DgsAdmit
else tblaccommodation.DgsAdmit
end AND
tblaccommodation.Safe = case
when @.Safe <> 0 then @.Safe
else tblaccommodation.Safe
end AND
tblaccommodation.CrossCtry = case
when @.CrossCtry <> 0 then @.CrossCtry
else tblaccommodation.CrossCtry
end AND
tblaccommodation.SuitDisable = case
when @.SuitDisable <> 0 then @.SuitDisable
else tblaccommodation.SuitDisable
end AND
tblaccommodation.OnTLake = case
when @.OnTLake <> 0 then @.OnTLake
else tblaccommodation.OnTLake
end AND
tblaccommodation.Solarium = case
when @.Solarium <> 0 then @.Solarium
else tblaccommodation.Solarium
end AND
tblaccommodation.QutZone = case
when @.QutZone <> 0 then @.QutZone
else tblaccommodation.QutZone
end AND
tblaccommodation.BeautyCb = case
when @.BeautyCb <> 0 then @.BeautyCb
else tblaccommodation.BeautyCb
end AND
tblaccommodation.Tennis = case
when @.Tennis <> 0 then @.Tennis
else tblaccommodation.Tennis
end AND
tblaccommodation.Whirlpl = case
when @.Whirlpl <> 0 then @.Whirlpl
else tblaccommodation.Whirlpl
end AND
tblaccommodation.Elevator = case
when @.Elevator <> 0 then @.Elevator
else tblaccommodation.Elevator
end AND
tblaccommodation.Sauna = case
when @.Sauna <> 0 then @.Sauna
else tblaccommodation.Sauna
end AND

tblaccommodation.PriceRgLow >= @.PriceRgLow AND
tblaccommodation.PriceRgLow <= @.PriceRgHigh AND
tblaccommodation.PriceRgHigh <= @.PriceRgHigh AND

tblaccommodation.FromDT >= @.DateFromTotal AND
tblaccommodation.ToDT <= @.DateToTotal AND
tblaccommodation.selfcatering = case
when @.selfcatering<> 0 then @.selfcatering
else tblaccommodation.selfcatering
end AND
tblaccommodation.halfboard = case
when @.halfboard<> 0 then @.halfboard
else tblaccommodation.halfboard
end AND
tblaccommodation.fullboard = case
when @.fullboard<> 0 then @.fullboard
else tblaccommodation.fullboard
end AND
tblaccommodation.Country = @.CountryID AND
tblaccommodation.displayAcc = '1'
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDT END,
GO
|||First, I made a mistake in my example code. I mistakenly used aWHERE clause instead of an ORDER BY clause. Sorry to be confusing:-(
You are missing the ORDER BY, and you had an unneeded comma at the end. Try:

tblaccommodation.displayAcc = '1'
ORDER BY
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND

|||LOL ... i am confused most of the time anyway usually why i am on here so much
I am still getting an sql error still when i try to check the syntax of the query in question any
thoughts what is going wrong here not found anything on google groups like it yet. I have
listed the error below.
Fuzzy

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identfying a column position. Variables are only allowed when ordering
by an expression referencing a column name
|||Hmmm, it's not liking the NEWID(). I suggest this as aworkaround, but it will force NEWID() to be generated for each row inthe resultset, which *might* be a performance hit if you have a lot ofrows:
ORDER BY
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND,
NEWID()
|||

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
While either version will work for you, I think this one might be a little cleaner so if someone else needs to update it, they can see exactly how it is supposed to use the new ID (if orderBy is 1).
Just my 1.50
Nick

|||

nick-w wrote:

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
Whileeither version will work for you, I think this one might be a littlecleaner so if someone else needs to update it, they can see exactly howit is supposed to use the new ID (if orderBy is 1).

I had thought about that, but had become concerned that NEWID()would be calculated for every row in every table, not just for theresultset. That sent me on a fruitless hunt to find a referencefor the processing sequence of all of the different portions of aSELECT statement so that I could confirm or deny that concern. When I couldn't find a reference (does anyone have one?) , I decidedon the approach I recommended. But, with that being said, I muchprefer the approach you've recommended as it doesn't make assumptionsand as you said it is cleaner. :-)

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:
>> Creating an index over a table causes its schema to change, and in turn
>> this causes queries that reference the table to be recompiled. So, the
>> short answer to "will the conditional index creation mess up the SQL
>> engine" is no.
>> SQL Server will first compile the procedure, and then start executing it.
>> If the schema of a table changes between the compilation and execution of
>> a statement referencing it, the statement will be recompiled.
>> Actually the behavior changed significantly between SQL 2000 and 2005. In
>> 2000, the recompilations would affect the entire batch or procedure. A
>> significant improvement has been made in SQL 2005 with statement-level
>> recompiles. As the name suggests, in SQL 2005 only the affected
>> statements are recompiled, rather than the entire batch or procedure.
>> For more information on the subject, we have a very good whitepaper here:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> The consequence of what you are doing is that if you interleave
>> executions of the procedure that do not cause the index creation with
>> others where the index is created, you will incur in a significant number
>> of recompiles, because the schema of the temp table won't match the
>> previous compiled plan. In SQL 2000, this will be exacerbated with the
>> lack of statement level recompiles. This might easily negate the benefits
>> of saving the overhead of creating an index when the table is small.
>> Also, creating an index on a small table is a low overhead operation
>> anyway. I'd consider always creating the index, and seeing if you can
>> make it part of the table definition altogether if applicable.

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 formatting in reporting services (2005)

I am new to reporting services and am unable to get cell colouring conditional formatting to work in of a report. Basically I need to have the following:

>-5% = "Red"

Between -5% and -1% = "Green"

0% = "White"

Between 1% and 5% = "Green"

>5% = "Red"

Here is what I have tried to use:

=iif((Fields!TrueUpPercQ1.Value >0 and

Fields!TrueUpPercQ1.Value <=0.05) or (Fields!TrueUpPercQ1.Value < 0 and

Fields!TrueUpPercQ1.Value >=-0.05), "Green",

iif(Fields!TrueUpPercQ1.Value =0, "White","Red"))

Any ideas?

Thanks

Try this....

=iif((Fields!TrueUpPercQ1.Value > 0, "Red", IIF(Fields!TrueUpPercQ1.Value <=0.05, "Red", IIF(Fields!TrueUpPercQ1.Value < 0, "Blue,

IIF(Fields!TrueUpPercQ1.Value >=-0.05, "Green", iif(Fields!TrueUpPercQ1.Value =0, "White","Red")))))

You may want to tweak this to suit your need. Also make sure the number of closing brackets are correct. This is the way the IIF is used. Hope it helps. Let me know.

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

1 2 3 4 5 6

|||

Iif would work, but I think switch would be better

Try this...you have to have even number of arguments for it to work correctly. The first is your statement to evaluate to true then the second argument is executed, if false it proceeds to the next (3rd) argument until something evaluates as true.

=SWITCH(Fields!TrueUpPercQ1.Value <=0.05, "Red", Fields!TrueUpPercQ1.Value < 0, "Green", Fields!TrueUpPercQ1.Value =0, "White")

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
--
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
Thanks
You will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

Concatenating Multiple Reports into One Output

Hello,
We have a number of different reports (all separate RDLs). We want to allow
the users to select one or more reports to generate. Basically, we want the
user to select a number of different reports into a report group, and then
generate the entire group of reports.
First question: is it possible to generate multiple reports at the same
time?
Next question: is it possible to output all the different reports into one
long report with each individual report separated by a page break?
Thanks.Hi Oliver,
If you implement a custom multiple selection box in your user interface,
what you want is possible, I think.
Create a list box. Put the subreport field inside the list box. Set the
dataset for the list box.
Hope this helps.
Regards,
Cem Demircioglu
"Oliver" <oliver@.nospam.com> wrote in message
news:%23UaqGLvCFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hello,
> We have a number of different reports (all separate RDLs). We want to
> allow
> the users to select one or more reports to generate. Basically, we want
> the
> user to select a number of different reports into a report group, and then
> generate the entire group of reports.
> First question: is it possible to generate multiple reports at the same
> time?
> Next question: is it possible to output all the different reports into one
> long report with each individual report separated by a page break?
> Thanks.
>|||Oliver,
I think that you can do this, however it is not very pretty.
First create a "master" report. The master report will have a bunch of
subreports, one for each report the user could display. The master
report will also have a series of parameters, one for each subreport,
with the values Yes & No. In each subreport set some custom Visibility
expression like "=iif(Parameters!1stReportName.Value = "Yes", true,
false)" This will display the subreport based on the value of the
parameter.
If you put each subreport in a separate group of a table, you can force
a page break at the end.
hopefully this will give you some ideas