Parameter label field

How do I refer to the label field of a parameter? I have a parameter named
Group with a lable field of 'HR' and value field of 23. I'm puting a text
box at the top of my report with the parameters in it. I dont want to
display '23' (which is what Parameters!Group.Value returns), I want to
display 'HR'
Thanks,
DougI almost hate to tell you this.
Parameters!Group.Label
The expression builder doesn't show you this but just type it in.
Bruce L-C
"Doug Stiers" <doug@.nospam.com> wrote in message
news:OhP5onOlEHA.3356@.TK2MSFTNGP15.phx.gbl...
> How do I refer to the label field of a parameter? I have a parameter named
> Group with a lable field of 'HR' and value field of 23. I'm puting a text
> box at the top of my report with the parameters in it. I dont want to
> display '23' (which is what Parameters!Group.Value returns), I want to
> display 'HR'
> Thanks,
> Doug
>|||I cant believe I didnt think of that. Thanks.
Doug
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23DCmz7OlEHA.596@.tk2msftngp13.phx.gbl...
> I almost hate to tell you this.
> Parameters!Group.Label
> The expression builder doesn't show you this but just type it in.
> Bruce L-C
> "Doug Stiers" <doug@.nospam.com> wrote in message
> news:OhP5onOlEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > How do I refer to the label field of a parameter? I have a parameter
named
> > Group with a lable field of 'HR' and value field of 23. I'm puting a
text
> > box at the top of my report with the parameters in it. I dont want to
> > display '23' (which is what Parameters!Group.Value returns), I want to
> > display 'HR'
> >
> > Thanks,
> > Doug
> >
> >
>

Parallel activations with GET CONV GROUP

Hey guys. I'll try to keep this as short as possible.

Was testing out some thresholds with a simple service broker application and ran into something interesting that I'm trying to find a workaround to. I setup a simple service on a single instance, and am transmitting messages to/from the same instance. I assigned a procedure to be activated on the queue, with a max of 20 parallel activations.

The procedure originally was reading messages off the queue by following a process with major steps outlined here:

Start loop until no more messages to process Call get conv. group with 3000 timeout into variable x If group retrieved, receive top 1 from queue where conv_group = x
If no group, exit
Process message(s) received (simply insert some of the msg data into user table, then waitfor 1 second to simulate doing some work) for the given group Loop to get next group and messages

So, with this type of configuration, I sent off a few thousand messages to the queue and monitored the queue count, user table count, and activated tasks count. To my surprise, despite the fact that there were thousands of messages in the queue waiting to be processed, only a single task was processing data, pulling about 1 per second.

I tried over and over with different values in the waitfor, and no additional tasks got spun up until I had a waitfor of 6 seconds or more...then only about 3-4 would get spun up at any time in parallel.

Given the way activation occurs, I thought I'd try things without the call to get conv group, and instead simply use a receive without a where clause...so, I rewrote the activation procedure to skip the call to GET CONV GROUP, and simply perform a receive without any where clause. I also removed the waitfor delay and simply left it to process messages as fast as it could...

This time, I sent over the same number of messages and checked the same counts, this time noticing that within 20 seconds or so, all 20 parallel instances of the activation procedure were processing messages off the queue, and doing so very quickly. Same occured if I put the waitfor delay back in...

The problem is, that this type of coding doesn't allow for processing related messages, even though it seems to be much better at allowing parallel activations. I tried to rewrite again using 2 types of receive statements (first one without a where clause and next one with a where clause on the conv. group from the first receive after processing the message) to see if that would allow for better parallel activation, however that worked about the same as using the GET CONV GROUP call.

So, if I have an application that mostly does not make use of grouping and want to ensure optimal parallel activation when needed, however also handle the times when related messages arrive and need to be processed together, what can I do? Any ideas?

I have test code scripts that can reproduce this behavior if needed. Thanks up front,

Can you send your scripts to Rushi (dot) Desai (at) microsoft (dot) com so that I can take a look at what is going on? Activation should not start tasks more frequently than once every 5 seconds whether you use GET CONVERSATION GROUP or RECEIVE directly. Given that, starting 20 concurrent queue readers should take 100 or more seconds.|||

Hi Rushi...you're correct, it appears that the tasks are getting started about 1 every 5 seconds at best. Not sure how I got the original 20 seconds I mentioned above, but I can't seem to reproduce that no matter what I do...must have been a brain mix-up on my part, sorry.

I can still forward you the scripts, but I'm mostly curious as if there's anything that can be done to 'force' the same type of activation when using the GET CONV GROUP call, then a RECEIVE with a filter on the given group? If my queue is getting filled with about 50 messages a second, 99.9% of which will contain unique conversation_group values, and each message takes about 1/2 second to process, it seems that the best I can get for parallel activation is about 3, and that isn't coming close to handling staying in front of the arrival rate...

Thanks,

|||

You should not notice any difference in the behavior of activation if you do:

WAITFOR (GET CONVERSATION GROUP ...)
... something that takes less than a second or two to complete ...
RECEIVE ... WHERE conversation_group_id = ...
... process message for long time...

and

WAITFOR (RECEIVE ... )
... process message for long time...

50 msgs on unique conversation groups with about 1/2 second per message would perform as follows (roughly):

t = 0: Start first task
5 < t < 10: Consumed 10 messages, start second task
10 < t < 15: Consumed 30 messages; start third task
15 < t < 20: Consumed 50 messages

Unfortunately, in SQL Server 2005, the 5 second timeout cannot be adjusted by the user and therefore user cannot adjust the "responsiveness" of activation. There is a tradeoff to be made between how quickly activation should launch additional task and how much time it should let existing tasks take to drain the queue. In future releases it would be nice to allow the user to control responsiveness. I would recommend that you write to product feedback (http://lab.msdn.microsoft.com/productfeedback/) so that we can open an issue to resolve this in the next release.

Current workaround may be using multiple identical services (and queues) and partitioning your dialogs among those services. Each of its queues will be activated independently and you could get more number of tasks started. (We know at least one internal customer who is doing this).

Thanks,
Rushi

|||

Hey again Rushi.

Well, I notice a HUGE difference in activation between those 2 exact scenarios. Using a GET CONV GROUP followed by a RECEIVE with a where clause, I can't get more than a single activation. However, using just a RECEIVE without a where clause and not call to GET CONV GROUP, I get max activations, 1 new activation every 5 seconds or so.

I'm not so concerned about the 5 second activation threshold, as that would be plenty fast enough an activation under load, and once the tasks are activated, they remain until they exit (i.e. queue is empty).

Since I can't attach scripts, I'll post another message immediately following this one with the full-text of the scripts that can easily reproduce this different behavior. To use the scripts:

1) SCRIPT 1 - will create the sample db, broker objects, endpoint, queue, service, etc., including the activation procedure (cp_test1). Notice in the intial creation, the procedure will make use of method #1 from above (i.e. a call to GET CONV GROUP, followed by a RECEIVE with where clause), and simply pulls top 1 from the queue w/ group filter, then inserts into another logging table, then pauses for 1/2 second before continuing.

2) SCRIPT 2 - will simply send @.c number of messages (2000 on post) into the queue, pausing for @.delay (2/100ths of a second on post) time between each send onto the queue

Run SCRIPT 1, then setup another script to simply the count of activated tasks, then start SCRIPT 2. You should notice that despite the fact that messages are arriving much faster than being processed, only a single task is activated no matter how long you wait.

3) SCRIPT 3 - will alter the cp_test1 procedure to use method #2 from above (i.e. no call to GET CONV GROUP, but instead just a call to RECEIVE without where clause). Run this to modify the proc, then start the tests over after all activated tasks have either exited or been killed.

After running script 3, you'll notice that tasks get activated every 5 seconds up to max activations...

|||

--

SCRIPT 1

--

/*
use master
drop database sbTest
drop endpoint ep_ServiceBroker_tcp
*/
execute as login = 'sa'
go

create database sbTest
go

use sbTest
go

create master key encryption by password = 'jpaiweqf2q938hq2-3980nhf9piunfwpojf';
alter master key add encryption by service master key;
go

create endpoint ep_ServiceBroker_tcp
state = stopped
as tcp (listener_port = 4022, listener_ip = all)
for service_broker (authentication = windows, encryption = disabled,
message_forwarding = disabled);
go

alter database sbTest set enable_broker;
alter database sbTest set error_broker_conversations;
go

create message type mt_test1 validation = well_formed_xml;
go

create contract ct_test1 (mt_test1 sent by initiator);
go

create procedure dbo.cp_test1
as
declare @.conversation_group_id uniqueidentifier,
@.conversation_handle uniqueidentifier,
@.msg_type_name nvarchar(128)

declare @.messages table (status tinyint, priority int, queing_order bigint, conversation_group_id uniqueidentifier,
conversation_handle uniqueidentifier, message_sequence_number bigint, service_name nvarchar(512),
service_id int, service_contract_name nvarchar(256), service_contract_id int, message_type_name nvarchar(256),
message_type_id int, validation nchar(2), message_body varbinary(max))

-- Loop until we manually break
while 1 = 1 begin
-- Start a transaction to receive the appropriate group
begin transaction;

-- Get the next available conversation group...wait for a few seconds then loop...
waitfor (
get conversation group @.conversation_group_id from dbo.qu_test1),
timeout 3000;

-- If we didn't get anything, break since we're all done and will be re-awoken automatically if needed
if @.conversation_group_id is null begin
if @.@.trancount > 0
rollback transaction;

break;
end

-- Got a group, so process all the messages for said group...
while 1 = 1 begin
-- Receive the next message for the conversation group...notice the where clause in the
-- receive statement, which ensures we only get messages for this group...no need for a waitfor here,
-- as calling the get conversation group will lock that group (so noone else gets any messages for it), and
-- we wouldn't even get to this point if there wasn't a message for this group...
receive top (1) *
from dbo.qu_test1
into @.messages
where conversation_group_id = @.conversation_group_id;

if @.@.rowcount = 0 or @.@.error <> 0
-- If an error occured, there is probably no more messages, or something happened in the receive
break;

-- Get the information that we need to process the message
select @.conversation_handle = conversation_handle,
@.msg_type_name = message_type_name
from @.messages

-- If an error occured (on the other end), or the message is an end conversation message,
-- end the conversation from our end
if @.msg_type_name in('http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error') begin
end conversation @.conversation_handle;
end

-- Stick the message data into our logging table
begin try
insert dbo.zztemp_messages
select status, priority, queing_order, conversation_group_id, conversation_handle,
message_sequence_number, service_name, service_id, service_contract_name,
service_contract_id, message_type_name, message_type_id, validation, message_body, getdate()
from @.messages;

-- Simulate think time for doing some work for a half a second or so
waitfor delay '000:00:00.5'
end try
begin catch
select error_message() as err_msg, error_number() as err_number,
error_line() as err_line, error_procedure() as err_proc;

print 'Caught error ' + quotename(error_message());

rollback transaction;
break;
end catch

delete @.messages
end -- inner - while 1 = 1

-- All done processing this conversation group, commit now...
commit transaction
end -- while 1 = 1
go

if isnull(object_id('dbo.zztemp_messages'), 0) = 0
create table dbo.zztemp_messages (status tinyint, priority int, queing_order bigint, conversation_group_id uniqueidentifier,
conversation_handle uniqueidentifier, message_sequence_number bigint, service_name nvarchar(512),
service_id int, service_contract_name nvarchar(256), service_contract_id int, message_type_name nvarchar(256),
message_type_id int, validation nchar(2), message_body varbinary(max), dt datetime default getdate());
go

-- Create the queue for the source server
create queue dbo.qu_test1 with
status = on,
retention = off,
activation (
status = on,
procedure_name = dbo.cp_test1,
max_queue_readers = 20,
execute as 'dbo'
)
on [default];
go

-- Create the source service
create service sv_test1
on queue dbo.qu_test1
(ct_test1);
go

alter endpoint ep_ServiceBroker_tcp state = started
go

|||

--

SCRIPT 2

--

use sbTest
go

declare @.i int, @.c int, @.delay varchar(25)

select @.i = 0, -- Incremental count...
@.c = 2000, -- Set this to the number of messages to send to the queue
@.delay = '000:00:00.02' -- waitfor delay value to pause on each iteration before sending next message

while @.i <= @.c begin

-- Send a test message to the target service
declare @.msg xml, @.hConversation uniqueidentifier;
set @.msg = N'<message>Test Message to Target</message>';

begin try
begin transaction;

-- Start the conversation
begin dialog conversation @.hConversation
from service sv_test1
to service 'sv_test1'
on contract ct_test1
with encryption = off;

-- Send the message on the dialog
send on conversation @.hConversation
message type mt_test1
(@.msg);

-- Commit the send...
commit transaction;

-- Not going to code for receiving back, as the auto-activated stored procedure handles that
-- NOTE that it also handles ending the conversation that was begun here...
end try
begin catch
-- Show the error information
select error_number() as err_num, error_message() as err_msg,
error_line() as err_line, error_procedure() as err_proc;

if @.hConversation is not null
end conversation @.hConversation

if @.@.trancount > 0
rollback transaction;

end catch

if @.@.trancount > 0
commit transaction;

set @.i = @.i + 1
waitfor delay @.delay
end

|||

--

SCRIPT 3

--

use sbTest
go

alter procedure dbo.cp_test1
as
declare @.conversation_group_id uniqueidentifier,
@.conversation_handle uniqueidentifier,
@.msg_type_name nvarchar(128),
@.error int

declare @.messages table (status tinyint, priority int, queing_order bigint, conversation_group_id uniqueidentifier,
conversation_handle uniqueidentifier, message_sequence_number bigint, service_name nvarchar(512),
service_id int, service_contract_name nvarchar(256), service_contract_id int, message_type_name nvarchar(256),
message_type_id int, validation nchar(2), message_body varbinary(max))

-- Got a group, so process all the messages for said group...
while 1 = 1 begin
begin transaction;

-- Notice not performing a call to GET CONVERSATION GROUP here...
waitfor (
receive top (1) *
from dbo.qu_test1
into @.messages), timeout 3000;

if @.@.error <> 0 begin
rollback tran;
break;
end

if (select count(*) from @.messages) = 0 begin
rollback tran;
break;
end

-- Get the information that we need to process the message
select @.conversation_handle = conversation_handle,
@.msg_type_name = message_type_name,
@.conversation_group_id = conversation_group_id
from @.messages

-- If an error occured (on the other end), or the message is an end conversation message,
-- end the conversation from our end
if @.msg_type_name in('http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error') begin
end conversation @.conversation_handle;
end

-- Stick the message data into our logging table
begin try
insert dbo.zztemp_messages
select status, priority, queing_order, conversation_group_id, conversation_handle,
message_sequence_number, service_name, service_id, service_contract_name,
service_contract_id, message_type_name, message_type_id, validation, message_body, getdate()
from @.messages;

-- Simulate think time for doing some work for a half a second or so
waitfor delay '000:00:00.5'
end try
begin catch
select error_message() as err_msg, error_number() as err_number,
error_line() as err_line, error_procedure() as err_proc;

print 'Caught error ' + quotename(error_message());

rollback transaction;
break;
end catch

-- All done processing this conversation group, commit now...
commit transaction;

delete @.messages
end -- while 1 = 1
go

|||

Yes, you can.

The problem is the inner loop that iterates over messages for a given group until RECEIVE returns empty result set. This kind of processing fools the activation machinery to believe that the activated proc is keeping up with the incomming rate of messages, because of the empty result set.

The solution is to receive ALL available messages for a group (no TOP clause), and then process the resultset, using a cursor:

use sbTest

go

alter procedure dbo.cp_test1

as

declare @.conversation_group_iduniqueidentifier,

@.conversation_handleuniqueidentifier,

@.msg_type_namenvarchar(128),

@.queuing_order INT;

declare @.messages table (status tinyint, priority int, queing_order bigint, conversation_group_id uniqueidentifier,

conversation_handle uniqueidentifier, message_sequence_number bigint, service_name nvarchar(512),

service_id int, service_contract_name nvarchar(256), service_contract_id int, message_type_name nvarchar(256),

message_type_id int, validation nchar(2), message_body varbinary(max))

-- declare a cursor for the @.messages

DECLARE crsMessages CURSOR

forward_only read_only

for

SELECT conversation_handle,

message_type_name,

queing_order

FROM @.messages;

-- Loop until we manually break

while 1 = 1 begin

-- Start a transaction to receive the appropriate group

begin transaction;

-- Get the next available conversation group...wait for a few seconds then loop...

waitfor (

get conversation group @.conversation_group_id from dbo.qu_test1),

timeout 3000;

-- If we didn't get anything, break since we're all done and will be re-awoken automatically if needed

if @.conversation_group_id is null begin

if @.@.trancount > 0

rollback transaction;

break;

end;

-- Got a group, so process all the messages for said group...

-- Receive the next message for the conversation group...notice the where clause in the

-- receive statement, which ensures we only get messages for this group...no need for a waitfor here,

-- as calling the get conversation group will lock that group (so noone else gets any messages for it), and

-- we wouldn't even get to this point if there wasn't a message for this group...

receive *

from dbo.qu_test1

into @.messages

where conversation_group_id = @.conversation_group_id;

if @.@.rowcount = 0 or @.@.error <> 0

-- If an error occured, there is probably no more messages, or something happened in the receive

break;

-- open the cursor over @.messages

OPEN crsMessages;

FETCH NEXT FROM crsMessages

INTO @.conversation_handle, @.msg_type_name, @.queuing_order;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN

-- If an error occured (on the other end), or the message is an end conversation message,

-- end the conversation from our end

if @.msg_type_name in('http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',

'http://schemas.microsoft.com/SQL/ServiceBroker/Error') begin

end conversation @.conversation_handle;

end

-- Stick the message data into our logging table

begin try

insert dbo.zztemp_messages

select status, priority, queing_order, conversation_group_id, conversation_handle,

message_sequence_number, service_name, service_id, service_contract_name,

service_contract_id, message_type_name, message_type_id, validation, message_body, getdate()

from @.messages

WHERE queing_order = @.queuing_order;

-- Simulate think time for doing some work for a half a second or so

waitfor delay '000:00:00.5'

end try

begin catch

select error_message() as err_msg, error_number() as err_number,

error_line() as err_line, error_procedure() as err_proc;

print 'Caught error ' + quotename(error_message());

rollback transaction;

break;

end catch

FETCH NEXT FROM crsMessages

INTO @.conversation_handle, @.msg_type_name, @.queuing_order;

END

CLOSE crsMessages;

delete @.messages

-- All done processing this conversation group, commit now...

commit transaction

end

DEALLOCATE crsMessages

go

HTH,
~ Remus

|||Yep, that makes sense...thanks guys!

Pairing Group

Hello, I have an interesting problem. I have to compare 2 consecutive
records. I'm using Crystal Reports v.10 if that helps. I have a nchar Col4
and a date/time in Col3. I need to compare the date/time of every 2nd of 2
records. The time in the second record of each pair is always a few seconds
,
or a few minutes, greater than in the first record. Col1 and Col2 have
values that are always the same; Col4 varies slightly by the text message.
Any help would be appreciated. Thanks.This would be a self join. For example:
SELECT T1.col1, T1.col2, T1.col3, T1.col4,
MIN(T2.col3)
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.col3 < T2.col3
GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
David Portas
SQL Server MVP
--|||David,
This query is helpful but I suppose I should have added that I need to CALC
a subtraction of the 1st row's date/timestamp from the 2nd row's
date/timestamp to produce an interval value such as :03 (seconds) etc. Is
this possible? Thanks.
"David Portas" wrote:

> This would be a self join. For example:
> SELECT T1.col1, T1.col2, T1.col3, T1.col4,
> MIN(T2.col3)
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.col3 < T2.col3
> GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
> --
> David Portas
> SQL Server MVP
> --
>
>|||SELECT T1.col1, T1.col2, T1.col3, T1.col4,
MIN(T2.col3), DATEDIFF(S,T1.col3,MIN(T2.col3))
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.col3 < T2.col3
GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
David Portas
SQL Server MVP
--|||This was what I needed and it worked great. I marked it a Helpful Post.
Thanks!
"David Portas" wrote:

> SELECT T1.col1, T1.col2, T1.col3, T1.col4,
> MIN(T2.col3), DATEDIFF(S,T1.col3,MIN(T2.col3))
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.col3 < T2.col3
> GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
> --
> David Portas
> SQL Server MVP
> --
>
>

Pagination Issue

I have a report which contains a list box, which in turn contains a
subreport. The list box is set up to group on one of the data set's columns.
There is only one row per group column value. I have not forced new pages
on the beginning and ending of the list box, nor have I specified to keep the
group together. The report was created and previewed using the report
designer, then was deployed. When rendered, the text box group and the sub
report works correctly. The issue is pagination.
When I first render the report, several groups are printed on a single
"Page". When I press the preview button again (in report designer) or export
the report to PDF (in report server), each group and subreport appears on one
page (which is what I want), but a new blank page now appears between groups.
How can I make this report paginate correctly to show 1 group per page and
no blank pages?
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208Jay,
Go into the layout tab of the report, then go up to the Report -> Report
Properties menu. Check your paper size & margins. Also, verify the width
and height of your list box. It could be that RS has re-sized your report to
fit data on it (or just to fit your list box without warning you - it's
really bad about that) and that is causing the extra pages to print. Also,
look at your padding values and border values for the list box and play with
them just to make sure they aren't causing the extra space to be generated
when the report is generated.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Jay Meredith" wrote:
> I have a report which contains a list box, which in turn contains a
> subreport. The list box is set up to group on one of the data set's columns.
> There is only one row per group column value. I have not forced new pages
> on the beginning and ending of the list box, nor have I specified to keep the
> group together. The report was created and previewed using the report
> designer, then was deployed. When rendered, the text box group and the sub
> report works correctly. The issue is pagination.
> When I first render the report, several groups are printed on a single
> "Page". When I press the preview button again (in report designer) or export
> the report to PDF (in report server), each group and subreport appears on one
> page (which is what I want), but a new blank page now appears between groups.
> How can I make this report paginate correctly to show 1 group per page and
> no blank pages?
> --
> Jay P. Meredith
> Senior Software Engineer
> Columbia Helicopters, Inc.
> PO Box 3500
> Portland, OR 97208|||Messing with the margins and object sizes did the trick. I also have a
better understanding about SRS's preview pane in the Report Designer. The
first time you preview a report, the preview is in HTML mode, which does not
support pagination, therefore pagination is not shown. If you click the
preview button a second time, it switches to paged mode, which reflects how
the report looks when exported to a format capable of pagination, such as PDF.
Thanks much for your help.
--
Jay P. Meredith
Senior Software Engineer
Columbia Helicopters, Inc.
PO Box 3500
Portland, OR 97208
"Catadmin" wrote:
> Jay,
> Go into the layout tab of the report, then go up to the Report -> Report
> Properties menu. Check your paper size & margins. Also, verify the width
> and height of your list box. It could be that RS has re-sized your report to
> fit data on it (or just to fit your list box without warning you - it's
> really bad about that) and that is causing the extra pages to print. Also,
> look at your padding values and border values for the list box and play with
> them just to make sure they aren't causing the extra space to be generated
> when the report is generated.
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "Jay Meredith" wrote:
> > I have a report which contains a list box, which in turn contains a
> > subreport. The list box is set up to group on one of the data set's columns.
> > There is only one row per group column value. I have not forced new pages
> > on the beginning and ending of the list box, nor have I specified to keep the
> > group together. The report was created and previewed using the report
> > designer, then was deployed. When rendered, the text box group and the sub
> > report works correctly. The issue is pagination.
> > When I first render the report, several groups are printed on a single
> > "Page". When I press the preview button again (in report designer) or export
> > the report to PDF (in report server), each group and subreport appears on one
> > page (which is what I want), but a new blank page now appears between groups.
> > How can I make this report paginate correctly to show 1 group per page and
> > no blank pages?
> >
> > --
> > Jay P. Meredith
> > Senior Software Engineer
> > Columbia Helicopters, Inc.
> > PO Box 3500
> > Portland, OR 97208

PageBreak after x items for group, different on first page

I am trying to add a page break for table details after x items with x being different for the first page of report.

I am able to add a page break by adding a group to table with group expression =Int((RowNumber(Nothing)-1)/25). But I can't figure out how to do this where groupexpression on page 1 of report = Int((RowNumber(Nothing)-1)/15) and on all other pages =Int((RowNumber(Nothing)-1)/25).

I tried

=IIF(Globals!PageNumber=1,Int((RowNumber(Nothing)-1)/15),Int((RowNumber(Nothing)-1)/25))

but Globals cannot be used in group expression. I also tried putting group expression in a textbox on the report but it will not let me refer to the textbox.

Any assistance is appreciated!
Chris

references: http://msdn2.microsoft.com/en-us/library/ms157328.aspx , http://msdn2.microsoft.com/en-us/library/ms157328(en-US,SQL.90).aspx

Try a group expression like this:

=iif(RowNumber(Nothing) <= 15, Int((RowNumber(Nothing)-1)/15), Int((RowNumber(Nothing)-1-15)/25))

This should result in 15 rows for the first page, and 25 rows on subsequent pages.

-- Robert

Page number reseting

Are there any plans in RS 2005 to have greater control over page numbers with
regard to group breaks?
For example, if we have a multi-page report for each group with page numbers
"page x of y" in the page footer, we would like the page number reset on each
group break so that group 1 may have 3 pages (page 1 of 3, page 2 of 3, page
3 of 3) and group b may have 2 pages, (page 1 of 2, page 2 of 2), etc...
Thank you,You may want to check the following blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
It is not fully achieve what you are looking for - but at least it provides
you a way to reset the page numbers.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:0E32BF23-F294-4E2E-868C-FE5F20154A7A@.microsoft.com...
> Are there any plans in RS 2005 to have greater control over page numbers
> with
> regard to group breaks?
> For example, if we have a multi-page report for each group with page
> numbers
> "page x of y" in the page footer, we would like the page number reset on
> each
> group break so that group 1 may have 3 pages (page 1 of 3, page 2 of 3,
> page
> 3 of 3) and group b may have 2 pages, (page 1 of 2, page 2 of 2), etc...
> Thank you,
>|||Thanks for the reply. I was able to get it to work with custom code shared in
another posting. I would still like to get a reply from someone at Microsoft
on whether or not there are plans to have greater control over page number as
worded in my original post.
"Robert Bruckner [MSFT]" wrote:
> You may want to check the following blog posting:
> http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
> It is not fully achieve what you are looking for - but at least it provides
> you a way to reset the page numbers.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:0E32BF23-F294-4E2E-868C-FE5F20154A7A@.microsoft.com...
> > Are there any plans in RS 2005 to have greater control over page numbers
> > with
> > regard to group breaks?
> >
> > For example, if we have a multi-page report for each group with page
> > numbers
> > "page x of y" in the page footer, we would like the page number reset on
> > each
> > group break so that group 1 may have 3 pages (page 1 of 3, page 2 of 3,
> > page
> > 3 of 3) and group b may have 2 pages, (page 1 of 2, page 2 of 2), etc...
> >
> > Thank you,
> >
> >
>
>|||Having "document sections" within a report with independent page numbers
etc. is under consideration for inclusion in a future release. It won't be
available in the upcoming SQL Server 2005 release.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:6045763F-2BE2-496E-8654-0A07FCBCFBCB@.microsoft.com...
> Thanks for the reply. I was able to get it to work with custom code shared
> in
> another posting. I would still like to get a reply from someone at
> Microsoft
> on whether or not there are plans to have greater control over page number
> as
> worded in my original post.
> "Robert Bruckner [MSFT]" wrote:
>> You may want to check the following blog posting:
>> http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
>> It is not fully achieve what you are looking for - but at least it
>> provides
>> you a way to reset the page numbers.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
>> news:0E32BF23-F294-4E2E-868C-FE5F20154A7A@.microsoft.com...
>> > Are there any plans in RS 2005 to have greater control over page
>> > numbers
>> > with
>> > regard to group breaks?
>> >
>> > For example, if we have a multi-page report for each group with page
>> > numbers
>> > "page x of y" in the page footer, we would like the page number reset
>> > on
>> > each
>> > group break so that group 1 may have 3 pages (page 1 of 3, page 2 of 3,
>> > page
>> > 3 of 3) and group b may have 2 pages, (page 1 of 2, page 2 of 2),
>> > etc...
>> >
>> > Thank you,
>> >
>> >
>>|||Thank you
"Robert Bruckner [MSFT]" wrote:
> Having "document sections" within a report with independent page numbers
> etc. is under consideration for inclusion in a future release. It won't be
> available in the upcoming SQL Server 2005 release.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:6045763F-2BE2-496E-8654-0A07FCBCFBCB@.microsoft.com...
> > Thanks for the reply. I was able to get it to work with custom code shared
> > in
> > another posting. I would still like to get a reply from someone at
> > Microsoft
> > on whether or not there are plans to have greater control over page number
> > as
> > worded in my original post.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> You may want to check the following blog posting:
> >> http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
> >>
> >> It is not fully achieve what you are looking for - but at least it
> >> provides
> >> you a way to reset the page numbers.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> >> news:0E32BF23-F294-4E2E-868C-FE5F20154A7A@.microsoft.com...
> >> > Are there any plans in RS 2005 to have greater control over page
> >> > numbers
> >> > with
> >> > regard to group breaks?
> >> >
> >> > For example, if we have a multi-page report for each group with page
> >> > numbers
> >> > "page x of y" in the page footer, we would like the page number reset
> >> > on
> >> > each
> >> > group break so that group 1 may have 3 pages (page 1 of 3, page 2 of 3,
> >> > page
> >> > 3 of 3) and group b may have 2 pages, (page 1 of 2, page 2 of 2),
> >> > etc...
> >> >
> >> > Thank you,
> >> >
> >> >
> >>
> >>
> >>
>
>|||Dave,
I am trying to do the same thing. Can you post a reply with a link to the
thread with the code you are referring below, I can't seem to find it?
"I was able to get it to work with custom code shared in
another posting."
Thanks.
Shawn Shannon
"Dave Sundell" wrote:
> Thanks for the reply. I was able to get it to work with custom code shared in
> another posting. I would still like to get a reply from someone at Microsoft
> on whether or not there are plans to have greater control over page number as
> worded in my original post.
> "Robert Bruckner [MSFT]" wrote:
> > You may want to check the following blog posting:
> > http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
> >
> > It is not fully achieve what you are looking for - but at least it provides
> > you a way to reset the page numbers.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> > news:0E32BF23-F294-4E2E-868C-FE5F20154A7A@.microsoft.com...
> > > Are there any plans in RS 2005 to have greater control over page numbers
> > > with
> > > regard to group breaks?
> > >
> > > For example, if we have a multi-page report for each group with page
> > > numbers
> > > "page x of y" in the page footer, we would like the page number reset on
> > > each
> > > group break so that group 1 may have 3 pages (page 1 of 3, page 2 of 3,
> > > page
> > > 3 of 3) and group b may have 2 pages, (page 1 of 2, page 2 of 2), etc...
> > >
> > > Thank you,
> > >
> > >
> >
> >
> >

Page number information available in report body

Hi Group,
How can I get the page number while in report body ? I need to show/hide
some controls, depending on the page number.
Any solution is welcome !
Thank you,
CataOn Apr 10, 3:46 pm, "Catalin Magher" <cmag...@.era-environmental.com>
wrote:
> Hi Group,
> How can I get the page number while in report body ? I need to show/hide
> some controls, depending on the page number.
> Any solution is welcome !
> Thank you,
> Cata
Normally, to use Globals!PageNumber, you have to be in the Page Header/
Footer. That said, this link might provide you a work around:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/2eee17d147fb5603/dda039e65c969ce2?lnk=st&q=get+page+number+in+report+body+reporting+services&rnum=1#dda039e65c969ce2
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||This is an older post, but I tried to find a solution in the forum and
found many with the same unanswered question.
This is how I worked the problem. What you are doing is having the
Report call the global function and pass to a string and then you are
calling the function return str and inserting into the textbox!
Add the following code to the Report properties (main menu
REPORT>Report Properities > Code )
Public Function PageNumber()
Dim str as String
str = me.Report.Globals!PageNumber.ToString()
Return str
End Function
Public Function TotalPages()
Dim str as String
str = me.Report.Globals!TotalPages.ToString()
Return str
End Function
Now add the following function calls to empty textboxes
=Code.PageNumber
=Code.TotalPages

Page Number display problem when using Cross Tab

I have created a cross tab report using Crystal Report 9. I have placed the Cross Tab object in Group Header section and in Page Footer i am displaying Page Number as well as Company Name in Page Header.
Now, the columns in Cross Tab exceeds the page and therefore goes on next page. Everything works fine, the only thing is the next page does not display Page Header and Page Footer.
What may be the problem ? I have tried placing Cross Tab object Report Header,Report Footer,Page Header,Page Footer but din't got solution.

Thanks in advance.
Chandrakant Shindehttp://support.businessobjects.com/library/kbase/articles/c2008113.asp

this explains the process

dave