Hierarchy is used more than once in the Crossjoin function

When I do this in Adventure Works:

SELECT NON EMPTY {
[Customer].[Customer Geography].[State-Province].Members *
[Customer].[Customer Geography].[City].Members
}
ON AXIS(0)
FROM [Adventure Works]

I get this: "The Customer Geography hierarchy is used more than once in the Crossjoin function."

How do I do the equivalent in an MDX statement that works? I want users to be able to drag multiple hierarchy levels onto the same axis. I have spyed on the MDX that BIDS is generating, and it seems to be cheating by not getting the states and citys back in the same MDX statement. It looks like its combining the results of two queries at run-time as the user expands nodes in the tree. Isn't there a better way?

Thanks,

Terry

Hi Terry,

Any reason why cross-joining the attribute hierarchies instead won't work for you, like:

SELECT NON EMPTY {

[Customer].[State-Province].[State-Province].Members *

[Customer].[City].[City].Members

}

ON AXIS(0)

FROM [Adventure Works]

|||

Hi Deepak,

Thanks for your reply. Our application allows users to drag-and-drop whatever they want, so in this instance I don't want to prevent the user from putting two hierarchy levels on the same axis. Maybe the solution is somehow use metadata to find the attribute names that built the hierarchy levels and use the attribute names behind the scenes in the crossjoin function? Is that possible?

|||You can use AMO to retrieve the necessary metadata to identify which attribute underlies a hierarchy level and its corresponding attribute hierarchy.

hierarchical trees

Hi all and thanx in advance...

There is a command in Oracle which is "connect by prior" and i think it's a function to build "hierarchical trees", so where can I find the source of this function, or if anybody knows it please tell me...

What i mean is there would be an SQL function before "connect by prior" function written...

Example... I can write a function called called sub

function sub(x,y)
{
Z= X - Y
return z
}

So and then in my program i can use z=sub(5,3) instead of z=5-3, i think connect by prior is same thing but of course longer coding....
Where can i get this code?Next MSSQLSERVER version will have ANSI resursive join implemented (maybe).
See http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20514065.html
for hints and resources where to start.

Good luck !

Hierarchical data in result set

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2

How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.

Thanks.

I didnot quite understand your question!

Is the "HierarchicalLevel" value calculated ? or it was stored in the DB?

IF it is the first case:

you'd better get the date first in the application layer,and then use recursion ways to calculated the "HierarchicalLevel" value .

it is easy to calculate the value in the application layer

|||

Shieldy, the HierarchicalLevel is a number to indicate in which node level the registry is.

So, imagine a family tree.

Grandfather __ Uncle
|
Father Daughter
|
Son

Grandfather is the level 0, the most high level on this hierarchy. Father and Uncle are the level 1, because they are under the level 0, its parent. And Son and Daughter are the level 2.

Just a detail, if I search for relationships about father, this changes.

Father -- Daughter
|
Son

In this case Father is the level 0, Daughter and Son are the level 1.

If you don't understand this examples, I can try explain it better.

|||

Hi Juliano,

The sequence of code that does what you need is inserted bellow. I wrote it based on the "The Guru's Guide To Transact-SQL" book.

DECLARE @.Categories TABLE (CategoryID Int, CategoryName Varchar(20), CategoryFather Int)

INSERT INTO @.Categories
VALUES (1, 'Video', 0)
INSERT INTO @.Categories
VALUES (2, 'DivX', 1)
INSERT INTO @.Categories
VALUES (3, 'WMV', 1)
INSERT INTO @.Categories
VALUES (4, 'Programming', 0)
INSERT INTO @.Categories
VALUES (5, 'Web', 4)
INSERT INTO @.Categories
VALUES (6, 'ASP.Net', 5)
INSERT INTO @.Categories
VALUES (7, 'ColdFusion', 5)

DECLARE @.TempCategories TABLE (HierarchicalLevel Int, CategoryID Int, CategoryFather Int)
INSERT INTO @.TempCategories (HierarchicalLevel, CategoryID, CategoryFather)
SELECT 1, CategoryID, CategoryFather
FROM @.Categories

WHILE (@.@.RowCount > 0)
BEGIN
INSERT INTO @.TempCategories (HierarchicalLevel, CategoryID, CategoryFather)
SELECT DISTINCT c1.HierarchicalLevel+1, c2.CategoryID, c1.CategoryFather
FROM @.TempCategories c1
INNER JOIN @.TempCategories c2 ON c1.CategoryID = c2.CategoryFather
WHERE c1.HierarchicalLevel=(SELECT MAX(HierarchicalLevel) FROM @.TempCategories)
AND c1.CategoryFather<>c2.CategoryID
END

SELECT t.CategoryID, c.CategoryName, c.CategoryFather, HierarchicalLevel = MAX(t.HierarchicalLevel)-1
FROM @.TempCategories t
INNER JOIN @.Categories c ON t.CategoryID=c.CategoryID
GROUP BY t.CategoryID, c.CategoryName, c.CategoryFather

Hope it helps!

|||

hi there

it looks like you need

to implement this using a self join.

Using Self-Joins

A table can be joined to itself in a self-join. For example, you can use a self-join to find out the authors in Oakland, California who live in the same ZIP Code area.

Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:

USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' ORDER BY au1.au_fname ASC, au1.au_lname ASC 

Here is the result set:

au_fname au_lname au_fname au_lname -- - -- Dean Straight Dean Straight Dean Straight Dirk Stringer Dean Straight Livia Karsen Dirk Stringer Dean Straight Dirk Stringer Dirk Stringer Dirk Stringer Livia Karsen Livia Karsen Dean Straight Livia Karsen Dirk Stringer Livia Karsen Livia Karsen Marjorie Green Marjorie Green Stearns MacFeather Stearns MacFeather (11 row(s) affected) 

To eliminate the rows in the results in which the authors match themselves and to eliminate rows that are identical, except the order of the authors is reversed, make this change to the Transact-SQL self-join query:

USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' AND au1.state = 'CA' AND au1.au_id < au2.au_id ORDER BY au1.au_lname ASC, au1.au_fname ASC 

Here is the result set:

au_fname au_lname au_fname au_lname -- -- -- Dean Straight Dirk Stringer Dean Straight Livia Karsen Dirk Stringer Livia Karsen (3 row(s) affected) 

It is now clear that Dean Straight, Dirk Stringer, and Livia Karsen all have the same ZIP Code and live in Oakland, California.

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