Creating Total to-date query...

I am trying to create a query where
the data is for a time period but there is
one column that would show a Total-to-date.

I've tried google and searching this forum
but everyone talks about year-to-date or
period-to-date but I don't want that. I would
like to do :
[from whenever data started being entered]-to-date

I know I can hack this by doing an aggregate like
so:

WITH MEMBER [Timesheet].[Date].[TimeChargedTotal] AS
'Aggregate ( { [Timesheet].[Date].[Year].&[1900].&[1].&[1]:[Timesheet].[Date].[Year].&[2006].&[9].&[15] } )'

Here I am betting on the fact that there is no data before 1-1-1900
which is a safe bet, but I'd prefer to do it the 'right' way if there is one.

Thanks.

If you're using AS 2005, then you can try the shorthand:

{ NULL : [Timesheet].[Date].[Year].&[2006].&[9].&[15] }

|||

Something like [Time].[Date].FirstChild:[Time].[Date].Lastchild might help. Be aware of performance issues with a large dimension.

Regards

Thomas Ivarsson

|||

Thanks guys.

Both answers work.
Is there any performance difference between
using NULL or [Time].[Date].FirstChild as the
beginning of the range?

|||Well, I'm still unclear about the usage scenario - if the calculated member is defined on the [Time].[Date] hierarchy, then on which hierarchy will the user select a time period? If you can translate this to the Adventure Works cube, that would help clarify.|||

I am not familiar enough with adventureworks
to give a proper example.

However I will attempt to explain my scenario.
We have a timesheet system where time spent
on various projects/tasks is collected and then
billed to a client.

A report needs to be created that shows the
previous weeks time (a particualr 1 week period with
a start and end date) spent on a particular project/task
by employee X and the total time spent so far on that
project/task by emplyee X up until the end of that period.
This means that year-to-date (or period-to-date) scenario
does not work because we want ALL time up until
the end of the selected period and there is no guarantee
that a project will last a week, month, year, etc...

Hope that somewhat clarifies the usage scenario.

|||

Okay this is not working the way I anticipated.
I am clearly not understanding MDX and the book
I am refering to:
'SQL Server 2005 Analysis Services 2005 with MDX'
is of no help.

Here is what I am trying to accomplish conceptually:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'AGGREGATE ( {NULL:@.ToTimesheetDate} )'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
([Project].[Project Number].[Project Number].ALLMEMBERS
* [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
}
ON ROWS
FROM
(
SELECT ( STRTOSET(@.ProjectProjectNumber, CONSTRAINED) ) ON COLUMNS
FROM
(
SELECT ( STRTOMEMBER(@.FromTimesheetDate, CONSTRAINED)
: STRTOMEMBER(@.ToTimesheetDate, CONSTRAINED) ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

So the moral of the story here is that I want to get
a list of all the projects, then all the users that have
charged time to each project. Then I want the total
hours for the time range specified AS WELL AS the
total time charge up until the cutoff date (@.ToTimesheetDate).

Now as I have just learned you cannot have aggregates
in your Measures, so can some kind soul please
point me to a source where I can ejumicate myself on
this MDX magic? It seems that its much more difficult
to operate with ranges of time in MDX than I though it should be.

|||If you are trying to do something like I
described above, here is how to do it:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
([Project].[Project Number].&[PA01203] * [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]} ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

|||If you are trying to do something like I
described above, here is how to do it:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
(
[Project].[Project Number].&[PA01203] *
[Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS
)
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]}
ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)