Collapse all details in SSRS 2005 report?

Hello,

I have a report that contains a single group for customers. Each customer order is placed as a detail record under the group. I have the group header setup to toggle the detail records (so if a user clicks the plus sign image, the details are shown, if the user clicks the minus image, the details are hidden). By default when the report opens up the details are shown.

Rather than having the user click through each customer row to collapse all the details, is it possible to place a "collapse all" image or control somewhere on the report that will handle the collapsing of all the detail rows for each customer record? I assume if there is a way, there is something similar for expanding all as well?

Thanks!

The "initial state" of toggles on groups can be determined by expressions. You could add e.g. a boolean report parameter and then set the initial toggle state of all toggles to be based on the report parameter value.

-- Robert

Cmdexec on SQL 2000

On a SQL 2000 I am running a Job with the following single step:
xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
The job executes and report success, however only the first line has
been carried out. Is it not possible to run several lines in a single
step?Hello,
Put all this commands in a single batch (.BAT) file and use the BAT file
name inside Agent Job and try?
Thanks
Hari
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1175843398.821347.252100@.n76g2000hsh.googlegroups.com...
> On a SQL 2000 I am running a Job with the following single step:
> xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
> xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
> xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
> xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
> The job executes and report success, however only the first line has
> been carried out. Is it not possible to run several lines in a single
> step?
>

Cmdexec on SQL 2000

On a SQL 2000 I am running a Job with the following single step:
xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
The job executes and report success, however only the first line has
been carried out. Is it not possible to run several lines in a single
step?Hello,
Put all this commands in a single batch (.BAT) file and use the BAT file
name inside Agent Job and try?
Thanks
Hari
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1175843398.821347.252100@.n76g2000hsh.googlegroups.com...
> On a SQL 2000 I am running a Job with the following single step:
> xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
> xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
> xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
> xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
> The job executes and report success, however only the first line has
> been carried out. Is it not possible to run several lines in a single
> step?
>

Clustered SQL 2000 and MSDE

I'm reviewing a plan put forward by a colleague for a 3-node Windows 2003
cluster hosting a single SQL Server 2000 (for now), and a clustered
fileshare. Each role would ideally run on it's own server. The third node
would only come into play in the event of failure of either of the first two.
The plan is for the third (normally idle) node to have Backup Exec installed
on it, not in a cluster-aware configuration, but as a standalone application,
which could then be used to backup the other servers.
My concern is the MSDE used by Backup Exec as a local data repository. How
would this interact with the installation of SQL 2000? Are there any caveats,
gotchas, no-no's we should be aware of?
Problems. The third node would need to be clustered, if you want to use it
for failover.
As far as I know you should not/can't run MSDE and SQL 2000 on the same
machine. BackupExec can use SQL for its DB.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"David Cornes" <David Cornes@.discussions.microsoft.com> wrote in message
news:B011807F-68E7-4D49-AC33-74415178E116@.microsoft.com...
> I'm reviewing a plan put forward by a colleague for a 3-node Windows 2003
> cluster hosting a single SQL Server 2000 (for now), and a clustered
> fileshare. Each role would ideally run on it's own server. The third node
> would only come into play in the event of failure of either of the first
> two.
> The plan is for the third (normally idle) node to have Backup Exec
> installed
> on it, not in a cluster-aware configuration, but as a standalone
> application,
> which could then be used to backup the other servers.
> My concern is the MSDE used by Backup Exec as a local data repository. How
> would this interact with the installation of SQL 2000? Are there any
> caveats,
> gotchas, no-no's we should be aware of?
|||The thirds node WILL be clustered for the purposes of hosting SQL Server and
the file share(s), but Backup Exec WON'T be installed into a cluster group,
and won't be able to move between the nodes.
You say MSDE and SQL 2000 can't exist on the same server? That was my
concern. Although BE could use the clustered SQL Server, Veritas recommend
using a separate data store for the application to the one(s) you wish to
back up.
"Rodney R. Fournier [MVP]" wrote:

> Problems. The third node would need to be clustered, if you want to use it
> for failover.
> As far as I know you should not/can't run MSDE and SQL 2000 on the same
> machine. BackupExec can use SQL for its DB.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> "David Cornes" <David Cornes@.discussions.microsoft.com> wrote in message
> news:B011807F-68E7-4D49-AC33-74415178E116@.microsoft.com...
>
>
|||Got it, I was not sure if you were really taking 3 node cluster or not.
I have configured BE with the database on SQL and it works fine. I would not
even try to run MSDE. I actually don't even like running BE on a clustered
node. I like to run it from another machine and use a dedicated backup
network. The BE network compression is very good.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"David Cornes" <DavidCornes@.discussions.microsoft.com> wrote in message
news:6C932AFA-46F6-494C-851D-0400E35F0DB6@.microsoft.com...[vbcol=seagreen]
> The thirds node WILL be clustered for the purposes of hosting SQL Server
> and
> the file share(s), but Backup Exec WON'T be installed into a cluster
> group,
> and won't be able to move between the nodes.
> You say MSDE and SQL 2000 can't exist on the same server? That was my
> concern. Although BE could use the clustered SQL Server, Veritas recommend
> using a separate data store for the application to the one(s) you wish to
> back up.
>
> "Rodney R. Fournier [MVP]" wrote:
|||A separate server would be my preferred approach too, but I wanted to get
some technical ammo before suggesting it. I'll show this thread to the TA...
;-)
Thanks for your help.
"Rodney R. Fournier [MVP]" wrote:

> Got it, I was not sure if you were really taking 3 node cluster or not.
> I have configured BE with the database on SQL and it works fine. I would not
> even try to run MSDE. I actually don't even like running BE on a clustered
> node. I like to run it from another machine and use a dedicated backup
> network. The BE network compression is very good.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> "David Cornes" <DavidCornes@.discussions.microsoft.com> wrote in message
> news:6C932AFA-46F6-494C-851D-0400E35F0DB6@.microsoft.com...
>
>
|||If you want, they can even call me, email me for the number.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"David Cornes" <DavidCornes@.discussions.microsoft.com> wrote in message
news:E4AA76D9-A48E-407D-A001-E18DE3C242B6@.microsoft.com...[vbcol=seagreen]
>A separate server would be my preferred approach too, but I wanted to get
> some technical ammo before suggesting it. I'll show this thread to the
> TA...
> ;-)
> Thanks for your help.
>
> "Rodney R. Fournier [MVP]" wrote:
|||I strongly prefer to have a console and management server for a big cluster.
That server hosts management and monitoring tools, backup file shares, tape
libraries, and anything else I need to support the cluster. Because many of
the tools I use require a SQL server back end, I go ahead and run Standard
Edition SQL server on the box. I have found such a system a key component
of a total high-availability solution.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Cornes" <DavidCornes@.discussions.microsoft.com> wrote in message
news:E4AA76D9-A48E-407D-A001-E18DE3C242B6@.microsoft.com...[vbcol=seagreen]
>A separate server would be my preferred approach too, but I wanted to get
> some technical ammo before suggesting it. I'll show this thread to the
> TA...
> ;-)
> Thanks for your help.
>
> "Rodney R. Fournier [MVP]" wrote:
|||I have had several customers running Backup Exec with the Backup Exec local
instance of SQL being MSDE with no issues.
I would recomend that database be upgraded to a normal SQL Server edtion if
possible, the only issue is licencing for the addtional SQL instance.
In either case I can recomend you keeping an eye on and possibiy limit the
memory usage of the BE instance as I have had the memory usage get quite
high and when you need to fail the clustered instance over there is memory
contention on startup.
If you are using LAN-free backups (ie backups via the SAN) you may want to
spend some effort on clustering the Backup Exec, if your are installing it
on the cluster anyway.
Regards
Gary Hope
iSolve Business Solutions
"David Cornes" <DavidCornes@.discussions.microsoft.com> wrote in message
news:B011807F-68E7-4D49-AC33-74415178E116@.microsoft.com...
> I'm reviewing a plan put forward by a colleague for a 3-node Windows 2003
> cluster hosting a single SQL Server 2000 (for now), and a clustered
> fileshare. Each role would ideally run on it's own server. The third node
> would only come into play in the event of failure of either of the first
> two.
> The plan is for the third (normally idle) node to have Backup Exec
> installed
> on it, not in a cluster-aware configuration, but as a standalone
> application,
> which could then be used to backup the other servers.
> My concern is the MSDE used by Backup Exec as a local data repository. How
> would this interact with the installation of SQL 2000? Are there any
> caveats,
> gotchas, no-no's we should be aware of?

Clustered Replication - Whose IP is It

I have a data warehouse transactional replication environment in which
we have muliple Publisher/Distributors(100+) and a single subscriber.
All Publishers and subscribers are SQL 2000.
We recently converted the subscriber box to a Win2003
Cluster.(Publishers are Win2K for the most part, there may be a couple
remaining NT machines)
The publishers are on a very large WAN and are replicating to a central
location. Because of the large WAN environment, there are a number of
network filters in place and traffic management is of the highest
concern.
Replication IS working. However, there is one anomoly that I would
like to know about (How to control/change).
Before the Cluster, replication was moving to the DNS IP of the single
subscriber. (lets call it .14)
When the cluster was installed the SQL Cluster was given the same IP
(.14). Each of the nodes were given an IP, .72 & .70 (.72 is the active
node)
Now as replication and snapshots occur it is happening between the
publisher and the .72 node.
Why is this happening and is there a way to control this?
Thanks Rum63
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Snapshots are running to a UNC file share based on the host node. You will
need to either snapshot to a clustered instance of a file share OR snapshot
to an FTP server (clustered or not is your choice).
Geoff N. Hiten
Microsoft SQL Server MVP
"rum63" <nospam@.devdex.com> wrote in message
news:eoS2N$WKFHA.3640@.TK2MSFTNGP12.phx.gbl...
>I have a data warehouse transactional replication environment in which
> we have muliple Publisher/Distributors(100+) and a single subscriber.
> All Publishers and subscribers are SQL 2000.
> We recently converted the subscriber box to a Win2003
> Cluster.(Publishers are Win2K for the most part, there may be a couple
> remaining NT machines)
> The publishers are on a very large WAN and are replicating to a central
> location. Because of the large WAN environment, there are a number of
> network filters in place and traffic management is of the highest
> concern.
> Replication IS working. However, there is one anomoly that I would
> like to know about (How to control/change).
> Before the Cluster, replication was moving to the DNS IP of the single
> subscriber. (lets call it .14)
> When the cluster was installed the SQL Cluster was given the same IP
> (.14). Each of the nodes were given an IP, .72 & .70 (.72 is the active
> node)
> Now as replication and snapshots occur it is happening between the
> publisher and the .72 node.
> Why is this happening and is there a way to control this?
> Thanks Rum63
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Clustered index with a truncate table operation question.

I have a situation at here that appears once in a while:
The table in question has a single clustered index on ReferenceID. It's
populated by the following process: Truncatae table MyTable, run DTS to
populate the table.
This runs every xx minutes.
The table has roughly 50K records but the records are large.
The problem occurs during a query similar to the following:
select ...
from MyTable
LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
MyTable.AssociatedID = seconds.MasterID) as Calculated on
Calculated.ReferenceID = MyTable.ReferenceID
At one point this query, which normally takes 30 seconds or so, went to over
16 minutes to complete consistently.
Eventually we ran
dbcc dbreindex(MyTable)
and that seems to have fixed it.
My question is why: We clear the table before repopulating, how did the
index get so jacked up?
Thanks,
EricYour problem is likely caused by internal or external Index Fragmentation
(based on your reporting that dbreindex() fixed the problem... This is cause
d
by having a clusterd index on a field (or COmbination od fields) for which
inserts are randomly dispersed. So, if your CLustered Index is on
ReferenceID, try to ensure that DTS sorts it's data by ReferenceID before it
runs... Then the pages in the clustered Index will be as full as possible,
and both internal and external fragmentation will be at a minimum. If you
can;t do this, then you need t orun dbcc reindex after every DTS run, or
choose a different Clustered Index that you can sort the DTS import data on.
.
"Eric" wrote:

> I have a situation at here that appears once in a while:
> The table in question has a single clustered index on ReferenceID. It's
> populated by the following process: Truncatae table MyTable, run DTS to
> populate the table.
> This runs every xx minutes.
> The table has roughly 50K records but the records are large.
> The problem occurs during a query similar to the following:
> select ...
> from MyTable
> LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
> MyTable.AssociatedID = seconds.MasterID) as Calculated on
> Calculated.ReferenceID = MyTable.ReferenceID
>
> At one point this query, which normally takes 30 seconds or so, went to ov
er
> 16 minutes to complete consistently.
> Eventually we ran
> dbcc dbreindex(MyTable)
> and that seems to have fixed it.
> My question is why: We clear the table before repopulating, how did the
> index get so jacked up?
> Thanks,
> Eric
>
>