Report's Parameter : From Date to Date

I want to add 2 parameters in my report to delimit a date range. The report is based on Analysis Services 2005 and I have created a Time Dimension. What is the best way to do this ?

I would prefer to use a Datetime parameter type because of the calendar component, but then I don't succeed to associate this parameter with the Time Dimension in the filter expression...

I really need an answer to that question, please help me |||You can't really use a datetime parameter (at least not using the built-in prompting) as AS dates are members of a dimension, not scalars. And you aren't really guaranteed that the range between two members will give you what you want. We could add a feature at some point to convert them but that doesn't exist now.|||

Thanks for the answer.

I just found a solution to my problem, even if the solution is not what I hoped for, it may help someone :

I created two Report Parameters "From Date" and "To Date" which use the calendar component to let the user choose a date. And in my Dataset, in the '...' button, in the 'Parameters' bar, I add a parameter whose value is :

="[Temps].[Temps - Date Key].[" & Year(Parameters!FromTempsTempsCalendrier.Value) & "-" & IIF(Month(Parameters!FromTempsTempsCalendrier.Value)<10,"0","") & Month(Parameters!FromTempsTempsCalendrier.Value) & "-" & IIF(Day(Parameters!FromTempsTempsCalendrier.Value)<10, "0","") & Day(Parameters!FromTempsTempsCalendrier.Value) & " 00:00:00]"

That way, I build the UNIQUENAME properties of my Time Dimension.

But an error occurs when the user select a date which does not exist in the Time Dimension.

|||

Thanks for the possible solution. However, I've been working on following your steps, but I am not getting it to work.

Could you explain in a little more detail how to accomplish this?

Report snapshot generation failed on MDX script

Hi,

I am running RS 2005 and Analysis Services 2005. I was able to get RS to connect to the OLAP database (via stored credential) when generating snapshot. However, it failed when processing MDX script in the report dataset. The same report works fine in browser and VStudio, but only failed during snapshot or cache generation. Below is the error message and the affected MDX script. Any help would be appreciated!

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Get Online Help

Query execution failed for data set 'PU_Floorcovering'. (rsErrorExecutingCommand) Get Online Help

Incorrect syntax near '{'. Incorrect syntax near 'Business Type'. Incorrect syntax near 'Facility'. Incorrect syntax near 'Production Units Reporting UOM'.

WITH SET [UOM] AS

' ([Raw Materials Source UOM].[Source UOM].ALLMEMBERS - [Raw Materials Source UOM].[Source UOM].[All]) *

([Raw Materials Reporting UOM].[Reporting UOM].ALLMEMBERS - [Raw Materials Reporting UOM].[Reporting UOM].[All]) *

([Production Units Source UOM].[PU Source UOM].ALLMEMBERS - [Production Units Source UOM].[PU Source UOM].[All]) *

([Production Units Reporting UOM].[PU Reporting UOM].ALLMEMBERS - [Production Units Reporting UOM].[PU Reporting UOM].[All])'

SELECT NON EMPTY

{

[Measures].[Weight], [Measures].[Weight per Production Unit Converted]

} ON COLUMNS,

NON EMPTY

{

(([Time].[Year].ALLMEMBERS - [Time].[Year].[All]) *

([Raw Materials].[Category Type].ALLMEMBERS - [Raw Materials].[Category Type].[All]) *

([Raw Materials].[Category].ALLMEMBERS - [Raw Materials].[Category].[All]) *

[UOM])

}

having not IsEmpty([Measures].[Weight]) And ([Measures].[All Facilities Production Unit Converted], [Production Units].[UOM]) > 0

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM

(

SELECT ( { [Business Type].[Business Type].&[{11116DDD-FD7B-46AA-A4D8-73492E220001}]}) ON COLUMNS

FROM

(

SELECT ({[Raw Materials Reporting UOM].[Reporting UOM].&[Lb]}) ON COLUMNS

FROM

(

SELECT ({[Production Units Reporting UOM].[PU Reporting UOM].&[Yd2]}) ON COLUMNS FROM [Pinnacle]

)

)

)

WHERE

(

[Facility].[LocationType].&[1],

[Facility].[Initiative - InitiativeFacility].[Initiative].&[{11136DDD-FD7B-46AA-A4D8-73492E220002}]

)

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

- SouBee

It was a mistake on my part. The data source was set to MS SQL Server, instead it should be MS SQL Analysis Services.

Thanks.

SouBee