Page Break after sub report - Not by design

All,

I have a report for a Quote print that has 1 table with 1 group in it by Quote #. The detail level is showing the line items on the quote and a second detail line for a sub report to show any notes. If the quote has 1 line on it, I am always getting a page break after the sub report even if it doesn't display anything.

The report shows the following:

Note each of these will contain a rectangle that the text boxes are displayed in unless it is a sub-report then no rectange.

Group header 1 - Address information

Group header 1 - contains a sub report for some notes

Group header 1 - Opening paragraph

Group header 1 - Blank line

Group header 1 - contains the line headings

Detail - contains the line details

Detail - contains the sub report for line notes

Detail - blank line for spacing

Group footer 1 - hidden based on expression

Group footer 1 - hidden based on expression ** One of these 2 lines will print based on the expression

Group footer 1 - sub report for more notes

Group footer 1 - Closing paragraph

Group footer 1 - Signature area

Group footer 1 - sub report for last page of terms / scope notes. NOTE: This sub report also contains a table and the table has a page break before so that I can get these on another page.

So, after # 8 above there is a page break. The remaining footers before # 14 easily fit on the rest of the page. The group 1 also has a page break at the end so it breaks between quotes. I have tried to insert a page break on the rectangle for # 13 and remove the page break on the sub report in # 14 but although everything fits it doesn't page break before the sub report.

It looks like it always page breaks after the last page in the sub report.

Any ideas on how to get around this?

Thanks in advance,

Sherry

Try this:
1.- In the sub report (on every List or Rectangle you have):
1.1.- Right click, Properties.
1.2.- uncheck "Insert a page break after this list".
1.3.- Click on "Edit details group"
1.4.- uncheck "Page break at end".|||

Thanks for the feedback. I had tried that and then it wouldn't page break.

I did resolve it although I feel the original way should have worked.

I created another Group 1 for the same grouping clause (Quote #). This pushed my original grouping to Group 2. I then put a page break after Group #2. I only had a group footer for Group # 1 with the sub report in it. I had to take the sub report page break before off.

Thanks again,

Sherry

Padding Issue

We are creating hierarchical report showing line of sight for given Organization. The report data is tree structure. Each node is a row in the report. Each row in the report is nested by using padding expression. Padding expression is based on Node Level. When we print report or create PDF padding is removed and all the rows appear aligned. It looks like reporting Services treat padding as white space. We tried several options like using gif images and rectangle but failed.

The report appears fine when we export to Web Archive, the working sample and sample with issue are as attached.

This issue occurs both in report viewer control and report services url.

Does "Padding expression" mean that you insert whitespace based on the node level?

There is another way of doing this: Every textbox has a Padding.Left property. Assuming that the node level is available as a numeric field value, you could set the Padding.Left property dynamically: =CStr(4 * Fields!NodeLevel.Value) & " pt"

-- Robert

|||

Thanks for the reply

I have tried both padding.left and padding.right. When report is rendered padding appears fine but on print or export to pdf, padding is removed.

|||

Which version of Reporting Services are you using? Did you install the latest service packs for that version?

-- Robert

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

Overriding any mdx statement using product line 'A' to also include product line 'B'

Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'? I started to look at the Scope keyword, but I'm at a loss for understanding it just yet.

The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Q:Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'?

A:

You can put set in Scope:

Scope ({[Product].[Product Line].[Accessory], [Product].[Product Line].[Components]});
This = 100;
END SCOPE;

Q: The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Mosha fixed me here. I thought it is hard to do, but he showed in the following post.

Vidas Matelis

|||

Well, you can always do the following:

Accessory = Accessory + Components;

But this will give you double counting of Components at the All level. To make this apply after 1/9/2007 - you can do:

SCOPE [1/9/2007] : NULL;

Accessory = Accessory + Components;

END SCOPE;

|||

Using the unique name for all of those items and then adding the scope script works, thanks.

Is there anyway to tackle the 'all' value being incorrect because of the duplication?
I tried to add another scope, but it didn't help - probably because all is always the aggregate of its children:

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

One way to deal with 'All' would be to do

FREEZE [All Products];

Before writing assignments on levels below All in Products dimension. This, of course, assumes that there are no other changes which may affect All further in the script.

|||Sweet, thanks!|||Searching through the forums, I have found that I have a use for this as well. The only exception is I need the calculation to work for certain calculated members and not all measures and calculated members. I tried adding another SCOPE statement with my specific measures, but it didn't work as I expected.
Here's an example of the code I have that works for all measures. It would be appreciated if I could get this updated to work for 3 specific measures. thanks

FREEZE [All Products];

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

FREEZE [All Products];

SCOPE ({Measure1, Measure2, Measure3}, [1/9/2007] : NULL);

Accessory = Accessory + Components;

END SCOPE

|||Works perfect. I realized I was trying to use calculated members instead of the measures in which they were derived.

Thanks for the help.

Overlay of textboxes are not rendering properly

In the page header I have a rectangle that has 2 textboxes on the same line and both are the full length of the page. One textbox has text centered and the other has text right justified. When I render report in Studio or print it, the text appears on the same line and justified like I want, but when the report is rendered when I call the report by URL, the textboxes wrap, like it's taking the width of each textbox in account. Has anyone seen this? Any suggestions on how to overlay text on the same line and the text keep their own individual characteristics?

I do not believe that you can overlap two textboxes. One will push the next one down to the next line. Can't you make one text box only long enough to allow the text that you need, then Format that text box ittself to align Format-Center in Form-Horizonally. Create the second text box, only as large as you need to hold that text that you are displaying. Then put that text box all the way to the right of your page and align the text in it to right.

|||Yes, that sounds like that would work, but how do you format-center in the form-horizonally?|||Create the first text box and make it only large enough to display the text that you need it to display. Click on the text box itsself to select it, go up to the menu bar and click format, slide down to center in form, select horizontally. This will center that text box in the middle of your page. Then you could actually choose text align in your properties and middle align the text within the text box itsself if you needed to do that. Hope this helps.|||

Kim,

Thanks for trying, but that won't work for me because of a couple reasons: 1) The text is dynamic, so I have no idea on how wide to make my textbox. 2) Even if my text is static and I shorten them up and center the one text box, they still overlap.

Overlay charts

Hello all.
I need to overlay two charts (column & line) & I keep seeing references
to this technique on these groups but no explanation of how to do it
(nor in the help). At the moment when I overlay charts the top one is
visible but not the bottom on,e & I can't see how to make the
background transparent.
Has anyone does this before?
All help appreciated.
Cheers
AustinHTML renderer (which is used by report viewer in Report Manager) does not
support overlayed controls. Although if you render such report to pdf or jpg
you will get charts overlayed.
"AustinRathe" <austinr@.gmail.com> wrote in message
news:1115648678.321251.127760@.o13g2000cwo.googlegroups.com...
> Hello all.
> I need to overlay two charts (column & line) & I keep seeing references
> to this technique on these groups but no explanation of how to do it
> (nor in the help). At the moment when I overlay charts the top one is
> visible but not the bottom on,e & I can't see how to make the
> background transparent.
> Has anyone does this before?
> All help appreciated.
> Cheers
> Austin
>

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph

> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.