MS SQL Server OLAP - problem with date range

Author

problem with date range

mickmack

2005-12-19, 7:24 am

Hi,

I am running SQL Server 2000 with Analysis Services and Reporting
Services. I would like to create a report in RS which contains data in
a special date range. The date range is defined by report parameter
with the type of DateTime.

Now I am trying do build a mdx query which returns all data in the date
range.

My first attemp was to create a memberfield like:
SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '

Unfortunatly this method creates some problems. First the named
dates "2005/12/01" and "2005/12/19" have to exist in my database.
Besides, my time dimension contains weeks instead of month. so this
way is not possible, unless I am programming a function which is
calculating a week number of a date. But this way is not the best one.

So I start using the filter() function. But I never got it running
correctly.
So how do I have to write an mdx-query returnning date between to
dates?

thanks

Darren Gosbell

2005-12-19, 7:24 am

Setup the parameters as lists to be populated from a query which hits
the cube and only returns valid date members. You can then use the
unique names of the selected members to construct your query.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1134989274.616499.273910@g44g2000cwa.googlegroups.com>,
access.20. mickmack@spamgourmet
.com says...
> Hi,
>
> I am running SQL Server 2000 with Analysis Services and Reporting
> Services. I would like to create a report in RS which contains data in
> a special date range. The date range is defined by report parameter
> with the type of DateTime.
>
> Now I am trying do build a mdx query which returns all data in the date
> range.
>
> My first attemp was to create a memberfield like:
> SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
>
> Unfortunatly this method creates some problems. First the named
> dates "2005/12/01" and "2005/12/19" have to exist in my database.
> Besides, my time dimension contains weeks instead of month. so this
> way is not possible, unless I am programming a function which is
> calculating a week number of a date. But this way is not the best one.
>
> So I start using the filter() function. But I never got it running
> correctly.
> So how do I have to write an mdx-query returnning date between to
> dates?
>
> thanks
>
>

mickmack

2005-12-19, 7:24 am

Hi Darren,

thanks for your prompt response.

I guess, that is an good idea. But I have to time dimensions, one with
an week-level and one with a month-level. When I create a list for the
parameters out of the cube I will get a something like "19.12.2005
11:11". How can I seperate the week number from this output, without
programming an own function?

thanks

Darren Gosbell

2005-12-20, 7:23 am

In article <1134995817.769021.312280@g14g2000cwa.googlegroups.com>,
access.20. mickmack@spamgourmet
.com says...
> Hi Darren,
>
> thanks for your prompt response.
>
> I guess, that is an good idea. But I have to time dimensions, one with
> an week-level and one with a month-level. When I create a list for the
> parameters out of the cube I will get a something like "19.12.2005
> 11:11". How can I seperate the week number from this output, without
> programming an own function?
>
> thanks
>

Sorry, but I am having trouble understanding the structure of your
dimensions, you mention 2 dimensions based around weeks and months, but
I am missing something as I don't understand how that relates to
"19.12.2005 11:11" which looks to me like a day and time reference.

Maybe if I expanded on my suggestion we may reach some common
understanding. :)

If you set a query like the one below as the source for you list of
parameters you can build your own user friendly label using a calculated
member like TimeCaption and you can use the TimeUniqueName as the value
that is pass back to the query so that it can be incorporated straight
back into the MDX
[color=darkred]
WITH
MEMBER Measures.TimeUniqueName as 'Time.CurrentMember.UniqueName'
MEMBER Measures.TimeCaption as '"Month " + Time.CurrentMember.Name +
", " + Ancestor(Time.CurrentMember,Year).Name'
SELECT
Time.Month.Members ON ROWS,
{Measures.TimeUniqueName, Measures.TimeCaption} ON COLUMNS
FROM Sales[color=darkred]

Note: The above query works with the FoodMart 2000 query that comes with
AS2k.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links