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

Package hanging on OLE DB Command with large result sets

I have an SSIS package (SQL 2005 SP2 and Visual Studio SP1) that does the following:

OLE DB Source --> Conditional Split --> OLE DB Command #1 --> OLE DB Command #2

The source reads from database A. Each row is variable-width and up to several KB wide, including two ntext columns.

Command #1 executes a stored proc in db A, using a bunch of inputs and two output parameters.

Cmd #2 executes an update in db B, using the two output params from cmd #1 as inputs.

When the rowset size is small, around 500, everything works fine.

However, when the rowset size is larger, around 5000, SSIS hangs when trying to execute cmd #2. The profiler shows that none of the cmd #2 updates are ever executed. No error messages are produced, and the connection never times out -- it just hangs forever.

If I replace the cmd #2 updates with a simple select, everything works fine. If I replace it with a stored proc that does an update, it hangs.

The work-around I came up with was to create a new table in db B, and do inserts into the table, but unless I'm missing something, this still seems like a bug...

Do you have the RetainSameConnection property set to true on the OLE DB connection manager you are using? Try that if not.