Urgent help - How to create a Calendar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Urgent help - How to create a Calendar

Author

Topic

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-07-23 : 11:47:55

How do i implement a calendar in SSRS 2008.
I am using a cube as my datasource.
I have a date dimension table.

How do i create a parameter that when displayed, displays a calendar.

yavvie
Starting Member

46 Posts

Posted - 2009-07-27 : 08:54:13

set the parameter to datatype Date/Time and it creates itself.

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-07-29 : 11:19:43

quote:
Originally posted by yavvie

set the parameter to datatype Date/Time and it creates itself.

When i do that, i get a message
"The property 'ValidValues' of report parameter 'TimeByDate' doesn't have the expected type.'

How do i fix this. The date itself it a datetime datatype

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-07-29 : 15:16:20

Here is my mdx
SELECT NON EMPTY { [Measures].[Checkouts] } ON COLUMNS, NON EMPTY { ([Time].[By Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@TimeByCalendarMonth, CONSTRAINED) ) ON COLUMNS FROM [B2R Analytics]) WHERE ( IIF( STRTOSET(@TimeByCalendarMonth, CONSTRAINED).Count = 1, STRTOSET(@TimeByCalendarMonth, CONSTRAINED), [Time].[By Calendar Month].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I am getting the following error

Query(1,188) the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated

Can someone please help.

yavvie
Starting Member

46 Posts

Posted - 2009-08-03 : 02:40:52

quote:
Originally posted by sgandhi
When i do that, i get a message
"The property 'ValidValues' of report parameter 'TimeByDate' doesn't have the expected type.'

How do i fix this. The date itself it a datetime datatype

You can't put there any Available Values, it just creates a calendar with all the days and years. You cannot modify it to show just the dates you choose.
When we need to show just some dates we have to use a parameter that is just a dropdown list (combobox) and you have to select the dates you need in the procedure and then pass them to the report as a list of values and dates. It can be of the type Date/Time but then the calendar is not created and just the list is displayed.

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-08-05 : 13:12:02

OK, i redid this, and changed it. This is what the query looks like

SELECT NON EMPTY { [Measures].[Checkouts] } ON COLUMNS, NON EMPTY { ([Time].[By Calendar Month].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@TimeByCalendarMonth, CONSTRAINED) ) ON COLUMNS FROM [B2R Analytics]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I changed the parameter to no default and no available values.
And set the datatype to date/time

and i put the following in the parameter expression in the dataset
="[Time].[By Calendar Month].&[" + Format(CDate(Parameters!TimeByCalendarMonth.Value), "s") + "]"

I still get the constrained error. If i remove the word ,CONSTRAINED then i still dont get any results, only the headings for the matrix.

Its like its not linking the data, or maybe datatypes dont match. Im confused. Please help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources