Hilary and Paul

Thank you both for all your help. This board would be
sorely lacking without the two of you.
Hilary: Heres the deal. Your custom sync object was
working, mine wasn't.
@.sync_method = N'character'
That was the whole problem. I know you asked me to try
this a while back and I did, but I have no idea what else
I was doing back then that would have affected this
outcome. You're example on the 08/11 didnt specify (not
blaming you by any means) and by then I had forgot you're
mentioning it.
Paul: from you're reply @. 1:59 am. Do you ever sleep?
>
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is
increased overhead on
the publisher to maintain the materialized data.
I see the huge benifit being not needing to write the
ins,upd, and del procs with the Indexed View option. What
increased overhead are you refering too? Does the whole
Arithabort thing ever become a real pain?
Thanks again both of you.
ChrisR
ChrisR,
for an indexed view you've got 2 types of overhead:
a.. The disk space taken up by the view
a.. The cost of maintaining the view on disk as the base tables are
modified - this is conceptually like a set of invisible triggers.
BTW I'm not really such an SQL insomniac :-) I guess the post times are
Western US timezone times: UK - 7 hours.
Regards,
Paul Ibison
|||Ahhh. I forgot you were in the UK. Thanks.

>--Original Message--
>ChrisR,
>for an indexed view you've got 2 types of overhead:
>a.. The disk space taken up by the view
>a.. The cost of maintaining the view on disk as the base
tables are
>modified - this is conceptually like a set of invisible
triggers.
>BTW I'm not really such an SQL insomniac :-) I guess the
post times are
>Western US timezone times: UK - 7 hours.
>Regards,
>Paul Ibison
>
>.
>

Hiding subtotal rows when there is only 1 row in the group

How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.

If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.

Try this:

Click on the whole row for your group footer
Go to the properties
Put this in the 'Visibility - Hidden' expression.

=IIf(CountRows("GroupName") > 1, False, True)

I tried this on one of my reports and it removed the space used by the group footer, it didn't just blank it out. If there was only one row in that group, the footer wasn't shown, but if there were more than 1 row, it would. Just as a test, you might want to create a new row below your group footer and just add some text in there so that it will show below your subtotals (if you have any). In my case, the row below my group footer was 'moved up' to be directly below the details if there was only one row displayed, otherwise, it was displayed directly below the subtotals.

Hope this helps.

Jarret

|||

It's in a matrix, so group headers and footers aren't apparent options. :(

I could see how that would work in a table though.

|||

When you choose the subtotal option for a group in a matrix, a row does get added. To affect only the subtotal cell in a matrix you need to use the InScope() function. The main thing to understand in the logic is that the subtotal cell for a group is not in scope of that group and hence the function return false for the cell.

For example, say on your rows you have 2 groups called region_group and country_group. You right-click the country textbox and select Subtotal. This adds an additional row containing just the header textbos for the subtotal. You now 3 stages for hiding the subtotal.

1.You now need to add an expression to the details cell for the Visibility -> Hidden property. The expression should be:

=Not InScope("country_group")

This should evaluate to Hidden = True for the total row as it is not in scope of the country_group. If you run this you will probably find that the details cell disappears but the heading remains.

2.Now if you try applying the same expression for Visibility to the subtotal header textbox it should also disappear but will probably leave a blank gap in it's place.

3.If you can apply this same expression to the entire subtotal row (by clicking on the row header) then this should also remove the visible gap.

I'm not sure if the last step is possible as I am unable to test this at the moment (on client site), the first 2 steps should work though.

Hope this helps. Please post the results of your attempts.

|||

Those are great suggestions, but there are no header or footer rows in a matrix.
If I select the entire row that contains the subtotal, a visible property is not exposed.

There are also column groupings after the one I'm mentioning - and if I mess with the group visibility, the successive columns are hidden or blanked out.
I'll see what else can be done to acheive the row hiding.

|||Try taking a look at Actions