Group By Statement

[claws2rip]

is there a Group By statement i can use in my cube?

 

[issahar]

please, send the specific query that you want to ask I I'll try to help... Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

here's the query in SQL:

Select JobID, WorkType, City
From t_WorkOrder
Group By JobID, WorkType, City
Where WorkType ="CU"

Except for the Group By statement, the rest i know how to put in MDX

thanx

 

[issahar]

so, if you have WorkType dimension, you should put the
"{[WorkType].[CU]} On Rows" and so you will get on your result cellset rows that data grouped by WorkType Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

I have a WorkType dimension, but my main focus is to display # WorkTypes based on JobID. For every 1 JobID i can have multiple WorkTypes

i may have 6 Worktypes=CU under on JobID and management wants all CUs grouped together under one JobID
ex: i have 3 JobIDs

JobID 15 has 3 WorkTypes=CU
JobID 16 has 4 worktypes=CU
JobID 17 has 2 worktypes=CU

so instead of showing a count of 9, i want to show a count of 3, the number of JobIDs

Did i make sense, if i didnt , let me know

thank you
sam



so instead of displaying 6 records i really only want to display 1

 

[issahar]

Hi, Sam.
In my previous Message I gave you a wrong answer.
So, Excuse me.

Now...
If the JobId is a dimension in your OLAP databse
I think (but I am not sure) that you cannot do this because the WorkType
dimension (or level) is not the same hierarchy with your JobID dimension so you cannot slice WorkType dimension by another dimension that has not any parent/Child relationship to it - (actually I think that you cannot slice one dimension by another at all but there are some tricks...)

if you have JobID dimension, but if not - I don't see possibility to group something in OLAP by value that is not member of some dimension..

May be you can think about using Mining Models in Olap 2000 but I have no experience in this and I don't know how to do that..

If the resolution of this problem is very critic for you managers, you can think about creating a little realtime cube sliced by JobID dimension (make this dimension a ROLAP one because as I guess, the number of JobID can be huge???) and by WorkType dimension and as measure select the count of WorkType (count of Works?)...

Sorry If I confused you.
If you will find the right solution for this, please, notify me it will be very interesting to know...

Have a nice day.

Issahar
senior software engineer

http://www.softwatch.com

 

[claws2rip]

thanks for the info

if i figure it out, i'll let u know

Sam