Page break and on last record problem

Hello,

In crystal these are available-

1- Conditional Page break 'OnLastRecord'- I have few groups and if I put page break it breaks on each group even if that group has 2 rows but I want a break at the end of the page. So is there conditional page break in SSRS 2005.

2- I am also having problem with controlling Number of lines in a page, few pages are very long as compared to others, though when export to pdf it gives according to the page size set, but not while viewing, is there any work around to this in SSRS 2005.

I will appreciate any suggestion or comment.

regards

1 - this might help you get started http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1682115&SiteID=1 -- if not, what have you tried so far?

2 - when you say "few pages are very long as compared to others" you have to specify: what output format are you talking about? PDF uses printable page size, HTML uses interactive page size, Excel is different because it's allowed to have as many rows as are within an explicit page break set by your code.

I have tried to define "explicit" and "implicit" page breaks separately; see the grey shaded note close to the beginning of this post: http://spacefold.com/lisa/post/Reset-On-Group-(Page-X-of-XX-in-a-group%2c-within-a-total-Y-of-YY-for-the-report)%2c-SQL-Reporting-Services-Style-.aspx. You will also find something similar referenced in the thread to which I linked above.

The difference between behavior of "implicit" page breaks for different formats isn't a bug requiring a "workaround"; it's a feature <s>. Different formats do have different requirements and that's a fact of output life.

So if you want page breaks based on some condition which is external to formatting requirements for a given output format, IOW consistent across output formats, you need to define what that condition is explicitly.

>L<

|||

Thanks for the reply.

I think I am still having problem.

Here is what I am doing-

I have three groups - 1-Customer, 2-Department,3-Priority and the details.

1- need to break if customer = Inactive

2- need to break if Department = Closed

3- need to break Priority = normal

So I made first group - Group1(Customer) then inside that I made another groupd Group1A(for customer) without any header and footer and changed the Group expresseion with a boolean parameter 'PageBreak' as is mentioned in the article and similarly did the for other groups but still no luck and it is breaking with the Group. Please let me know if I am following this correctly.

I am hiding the boolean parameter as I don't want to prompt it.

thanks

|||

I think you are mixing up a couple of things. The idea of using a parameter had nothing to do with data-based conditional page breaks <s>.

That example I pointed you to had a condition that came in from the outside, so it was a parameter. Your condition has to do with your data, and I didn't mean you to emulate the parameter part . I'm sorry about this...

I wanted you to see the idea of the nested groups in which one group handles your real group break and the other one is responsible for the conditional page break, by grouping on what may be a completely unrelated expression. OK?

I don't have your exact data set but I have created an example with a completely arbitrary page break that is caused by a data condition, instead of the external (parameterized) condition in the other example I pointed to. I will tell you what I did and maybe this will be a better example for you. It isn't going to be *exactly* what you need. But it should show you what you have to do, depending on the effect you're after.

1. I have a table of orders for customers with locale data. I did a query out of this data that looks like this:

Code Snippet

SELECT Customer, Locale FROM OrderHeader ORDER BY Customer

2. In this dataset, let's say I wanted to group by the customer's last name. I only want page breaks if the Locale starts with "N". (I told you it was totally arbitrary! )

3. I set a group on a "normal" group break expression -- in this case

Code Snippet

=Fields!Customer.Value

I set this group to repeat headers since that happened to be where the headers were.

4. I create an outer group on the following, non-normal expression:

Code Snippet

=IIF(LEFT(Fields!Locale.Value,1) ="N",
Fields!Customer.Value,
False)

... and this is the group that has the page break.

Does this help you understand better? The point is, the outer group is going to force a page break when you provide the group break expression with the value that is actually changing, which I've done in the first argument to IIF() here. In the second argument, the value False isn't changing, and the outer group doesn't break.

>L<

|||

Thanks a lot, I thing I got it.

Is there anything like 'OnLastRecord' in SSRS.

and is there a way to get a certain number of rows in a page.

thanks

PAD and Left justify in T SQL

Hi,
Is it possible to PAD and justify output from a select statement? I have to
build an input record from two fields in a table that I am concatenating
together. The result is then going to be passed as a parameter value to a
dll. The dll is looking for input of the form
Field Name position Length
record type 1 1
ClaimID 2 17
some of the ClaimID's are less than 17 characters in length
typical output (an underscore here denotes a space)
Record # Data
1 C_______1234567890
so I need to pad the '1234567890' value with 7 spaces to the left.
At the moment I am doing this after the SELECT from SQL Server but I
wondered if I could do this in the actual SELECT itself?
Any help appreciatedYes, but it is also possible to do this in the DLL or elsewhere in the
presentation area.
(As a demonstration):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1234567890), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(1565), 17)
UNION ALL
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(12345678901234567), 17)
(In your query):
SELECT 'C' + RIGHT(REPLICATE('_', 17) + RTRIM(ClaimID), 17)
So, as long as claimID is never more than 17 characters, this will work. If
you exceed 17, you will trim off the leading character(s).
A
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Joe,
Is this what you're asking for:?
DECLARE @.VAR VARCHAR(50)
SET @.VAR = '1234567890'
SELECT REPLICATE(' ',7) + @.VAR AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Or...if char:
DECLARE @.VAR CHAR(50)
SET @.VAR = '1234567890'
SELECT RTRIM(REPLICATE(' ',7) + @.VAR) AS 'NEW VALUE'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have
> to
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||This should get you going. Add 17 spaces to right of the ClaimID, and then k
eep the 17 right.most
characters:
SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
FROM
(
SELECT 'C' as c1, '1234567890' AS c2
) AS tbl
I prefer doing this stuff in the client app, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
> Hi,
> Is it possible to PAD and justify output from a select statement? I have t
o
> build an input record from two fields in a table that I am concatenating
> together. The result is then going to be passed as a parameter value to a
> dll. The dll is looking for input of the form
> Field Name position Length
> record type 1 1
> ClaimID 2 17
> some of the ClaimID's are less than 17 characters in length
> typical output (an underscore here denotes a space)
> Record # Data
> 1 C_______1234567890
> so I need to pad the '1234567890' value with 7 spaces to the left.
> At the moment I am doing this after the SELECT from SQL Server but I
> wondered if I could do this in the actual SELECT itself?
> Any help appreciated
>|||Thank you for the replies , its working well, without help from you guys I
think I would be out of a job.
"Tibor Karaszi" wrote:

> This should get you going. Add 17 spaces to right of the ClaimID, and then
keep the 17 right.most
> characters:
> SELECT c1 + (RIGHT(REPLICATE(' ', 17) + c2, 17))
> FROM
> (
> SELECT 'C' as c1, '1234567890' AS c2
> ) AS tbl
> I prefer doing this stuff in the client app, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:55671CC5-84FD-4580-9E30-3FECB495F2DF@.microsoft.com...
>|||> Thank you for the replies , its working well, without help from you guys I
> think I would be out of a job.
I've often wondered if there is an easy way to divert portions of salaries
of the people we help, then I wouldn't need a job. :-)|||If you're anything like me you couldn't stop this even if you wanted to or
was rich enough to. We'd get bored ;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> I've often wondered if there is an easy way to divert portions of salaries
> of the people we help, then I wouldn't need a job. :-)
>|||rest assured my salary sucks - perphaps the newsgroup could introduce a
voluntary donation to a charity of choice for really good advice.
"Jerry Spivey" wrote:

> If you're anything like me you couldn't stop this even if you wanted to or
> was rich enough to. We'd get bored ;-)
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:eOrObWZ2FHA.2604@.TK2MSFTNGP12.phx.gbl...
>
>sql

P2P Replication Conflicts

Can someone who has had direct experience with this tell me exactly what happens when a conflict (updating same record on two nodes at the same time) occurs in a P2P replication topology? Does the Dist. Agent throw an error? More importantly does the replication set continue to replicate the articles after any error occurs?

Thanks,

Derek

the distribution agent(s) fail. They won't start again until you manually fix the error. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com wrote in message news:e01a768f-ced3-43c1-854f-949ddbd00a29@.discussions.microsoft.com... Can someone who has had direct experience with this tell me exactly what happens when a conflict (updating same record on two nodes at the same time) occurs in a P2P replication topology? Does the Dist. Agent throw an error? More importantly does the replication set continue to replicate the articles after any error occurs? Thanks, Derek|||Peer-To-Peer is transactional replication from everyone to everyone. Transactional replication does not have any capability to detect or resolve conflicts. Therefore, you have to architect the solution such that conflicts can not occur.|||Actually an update "conflict" like the one you describe will not be detected. The last update in will be the one which persists in each peer. An attempt to update rows which don't exist, or delete rows which don't exist, or pk collisions will cause the distribution agent to fail and you will have to manually fix them unless you run in the continue on data consistency errors profile. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "Hilary Cotter" wrote in message news:eZQBYAHkGHA.1564@.TK2MSFTNGSA01.privatenews.microsoft.com...
> the distribution agent(s) fail. They won't start again until you manually
> fix the error. >
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions. >
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html >
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com > > >
> wrote in message
> news:e01a768f-ced3-43c1-854f-949ddbd00a29@.discussions.microsoft.com...
> Can someone who has had direct experience with this tell me exactly what
> happens when a conflict (updating same record on two nodes at the same
> time) occurs in a P2P replication topology? Does the Dist. Agent throw an
> error? More importantly does the replication set continue to replicate the
> articles after any error occurs?
> Thanks,
> Derek
>

overwrite a record on the subscriber

Hello. I have a table which I replicate to server B. Does it pose any
problems if I update the table Server B, then replication overlays the rows
a minute later? Someone told me that replicated tables keep track of the
last date/time the row was updated, but I thought that was only the
published table. Is this true?
Thank you,
Steve
You are free to update tables on the subscriber if the are part of a
transactional or snapshot publication.
With merge replication the change will make their way back to the publisher.
If you insert records with transactional replication on the subscriber you
may get a primary key violation. If you are ok with this, you can change
your profile of your distribution agent to continue on data consistency
errors. To do this expand Replication monitor in EM, expand the Replication
Agents folder, expand distribution agents folder, right click on your
distribution agent, and select agent profiles. Then select the continue on
data consistency errors profile. Stop and start your distribution agent.
Keep in mind you have lost database consistency between your publisher and
subscriber.
For most replication solutions this is NOT a good thing, but your particular
solution might benefit from it.
"SteveS" <ssinger@.trendmls.com> wrote in message
news:%23uznj2cFEHA.3080@.tk2msftngp13.phx.gbl...
> Hello. I have a table which I replicate to server B. Does it pose any
> problems if I update the table Server B, then replication overlays the
rows
> a minute later? Someone told me that replicated tables keep track of the
> last date/time the row was updated, but I thought that was only the
> published table. Is this true?
> Thank you,
> Steve
>

Over-ride a trigger

I have a table which, until now had an update trigger only.
That trigger was used to record the record as it existed BEFORE the change,
and who made the change. It works fine.
Now I have a situation where a program will INSERT a row into the table
(which now has a new field). The value of the field is calculated. I have
two choices,
1) Re-write a lot of code to calculate the value before the insert
2) Write a simple insert trigger to calculate the value and update it in
the table.
Problem:
If I use the insert trigger to update the value, I am doing an update. The
update trigger will fire. I do not want the update trigger to fire when this
operation is performed.
Is there a way I can add code to the Insert trigger to tell it that when it
updates the value it should prevent the update trigger from firing'
Thanks.Roger,
If the value is calculated:
Can you use a calculated column?
Can you calculate the value in the select statement?
AMB
"Roger Twomey" wrote:

> I have a table which, until now had an update trigger only.
> That trigger was used to record the record as it existed BEFORE the change
,
> and who made the change. It works fine.
> Now I have a situation where a program will INSERT a row into the table
> (which now has a new field). The value of the field is calculated. I have
> two choices,
> 1) Re-write a lot of code to calculate the value before the insert
> 2) Write a simple insert trigger to calculate the value and update it i
n
> the table.
> Problem:
> If I use the insert trigger to update the value, I am doing an update. The
> update trigger will fire. I do not want the update trigger to fire when th
is
> operation is performed.
> Is there a way I can add code to the Insert trigger to tell it that when i
t
> updates the value it should prevent the update trigger from firing'
> Thanks.
>
>|||We cannot use a calculated column as the values must not change and the
values used to calculate can change over time.
We are currently using select statements that calculate but the process is
labourious and complex so we are trying to force consistent data with lower
cpu utilization (One insert is read many many times)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B0B63AC0-5557-4C66-83DE-BBFC526E8C44@.microsoft.com...
> Roger,
> If the value is calculated:
> Can you use a calculated column?
> Can you calculate the value in the select statement?
>
> AMB
> "Roger Twomey" wrote:
>|||Roger,
If I understood, you calculate the value during the insert but you want it
to be static after it is done?, In this case you can stop the update trigger
.
...
if update(calculated_column) return
...
If you do not use bulk insert, then I would recommend to insert the row
using a stored procedure. If as you said, the calculation is labourious and
complex, doing it in a trigger will make the transaction longer and will
impact the performance of the engine and scalability of the db.
AMB
"Roger Twomey" wrote:

> We cannot use a calculated column as the values must not change and the
> values used to calculate can change over time.
> We are currently using select statements that calculate but the process is
> labourious and complex so we are trying to force consistent data with lowe
r
> cpu utilization (One insert is read many many times)
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:B0B63AC0-5557-4C66-83DE-BBFC526E8C44@.microsoft.com...
>
>|||That is true, but the impact will only occur on insert, not on every read.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3CF33806-F33D-433E-8305-0450AF8E938B@.microsoft.com...
> Roger,
> If I understood, you calculate the value during the insert but you want it
> to be static after it is done?, In this case you can stop the update
> trigger.
> ...
> if update(calculated_column) return
> ...
> If you do not use bulk insert, then I would recommend to insert the row
> using a stored procedure. If as you said, the calculation is labourious
> and
> complex, doing it in a trigger will make the transaction longer and will
> impact the performance of the engine and scalability of the db.
>
> AMB
> "Roger Twomey" wrote:
>|||Thanks for the tip (If update(calculated_column) return)
I think that should work.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3CF33806-F33D-433E-8305-0450AF8E938B@.microsoft.com...
> Roger,
> If I understood, you calculate the value during the insert but you want it
> to be static after it is done?, In this case you can stop the update
> trigger.
> ...
> if update(calculated_column) return
> ...
> If you do not use bulk insert, then I would recommend to insert the row
> using a stored procedure. If as you said, the calculation is labourious
> and
> complex, doing it in a trigger will make the transaction longer and will
> impact the performance of the engine and scalability of the db.
>
> AMB
> "Roger Twomey" wrote:
>|||On Mon, 28 Feb 2005 13:07:46 -0500, Roger Twomey wrote:

>Thanks for the tip (If update(calculated_column) return)
>I think that should work.
Hi Roger,
That will only work if the calculated_column is never updated from other
sources than the insert trigger.
Other options to investigate are:
* Check if it's possible to disable nested triggers (note: this can only
be done at the server level; it will affect all databases on the
server).
* Checking the value of TRIGGER_NESTLEVEL() (or maybe even the value of
TRIGGER_NESTLEVEL(object_ID('upd_trigger
'))).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)