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 Line Breaks

There has to be a simple solution to this, but I can't find one. I have several fields dropped onto a report which display the relevant company address for that customer. But, some of the addresses don't have the full itinary, some may not have state or country where other may have. How can I implement a conditional statement which will remove remove the line break if the content is present. Or vice versa if no content is there, how can I not display that field?

Thanks in advanceIn the suppress event write this

If IsNull({filed}) then True;

Conditional Formating in Reporting Services 2005

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

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

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

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

Stefoon

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

Shyam

|||

Hi Shyam,

you are right. But it does not work either.

Stefoon

conditional detail sections

i have 3 detail sections (assume detail prints 5 lines)

detail a - prints every line
detail b - has to print n blank lines if detail a prints < 10 lines otherwise nothing
detail c - has to print at 11th line always

i cant have this at footer because another detail e and detail f

Thanksi have 3 detail sections (assume detail prints 5 lines)

detail a - prints every line
detail b - has to print n blank lines if detail a prints < 10 lines otherwise nothing
detail c - has to print at 11th line always

i cant have this at footer because another detail e and detail f

Thanks

What I am getting from this is :
You want 'detail a' section to be 10 lines in height even if it contains less than 10 records.

If you want this only once on each page then do the following:
Remove your 'details b' section.
Make Page Header 10 lines in height approx.
Go to Page Header's properties in 'Format' and Check 'Underlay following sections'.

If you want this effect more than once on each page then you can group your records and do the same settings as mentioned above to that group header.

Just keep in mind you have to remove 'detail b' section.

Concatenating SQL query results on one line.

Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.Originally posted by bigfootguy
Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.

Hi BigFoot,

Since SQL Server does not support Cross-Tab constructs, you will have to do some more work. I worked out a solution, but for the Customers and Orders table in the NorthWind demo database; so please translate my answer into your problem.

First of all, you have to know, how much Orders you may expect at least. You can query the actual maximum by

SELECT MAX(Num)
FROM (SELECT COUNT(*) AS Num, CustomerID
FROM Orders
GROUP BY CustomerID) T

As much orders you expect, as much views you have to create:

1) CREATE VIEW Order1 AS SELECT MAX(orderid) AS ID, customerid FROM Orders GROUP BY customerid

2) CREATE VIEW Orders2 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders1 WHERE orders.CustomerID = Orders1.CustomerID AND Orders.OrderID < Orders1.ID GROUP BY orders.CustomerID

3) CREATE VIEW Orders3 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders2 O WHERE orders.CustomerID = O.CustomerID AND Orders.OrderID < O.ID GROUP BY orders.CustomerID

Got the point? Select one or no order by customer per view, excluding the orders already selected in earlier views.

Having created those views, you may select you required result as :

SELECT C.CustomerID, C.CompanyName, O1.ID AS Order1,
O2.ID AS Order2, O3.ID AS Order3
FROM Customers C LEFT OUTER JOIN
(Orders1 O1 LEFT OUTER JOIN
(Orders2 O2 LEFT OUTER JOIN
Orders3 O3 ON O2.CustomerID = O3.CustomerID) ON
O1.CustomerID = O2.CustomerID) ON
C.CustomerID = O1.CustomerID

This works fine if your expected number of orders can be limited. If not, you will have to write a stored procedure returning your recordset.

Cheers :p|||If your hors_owners table has some field for categoring the owners (say, owner_type) for each hors, you can write a cross-tab query that will place each owner_type in its own column. You could use any type description you want, as long as each hors has at most one of each type. For example, "Primary_Owner", "Secondary_Owner", "Investor", or even an ID like "1", "2", "3"... If you search books on-line for "Crosstab", they show a good example of how to write such a query. If you still have problems, post them to the forum.

If you can't create an owner_type field, well that that is a "hors of a different color". (I couldn't resist...). This is one of those rare situations where I would recommend using a cursor, because you won't need to hard-code the number of owners. If you aren't returning hundreds or thousands of hors records, then consider putting the cursor logic in a user-defined function named something like "udf_HorsOwner_String". Your end-query could then be as simple as:

Select *, dbo.udf_HorsOwnerString(hors_id) from tbl_hors

If you need more guidance, post again when you have an idea of what direction you want to take with this.

blindman