Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

DateDiff question

Technically, what is the difference between these two pieces:
datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
I know one does day and one does day of year and their results are slightly
different, so which one will actually give me a record that is >= one year
old?
WillieIf you want all the rows more then 1 year old and you also want to correctly
handle leap years, why not use the following:
WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), dtInsertDate,
101))
--Brian
(Please reply to the newsgroups only.)
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23bviLhkuFHA.3400@.TK2MSFTNGP14.phx.gbl...
> Technically, what is the difference between these two pieces:
> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> I know one does day and one does day of year and their results are
> slightly different, so which one will actually give me a record that is >=
> one year old?
> Willie
>|||Sorry, I'm a little slow sometimes, but do you mean
WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), getdate(),
101))? Otherwise I don't see how it gets today's date to check from? And
then, would it work to just use getdate() instead of the whole convert
thing, or do I need that to properly handle the dateadd?
Thanks,
Willie
"Brian Lawton" <brian.k.lawton@.redtailcr.com> wrote in message
news:u%23BqQJluFHA.664@.tk2msftngp13.phx.gbl...
> If you want all the rows more then 1 year old and you also want to
> correctly handle leap years, why not use the following:
> WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), dtInsertDate,
> 101))
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23bviLhkuFHA.3400@.TK2MSFTNGP14.phx.gbl...
>|||Sorry about that. You're correct, the GETDATE () needs to be inside of the
convert. I included the CONVERT to ensure that the time portion of the
GETDATE() return value is truncated thereby forcing a consistent comparison
to midnight rather than arbitrary comparison based on the current run time.
--Brian
(Please reply to the newsgroups only.)
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:OprzcbuuFHA.3048@.TK2MSFTNGP10.phx.gbl...
> Sorry, I'm a little slow sometimes, but do you mean
> WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), getdate(),
> 101))? Otherwise I don't see how it gets today's date to check from? And
> then, would it work to just use getdate() instead of the whole convert
> thing, or do I need that to properly handle the dateadd?
> Thanks,
> Willie
> "Brian Lawton" <brian.k.lawton@.redtailcr.com> wrote in message
> news:u%23BqQJluFHA.664@.tk2msftngp13.phx.gbl...
>|||On Thu, 15 Sep 2005 15:53:39 -0700, Willie Bodger wrote:

>Technically, what is the difference between these two pieces:
> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
Hi Willie,
As far as I know, there is no diffference at all. The y parameter only
differs from the day parameter in the context of the DATEPART function,
not in the context of DATEDIFF.

>I know one does day and one does day of year and their results are slightly
>different, so which one will actually give me a record that is >= one year
>old?
Could you post an example where the results are different? I ust tested
it on a cross self-join of a table with all dates from 2000 up to and
including 2005, and I didn't found a single combination of dates where
they differ.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)