Joining Two Measure Groups

Hey all,

Our product structure is:

Category

Class

Subclass

Item

We have 1 measure group by category, and another measure group by item. So we are forced to join by Category. Which works fine except for whereever we join on Category some reason the measures get rolled down all the way to the Item level.

Example:

Category Sales

00001 50.75

Product Measure Group 1 Measure Group 2
Category 00001 50.75 50.75

Class 00002 0.00 50.75

Subclass 00003 0.00 50.75

Item 00004 0.00 50.75

It's important to note Measure Group 1 is ONLY at the category level. Some reason it rolls down when to joni to Measure Group 2? Any ideas how to prevent this?

Thanks a lot.

Hello! I have written a short post about this problem on my blog: http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!381.entry

My example is from the Adventure Works project but the way to solve this should work for your problem also.

HTH

Thomas Ivarsson

|||

Perfect - thanks!

Join the Result of xp_logininfo with a table

Hello group!

i have a table "group_code" wich relates the names of
nt-(domain)-groups to codes. now i want use the stored procedure
xp_logininfo (asking for the group-membership of the current user) to
join the result to "group_code". then i must use the new result (the
code) to join against other tables.

i know now, that i cant join results of SPs against tables. may be that
a UDF with a table result is the correct approach. but i have no idea
how to wrap the xp_logininfo in a UDF.

a other way can be to do in a UDF the same thing like the xp_logininfo.
then this UDF should deliver at least the nt-(domain)-groups in wich
the current-user is a member.

Is there anybody who can give me the code for that?
many thanx in advance.

Karl(Karl.Auer@.gmail.com) writes:
> i have a table "group_code" wich relates the names of
> nt-(domain)-groups to codes. now i want use the stored procedure
> xp_logininfo (asking for the group-membership of the current user) to
> join the result to "group_code". then i must use the new result (the
> code) to join against other tables.
> i know now, that i cant join results of SPs against tables. may be that
> a UDF with a table result is the correct approach. but i have no idea
> how to wrap the xp_logininfo in a UDF.
> a other way can be to do in a UDF the same thing like the xp_logininfo.
> then this UDF should deliver at least the nt-(domain)-groups in wich
> the current-user is a member.

I don't think you can do this with a UDF. You can do:

CREATE TABLE #temp (...)
INSERT #temp (...)
EXEC master..xp_logininfo

But you cannot do this in a UDF, since you cannot create temp
tables in a UDF, and you cannot do INSERT EXEC on a table variable
if memory serves.

As for how #temp in the example should be created, see the definition
of the columns for xp_logininfo in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp