Configuration failing: The path is not of a legal form.

When I push Add in the configuration dialog, I cannot even get to the first page of the configuration wizard, due to the following error, in several solutions (but not in several others). All have at least one existing configuration (necessarily, to get passwords for connections).
**
An error occurred while a new configuration was being added.
Additional Information:
The path is not of a legal form. (mscorlib)

**

PS: How have the rest of you been posting? Passport hasn't been working much this week, so I've been unable to post. Is there a way to post without using passport? Please tell me if so :)

What types of configuration (XML configuration file, Environment variable, etc) do your packages use?|||This is a known issue when you edit the package "standalone",
i.e. without creating the SSIS project. A workaround is to create
the project, add the package to the project, and then edit
the configuration.

We plan to fix this issue for SP1 (it was reported too late to
consider changing this for RTM release).

Thanks,
Michael.|||(Sorry, I have to wade through a lot of script errors and bad passport certificate errors to be able to post to this forum.)
The configuration involved is an environment variable pointing at an XML config file.
The package is in a project in a solution.
However, the solution has two packages, one of which calls the other.
Because of the bug that Execute SSIS task cannot handle relative paths, we have to use a mapped drive to call the package, so the target package is called via a mapped drive fully qualified file path.
I've since discovered that the bug here is that if the package is opened from the mapped drive, even though it is the same file, the Package Configuration Wizard will give this mscorlib error. The workaround for this bug is to be careful and be sure to first close the package, then open it by double-clicking it in the solution, when you need to examine or edit package configurations.
Of course, I hope this bug gets fixed, but truly, it is not nearly as bad as the bug that relative paths get wrecked and fail in the Execute SSIS task.
|||I see - this indeed seems like a different bug.
Could you please open a bug on betaplace?|||re: bug on betaplace
I invested some time trying to get into betaplace, which involved waving my cursor around to find an invisible button I had to click, and then getting to a page which said I would be granted access at some point. I tried several times on successive days, but I never got further than that. I posted my travails several times to the newsgroup, but noone had any suggestions, so I finally gave it up as something that only costs me time, and brought no benefit to anyone (as I was never able to get to a point of actually accessing any information, much less posting new bugs).
Could you open the bug on betaplace? I have a suspicion that it probably works much better for people working at Microsoft :)

|||OK, I'll open a bug directly in our bug tracking system.|||Hi,
I know its probably to late for you ( Perry_Rapp )
but for other who trying to find solution to the "The path is not of a legal form" problem here it is:

1. open a new project/solution
2. copy the old project\s to the new "solution folder"
3. add the old project\s to the new solution from the new "solution folder".
(right click on "solution" -> Add -> Existing Project... )

Note:
if you have virtual folders in your solution first add the old project\s to the new solution root and after saving .. create the virtual folders

Configuration failing: The path is not of a legal form.

When I push Add in the configuration dialog, I cannot even get to the first page of the configuration wizard, due to the following error, in several solutions (but not in several others). All have at least one existing configuration (necessarily, to get passwords for connections).
**
An error occurred while a new configuration was being added.
Additional Information:
The path is not of a legal form. (mscorlib)

**

PS: How have the rest of you been posting? Passport hasn't been working much this week, so I've been unable to post. Is there a way to post without using passport? Please tell me if so :)

What types of configuration (XML configuration file, Environment variable, etc) do your packages use?|||This is a known issue when you edit the package "standalone",
i.e. without creating the SSIS project. A workaround is to create
the project, add the package to the project, and then edit
the configuration.

We plan to fix this issue for SP1 (it was reported too late to
consider changing this for RTM release).

Thanks,
Michael.|||(Sorry, I have to wade through a lot of script errors and bad passport certificate errors to be able to post to this forum.)
The configuration involved is an environment variable pointing at an XML config file.
The package is in a project in a solution.
However, the solution has two packages, one of which calls the other.
Because of the bug that Execute SSIS task cannot handle relative paths, we have to use a mapped drive to call the package, so the target package is called via a mapped drive fully qualified file path.
I've since discovered that the bug here is that if the package is opened from the mapped drive, even though it is the same file, the Package Configuration Wizard will give this mscorlib error. The workaround for this bug is to be careful and be sure to first close the package, then open it by double-clicking it in the solution, when you need to examine or edit package configurations.
Of course, I hope this bug gets fixed, but truly, it is not nearly as bad as the bug that relative paths get wrecked and fail in the Execute SSIS task.
|||I see - this indeed seems like a different bug.
Could you please open a bug on betaplace?|||re: bug on betaplace
I invested some time trying to get into betaplace, which involved waving my cursor around to find an invisible button I had to click, and then getting to a page which said I would be granted access at some point. I tried several times on successive days, but I never got further than that. I posted my travails several times to the newsgroup, but noone had any suggestions, so I finally gave it up as something that only costs me time, and brought no benefit to anyone (as I was never able to get to a point of actually accessing any information, much less posting new bugs).
Could you open the bug on betaplace? I have a suspicion that it probably works much better for people working at Microsoft :)

|||OK, I'll open a bug directly in our bug tracking system.|||Hi,
I know its probably to late for you ( Perry_Rapp )
but for other who trying to find solution to the "The path is not of a legal form" problem here it is:

1. open a new project/solution
2. copy the old project\s to the new "solution folder"
3. add the old project\s to the new solution from the new "solution folder".
(right click on "solution" -> Add -> Existing Project... )

Note:
if you have virtual folders in your solution first add the old project\s to the new solution root and after saving .. create the virtual folders

Configuration failing: The path is not of a legal form.

When I push Add in the configuration dialog, I cannot even get to the first page of the configuration wizard, due to the following error, in several solutions (but not in several others). All have at least one existing configuration (necessarily, to get passwords for connections).
**
An error occurred while a new configuration was being added.
Additional Information:
The path is not of a legal form. (mscorlib)

**

PS: How have the rest of you been posting? Passport hasn't been working much this week, so I've been unable to post. Is there a way to post without using passport? Please tell me if so :)
What types of configuration (XML configuration file, Environment variable, etc) do your packages use?|||This is a known issue when you edit the package "standalone",
i.e. without creating the SSIS project. A workaround is to create
the project, add the package to the project, and then edit
the configuration.

We plan to fix this issue for SP1 (it was reported too late to
consider changing this for RTM release).

Thanks,
Michael.|||(Sorry, I have to wade through a lot of script errors and bad passport certificate errors to be able to post to this forum.)
The configuration involved is an environment variable pointing at an XML config file.
The package is in a project in a solution.
However, the solution has two packages, one of which calls the other.
Because of the bug that Execute SSIS task cannot handle relative paths, we have to use a mapped drive to call the package, so the target package is called via a mapped drive fully qualified file path.
I've since discovered that the bug here is that if the package is opened from the mapped drive, even though it is the same file, the Package Configuration Wizard will give this mscorlib error. The workaround for this bug is to be careful and be sure to first close the package, then open it by double-clicking it in the solution, when you need to examine or edit package configurations.
Of course, I hope this bug gets fixed, but truly, it is not nearly as bad as the bug that relative paths get wrecked and fail in the Execute SSIS task.
|||I see - this indeed seems like a different bug.
Could you please open a bug on betaplace?|||re: bug on betaplace
I invested some time trying to get into betaplace, which involved waving my cursor around to find an invisible button I had to click, and then getting to a page which said I would be granted access at some point. I tried several times on successive days, but I never got further than that. I posted my travails several times to the newsgroup, but noone had any suggestions, so I finally gave it up as something that only costs me time, and brought no benefit to anyone (as I was never able to get to a point of actually accessing any information, much less posting new bugs).
Could you open the bug on betaplace? I have a suspicion that it probably works much better for people working at Microsoft :)
|||OK, I'll open a bug directly in our bug tracking system.|||Hi,
I know its probably to late for you (

Perry_Rapp )
but for other who trying to find solution to the "The path is not of a legal form" problem here it is:

1. open a new project/solution
2. copy the old project\s to the new "solution folder"
3. add the old project\s to the new solution from the new "solution folder".
(right click on "solution" -> Add -> Existing Project... )

Note:
if you have virtual folders in your solution first add the old project\s to the new solution root and after saving .. create the virtual folders
sqlsql

Conditionally hiding column in matrix

I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave

Conditionally Expand a Table

I have a table that is centered on the page. I have some conditional
hiding on the columns in the table. Is there a way to keep my table
centered when columns conditionally hide? I tried embedding the table
inside another table, but that didn't work. I also tried embedding
the table in a rectangle, but that didn't work either. I thought
someone might have a clever trick for doing this.
BillyOn Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
As far as I know, there is not a way to handle this. If you haven't
already, you could try putting the table in a list control and set
'fit table in same page if possible' -or- you could try controlling
the resultset in the stored procedure or query used to source the
report. Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 12, 5:35 pm, billyburd...@.gmail.com wrote:
> I have a table that is centered on the page. I have some conditional
> hiding on the columns in the table. Is there a way to keep my table
> centered when columns conditionally hide? I tried embedding the table
> inside another table, but that didn't work. I also tried embedding
> the table in a rectangle, but that didn't work either. I thought
> someone might have a clever trick for doing this.
> Billy
The only other things I can think of are:
- Verify that 'Fit table on one page if possible' is selected in the
table properties.
- Put the table in a list control and verify that 'Fit table on one
page if possible' is selected for the list control properties.
- Try to control the table flow from the stored procedure or query
that is sourcing the report.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Conditional SQL Statements?

Hi There

I am writing a shopping cart page and I need some help in 'doing the math' for an SQL statement.

I have a main cart table that adds all the products.prices together from the 'price' field in the database to give the total amount payable.

SELECT SUM(TotPric) AS TheTotal FROM ( SELECT cart.cart_quantity * product_options.price AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"

I have however introduced a 'sale_price' that I would like the statement to select over the 'price' if the sale_price isn't 0.

Do I need to have another select statement in the mix to specifically select the sale_price if not zero, something like

SELECT SUM(TotPric) AS TheTotal FROM ( SELECT cart.cart_quantity * (SELECT product_options.price AS sp WHERE po.sale_price <> 0) AS TotPric FROM product_options INNER JOIN (products INNER JOIN (cart INNER JOIN main ON cart.main_id = main.main_id) ON products.product_id = main.product_id) ON product_options.product_options_id = main.product_options_id WHERE cart.session_id = "&cookiesesh&")AS TTT;"

I am a bit confused. Can you point me in the right direction, maybe a link to a tutorial or something.

I appreciate any help.

CheersRewritten for viewing

SELECT SUM(TotPric) AS TheTotal,
FROM (
SELECT (c.cart_quantity * po.price) AS TotPric
FROM product_options po
INNER JOIN main m ON po.product_options_id = m.product_options_id
INNER JOIN products p ON p.product_id = m.product_id
INNER JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = "&cookiesesh&"
) AS TTT"

Potential answer

SELECT SUM(TotPric) AS TheTotal,
FROM (
SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price) AS TotPric
FROM product_options po
INNER JOIN main m ON po.product_options_id = m.product_options_id
INNER JOIN products p ON p.product_id = m.product_id
INNER JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = "&cookiesesh&"
) AS TTT"|||thanks aschk.|||Does it work? What DBMS are you using? Can you provide a sample table layout? Do you have some sample data for a test? If you could provide this information I'm sure I could give you a better answer.|||Hi Aschk

Its not working. Thanks for the effort though.

I am using MySQL.

Tables

tblCart
cart_id,session_id,main_id,cart_quantity,timestamp

tblProduct_Options
product_options_id, product_options_text,price,sale_price,stock

tblMain
main_id,product_id,product_options_id

tblProducts
product_id,product_name,description

(some fields removed for clarity)

I am basically trying to do is mutliply cart_quantity with the sale_price if the sale_price is not zero, if it is zero then multiply by the price.

I have included the sql_dump with all the data.

Thanks for your time and effort|||SELECT SUM(TotPric) as TheTotal
FROM (
SELECT (c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TotPric
FROM product_options po
JOIN main m ON po.product_options_id = m.product_options_id
JOIN products p ON p.product_id = m.product_id
JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = '@.sessionid'
) ttt|||The original answer worked. Sorry aschk, I can't even copy and paste. time to hang up my coding hat and apply for that Burger King vacancy.

Thanks for your help and your time
:beer:
:)|||i was just looking through your table structure and had a question regarding a few columns you are using.

In your products table you are using a `text` column for your product name. I was wondering why? I suspect your names of products won't be larger than 255 variable characters so use a VARCHAR(255).

Also the same for your product_code and cart_finish columns in your cart and main tables. I can't see a reason why these need to be text columns either.

Maybe a rethink on those or explanation would be good ;) ?|||Thanks for pointing this out to me.

Definately a rethink.

I have recently moved from access to mysql and haven't really studied the benefits of choosing the right field type or best practises for mysql.

I am looking into it now.

Do you have any advice or know of any sites I can look at before I google it myself.

I am guessing that VARCHAR keeps the database size down & makes it quicker?|||In a word yes. I'm not 100% keyed up on the data types especially with regards to Text, however what I do is that it's a self altering column meaning that data could continually be placed in it and it could differ per row of the database. Unless you're dealing with text of an unknown size it's best to restrict fields to sensible values. e.g. usernames will probably be no longer than 30 characters and thus you column shouldn't need to be anything bigger than varchar(30).|||You know, using a subquery here is superfluous. This will return the same result with less obfuscation:
SELECT Sum((c.cart_quantity * IF(po.sale_price>0,sale_price,price)) AS TheTotal
FROM product_options po
JOIN main m ON po.product_options_id = m.product_options_id
JOIN products p ON p.product_id = m.product_id
JOIN cart c ON c.main_id=m.main_id
WHERE c.session_id = '@.sessionid'|||The IF thing is not SQL. A case expression has to be used:

SELECT SUM(c.cart_quantity * CASE po.sale_price > 0 THEN sale_price ELSE price END) AS theTotal
FROM ...|||The IF thing is not SQL.The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.|||First of all: thanks Blindman and Stolze for the time you took in posting to this thread.

My Db is MySQL and I am using classic ASP to write the 'other bits'

I am a newb to this so I am a little lost when it comes to 'The IF thing is not SQL'

What I am trying to do is write fast, correct, standard code and I appreciate your input.

As Blindman pointed out the original query supplied by Aschk works fine, what I would like to know is: Are there performance gains to be made by losing the subquery, is it more 'standard and correct' to use, as Stolze said, the CASE expression instead.

I can see myself coming across this situation again and again and it would be good for me to get it right inthe future.

Thanks again for your input guys. enlightening and educating.

:)|||There are certainly no performance GAINS to be had by using unnecessary subqueries. If you are a consultant being paid to code by the line, then that method may work out well for you, but otherwise it just makes it more difficult to debug.|||The poster does not indicate what platform he is using, but he did say that the code worked as written. You are correct that the statement is not in the correct syntax for MSSQL (or Oracle, I think), but it may execute for Access which is bastardized with VB.

Here we discuss standardized SQL as defined in ISO/IEC 9075 (aka SQL:2003). That's why there is this sticky "Read This First" article: http://www.dbforums.com/announcement.php?f=11 So platform questions are usually irrelevant (except to state if a DBMS is conforming to SQL:2003 or not).

As for the question whether CASE expression or "the IF thing" is "more standard and correct", this is easy to answer: CASE expressions are defined in the SQL standard; so it is conforming to use that. "The IF thing" is not standard SQL and only a product-specific extension. If you want to write mostly portable SQL code (very hard btw), you shouldn't use it.|||Heh, if you're being paid line by line then space your work out, don't write subqueries ;)

Conditional query on ASPNET page

Dear Friends,

I am working on search customer information page.

I have 5 search options,

Name,

Email,

Order Number,

Product Name,

Order Date

I am using check boxes, I need to allow admin to enter above information and click on search,

How will I make my query and sends to DB server to pull up records which satisfies where clause:

For example,

Select * from orders where Email = #Email#

This is simple, but I can not hardcode all queries, I don’t know in advance what different search option ADMIN may choose.

Any suggestion for logic or query make up,

Thanks,

Fahim.

So here are a couple suggestions that might help you with your task:

1. Look into calling system stored procedure sp_columns (http://msdn2.microsoft.com/en-us/library/ms176077.aspx) on the table to get the column metadata information.

Using this information you can then dynamically generate a query based on the check boxes selected by the user.

2, If you schema is simple and you already know the column information, then all you need to do is have a string that concatenates differect WHERE clauses based on the options selected and then submit the query when the user hits the search button.

HTH,

Conditional Page Throw

I'm looking for help on how to throw a page so that when printing duplex (both sides) a group will start on an odd page number eg 1, 3, 7, 9 etc. My large report needs to be split up into departments. I don't want a department starting on the reverse side of a sheet of paper.

Your help would be appreciated.

First make sure that the location of your table/list is 0,0.

Then edit the group and check both the properties "page break at start" and "page break at end". Since your table/list is at the top, "page break at start" will not be effective so your first page will have data and from the subsequent groups, the page break will be applied at the beggining and also at the end.

But you cannot stop the beginning page break if your group ends on page 2 (reverse of page 1) which means your second group (new department) will start from page 4 (reverse of page 2). I hope Microssoft provides an expression for page break conditions in future releases.

Shyam

Conditional Page Header

Will Crystal Reports have a page header that will hide if a condition is not met or viceaversa? If so how is this done?
Example: If Company_Name = 'xyz' then unhide page header A
thanks in advance
cgInstead of hard coding the header,write a formula like :

if companyname = 'xyz' then

'Header name'

else

' '

and drag the formula in page header!

or

right click on Page header section,select Section Expert,select Common Tab,
click formula button on right of suppress button ,and write

companyname <> 'xyx'|||how exactly do i do this in Crystal? I will have to do the same for the footer too.

thanks
cg|||you mean writing the formula ?

I think you should go for 2nd option for both header and footer i.e suppress the section based on your condition.|||i tried the conditional supressing the header and it is not working.
i have tried with and with out checking the supress box with the formula in there.

this is what i have in the formula, the first 2 are the same. i was testing if it was case sensitive or not. this is a parameter field instead of a db field.

{?CustomerName} <> "Typenex Medical, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL L.L.C."
or
{?CustomerName} <> "TYPENEX"
or
{?CustomerName} <> "TYPENEX LLC"

not sure if this makes a difference but i have version 8.5

thanks
cg|||i tried the conditional supressing the header and it is not working.
i have tried with and with out checking the supress box with the formula in there.

this is what i have in the formula, the first 2 are the same. i was testing if it was case sensitive or not. this is a parameter field instead of a db field.

{?CustomerName} <> "Typenex Medical, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL L.L.C."
or
{?CustomerName} <> "TYPENEX"
or
{?CustomerName} <> "TYPENEX LLC"

not sure if this makes a difference but i have version 8.5

thanks
cg

You have written the expression wrong.
Whatever you have written will be always true in any condition. Just check it properly. Use

NOT (condition1 AND condition2 AND so on)|||Make it simple :

- Right click on Formula field in field explorer

- click New

-name your formula

Type this

if {?CustomerName} <> 'ABC' and {?CustomerName} <> 'XYZ' then

' '
else

'HEADER'

Drag the formula field in report where you want to print the header.

Conditional page breaks in SSRS

Hi,

I am having trouble setting conditional page breaks to my reports.

i.e... I am having a report where I need to allow user the option to set page break between a group or not.

Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

- Rayz

OK, I think I have succeeded in doing this...

create a boolean parameter, something like PageBreak --
I would give it a default value, but it doesn't appear to be necessary if you don't want it.
create a group with "page break at end", as you normally would. Don't put anything in its header and footer lines.
here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
something like the following example:|||

I tried it out ..but no luck for me

Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

Thanks for your suggestion

-Rayz

|||

Well, let's see...

>>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

What is the condition you've specified?

Also, to make sure I can repro, is this an RDL or RDLC?

>L<

|||Perhaps you should extend the syntax that Lisa has shown with the IIF() function. If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

=IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

This way you will get a field reference to evaluate the grouping to. You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

Hope this helps.

-Paul R.
|||

Hi Paul,

It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general. So I didn't try it in response to this query. I will try it if you think it's a better idea.

Regrads,

>L<

|||

Thanx a bunch Lisa....

ur initial suggestion worked !!

|||

Thanks for confirming!

>L<

|||

Hi,

I have five tables in a report which has to be repeated on every sheet of excel based on a variable

Can i also use the same condition?

Kindly help me out.

Thanks in advance

Nalini

|||

"Every sheet of Excel" is basically "every explicitly-requested page of the report". By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

So, you want to have five tables that you want to see on each page. Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets? Are they related to each other or completely distinct, data-wise? Is there additional data in the report? are the tables positioned adjacent to each other vertically, or horizontally, or what?

>L<

Conditional page breaks in SSRS

Hi,

I am having trouble setting conditional page breaks to my reports.

i.e... I am having a report where I need to allow user the option to set page break between a group or not.

Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

- Rayz

OK, I think I have succeeded in doing this...

create a boolean parameter, something like PageBreak --
I would give it a default value, but it doesn't appear to be necessary if you don't want it.
create a group with "page break at end", as you normally would. Don't put anything in its header and footer lines.
here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
something like the following example:|||

I tried it out ..but no luck for me

Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

Thanks for your suggestion

-Rayz

|||

Well, let's see...

>>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

What is the condition you've specified?

Also, to make sure I can repro, is this an RDL or RDLC?

>L<

|||Perhaps you should extend the syntax that Lisa has shown with the IIF() function. If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

=IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

This way you will get a field reference to evaluate the grouping to. You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

Hope this helps.

-Paul R.
|||

Hi Paul,

It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general. So I didn't try it in response to this query. I will try it if you think it's a better idea.

Regrads,

>L<

|||

Thanx a bunch Lisa....

ur initial suggestion worked !!

|||

Thanks for confirming!

>L<

|||

Hi,

I have five tables in a report which has to be repeated on every sheet of excel based on a variable

Can i also use the same condition?

Kindly help me out.

Thanks in advance

Nalini

|||

"Every sheet of Excel" is basically "every explicitly-requested page of the report". By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

So, you want to have five tables that you want to see on each page. Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets? Are they related to each other or completely distinct, data-wise? Is there additional data in the report? are the tables positioned adjacent to each other vertically, or horizontally, or what?

>L<

|||

Lisa, I tried your approach and found two problems that I still can't figure out.

1) I had to do the Page break before the sections rather than after section in order for it to work.

2) I need to be able to choose the group level to page break on OR choose that no page break occurs at all. In this pursuit, I can't find a solution. Even if the Parameter!PageBreak criteria is not met, the top level group always page breaks. I need the ability to eliminate the page break as one of my options.

Any ideas?

|||

Well, it definitely does work and should not require page break before -- in fact using page break before might be why you're saying "top level always breaks".

I am not on a machine where I have any of these examples, and, clearly, we need to take this from the top.

Can I have a clear(er) statement of exactly what you've done so far in this report and also a separate statement of what the requirements are? This would optimally include some simplified SELECT or other way that I can repro exactly what your group breaks look like, there may be some collision that we have to account for that wasn't in the original suggestion (or maybe the collision *can't* be accounted for in your situation -- right now I honestly have no idea).

I'm leaving for the day now, so no rush on your response <s>

>L<

|||

I just created a stripped down sample report, and the issue with using page break after went away, but the other issue remains. I have a parameter named PageBreak with three option values: NONE, GROUP1, and GROUP2. When i choose NONE, it should always evaluate the Group1pre and Group2pre as ="" and therefore never cause a page break during the report. Unfortunately, it still causes the GROUP1 page breaks even if I choose NONE.

I created four group levels sequenced as follows (as seen in the Groups tab of Table Properties) and a detail row with the Detail values:

table1_Group1pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP1",Fields!Group1.Value,"")

table1_Group1, no page breaks, group expression =Fields!Group1.Value

table1_Group2pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP2",Fields!Group2.Value,"")

table1_Group2, no page breaks, group expression =Fields!Group2.Value

Here is the data set I created for testing.

select 'ABC' as Group1, '123' as Group2, 'slfje' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'redfg' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'wqsde' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'rdrgd' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'f5e4s6' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'rhfth' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'effgfg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'kuhjg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'efjklrg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'euurio' as Data
union all
select 'DEF' as Group1, '654' as Group2, 'wesdf' as Data
union all
select 'GHI' as Group1, '789' as Group2, 'yghfg' as Data
union all
select 'GHI' as Group1, '789' as Group2, '56215hh' as Data
union all
select 'GHI' as Group1, '456' as Group2, 'hbvfg' as Data
union all
select 'GHI' as Group1, '456' as Group2, 't456e4w' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'ouuyf' as Data
union all
select 'GHI' as Group1, '123' as Group2, '5f4g8r9' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'zzzzzzzz' as Data

|||

Thank you for taking such care to provide a reproducible sample. I will check this out and write back, whether I can do this or not, and I have no idea at the moment <s>. I do know that it's a chancey thing, and it's often tricky to get the conditional expressions right, so I usually take a while to get it to work.

Anyway, I'll give this a shot and report back...

>L<

|||

OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups. IOW, your group order is this:

Group1pre

Group1

Group2pre

Group2

... and it is quite possible that it would have worked something like this:

Group1pre

Group2pre

Group1

Group2

... although I didn't try it that way.

What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

GroupsPre

Group1

Group2

... using the following expression as my conditional break on GroupsPre:

Code Snippet

=IIF(Parameters!PageBreak.Value="NONE","",

IIF(Parameters!PageBreak.Value="GROUP1",
Fields!Group1.Value,

Fields!Group2.Value)
)

I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report). It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

[LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

Regards,

>L<

Conditional page breaks in SSRS

Hi,

I am having trouble setting conditional page breaks to my reports.

i.e... I am having a report where I need to allow user the option to set page break between a group or not.

Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

- Rayz

OK, I think I have succeeded in doing this...

create a boolean parameter, something like PageBreak --
I would give it a default value, but it doesn't appear to be necessary if you don't want it.
create a group with "page break at end", as you normally would. Don't put anything in its header and footer lines.
here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
something like the following example:|||

I tried it out ..but no luck for me

Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

Thanks for your suggestion

-Rayz

|||

Well, let's see...

>>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

What is the condition you've specified?

Also, to make sure I can repro, is this an RDL or RDLC?

>L<

|||Perhaps you should extend the syntax that Lisa has shown with the IIF() function. If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

=IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

This way you will get a field reference to evaluate the grouping to. You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

Hope this helps.

-Paul R.
|||

Hi Paul,

It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general. So I didn't try it in response to this query. I will try it if you think it's a better idea.

Regrads,

>L<

|||

Thanx a bunch Lisa....

ur initial suggestion worked !!

|||

Thanks for confirming!

>L<

|||

Hi,

I have five tables in a report which has to be repeated on every sheet of excel based on a variable

Can i also use the same condition?

Kindly help me out.

Thanks in advance

Nalini

|||

"Every sheet of Excel" is basically "every explicitly-requested page of the report". By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

So, you want to have five tables that you want to see on each page. Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets? Are they related to each other or completely distinct, data-wise? Is there additional data in the report? are the tables positioned adjacent to each other vertically, or horizontally, or what?

>L<

|||

Lisa, I tried your approach and found two problems that I still can't figure out.

1) I had to do the Page break before the sections rather than after section in order for it to work.

2) I need to be able to choose the group level to page break on OR choose that no page break occurs at all. In this pursuit, I can't find a solution. Even if the Parameter!PageBreak criteria is not met, the top level group always page breaks. I need the ability to eliminate the page break as one of my options.

Any ideas?

|||

Well, it definitely does work and should not require page break before -- in fact using page break before might be why you're saying "top level always breaks".

I am not on a machine where I have any of these examples, and, clearly, we need to take this from the top.

Can I have a clear(er) statement of exactly what you've done so far in this report and also a separate statement of what the requirements are? This would optimally include some simplified SELECT or other way that I can repro exactly what your group breaks look like, there may be some collision that we have to account for that wasn't in the original suggestion (or maybe the collision *can't* be accounted for in your situation -- right now I honestly have no idea).

I'm leaving for the day now, so no rush on your response <s>

>L<

|||

I just created a stripped down sample report, and the issue with using page break after went away, but the other issue remains. I have a parameter named PageBreak with three option values: NONE, GROUP1, and GROUP2. When i choose NONE, it should always evaluate the Group1pre and Group2pre as ="" and therefore never cause a page break during the report. Unfortunately, it still causes the GROUP1 page breaks even if I choose NONE.

I created four group levels sequenced as follows (as seen in the Groups tab of Table Properties) and a detail row with the Detail values:

table1_Group1pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP1",Fields!Group1.Value,"")

table1_Group1, no page breaks, group expression =Fields!Group1.Value

table1_Group2pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP2",Fields!Group2.Value,"")

table1_Group2, no page breaks, group expression =Fields!Group2.Value

Here is the data set I created for testing.

select 'ABC' as Group1, '123' as Group2, 'slfje' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'redfg' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'wqsde' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'rdrgd' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'f5e4s6' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'rhfth' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'effgfg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'kuhjg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'efjklrg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'euurio' as Data
union all
select 'DEF' as Group1, '654' as Group2, 'wesdf' as Data
union all
select 'GHI' as Group1, '789' as Group2, 'yghfg' as Data
union all
select 'GHI' as Group1, '789' as Group2, '56215hh' as Data
union all
select 'GHI' as Group1, '456' as Group2, 'hbvfg' as Data
union all
select 'GHI' as Group1, '456' as Group2, 't456e4w' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'ouuyf' as Data
union all
select 'GHI' as Group1, '123' as Group2, '5f4g8r9' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'zzzzzzzz' as Data

|||

Thank you for taking such care to provide a reproducible sample. I will check this out and write back, whether I can do this or not, and I have no idea at the moment <s>. I do know that it's a chancey thing, and it's often tricky to get the conditional expressions right, so I usually take a while to get it to work.

Anyway, I'll give this a shot and report back...

>L<

|||

OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups. IOW, your group order is this:

Group1pre

Group1

Group2pre

Group2

... and it is quite possible that it would have worked something like this:

Group1pre

Group2pre

Group1

Group2

... although I didn't try it that way.

What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

GroupsPre

Group1

Group2

... using the following expression as my conditional break on GroupsPre:

Code Snippet

=IIF(Parameters!PageBreak.Value="NONE","",

IIF(Parameters!PageBreak.Value="GROUP1",
Fields!Group1.Value,

Fields!Group2.Value)
)

I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report). It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

[LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

Regards,

>L<

Conditional page breaks in SSRS

Hi,

I am having trouble setting conditional page breaks to my reports.

i.e... I am having a report where I need to allow user the option to set page break between a group or not.

Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

- Rayz

OK, I think I have succeeded in doing this...

create a boolean parameter, something like PageBreak --
I would give it a default value, but it doesn't appear to be necessary if you don't want it.
create a group with "page break at end", as you normally would. Don't put anything in its header and footer lines.
here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
something like the following example:|||

I tried it out ..but no luck for me

Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

Thanks for your suggestion

-Rayz

|||

Well, let's see...

>>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

What is the condition you've specified?

Also, to make sure I can repro, is this an RDL or RDLC?

>L<

|||Perhaps you should extend the syntax that Lisa has shown with the IIF() function. If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

=IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

This way you will get a field reference to evaluate the grouping to. You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

Hope this helps.

-Paul R.
|||

Hi Paul,

It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general. So I didn't try it in response to this query. I will try it if you think it's a better idea.

Regrads,

>L<

|||

Thanx a bunch Lisa....

ur initial suggestion worked !!

|||

Thanks for confirming!

>L<

|||

Hi,

I have five tables in a report which has to be repeated on every sheet of excel based on a variable

Can i also use the same condition?

Kindly help me out.

Thanks in advance

Nalini

|||

"Every sheet of Excel" is basically "every explicitly-requested page of the report". By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

So, you want to have five tables that you want to see on each page. Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets? Are they related to each other or completely distinct, data-wise? Is there additional data in the report? are the tables positioned adjacent to each other vertically, or horizontally, or what?

>L<

|||

Lisa, I tried your approach and found two problems that I still can't figure out.

1) I had to do the Page break before the sections rather than after section in order for it to work.

2) I need to be able to choose the group level to page break on OR choose that no page break occurs at all. In this pursuit, I can't find a solution. Even if the Parameter!PageBreak criteria is not met, the top level group always page breaks. I need the ability to eliminate the page break as one of my options.

Any ideas?

|||

Well, it definitely does work and should not require page break before -- in fact using page break before might be why you're saying "top level always breaks".

I am not on a machine where I have any of these examples, and, clearly, we need to take this from the top.

Can I have a clear(er) statement of exactly what you've done so far in this report and also a separate statement of what the requirements are? This would optimally include some simplified SELECT or other way that I can repro exactly what your group breaks look like, there may be some collision that we have to account for that wasn't in the original suggestion (or maybe the collision *can't* be accounted for in your situation -- right now I honestly have no idea).

I'm leaving for the day now, so no rush on your response <s>

>L<

|||

I just created a stripped down sample report, and the issue with using page break after went away, but the other issue remains. I have a parameter named PageBreak with three option values: NONE, GROUP1, and GROUP2. When i choose NONE, it should always evaluate the Group1pre and Group2pre as ="" and therefore never cause a page break during the report. Unfortunately, it still causes the GROUP1 page breaks even if I choose NONE.

I created four group levels sequenced as follows (as seen in the Groups tab of Table Properties) and a detail row with the Detail values:

table1_Group1pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP1",Fields!Group1.Value,"")

table1_Group1, no page breaks, group expression =Fields!Group1.Value

table1_Group2pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP2",Fields!Group2.Value,"")

table1_Group2, no page breaks, group expression =Fields!Group2.Value

Here is the data set I created for testing.

select 'ABC' as Group1, '123' as Group2, 'slfje' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'redfg' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'wqsde' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'rdrgd' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'f5e4s6' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'rhfth' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'effgfg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'kuhjg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'efjklrg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'euurio' as Data
union all
select 'DEF' as Group1, '654' as Group2, 'wesdf' as Data
union all
select 'GHI' as Group1, '789' as Group2, 'yghfg' as Data
union all
select 'GHI' as Group1, '789' as Group2, '56215hh' as Data
union all
select 'GHI' as Group1, '456' as Group2, 'hbvfg' as Data
union all
select 'GHI' as Group1, '456' as Group2, 't456e4w' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'ouuyf' as Data
union all
select 'GHI' as Group1, '123' as Group2, '5f4g8r9' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'zzzzzzzz' as Data

|||

Thank you for taking such care to provide a reproducible sample. I will check this out and write back, whether I can do this or not, and I have no idea at the moment <s>. I do know that it's a chancey thing, and it's often tricky to get the conditional expressions right, so I usually take a while to get it to work.

Anyway, I'll give this a shot and report back...

>L<

|||

OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups. IOW, your group order is this:

Group1pre

Group1

Group2pre

Group2

... and it is quite possible that it would have worked something like this:

Group1pre

Group2pre

Group1

Group2

... although I didn't try it that way.

What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

GroupsPre

Group1

Group2

... using the following expression as my conditional break on GroupsPre:

Code Snippet

=IIF(Parameters!PageBreak.Value="NONE","",

IIF(Parameters!PageBreak.Value="GROUP1",
Fields!Group1.Value,

Fields!Group2.Value)
)

I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report). It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

[LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

Regards,

>L<

Conditional page breaks in SSRS

Hi,

I am having trouble setting conditional page breaks to my reports.

i.e... I am having a report where I need to allow user the option to set page break between a group or not.

Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

- Rayz

OK, I think I have succeeded in doing this...

create a boolean parameter, something like PageBreak --
I would give it a default value, but it doesn't appear to be necessary if you don't want it.
create a group with "page break at end", as you normally would. Don't put anything in its header and footer lines.
here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
something like the following example:|||

I tried it out ..but no luck for me

Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

Thanks for your suggestion

-Rayz

|||

Well, let's see...

>>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

What is the condition you've specified?

Also, to make sure I can repro, is this an RDL or RDLC?

>L<

|||Perhaps you should extend the syntax that Lisa has shown with the IIF() function. If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

=IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

This way you will get a field reference to evaluate the grouping to. You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

Hope this helps.

-Paul R.
|||

Hi Paul,

It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general. So I didn't try it in response to this query. I will try it if you think it's a better idea.

Regrads,

>L<

|||

Thanx a bunch Lisa....

ur initial suggestion worked !!

|||

Thanks for confirming!

>L<

|||

Hi,

I have five tables in a report which has to be repeated on every sheet of excel based on a variable

Can i also use the same condition?

Kindly help me out.

Thanks in advance

Nalini

|||

"Every sheet of Excel" is basically "every explicitly-requested page of the report". By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

So, you want to have five tables that you want to see on each page. Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets? Are they related to each other or completely distinct, data-wise? Is there additional data in the report? are the tables positioned adjacent to each other vertically, or horizontally, or what?

>L<

|||

Lisa, I tried your approach and found two problems that I still can't figure out.

1) I had to do the Page break before the sections rather than after section in order for it to work.

2) I need to be able to choose the group level to page break on OR choose that no page break occurs at all. In this pursuit, I can't find a solution. Even if the Parameter!PageBreak criteria is not met, the top level group always page breaks. I need the ability to eliminate the page break as one of my options.

Any ideas?

|||

Well, it definitely does work and should not require page break before -- in fact using page break before might be why you're saying "top level always breaks".

I am not on a machine where I have any of these examples, and, clearly, we need to take this from the top.

Can I have a clear(er) statement of exactly what you've done so far in this report and also a separate statement of what the requirements are? This would optimally include some simplified SELECT or other way that I can repro exactly what your group breaks look like, there may be some collision that we have to account for that wasn't in the original suggestion (or maybe the collision *can't* be accounted for in your situation -- right now I honestly have no idea).

I'm leaving for the day now, so no rush on your response <s>

>L<

|||

I just created a stripped down sample report, and the issue with using page break after went away, but the other issue remains. I have a parameter named PageBreak with three option values: NONE, GROUP1, and GROUP2. When i choose NONE, it should always evaluate the Group1pre and Group2pre as ="" and therefore never cause a page break during the report. Unfortunately, it still causes the GROUP1 page breaks even if I choose NONE.

I created four group levels sequenced as follows (as seen in the Groups tab of Table Properties) and a detail row with the Detail values:

table1_Group1pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP1",Fields!Group1.Value,"")

table1_Group1, no page breaks, group expression =Fields!Group1.Value

table1_Group2pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP2",Fields!Group2.Value,"")

table1_Group2, no page breaks, group expression =Fields!Group2.Value

Here is the data set I created for testing.

select 'ABC' as Group1, '123' as Group2, 'slfje' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'redfg' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'wqsde' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'rdrgd' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'f5e4s6' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'rhfth' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'effgfg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'kuhjg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'efjklrg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'euurio' as Data
union all
select 'DEF' as Group1, '654' as Group2, 'wesdf' as Data
union all
select 'GHI' as Group1, '789' as Group2, 'yghfg' as Data
union all
select 'GHI' as Group1, '789' as Group2, '56215hh' as Data
union all
select 'GHI' as Group1, '456' as Group2, 'hbvfg' as Data
union all
select 'GHI' as Group1, '456' as Group2, 't456e4w' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'ouuyf' as Data
union all
select 'GHI' as Group1, '123' as Group2, '5f4g8r9' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'zzzzzzzz' as Data

|||

Thank you for taking such care to provide a reproducible sample. I will check this out and write back, whether I can do this or not, and I have no idea at the moment <s>. I do know that it's a chancey thing, and it's often tricky to get the conditional expressions right, so I usually take a while to get it to work.

Anyway, I'll give this a shot and report back...

>L<

|||

OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups. IOW, your group order is this:

Group1pre

Group1

Group2pre

Group2

... and it is quite possible that it would have worked something like this:

Group1pre

Group2pre

Group1

Group2

... although I didn't try it that way.

What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

GroupsPre

Group1

Group2

... using the following expression as my conditional break on GroupsPre:

Code Snippet

=IIF(Parameters!PageBreak.Value="NONE","",

IIF(Parameters!PageBreak.Value="GROUP1",
Fields!Group1.Value,

Fields!Group2.Value)
)

I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report). It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

[LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

Regards,

>L<

Conditional page breaks in SSRS

Hi,

I am having trouble setting conditional page breaks to my reports.

i.e... I am having a report where I need to allow user the option to set page break between a group or not.

Based upon the option selected by the user, I need to add page break to the report. I tried with all possibilities inside Sort and Group dialog box, but could not figure out how to toggle the option at runtime.

Does anyone know how to implement page breaks on runtime? Help me plzzzz!! I really need this to be done…Thanx in advance for any help..

- Rayz

OK, I think I have succeeded in doing this...

create a boolean parameter, something like PageBreak --
I would give it a default value, but it doesn't appear to be necessary if you don't want it.
create a group with "page break at end", as you normally would. Don't put anything in its header and footer lines.
here's the trick: make this group's grouip expression an IIF() containing your "real" group expression,
something like the following example:|||

I tried it out ..but no luck for me

Wherever i apply page break..it seems it wont take the condition that i've specified..and pagebreaks appears permanently..even if i dont put pagebreak no..the page break comes..

Thanks for your suggestion

-Rayz

|||

Well, let's see...

>>it seems it wont take the condition that i've specified..and pagebreaks appears permanently

What is the condition you've specified?

Also, to make sure I can repro, is this an RDL or RDLC?

>L<

|||Perhaps you should extend the syntax that Lisa has shown with the IIF() function. If you set the group expression as previously mentioned and add a field reference that is global to the report instead of the empty field ( "" ) then perhaps this evaluate correctly.

=IIF(Parameters!PageBreak.Value, Fields!MyField.Value, Fields!MyMostGlobalField.Value)

This way you will get a field reference to evaluate the grouping to. You may additionally want to hide this group with a "Visibility" expression on the group, though you may still get a page break before your report footer if one exists.

Hope this helps.

-Paul R.
|||

Hi Paul,

It actually does work fine with the "" -- and in the past I've done this with other "global/invariant" expressions -- such as True or 1. Is there some reason why this is a bad idea?

BTW another way I do this, without an IIF(), is to group on a variable and increment the variable in code when I want to force a page break -- I haven't tried that in RS and it can be a little more difficult to manage in general. So I didn't try it in response to this query. I will try it if you think it's a better idea.

Regrads,

>L<

|||

Thanx a bunch Lisa....

ur initial suggestion worked !!

|||

Thanks for confirming!

>L<

|||

Hi,

I have five tables in a report which has to be repeated on every sheet of excel based on a variable

Can i also use the same condition?

Kindly help me out.

Thanks in advance

Nalini

|||

"Every sheet of Excel" is basically "every explicitly-requested page of the report". By that I mean you get a sheet for each page break you explicitly asked for with a page break condition, rather than the report just deciding that it has to page break based on some constraints of the host format (for example, physical page size).

So, "Excel" shouldn't be handled specifically when trying to repeat tables -- it should work the way you want, if you get the explicit page break requests correct.

I will assume you understand this (I almost wrote "that we're on the same page" <g>) and ignore the "Excel" part of the question...

So, you want to have five tables that you want to see on each page. Can you tell me some more about your layout, what each of the tables represent from the point of view of datasets? Are they related to each other or completely distinct, data-wise? Is there additional data in the report? are the tables positioned adjacent to each other vertically, or horizontally, or what?

>L<

|||

Lisa, I tried your approach and found two problems that I still can't figure out.

1) I had to do the Page break before the sections rather than after section in order for it to work.

2) I need to be able to choose the group level to page break on OR choose that no page break occurs at all. In this pursuit, I can't find a solution. Even if the Parameter!PageBreak criteria is not met, the top level group always page breaks. I need the ability to eliminate the page break as one of my options.

Any ideas?

|||

Well, it definitely does work and should not require page break before -- in fact using page break before might be why you're saying "top level always breaks".

I am not on a machine where I have any of these examples, and, clearly, we need to take this from the top.

Can I have a clear(er) statement of exactly what you've done so far in this report and also a separate statement of what the requirements are? This would optimally include some simplified SELECT or other way that I can repro exactly what your group breaks look like, there may be some collision that we have to account for that wasn't in the original suggestion (or maybe the collision *can't* be accounted for in your situation -- right now I honestly have no idea).

I'm leaving for the day now, so no rush on your response <s>

>L<

|||

I just created a stripped down sample report, and the issue with using page break after went away, but the other issue remains. I have a parameter named PageBreak with three option values: NONE, GROUP1, and GROUP2. When i choose NONE, it should always evaluate the Group1pre and Group2pre as ="" and therefore never cause a page break during the report. Unfortunately, it still causes the GROUP1 page breaks even if I choose NONE.

I created four group levels sequenced as follows (as seen in the Groups tab of Table Properties) and a detail row with the Detail values:

table1_Group1pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP1",Fields!Group1.Value,"")

table1_Group1, no page breaks, group expression =Fields!Group1.Value

table1_Group2pre, page break at end checked, group expression =IIF(Parameters!PageBreak.Value="GROUP2",Fields!Group2.Value,"")

table1_Group2, no page breaks, group expression =Fields!Group2.Value

Here is the data set I created for testing.

select 'ABC' as Group1, '123' as Group2, 'slfje' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'redfg' as Data
union all
select 'ABC' as Group1, '123' as Group2, 'wqsde' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'rdrgd' as Data
union all
select 'ABC' as Group1, '456' as Group2, 'f5e4s6' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'rhfth' as Data
union all
select 'ABC' as Group1, '789' as Group2, 'effgfg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'kuhjg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'efjklrg' as Data
union all
select 'DEF' as Group1, '987' as Group2, 'euurio' as Data
union all
select 'DEF' as Group1, '654' as Group2, 'wesdf' as Data
union all
select 'GHI' as Group1, '789' as Group2, 'yghfg' as Data
union all
select 'GHI' as Group1, '789' as Group2, '56215hh' as Data
union all
select 'GHI' as Group1, '456' as Group2, 'hbvfg' as Data
union all
select 'GHI' as Group1, '456' as Group2, 't456e4w' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'ouuyf' as Data
union all
select 'GHI' as Group1, '123' as Group2, '5f4g8r9' as Data
union all
select 'GHI' as Group1, '123' as Group2, 'zzzzzzzz' as Data

|||

Thank you for taking such care to provide a reproducible sample. I will check this out and write back, whether I can do this or not, and I have no idea at the moment <s>. I do know that it's a chancey thing, and it's often tricky to get the conditional expressions right, so I usually take a while to get it to work.

Anyway, I'll give this a shot and report back...

>L<

|||

OK -- you can do this, it will work <s>. What you're missing (I think) is that you have to put the conditionals on the outer rim of the "real" groups. IOW, your group order is this:

Group1pre

Group1

Group2pre

Group2

... and it is quite possible that it would have worked something like this:

Group1pre

Group2pre

Group1

Group2

... although I didn't try it that way.

What I did try successfully, and what appears to follow the "rules of engagement" as I understand them (my understanding being without inside knowledge, just observation, and is admittedly imperfect!), is this:

GroupsPre

Group1

Group2

... using the following expression as my conditional break on GroupsPre:

Code Snippet

=IIF(Parameters!PageBreak.Value="NONE","",

IIF(Parameters!PageBreak.Value="GROUP1",
Fields!Group1.Value,

Fields!Group2.Value)
)

I want to say one other thing, based on your sample data -- which may be compounded in something you're doing in your "real" data set:

I am not sure what effect you are after on the inner group break, but you may have to dynamically sort your data to get the effect you are after when the break is on "GROUP2" (your inner group as expressed in the report). It is certainly possible to do this, of course, and I'm not even sure that is an issue for you.

[LSN Editing next morning: instead of dynamic orderng you can probably concatenate the value in the last part of the expression as csi_hugh shows in the next post]

Regards,

>L<

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 page breaks

I want to set a page break at the change of a group conditionally based on a
parameter value. Is there any way to do this in Reporting Services?Any help here? Because I would *really* like to figure out how to do this.
Once again, I have a grouped table in a report layout. I would like to have
a page break at the end of a grouping happen conditionally based on a boolean
parameter that I pass to the report.

Conditional Page breaks

How do i create page breaks that depend on report parameters?Nope, page breaks are hardcoded at design time.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"news.microsoft.com" <rodrigoss@.hotmail.com> wrote in message
news:OqO%236aW5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> How do i create page breaks that depend on report parameters?
>

Conditional Page Breaks

Greetings,
Has anyone figured out a workaround to do conditional page breaks within the
body section of a report?Go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
and scroll down to the Page Breaks section. Hope that helps.
-jk
"Rob Olson" wrote:
> Greetings,
> Has anyone figured out a workaround to do conditional page breaks within the
> body section of a report?

Conditional input params to SP

I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?

Dim strWhere
strWhere = " WHERE dbo.""User"".UID IS NOT NULL "

If Not request.querystring("EmployerID") = "" Then
strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'"
End If

If Not request.querystring("AccountNumber") = "" Then
strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'"
End If

If Not request.querystring("LastName") = "" Then
strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'"
End If

If Not request.querystring("FirstName") = "" Then
strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'"
End If

DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr"))
DBCommand = New OleDbDataAdapter _
("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)

Thanks in advance?Here is a pretty complete, though somewhat dated, overview on doing what you are looking to do (in this case inside a stored procedure, but you could do the same with a parameterized query, I expect).

What you are doing is succeptable to SQL Injection attacks, and a number of other potential problems...|||Thanks much. Great article, just what I was looking for.

I'll be coding with SQL Injection in mind as well...|||example of how i go about optional inputs

create procedure myprocedure
@.field1 type = null,
@.field2 type = null,
@.field3 type = null

as

select * from mytable where field1 = isnull(@.field1, field1)
and field2 = isnull(@.field2, field2)
and field3 = isnull(@.field3, field3)

this could be taxing on a table with a lot of entries, but for small tables this works out well and keeps me from having to write multiple sprocs for different parms