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 with measures in row

Hi,
I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:

Jan Feb Mar ...
Measure1 10 12 14
Measure2 20 22 24
..

How to create with either Tabular or Matrix report type?

Thanks,
Marcus

You can either:

1. Use the RS 2005 SSAS provider but change the report format to request measures on columns.

2. Bypass the RS 2005 SSAS provider and use the MS OLE DB 9.0 Provider for Analysis Services to request the measures on rows and Time on columns. However, in this case you can't use the matrix region and your table columns will be fixed. So, you have to create 12 columns (assuming 12 months on columns) and hide the ones that are not returned.

|||Thank you very much Teo,
I had to chose alternative 2 and created a report with measures in rows and tabular structure. Now I got stuck in some details. I need cascading parameters following the whitepaper " Integrating Analysis Services with Reporting Services" for SQL 2000. But I'm working with SQL2005. I easily establish a parameter for the highest parameter level (here ProductCategory in Adventure Works)

="WITH MEMBER [Product].[Category].[Prod] AS
'[Product].[Category].[" + Parameters!ProductCategory.Value + "]'

SELECT

{ [Measures].[Internet Sales-Discount Amount],[Measures].[Internet Sales-Extended Amount] } ON ROWS ,

{[Date].[Calendar Time].[Month].[January 2003]} ON COLUMNS

FROM [Adventure Works UDM]
WHERE [Product].[Category].[Prod] "

but a link to this existing parameter in a cascading parameter yields an empty list and the report is not executable ([Product Categories] is a natural hierachy):

= "WITH MEMBER Measures.NullColumn AS 'Null'

SELECT
{Measures.NullColumn} ON COLUMNS,
{DESCENDANTS({" + Parameters!ProductCategory.Value + " },
[Product].[Product Categories].[Subcategory])} ON ROWS
FROM
[Adventure Works UDM]"

Are cascading parameters with OLE DB 9.0 and SQL 2005 possible, can you provide an example?

Thanks a lot,
Marcus

sql

Report with measures in row

Hi,
I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:

Jan Feb Mar ...
Measure1 10 12 14
Measure2 20 22 24
..

How to create with either Tabular or Matrix report type?

Thanks,
Marcus

You can either:

1. Use the RS 2005 SSAS provider but change the report format to request measures on columns.

2. Bypass the RS 2005 SSAS provider and use the MS OLE DB 9.0 Provider for Analysis Services to request the measures on rows and Time on columns. However, in this case you can't use the matrix region and your table columns will be fixed. So, you have to create 12 columns (assuming 12 months on columns) and hide the ones that are not returned.

|||Thank you very much Teo,
I had to chose alternative 2 and created a report with measures in rows and tabular structure. Now I got stuck in some details. I need cascading parameters following the whitepaper " Integrating Analysis Services with Reporting Services" for SQL 2000. But I'm working with SQL2005. I easily establish a parameter for the highest parameter level (here ProductCategory in Adventure Works)

="WITH MEMBER [Product].[Category].[Prod] AS
'[Product].[Category].[" + Parameters!ProductCategory.Value + "]'

SELECT

{ [Measures].[Internet Sales-Discount Amount],[Measures].[Internet Sales-Extended Amount] } ON ROWS ,

{[Date].[Calendar Time].[Month].[January 2003]} ON COLUMNS

FROM [Adventure Works UDM]
WHERE [Product].[Category].[Prod] "

but a link to this existing parameter in a cascading parameter yields an empty list and the report is not executable ([Product Categories] is a natural hierachy):

= "WITH MEMBER Measures.NullColumn AS 'Null'

SELECT
{Measures.NullColumn} ON COLUMNS,
{DESCENDANTS({" + Parameters!ProductCategory.Value + " },
[Product].[Product Categories].[Subcategory])} ON ROWS
FROM
[Adventure Works UDM]"

Are cascading parameters with OLE DB 9.0 and SQL 2005 possible, can you provide an example?

Thanks a lot,
Marcus

Report Two Data Subsets in the same Grid

This is likely very easy, but I'm not sure which direction is the best
way to go...
Dimension:
Time: Date and Hour the record occurred
Cube:
Has a field that counts the number of times an event occurred in a
given hour
I want to show a grid that has for each day of the month how many times
an event occured between 8:00 and 16:00 and from 16:00 to 20:00 hours.
What is the best way to role up the records in the report?
I've seen two possible ways to do it...
1) Use calculated fields with IIF statements to try to role up the
records.
Problem: IIF statement doesn't seem to work correctly.
2) Use a Matrix control to Filter the records correctly.
Problem: I can't seem to have two Filters on the Hour Group in the same
Matrix. One for 08:00 to 16:00 and one for 16:00 to 20:00.
Clearly I'm missing some key concept, because what I'm asking has to be
commonplace. Can someone point me in the right direction?
Thanks,
Paul WI might have missed something, but this is my suggestion:
Create a dataset that returns the number of events in two section, one for
8-16 and the other for 16-20. You should be able to do this with a
calculated member and filter in your MDX query. (No good examples spring to
mind, but you can always ask in the microsoft.public.sqlserver.olap news
group.)
Then create a matrix, and have two coloums (one for each section of the day)
and group on date.
Kaisa M. Lindahl Lervik
"Paul W" <pbwedz@.yahoo.com> wrote in message
news:1155143250.949345.213080@.n13g2000cwa.googlegroups.com...
> This is likely very easy, but I'm not sure which direction is the best
> way to go...
> Dimension:
> Time: Date and Hour the record occurred
> Cube:
> Has a field that counts the number of times an event occurred in a
> given hour
> I want to show a grid that has for each day of the month how many times
> an event occured between 8:00 and 16:00 and from 16:00 to 20:00 hours.
> What is the best way to role up the records in the report?
> I've seen two possible ways to do it...
> 1) Use calculated fields with IIF statements to try to role up the
> records.
> Problem: IIF statement doesn't seem to work correctly.
> 2) Use a Matrix control to Filter the records correctly.
> Problem: I can't seem to have two Filters on the Hour Group in the same
> Matrix. One for 08:00 to 16:00 and one for 16:00 to 20:00.
> Clearly I'm missing some key concept, because what I'm asking has to be
> commonplace. Can someone point me in the right direction?
> Thanks,
> Paul W
>