I have this MDX in RS:

SELECT
{measures.[net hours]} on COLUMNS,
NONEMPTYCROSSJOIN(Worker_code.children, Worker_name.children,
Worker_managerName.children, TimeDate.Week.children) on ROWS
FROM WorkHours
WHERE (Year.[2004], Month.[July], [Organization].[All Organization].[EPO -
Enterprise Program Office] )


I need to be able to count how many weeks are in the current month (or any
other period I have selected in the slicer dimension)... I would like to
account for the hiredate for the employee, going to be a member property.. i
can get that.. i just can't figure out how to get the coutn of the weeks.

(in other words, I'm not worried about the hiredate right now-- i just need
to figure out the weekcount and I'll get it from there I think)

I could even do the count of weeks as a different dataset..

Right now, my weeks look like this: 'Week 36', 'Week 37', etc

I am basically using this dataset in order to build a report that looks like
this:

EmployeeCode, EmployeeName, ManagerName, '4/15/2004', '4/22/2004', Total
Hours, ExpectedHours (number of weeks times 38.75) and Variance.

I have to do all of this on columns so that I can fit it into a matrix in
Reporting Services.

ps - year and month are Virtual Dims based off of the TimeDate dimension.. I
may eventually change this to one single dimension (move the filtering from
the where clause (slicerdimension) to
TimeDate.Year.[2004].Month.[July].Weeks.members -- I can do this right??

Thanks so much guys

Assuming that weeks don't span multiple months, then the set of
non-empty weeks for the query slicer can be counted and incorporated in
a calculated measure:

'Count(NonEmptyCrossJoin(TimeDate.Week.Children)) * 38.75'
[Measures].[Variance] as
'[Measures].[Expected Hours] - [Measures].[Net Hours]'
SELECT
{[Measures].[Net Hours], [Measures].[Expected Hours],
[Measures].[Variance]]} on COLUMNS,
NONEMPTYCROSSJOIN(Worker_code.children, Worker_name.children,
Worker_managerName.children, TimeDate.Week.children) on ROWS
FROM WorkHours
WHERE (Year.[2004], Month.[July],
[Organization].[All Organization].[EPO - Enterprise Program Office])

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

yeah.. i thought that was the right direction..

i almost wanted to add it as a member property to the month level..

thanks!!


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote