Parent-Child Hierarchy but reversed(!)

Hi!

I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly.

However, instead of having it look like this(simple example):

+ Profit

After the + has been clicked:

-Profit
+Sales
+Cost

I would like it to look like this:

+Sales
+Cost
-Profit

Is this possible with when using a parent-child hierarchy?

http://ssasfreak.spaces.live.com/

Have you tried using the group footer row instead of the group header row to control visibility?|||

Yes. That only works when you have the diffrent levels in different fields. Like this:

Level1 Level2 Level2

I use the parentgroup property(for the grouping) with a recordset that looks like this:

LevelID LevelName ParentLevelID

Any ideas?

Parent Child index?

I have a table that has recursive relationship with itself. The primary key
is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier). I
would like to create an index for these two fields, including cascading
deletes (regardless of the number of decendants). Is this possible? If so,
can someone step me thru it?
Thanks,
Craig BuchananHi
You can't create a FK with cascading deletes when the FK refers to the same
table. If you implemented this with a trigger you would be restricted to 32
levels. What you may want to do is to run a batch process that clears up the
table.
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message
news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> I have a table that has recursive relationship with itself. The primary
key
> is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier).
I
> would like to create an index for these two fields, including cascading
> deletes (regardless of the number of decendants). Is this possible? If
so,
> can someone step me thru it?
> Thanks,
> Craig Buchanan
>|||John-
How would I create a trigger between two tables where PK and FK are
uniqueidentifiers?
Thanks,
Craig
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> Hi
> You can't create a FK with cascading deletes when the FK refers to the
same
> table. If you implemented this with a trigger you would be restricted to
32
> levels. What you may want to do is to run a batch process that clears up
the
> table.
> John
> "Craig Buchanan" <someone@.microsoft.com> wrote in message
> news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> key
(uniqueidentifier).
> I
> so,
>|||Hi Craig
Something like:
CREATE TABLE ParentChildTable ( id int not null CONSTRAINT
PK_ParentChild PRIMARY KEY,
parentid int not null )
DROP TABLE ParentChildTable
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
DELETE FROM ParentChildTable
FROM ParentChildTable P JOIN DELETED D ON P.Parentid = d.id
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
sp_configure 'nested triggers'
/*
Needs to be 1
name minimum maximum
config_value run_value
-- -- --
-- --
nested triggers 0 1 1
1
*/
sp_dboption testdb, 'recursive triggers'
/*
Needs to be ON
OptionName CurrentSetting
-- --
recursive triggers ON
*/
DELETE FROM ParentChildTable WHERE id = 3
/* id parentid
-- --
3 2
(1 row(s) affected)
id parentid
-- --
4 3
(1 row(s) affected)
id parentid
-- --
5 4
(1 row(s) affected)
id parentid
-- --
6 5
(1 row(s) affected)
id parentid
-- --
7 6
(1 row(s) affected)
id parentid
-- --
8 7
(1 row(s) affected)
id parentid
-- --
9 8
(1 row(s) affected)
id parentid
-- --
10 9
(1 row(s) affected)
id parentid
-- --
11 10
(1 row(s) affected)
id parentid
-- --
12 11
(1 row(s) affected)
id parentid
-- --
13 12
(1 row(s) affected)
id parentid
-- --
14 13
(1 row(s) affected)
id parentid
-- --
15 14
(1 row(s) affected)
id parentid
-- --
16 15
(1 row(s) affected)
id parentid
-- --
17 16
(1 row(s) affected)
id parentid
-- --
18 17
(1 row(s) affected)
id parentid
-- --
19 18
(1 row(s) affected)
id parentid
-- --
20 19
(1 row(s) affected)
id parentid
-- --
21 20
(1 row(s) affected)
id parentid
-- --
22 21
(1 row(s) affected)
id parentid
-- --
23 22
(1 row(s) affected)
id parentid
-- --
24 23
(1 row(s) affected)
id parentid
-- --
25 24
(1 row(s) affected)
id parentid
-- --
26 25
(1 row(s) affected)
id parentid
-- --
27 26
(1 row(s) affected)
id parentid
-- --
28 27
(1 row(s) affected)
id parentid
-- --
29 28
(1 row(s) affected)
id parentid
-- --
30 29
(1 row(s) affected)
id parentid
-- --
31 30
(1 row(s) affected)
id parentid
-- --
32 31
(1 row(s) affected)
id parentid
-- --
33 32
(1 row(s) affected)
id parentid
-- --
34 33
(1 row(s) affected)
Server: Msg 217, Level 16, State 1, Procedure TRG_ParentChildTable,
Line 7
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
*/
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message news:<eOG$Y6BEEHA.2404@.TK2MSFTNGP
11.phx.gbl>...
> John-
> How would I create a trigger between two tables where PK and FK are
> uniqueidentifiers?
> Thanks,
> Craig
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> same
> 32
> the
> key
> (uniqueidentifier).
> I
> so,|||Hi Craig
This may be an alternative, but if it may cause contention on this table and
you may be better off running a scheduled task to do the deletions in a qui
et period.
sp_dboption testdb, 'recursive triggers', false
sp_dboption testdb, 'recursive triggers'
/*
Needs to be off
OptionName CurrentSetting
-- --
recursive triggers off
*/
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
WHILE @.@.ROWCOUNT > 0
DELETE FROM ParentChildTable
FROM ParentChildTable
WHERE ParentId <> 0
AND ParentId NOT IN ( SELECT Id FROM ParentChildTable )
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
DELETE FROM ParentChildTable WHERE Id = 3
SELECT * FROM ParentChildTable
John

Partitioned view doesn't allow to query itself while insertion

I created 2 similar tables for different accounts within one database and
partitioned view on them.
create table t
(
[ID] int identity(1,1),
[Name] varchar(15),
[Account] varchar(20) default 'Account1' check(Team = 'Account1'),
PRIMARY KEY CLUSTERED
(
[ID], [Account]
)
)
create view v
as
select * from Account1.t
union all
select * from Account2.t
I run query like this:
"select top 100 * from v where Account = 'Account1'"
and massive insertion from staging table like this at the same time:
"insert into t ( name ) select name from tt under different account"
my query stops until insertion is completed.
FYI: each tables reside in it's own filegroup. Each filgroup has its files
on physically different HDD.
There are 2 multithreaded processors there and parallelism works.
Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so o
n.
Isolation level is Read Commited (when I set read uncommited it works
without locks, of course).
I don't know why my query wait for end of insertion. Query and insertion
don't use common resources. They MUST works independently...Can you cut and paste the exact SQL you're running, or provide
a repro without typos? What you posted here includes a CHECK
constraint that refers to a non-existent column named [Team].
You might try reversing the order of the primary key columns.
Steve Kass
Drew University
OSA wrote:

>I created 2 similar tables for different accounts within one database and
>partitioned view on them.
>create table t
>(
> [ID] int identity(1,1),
> [Name] varchar(15),
> [Account] varchar(20) default 'Account1' check(Team = 'Account1'),
> PRIMARY KEY CLUSTERED
> (
> [ID], [Account]
> )
> )
>create view v
>as
> select * from Account1.t
> union all
> select * from Account2.t
>I run query like this:
>"select top 100 * from v where Account = 'Account1'"
> and massive insertion from staging table like this at the same time:
>"insert into t ( name ) select name from tt under different account"
>my query stops until insertion is completed.
>FYI: each tables reside in it's own filegroup. Each filgroup has its files
>on physically different HDD.
>There are 2 multithreaded processors there and parallelism works.
>Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so
on.
>Isolation level is Read Commited (when I set read uncommited it works
>without locks, of course).
>I don't know why my query wait for end of insertion. Query and insertion
>don't use common resources. They MUST works independently...
>