Count Group Members

Hi,

I'm trying to make a matrix report for current and previous month. I've gotten the matrix to show current month dat on the top data region and previous month on the side data region. Now i'm trying to compare and count group members for current and previous month.

example: count for students with the same exam score. If a student got 20 on thier exam Previous month and 20 on their current month exam, they will be counted as 1.

20

40

60

80

100

20

2

4

0

7

1

60

0

1

0

4

0

80

2

0

3

0

0

so the data shows 2 students who got 20 last month also got 20 this month. It also shows 4 students who got 20 last month managed to get 40 this month, and so on.

How do i count the students as i'm using student ID.

Thanks in advance.

Hi,

count is overloaded. YOu can also use that with a group expressions COUNT(SomeValue, SomeGroup)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thanks for the reply. I've already tried that method. the count only produced 0, but there is data to be counted.

the data grouping is like this: Year -> Month -> Class -> ID -> Score becuase the score is the measure or detail data. However i group the data in the report by Year -> Month -> Class -> Score and then count the IDs that are associated with each distinct score value.

This is to compare how many student got a certain score amount and what are thier scores for the current month as in the sample I have given previous post.

Hope this is much clearer explanation than before.

|||Here's an idea for getting this to work. When querying for the data, join the table with itself, based on the student's id and the month. This will give you one dataset with the student's data for each month compared to each of the following months that you can use to build the matrix.
Using this data

TestDate ID Score
1/1/2000 1 20
1/1/2000 2 20
1/1/2000 3 20

1/1/2000 4 20
1/1/2000 5 20

1/1/2000 6 20
2/1/2000 1 20

2/1/2000 2 20

2/1/2000 3 40

2/1/2000 4 40

2/1/2000 5 40

2/1/2000 6 40

The query would look something like this

Select t1.TestDate AS TestDate, t2.TestDate AS TestDate2, t1.Score AS Score, t2.Score AS Score2, t1.ID FROM TestScores As t1 INNER JOIN TestScores As t2 ON t1.ID = t2.ID AND t1.TestDate < t2.TestDatesql

count for each month

I have 2 tables in Access 2000 : Members and Messages

I get all the members.Id for a category with a Procedure

List_Members_3 >>>

SELECT Members.Members_Id
FROM Members
WHERE Members.Cat = 3

then I want to get all the Messages of the Members for january 2004

SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id
GROUP BY Year([DateMessages]), Month([DateMessages])
HAVING (((Year([DateMessages]))=2004) AND ((Month([DateMessages]))=1));

I get one row = Count

how can I get 12 rows for each month ?

Month([DateMessages])=(1 to 12)

--------

and how can I avoid >>>

SELECT Count(Messages.Id) AS CountOfId
FROM Messages INNER JOIN List_Members_3 ON Messages.Id = List_Members_3.Id

with sommething like >>>

SELECT Count(Messages.Id) AS CountOfId
FROM Messages Where Messages.Members_Id IN (SELECT Members.Id
FROM Members
WHERE Members.Cat = 3)

thank youuse an integers table:

create table integers ( i integer )
insert into integers values ( 1 )
insert into integers values ( 2 )
insert into integers values ( 3)
insert into integers values ( 4)
insert into integers values ( 5)
insert into integers values ( 6)
insert into integers values ( 7)
insert into integers values ( 8)
insert into integers values ( 9)
insert into integers values ( 10 )
insert into integers values ( 11 )
insert into integers values ( 12 )

then use a LEFT join from the integers to your data using i to match the month number

select i as month
, Count(Messages.Id) as CountOfId
from integers
left outer
join Messages
on i = Month(DateMessages)
and Year(DateMessages) = 2004
inner
join List_Members_3
on Messages.Id = List_Members_3.Id
group
by i|||it seems absolutly crazy and fantastic ! ::-))

thank you !!!

count distinct

can you set up a measure with "countdistinct" where some column in fact tabl
e
<> 0
i want a count of members in my fact table where total_bal <> 0 by all
dimensions
does someone have an exampleYou can create a separate cube with a "Distinct Count" measure for the
member column, and set the "Source Table Filter" for the cube to
"total_bal <> 0". This cube can be combined with existing cubes in a
virtual cube.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I have cube with staff dimension which has staff_member_id as a level
Cube has metrics for example total_bal
I want to count distinct staff_member id's for each level in my time dimensi
on
daily, weekly, monthly etc where total_bal > 0
Is this possible to do within an MDX, can you send me an example of
something like this
Creating separate cubes would mean we have to create atleast 25 cubes as we
have 25 different measures we want to calculate like above
Thanks
"Deepak Puri" wrote:

> You can create a separate cube with a "Distinct Count" measure for the
> member column, and set the "Source Table Filter" for the cube to
> "total_bal <> 0". This cube can be combined with existing cubes in a
> virtual cube.
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Assuming that there is a [tot_bal] "sum" measure, based on a "tot_bal"
fact table field, and that this field is always >= 0, MDX can be used to
count [staff_member_id] members for a given node in the time hierarchy:
[vbcol=seagreen]
With Member [Measures].[StaffCount] as
'Count(Filter(NonEmptyCrossJoin(
[Staff].[staff_member_id].Members,
{[Time].CurrentMember}, 1),
[Measures].[tot_bal] > 0))'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||this is my exact MDX statement
i have result set out oof this MDX like this in columns which separate WTD,
MTD etc
MEMBERCAPTION thisdate wktd monthtd lastmonth ytd
1008 21 43 90 100 8
97
StaffCount 12 23 12 13 88
8
Can you help me write Measures.Staffcount please..
select 1 as col1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'aggregate(time.[20040430]:time.&#
91;20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[2
0040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):tim
e.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.
1;20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20
040430])'
-- Member [Measures].[StaffCount] as ''
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
select { [measures].[StaffCount] , [measures].[1008] }
on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")
"Deepak Puri" wrote:

> Assuming that there is a [tot_bal] "sum" measure, based on a "tot_bal"
> fact table field, and that this field is always >= 0, MDX can be used to
> count [staff_member_id] members for a given node in the time hierarchy
:
>
> With Member [Measures].[StaffCount] as
> 'Count(Filter(NonEmptyCrossJoin(
> [Staff].[staff_member_id].Members,
> {[Time].CurrentMember}, 1),
> [Measures].[tot_bal] > 0))'
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||To count staff where [Measures].[1008] > 0:
[vbcol=seagreen]
With Member [Measures].[StaffCount] as
'Count(Filter(
[Staff].[staff_member_id].Members,
[Measures].[1008] > 0))'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I tied this and it makes the MX extremely slow, 50 secs instead of 2 secs
which it was taking earlier
is there any way to speed this up or some other mechanism
"Deepak Puri" wrote:

> To count staff where [Measures].[1008] > 0:
>
> With Member [Measures].[StaffCount] as
> 'Count(Filter(
> [Staff].[staff_member_id].Members,
> [Measures].[1008] > 0))'
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||You could try AS 2005 (Yukon), which supports aggregation of distinct
count measures.
If the different time ranges are defined in Named Sets (like [YTDSet]),
then staff count for each set could be optimized by NonEmptyCrossJoin()
pre-filtering.
Short of these options, you could try optimizing the longer time ranges,
using the approach discussed in this thread, where Chris Webb discusses
the possibility to optimize a time series, and I posted some sample MDX
code. You can refer to Chris's BI Blog (referenced in this thread) for
more details:
http://groups-beta.google.com/_grou...serv_er.olap/m.
.
Newsgroups: microsoft.public.sqlserver.ola_p
Subject: RE: Problem with MDX query
Chris Webb Dec 31 2004, 4:01 am
- Hide quoted text -
- Show quoted text -
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/__cwebbbi/Blog/cns!1pi7ETChsJ1un___2s41jm9
I.|||Deepak
I could not go to the link and am not an expret an MDX to understand your
code so easily
can you pls re-write this according to your recomendation so it can improve
in speed
THANKS!!!!
select 1 as col1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'aggregate(time.[20040430]:time.&#
91;20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[2
0040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):tim
e.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.
1;20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20
040430])'
Member [Measures].[StaffCount] as
'Count(Filter([Staff].[agent].Members,[Measures].[CAS_IC_C]
> 0))'
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[1008], [Measures].[StaffCount] }
on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])")|||You should really work this out yourself, since I don't have your cube
to test against, but something like:
[vbcol=seagreen]
select 1 as col1, * from openquery (ROP_OLAP, "
with
set [ThisDateSet] as
'{time.[20040430]:time.[20040430]}'
set [WktdSet] as
'{time.[20040426]:time.[20040430]}'
set [MonthdSet] as
'{openingperiod([day],ancestor([20040430],[month])):time.&#
91;20040430]}'
set [LastMonthSet] as
'{time.[20040301]:time.[20040331]}'
set [YTDSet] as
'{time.[20040301]:time.[20040430]}'
Member [Measures].[InMonthd] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[MonthdSet]).Count
=
0'
Member [Measures].[InLastMonth] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[LastMonthSet]).Co
unt
= 0'
Member [Measures].[InYTD] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[YTDSet]).Count =
0'
Set [MonthdOpt] as 'Filter([Time].Members, [Measures].[InMon
thd]
AND Not ( [Measures].[InMonthd], [Time].Parent))'
Set [LastMonthOpt] as 'Filter([Time].Members, [Measures].[In
LastMonth]
AND Not ( [Measures].[InLastMonth], [Time].Parent))'
Set [YTDOpt] as 'Filter([Time].Members, [Measures].[InYTD]
AND Not ( [Measures].[InYTD], [Time].Parent))'
member [time].[thisdate] as 'aggregate([WktdSet])'
member [time].[wktd] as 'aggregate([WktdSet])'
member [time].[monthtd] as 'aggregate([MonthdOpt])'
member [time].[lastmonth] as 'aggregate([LastMonthOpt])'
member [time].[ytd] as 'aggregate([YTDOpt])'
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
Member [Measures].[StaffCount] as
'Count(Filter(NonEmptyCrossJoin(
[Staff].[staff_member_id].Members),
[Measures].[CAS_IC_C] > 0))'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[1008], [Measures].[StaffCount]
} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])")[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!