configuration tool

In SSRS 2005, where do I find Reporting Services Configuration tool ?
Thanks

Assuming you installed SSRS 2005 on C-drive then the path should be "C:\Program Files\Microsoft SQL Server\90\Tools\binn\RSConfigTool.exe"

configuration tool

In SSRS 2005, where do I find Reporting Services Configuration tool ?
Thanksstart > programs > ms sql server 2005 > configuration tools > reporting
services configuration
OR
"\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
> In SSRS 2005, where do I find Reporting Services Configuration tool ?
> Thanks|||I am now in E-mail settings for SSRS 2005
It is asking for Sender address and SMTP address.
I guess I can get hold of the SMTP address which is probably the server the
send the emails but not sure what to place for Sender address ?
Thanks
"Immy" wrote:
> start > programs > ms sql server 2005 > configuration tools > reporting
> services configuration
> OR
> "\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
> > In SSRS 2005, where do I find Reporting Services Configuration tool ?
> > Thanks
>
>|||whatever you want.
e.g Reporting@.server.com
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:A21C5499-5FA5-4FFB-BD7E-78972D0E8D15@.microsoft.com...
>I am now in E-mail settings for SSRS 2005
> It is asking for Sender address and SMTP address.
> I guess I can get hold of the SMTP address which is probably the server
> the
> send the emails but not sure what to place for Sender address ?
> Thanks
>
> "Immy" wrote:
>> start > programs > ms sql server 2005 > configuration tools > reporting
>> services configuration
>> OR
>> "\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
>> "farshad" <farshad@.discussions.microsoft.com> wrote in message
>> news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
>> > In SSRS 2005, where do I find Reporting Services Configuration tool ?
>> > Thanks
>>|||If the emails do not get sent, is there a place to check for status? i.e.
error message as for why email was not sent?
Thanks
"Immy" wrote:
> whatever you want.
> e.g Reporting@.server.com
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:A21C5499-5FA5-4FFB-BD7E-78972D0E8D15@.microsoft.com...
> >I am now in E-mail settings for SSRS 2005
> > It is asking for Sender address and SMTP address.
> > I guess I can get hold of the SMTP address which is probably the server
> > the
> > send the emails but not sure what to place for Sender address ?
> > Thanks
> >
> >
> > "Immy" wrote:
> >
> >> start > programs > ms sql server 2005 > configuration tools > reporting
> >> services configuration
> >>
> >> OR
> >>
> >> "\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
> >>
> >> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> >> news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
> >> > In SSRS 2005, where do I find Reporting Services Configuration tool ?
> >> > Thanks
> >>
> >>
> >>
>
>sqlsql

Conditionally load Drop downs in Parameter toolbar

In trying to incorporate business rules into my SSRS report, I need to be
able to conditionally load drop downs based upon what the selects for other
drop downs.
Can anyone tell me how? Example:
DropDown1 = Country
DropDown2 = State/Region
How Can i leave DropDown2 empty until they select from DropDown1?
Thanks.Hi JrMcG,
Thank you for your posting!
Based on my experience, you could do the following step to get the
Parameters related.
1. Create a dataset and add a Report Parameter named Country.
2. Create another dataset named States and use the parameter in the query
text. For example:
select State from tbl_Region where Country = @.Country
3. Create a new Report Patameter named State and in the Available values,
you need to use From query, and choose the dataset States, Value filed and
Label filed use State.
Then, in the preview, you could see the Parameter State could not get the
value untill you specify the value of Country.
Please try the above steps and let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi JrMcG,
Have you got any chance to check this issue? Please let me know if you need
any help, thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||You are looking for a "Cascading Parameter" Report; there is a good
example in the sample set for SSRS 2005.
Dennis Graham
JrMcG wrote:
> In trying to incorporate business rules into my SSRS report, I need to be
> able to conditionally load drop downs based upon what the selects for other
> drop downs.
> Can anyone tell me how? Example:
> DropDown1 = Country
> DropDown2 = State/Region
> How Can i leave DropDown2 empty until they select from DropDown1?
> Thanks.|||My subject is very closeley tied to this one so i hope it's OK if I post
here...
I did the same thing but also added an 'all' option in my dataset. Selecting
'all' and a single option works but when selecting multi values the report
breaks. What can i do in my WHERE claus to get this working. Without it the
Bussiness Rules are useless.
"Wei Lu [MSFT]" wrote:
> Hi JrMcG,
> Have you got any chance to check this issue? Please let me know if you need
> any help, thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

COnditional visibility of a field in SSRS 2005

Hi,

I have a report in which there is a field called "Returned Qty" and there is a parameter called Show Qty now I want this field returned Qty to only appear if this show Qty parameter is set to yes. Can Anyone suggest me how to do this?

I guess It could be done by using the properties option and writing some expression. I appreciate the response,

Thanks,

Rashi

Hello Rashi,

Try putting this in the Hidden property of your textbox:

=IIf(Parameters!ShowQty.Value = "Yes", "False", "True")

Or, you can just replace the value directly in the textbox, using this expression:

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks for the solution, I am sure this should work but somehow it gives me error both ways. I wrote the first expression under the text properties of the box by checking the expression option but it dint work. I wrote the same expression under the hidden properties too but it too dint work. The second expression display field but the filed contains "Error".

I am sure the expresison is correct but I am not using it under correct text box may be.

My aim is not to display the field at all if showQty is set to No.

Pls let me know if I am commiting any mistake while doing this

Thanks again for your help,

Rashi

|||

Hi Rashi,

Try false and true without the quotes ""

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

Thanks,

Panna

|||

Yep, I tried this and it gives me an error " input string was not in correct format". No matter whatever expression do I write I am getting the same error message.

Any idea what it is?

|||

Sorry, yes, you will need to remove the " " from around the false and true. Right click and select Properties on your textbox that will hold the returned quantity. Select the Visibility tab, then select 'Expression:' from the 'Initial visibility:' section. Enter this as the expression here:

=IIf(Parameters!ShowQty.Value = "Yes", False, True)

The other way to do this is to replace the value with nothing in the textbox where it actually shows the value. Right click on the textbox that will hold the returned quantity and select 'Expression:'. Enter this and hit ok.

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, "")

But, you said this shows Error when you did it, so can you try this instead?

=IIf(Parameters!ShowQty.Value = "Yes", Fields!ReturnedQty.Value, nothing)

Jarret

|||

When referencing parameters you can either look at the "Value" or the "Label".

=IIf(Parameters!ShowQty.Label = "Yes", False, True)

=IIf(Parameters!ShowQty.Value = False, False, True)

Either way should work!

GiveMeABreak. . .

|||I have been doing both ways n the error message is still the same no matter what I try it keeps giving: " input string not in correct format". If I remove all the expression it does build and shows me the returned qty firld but not with the expression values|||

Can you post the expression exactly as you have it in your textbox and in your hidden property?

Also, how do you have your parameter setup?

Jarret

|||

Thank you all, it has been fixed now.

I used the one below

IIf(Parameters!ShowQty.Value = False, False, True)

The mistake was in refrencing the parameters, otherwise its perfect!

Thanks again. :)

Rgds,

Rashi

sqlsql

Conditional Sum/Runnining Total

I have a simple table in ssrs where data is returned from a stored procedure.

I have detail data group totals of the detail data.

I want to be able to create a sum of the detail data matching certain criteria.

i.e.

I have the following total field

sum(Fields!hours_m2.Value)

what I also want to be able to do is create a conditional formula like ...

sum(iif(Fields!Sort_Order.Value = "E1",Fields!hours_m2.Value,0))

When I create this on my report and preview it I get the following message in the field #Error.

Can someone please tell me where I've gone wrong and how to fix ... I know I can change the stored proc but I have 12 columns which I want to do the same thing with which would mean adding 12 columns to my stored proc.

Hello Derek,

In Visual Studio, click on the preview tab and run your report. Then look in the Output window (Ctrl+Alt+O), it should have a description as to what the error is. Can you post that error message?

Jarret

|||

One of the things I have found out about summing in ssrs is that it treats values of doubles and integers seperatley.

try changing the the "0" to "0.0" this will then tell ssrs it is a double type and your sum should work.

|||

description of error message

[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox101’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

|||

I have found that using the following

sum(iif(Fields!Sort_Order.Value = "E1" or Fields!Sort_Order.Value = "E2" or Fields!Sort_Order.Value = "F1",cdbl(Fields!hours_m2.Value),cdbl(0.0)))

works

Thanks for you responses

|||

The problem is as Mainiac said. Try this:

=sum(iif(Fields!Sort_Order.Value = "E1", cDec(Fields!hours_m2.Value), cDec(0)))

Hope this helps.

Jarret

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 Formating in Reporting Services 2005

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

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

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

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

Stefoon

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

Shyam

|||

Hi Shyam,

you are right. But it does not work either.

Stefoon

Conditional Filtering in Reporting Services

I am using the following SQL query in a SSRS 2005 report (see below).
I need dbo.FilteredNew_Assets.new_assettypename = "Computer" ONLY when
the value of dbo.FilteredNew_Assets.new_assignedemployeeid is NOT
null. In other words, only when
dbo.FilteredNew_Assets.new_assignedemployeeid has a value do I need
dbo.FilteredNew_Assets.new_assettypename to be filtered.
Is this a task that needs to be accomplished within my SQL query or
somewhere within the context of the actual report? Either way, how do
I accomplish this?
SELECT dbo.FilteredNew_Employee.new_employeeid,
dbo.FilteredNew_Assets.new_assignedemployeeid,
dbo.FilteredNew_Employee.new_employeetypename,
dbo.FilteredNew_Employee.new_firstname,
dbo.FilteredNew_Employee.new_lastname,
dbo.FilteredNew_Assets.new_assettypename,
dbo.FilteredNew_Assets.new_computertypename,
dbo.FilteredNew_Assets.new_manufacturer,
dbo.FilteredNew_Assets.new_model,
dbo.FilteredNew_Assets.new_modelnumber,
dbo.FilteredNew_Assets.new_assetsid,
dbo.FilteredNew_Assets.new_name
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
dbo.FilteredNew_Assets ON
dbo.FilteredNew_Employee.new_employeeid = dbo.FilteredNew_Assets.new_assignedemployeeid
ORDER BY dbo.FilteredNew_Employee.new_lastnamePut filterednew_assets into a derived table like this and you=B4re able
to link on this derived table.
The union all reunites both sets of data, one set containing specific
conditional filter
SELECT *
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
(
SELECT
FNA.new_assignedemployeeid,
FNA.new_assettypename,
FNA.new_computertypename,
FNA.new_manufacturer,
FNA.new_model,
FNA.new_modelnumber,
FNA.new_assetsid
FROM dbo.FilteredNew_Assets FNA
WHERE FNA.new_assignedemployeeid IS NOT NULL And
FNA.New_assettypename =3D "Computer"
UNION ALL
SELECT
FNA.new_assignedemployeeid,
FNA.new_assettypename,
FNA.new_computertypename,
FNA.new_manufacturer,
FNA.new_model,
FNA.new_modelnumber,
FNA.new_assetsid
FROM dbo.FilteredNew_Assets FNA
WHERE FNA.new_assignedemployeeid IS NULL
) DerivedNewAssets ON dbo.FilteredNew_Employee.new_employeeid =3D
DerivedNewAssets.new_assignedemployeeid