MDX, dates and Calendar Control in Reporting services 2005

Hi,
I need to develop several reports in RS 2005 where the report
parameters are dates. The reports are based on a SQL Server 2005
Analysis Services Cube and consequently the reports need to be based
on MDX. My dates are formatted according to Swedish date standard. I
would like the users to pick the dates from a Calendar control.
(I have seen several smilar problems with dates, but all referring to
SQ statements, not MDX)
Here's my problem:
When I design my query in the Data tab of the report, I choose the
field that I want to use as a parameter (in this case a date) and tick
the checkbox indicating that I want a Report parameter created.
When I inspect the report in Preview, the date is not displayed in a
calendar, but instead as a tremendously long list. (Not good!) When I
look at the report parameter in Layout it has the datatype String.
- If I change the parameter to DateTime (in order to get a calendar),
I get a runtime error: "an error occurred during local report
processing. The property 'Valid Values' of report paramater
'KalenderDatum' doesn't have the expected type."
My question:
- What can I do to make Reporting Services understand that I want to
display dates?
- Can calendar controls be used with MDX at all'
- Is there someting I need to do in MDX to format my date as a
DateTime field?
- Does RS just not like Swedish' ;-)
Additional info:
- My cube is built on a staging database, and if I design a report
against that database with ordinary SQL statements using the same
DateTime field, I get a calendar control, and RS understands that I
want to display dates. Everything works fine.
- I have set the language property on my report to Swedish (the date
format being yyyy-mm-dd)
Please let me know if there is a workaround for this. Unfortunately
MDX is not exactly my second language either...
CarinaI had the same problem (but with US date formats). What you need to do is on
the "Data" tab in your RS project, change the MDX query to an expression by
putting an = there followed by your whole MDX query in double quotes. Then
concantenate in your parameter date fields. Here is an example of how I did
it for a range on [Start Date] based on 2 datetime parameters called
Begin_Date and End_Date. Note, be sure there are no control characters or
carriage returns in your MDX expression.
="SELECT NON EMPTY { [Measures].[Login Count]} ON COLUMNS, NON EMPTY {
[GroupUsers].[Application - Name].[Login Name].ALLMEMBERS * [Date].[Start
Date].&[" & Format(Parameters!Begin_Date.Value,"MM/dd/yyyy") &
"]:[Date].[Start Date].&[" & Format(Parameters!End_Date.Value,"MM/dd/yyyy") &
"] } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
[MyCubeDataset])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
I just set up regular Datetime report parameters for Begin_Date and End_Date
and added them to the dataset on the parameters tab. I formatted them into
the format I wanted inside the expression above because I needed the leading
zeroes for the date comparisons to work in MDX. Also, here is a link that
helped me:
http://msdn2.microsoft.com/en-us/library/aa902647(sql.80).aspx
Hope this helps.
"carina.jones@.sogeti.se" wrote:
> Hi,
> I need to develop several reports in RS 2005 where the report
> parameters are dates. The reports are based on a SQL Server 2005
> Analysis Services Cube and consequently the reports need to be based
> on MDX. My dates are formatted according to Swedish date standard. I
> would like the users to pick the dates from a Calendar control.
> (I have seen several smilar problems with dates, but all referring to
> SQ statements, not MDX)
> Here's my problem:
> When I design my query in the Data tab of the report, I choose the
> field that I want to use as a parameter (in this case a date) and tick
> the checkbox indicating that I want a Report parameter created.
> When I inspect the report in Preview, the date is not displayed in a
> calendar, but instead as a tremendously long list. (Not good!) When I
> look at the report parameter in Layout it has the datatype String.
> - If I change the parameter to DateTime (in order to get a calendar),
> I get a runtime error: "an error occurred during local report
> processing. The property 'Valid Values' of report paramater
> 'KalenderDatum' doesn't have the expected type."
> My question:
> - What can I do to make Reporting Services understand that I want to
> display dates?
> - Can calendar controls be used with MDX at all'
> - Is there someting I need to do in MDX to format my date as a
> DateTime field?
> - Does RS just not like Swedish' ;-)
> Additional info:
> - My cube is built on a staging database, and if I design a report
> against that database with ordinary SQL statements using the same
> DateTime field, I get a calendar control, and RS understands that I
> want to display dates. Everything works fine.
> - I have set the language property on my report to Swedish (the date
> format being yyyy-mm-dd)
>
> Please let me know if there is a workaround for this. Unfortunately
> MDX is not exactly my second language either...
> Carina
>|||Thank you so much for your input! With your help and the explanation
below I did figure it out :-). I'm a happy camper!
/Carina
In short (found this explanation on another site):
1. Develop the report.
2. Change the Data Type of the date parameters to DateTime, and set
Available Values to Non-queried.
3. On the Data tab, edit the cube's Dataset (click on the ... button).
4. Click on the Parameters tab of the Dataset dialog box. Replace the
Value of the date parameters with ="[Dimension].[Member].&[" +
Format(CDate(Parameters!ParameterName.Value), "yyyy-MM-ddT00:00:00") +
"]" where [Dimension].[Member] and ParameterName are the dimensions
and parameter names of your particular cube. Depending on how your
dimension is set up, you may also need to change the format of the
date.
5. There is no need to change the MDX of the query.

MDX statement using Parameters as Calendar

Hello People,
I'm developing reports using SQL2005 and Reporting Service 2005 where I'm
using MDX to extract the information from the cube, but now I need to get a
range of data as a parameter, but using the Calendar date picker from
Reporting server, do you know if is it possible ?
Thanks,
ElvisHi Elvis,
This issue was discussed a couple of months back (June CTP time-frame) in
the SQL Server 2005 Beta newsgroup, but unfortunately I can no longer locate
it. Anyway, what I recall is that I had to resort to using the old OLE DB for
OLAP Provider (which worked with MDX in RS 2000), because in that case the
MDX query is dynamically built as an expression; and the data type of date
parameters can then be set to DateTime, which will enable the Calendar date
picker. Of course, in that case, the expression has to transform the returned
date string to an appropriate MDX Date Dimension member name.
"elvispires" wrote:
> Hello People,
> I'm developing reports using SQL2005 and Reporting Service 2005 where I'm
> using MDX to extract the information from the cube, but now I need to get a
> range of data as a parameter, but using the Calendar date picker from
> Reporting server, do you know if is it possible ?
> Thanks,
> Elvis
>
>|||hi,
do you have an example of dynamically built mdx ? Becaus i also have that
problem.
with
member [month_list].[jan-sept] AS 'sum([month_list].[All
month_list].[January]:[Month_list].[All month_list].[September])'
the last [September] is supposed to be tha name of the current month
Thanks!
"Deepak" wrote:
> Hi Elvis,
> This issue was discussed a couple of months back (June CTP time-frame) in
> the SQL Server 2005 Beta newsgroup, but unfortunately I can no longer locate
> it. Anyway, what I recall is that I had to resort to using the old OLE DB for
> OLAP Provider (which worked with MDX in RS 2000), because in that case the
> MDX query is dynamically built as an expression; and the data type of date
> parameters can then be set to DateTime, which will enable the Calendar date
> picker. Of course, in that case, the expression has to transform the returned
> date string to an appropriate MDX Date Dimension member name.
> "elvispires" wrote:
> > Hello People,
> >
> > I'm developing reports using SQL2005 and Reporting Service 2005 where I'm
> > using MDX to extract the information from the cube, but now I need to get a
> > range of data as a parameter, but using the Calendar date picker from
> > Reporting server, do you know if is it possible ?
> >
> > Thanks,
> > Elvis
> >
> >
> >
> >

MDX Multiselect and existing keyword usage

WITH

MEMBER LastYearSales
as
'
sum(
existing [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].MEMBERS,
(ParallelPeriod([Time].[Fiscal Calendar Hierarchy].[Fiscal Year]),
[TY Net Sales Amount])
)
'

MEMBER [Time].[Fiscal Calendar Hierarchy].[All Fiscal Periods].[ Grand Total]

AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101],

[Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010103], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010104] }, [Time].[Fiscal Calendar Hierarchy] ) }

}, { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010103], [Time].[Fiscal Calendar

Hierarchy].[Fiscal Week].&[2006010104] } ) )', SOLVE_ORDER = 1000

SELECT { LastYearSales } ON COLUMNS,

{ { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010103], [Time].[Fiscal Calendar

Hierarchy].[Fiscal Week].&[2006010104] }, ( [Time].[Fiscal Calendar Hierarchy].[All Fiscal Periods].[ Grand Total] ) } ON

ROWS

FROM [Sales]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

========

OUTPUT

LastYearSales
Week 1 100
Week 2 200
Week 3 300
Week 4 400
Grand Total 50000 -> This is not the sum of the four weeks which should be 900. Instead it is sum across all weeks across all years in the time dimension. So effectively, the Existing statement is not getting the correct context (which is

the set of four fiscal weeks) while processing the aggregate (grand total) row. The only part of the code above that we can change is the definition of the calculated measure LastYearSales. The remaining is generated by proclarity.

We followed the recommendations by Mosha in his blog,
http://sqljunkies.com/WebLog/mosha/archive/2007/01/13/multiselect_parallelperiod.aspx

There is a problem with how Proclarity generates MDX in this case. (In fact I just reviewed very similar scenario this week). This is actually not multiselect scenario for which I wrote blog, but Visual Totals scenario, which is harder. The calculated member with Aggregate is not in the WHERE clause, therefore context for current coordinate is not set, and EXISTING in the definition of calculated member does nothing. Sorry, but the solution for multiselect won't work for Visual Totals.

MDX hierarchy problem

Hello,

My point is I wonder how to retrieve a leaf member from a date hierarchy.

I would like to retrieve the leaf of [Hierarchy Calendar] at the CurrentMember, which is a Date (as 2007/01/07).
[Hierarchy Calendar] is composed of : Year, Semester, Quarter, Month, Week of year, Date.

I can't retrieve the date of the hierarchy using [Date].[Hierarchy Calendar].[Date].CurrentMember, and I absolutly need to use a hierarchy, not the dimension : [DATE].[Date]

My final MDX must look like that:
IIF(IsEmpty(([Date].[Hierarchy Calendar].CurrentMember,[Measures].[NAV PER SHARE AMOUNT])),

NULL,

IIF(Not IsEmpty([Measures].[NAV PER SHARE AMOUNT]),[Measures].[PRODUCT FIRST NAV DATE] ,([Measures].[FIRST NAV DATE],[Date].[Hierarchy Calendar].[Date].CurrentMember.PrevMember)

)

)

Any help would be appreciated.

Thx,

Damien

Here are two queries that might help point you in the right direction.

Good luck,
Bryan

Code Snippet

with member [Measures].[x] as

SETTOSTR(DESCENDANTS([Date].[Calendar].CurrentMember,,LEAVES))

select

x on 0,

[Date].[Calendar].[Month].[October 2003] on 1

from [Adventure Works]

;

select

{} on 0,

EXISTS([Date].[Date].[Date].Members,[Date].[Calendar].[Month].[October 2003]) on 1

from [Adventure Works]

;

|||

I know the DESCENDANTS function but I think that's not the solution ...

Maybe another solution ?

|||

How about that EXISTS function (in the second query)?

B.

|||

Thanks for your help.

For information, I resolved my problem with this, it's not very optimized but works under my scheduling constraints

Thanks again.

.FIRSTCHILD:[DATE].[Hierarchy Calendar].CURRENTMEMBER,NOT ISEMPTY([Measures].[NAV PER SHARE AMOUNT])),EXCLUDEEMPTY) =0,

([MEASURES].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent.PrevMember),

([DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent,[Measures].[PRODUCT FIRST NAV DATE])

)

)

),

IIF(ISEMPTY(([Measures].[NAV PER SHARE AMOUNT])),

IIF([DATE].[Hierarchy Calendar].CurrentMember.Level IS [DATE].[Hierarchy Calendar].[Year] OR COUNT(filter([DATE].[Hierarchy Calendar].CurrentMember.Parent.FirstChild:[DATE].[Hierarchy Calendar].CurrentMember,not isempty([Measures].[NAV PER SHARE AMOUNT])))>0,

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.PrevMember),

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.PrevMember)

),

[Measures].[PRODUCT FIRST NAV DATE]

)

)

)

MDX hierarchy problem

Hello,

My point is I wonder how to retrieve a leaf member from a date hierarchy.

I would like to retrieve the leaf of [Hierarchy Calendar] at the CurrentMember, which is a Date (as 2007/01/07).
[Hierarchy Calendar] is composed of : Year, Semester, Quarter, Month, Week of year, Date.

I can't retrieve the date of the hierarchy using [Date].[Hierarchy Calendar].[Date].CurrentMember, and I absolutly need to use a hierarchy, not the dimension : [DATE].[Date]

My final MDX must look like that:
IIF(IsEmpty(([Date].[Hierarchy Calendar].CurrentMember,[Measures].[NAV PER SHARE AMOUNT])),

NULL,

IIF(Not IsEmpty([Measures].[NAV PER SHARE AMOUNT]),[Measures].[PRODUCT FIRST NAV DATE] ,([Measures].[FIRST NAV DATE],[Date].[Hierarchy Calendar].[Date].CurrentMember.PrevMember)

)

)

Any help would be appreciated.

Thx,

Damien

Here are two queries that might help point you in the right direction.

Good luck,
Bryan

Code Snippet

withmember [Measures].[x] as

SETTOSTR(DESCENDANTS([Date].[Calendar].CurrentMember,,LEAVES))

select

x on 0,

[Date].[Calendar].[Month].[October 2003] on 1

from [Adventure Works]

;

select

{} on 0,

EXISTS([Date].[Date].[Date].Members,[Date].[Calendar].[Month].[October 2003]) on 1

from [Adventure Works]

;

|||

I know the DESCENDANTS function but I think that's not the solution ...

Maybe another solution ?

|||

How about that EXISTS function (in the second query)?

B.

|||

Thanks for your help.

For information, I resolved my problem with this, it's not very optimized but works under my scheduling constraints

Thanks again.

.FIRSTCHILD:[DATE].[Hierarchy Calendar].CURRENTMEMBER,NOTISEMPTY([Measures].[NAV PER SHARE AMOUNT])),EXCLUDEEMPTY) =0,

([MEASURES].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent.PrevMember),

([DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent,[Measures].[PRODUCT FIRST NAV DATE])

)

)

),

IIF(ISEMPTY(([Measures].[NAV PER SHARE AMOUNT])),

IIF([DATE].[Hierarchy Calendar].CurrentMember.LevelIS [DATE].[Hierarchy Calendar].[Year] ORCOUNT(filter([DATE].[Hierarchy Calendar].CurrentMember.Parent.FirstChild:[DATE].[Hierarchy Calendar].CurrentMember,notisempty([Measures].[NAV PER SHARE AMOUNT])))>0,

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.PrevMember),

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.PrevMember)

),

[Measures].[PRODUCT FIRST NAV DATE]

)

)

)

MDX Hierarchy help

I have two calendar hierarchies in a 2005 SSAS cube. I have to make some calculations based on the date level, but of course, my calculated measures dont work when the wrong hierarchy is chosen.

I have tried to look at the .hierarchy and hierarchy() function but am unable to really understand how they work and I haven't been terribly successful looking online for info on them.

Has anyone had a similar issue? How did you test for the current hierarchy?

Here's an Adventure Works example, when either the Calendar or Fiscal hierarchy is selected on rows:

>>

With Member [Measures].[DateHierarchy] as

Axis(1).Item(0).Item(0).Hierarchy.Name

select {[Measures].[Order Quantity],

[Measures].[DateHierarchy]} on 0,

Non Empty [Date].[Calendar].[Calendar Year] on 1

from [Adventure Works]

-

Order Quantity DateHierarchy
CY 2001 11,848 Calendar
CY 2002 60,918 Calendar
CY 2003 124,615 Calendar
CY 2004 77,395 Calendar

>>

Another approach is to test whether the current member of each hierarchy is the default member (assuming that only 1 hierarchy has been navigated).

MDX function ORDER over CROSSJOINed Set

Hello!

I have simple MDX query (SSAS2005):

SELECT
{ [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,
ORDER(
DESCENDANTS( [Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Subcategory] ) *
{[Product].[Size].[All Products], DESCENDANTS([Product].[Size].[All Products], [Product].[Size].[Size]) },
[Measures].[Reseller Sales Amount],
DESC) ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )

Why members of [Product].[Product Model Categories].[Subcategory] is not arranged?

It is, you haven't specified to break the hierarchy order in the ORDER function and you're not sorting by the column you're displaying. Try the following query:

SELECT

{ [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,

ORDER(

DESCENDANTS( [Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Subcategory] ) *

{[Product].[Size].[All Products], DESCENDANTS([Product].[Size].[All Products], [Product].[Size].[Size]) },

([Measures].[Reseller Sales Amount],[Date].[Calendar].[Calendar Year].&[2004]),

BDESC) ON ROWS

FROM [Adventure Works]

WHERE ( [Measures].[Reseller Sales Amount] )

I've highlighted in red the bits that I've added. It's easy to forget to add the 'B' in front of ASC and DESC, and you have to remember that ORDER won't pick up on any selection you've made on columns - you need to specify the column you want to sort by by adding it to the tuple in the second parameter.

HTH,

Chris

|||

Hi, Chris! Thanks for answer, but...

the hierarchy should be kept. Members of both dimensions should be arranged in the descending order. In the MSAS2000 function ORDER worked correctly. Here is a similar example for Foodmart2000, "Cities" are sorted in the desc and "Education level" is sorted inside of cities too.

SELECT { [Time].&[1997] } ON COLUMNS ,
ORDER(
{
DESCENDANTS( [Customers].[All Customers], [Customers].[City] ) *
{ [Education Level].[All Education Level], DESCENDANTS( [Education Level].[All Education Level], [Education Level].[Education Level] ) }
},
( [Time].&[1997] ),
DESC
) ON ROWS
FROM [Sales]
WHERE ( [Measures].[Unit Sales] )

http://img206.imageshack.us/img206/3055/ordermsas200eg4.jpg

|||

Ah, I see what you mean. This looks like a bug to me - you should report it at Connect (http://connect.microsoft.com/).

In the meantime, does the following query do what you want?

SELECT

{ [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,

GENERATE(

ORDER(

DESCENDANTS( [Product].[Product Model Categories].[All Products], [Product].[Product Model Categories].[Subcategory] )

,([Measures].[Reseller Sales Amount],[Date].[Calendar].[Calendar Year].&[2004]), DESC)

, [Product].[Product Model Categories].CURRENTMEMBER

*

ORDER(

{[Product].[Size].[All Products], DESCENDANTS([Product].[Size].[All Products], [Product].[Size].[Size]) }

,([Measures].[Reseller Sales Amount],[Date].[Calendar].[Calendar Year].&[2004],[Product].[Product Model Categories].CURRENTMEMBER), DESC)

)

ON ROWS

FROM [Adventure Works]

WHERE ( [Measures].[Reseller Sales Amount] )

Chris

|||

I have made so, but If I need 5-7 crossjoined dimensions?
So many calls instead one ORDER!!!

Thanks.