Date Range filter

Hi guys,

I need to filter result in MDX query by passed Start Date and EndDate

I come up with following solution

---

WHERE (

FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'

AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'

)

---

But when I execute script it returns me null for all values in fact table.

I am sure there is data in specified interval

Any suggestions are very appreciated.

What is best approach to filter by date range in MDX query?

What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||

Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

|||

This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns

Does anyone has idea how to make it works?

|||

Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.

Date Range filter

Hi guys,

I need to filter result in MDX query by passed Start Date and EndDate

I come up with following solution

---

WHERE (

FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'

AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'

)

---

But when I execute script it returns me null for all values in fact table.

I am sure there is data in specified interval

Any suggestions are very appreciated.

What is best approach to filter by date range in MDX query?

What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||

Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

|||

This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns

Does anyone has idea how to make it works?

|||

Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.

Date Range filter

Hi guys,

I need to filter result in MDX query by passed Start Date and EndDate

I come up with following solution

---

WHERE (

FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'

AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'

)

---

But when I execute script it returns me null for all values in fact table.

I am sure there is data in specified interval

Any suggestions are very appreciated.

What is best approach to filter by date range in MDX query?

What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||

Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

|||

This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns

Does anyone has idea how to make it works?

|||

Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.sql

Date problem

Hi,
Can anybody help me to find an easy solution to this problem?
I have a table

CREATE TABLE T1 (
Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(20)
, Col4 DATETIME
, Col5 INT )

INSERT INTO T1 VALUES ('A01','B01','C01',23-03-2006,4)

I want to pass a parameter to a stored proc such as Col1 ('A01'),and it will
check value of Col5, which is 4 here in our data.And then it will generate a resultset by adding 1+ to the month of Col4.

And I want to get a resultset like

A01 B01 C01 23-03-2006
A01 B01 C01 23-04-2006
A01 B01 C01 23-05-2006
A01 B01 C01 23-06-2006

It will also check if the date is 25-12-2006 the next date would be 25-01-2007 and also if 29-01-2006 the next date would be 28-02-2006.
I am trying to avoid Cursor.
Any solution would be really appreciated.
Thanks!!create an integers table like this --create table integers (i integer not null primary key)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9) then in the stored proc, run this query --select Col1
, Col2
, Col3
, dateadd(mm,i,Col4) as Col4
from integers
cross
join T1
where i < Col5|||You are one of the smartest guy I ever seen.
Rudy ,thanks a ton.;)|||thanks for the kind words

but there are a half dozen guys in this very forum smarter than me ;)|||create an integers table like this --create table integers (i integer not null primary key)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9) Just an FYI - if you want a BIG integers table (and one day you will :) ) this is a nice function to create one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx|||Just an FYI - if you want a BIG integers table (and one day you will :) ) this is a nice function to create one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

Thanks for the link Pootie;)

Date Picker RS2005SP2 useless

I read a lot of posts in this forum, but none of them provide a solution.

Probably it is related to SP2 as many report, I have SP2 as well...

A funny situation:
I have a report parameter @.date, datatype datetime, so I can use the datepicker control.

But, even when not using this @.date parameter in my dataset, I still get the error:

The value provided for the report parameter 'date' is not valid for its type. (rsReportParameterTypeMismatch)

Of course it has to do with regional settings and languages etc... But at my international client's site, workstations are in any regional setting and servers are in en-US, nothing I can do about that.

Come'on Microsoft, don't tell us this datePicker is only for the US market?

Thank You MS!

Apparently it is now solved in the Cumulative update package 2 for SQL Server 2005 Service Pack 2

See http://support.microsoft.com/kb/936305, bug n_ 50001284

Date Parameter used in filter

Hi sorry if trolling through the other postings that I have not found a
solution to this and it is repetition.
I am trying to set a filter for my table that compares a date from my data
to a date parameter.
I have no problem getting the date parameter with the new funky date picker,
but when I select a date it returns in US format mm/dd/yyyy but my data is in
dd/mm/yyyy. This then causes the report to fall over "value for the report
parameter is not valid".
I would like to maintain the date in the format dd/mm/yyyy for the users
benefit (UK users) but would also like to keep the date picker.
When I pick a date that is the same month and date eg 10/10/2005 all works
fine.
Thanks for the HelpHi,
did you get any resolution for this? I'm having the same issue
thanks
Matt
"Are friends electric?" wrote:
> Hi sorry if trolling through the other postings that I have not found a
> solution to this and it is repetition.
> I am trying to set a filter for my table that compares a date from my data
> to a date parameter.
> I have no problem getting the date parameter with the new funky date picker,
> but when I select a date it returns in US format mm/dd/yyyy but my data is in
> dd/mm/yyyy. This then causes the report to fall over "value for the report
> parameter is not valid".
> I would like to maintain the date in the format dd/mm/yyyy for the users
> benefit (UK users) but would also like to keep the date picker.
> When I pick a date that is the same month and date eg 10/10/2005 all works
> fine.
> Thanks for the Help|||Hi Matt
I did not find a solution on the report designer but when I deploy the
report and run it in explorer it works fine. I suppose this is a solution,
just a pain when trying to work on the design of the report.
Not that I am a fundi in any shape or form but if you need help on this I
can let you know how I set it up.
Steve
"BlackDuck" wrote:
> Hi,
> did you get any resolution for this? I'm having the same issue
> thanks
> Matt
> "Are friends electric?" wrote:
> > Hi sorry if trolling through the other postings that I have not found a
> > solution to this and it is repetition.
> >
> > I am trying to set a filter for my table that compares a date from my data
> > to a date parameter.
> >
> > I have no problem getting the date parameter with the new funky date picker,
> > but when I select a date it returns in US format mm/dd/yyyy but my data is in
> > dd/mm/yyyy. This then causes the report to fall over "value for the report
> > parameter is not valid".
> >
> > I would like to maintain the date in the format dd/mm/yyyy for the users
> > benefit (UK users) but would also like to keep the date picker.
> >
> > When I pick a date that is the same month and date eg 10/10/2005 all works
> > fine.
> >
> > Thanks for the Help|||Yep, I found the same thing. It's a pain. It seems odd that the date picker
obeys your regional settings but the target textbox doesn't.
"Are friends electric?" wrote:
> Hi Matt
> I did not find a solution on the report designer but when I deploy the
> report and run it in explorer it works fine. I suppose this is a solution,
> just a pain when trying to work on the design of the report.
> Not that I am a fundi in any shape or form but if you need help on this I
> can let you know how I set it up.
> Steve
> "BlackDuck" wrote:
> > Hi,
> >
> > did you get any resolution for this? I'm having the same issue
> >
> > thanks
> >
> > Matt
> >
> > "Are friends electric?" wrote:
> >
> > > Hi sorry if trolling through the other postings that I have not found a
> > > solution to this and it is repetition.
> > >
> > > I am trying to set a filter for my table that compares a date from my data
> > > to a date parameter.
> > >
> > > I have no problem getting the date parameter with the new funky date picker,
> > > but when I select a date it returns in US format mm/dd/yyyy but my data is in
> > > dd/mm/yyyy. This then causes the report to fall over "value for the report
> > > parameter is not valid".
> > >
> > > I would like to maintain the date in the format dd/mm/yyyy for the users
> > > benefit (UK users) but would also like to keep the date picker.
> > >
> > > When I pick a date that is the same month and date eg 10/10/2005 all works
> > > fine.
> > >
> > > Thanks for the Helpsql

Date parameter problem

I found a possible solution to my problem in a previous post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1177748&SiteID=1

I need to do the same thing. However, I have problem in getting the exposed parameter to the hidden parameter. My date dimension is not continuous. If the user select a date that doesn't have the correct date in the dimension, how can I find the nearest one?

Best bet is to use the OLAP data set builder in SSRS and set parameters there. It should build a date parameter for you that populates from your cube. The only dates displayed would then be valid dates in the dimension.

Good luck,

Bryan

|||The problem with this approach is a result of long date list after running the cube for years.

I have used the method of passing parameter to report viewer through another web page. The only problem that I got is about the parameter value. I understand that the parameter must use the format like [Dim Name].[Att name].[Value]. This solves most of my problem now.

Thanks for your input.|||

Alex,

I struggled with MDX date parameters in SSRS for a while. In the end, the approach I adopted was the following:

1. create a visible non-queried parameter strongly typed as date. (say it's called FromDate)

2. create a hidden parameter typed as a string (say, FromDateHiddenString)

3. set the default value for the hidden parameter to be somethign like: ="[TIME].[Date].&[" & Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "T00:00:00]"

Benefit of this is that you get a calendar picker for the date (FromDate). Then the value chosen by the user is passed into the hidden parameter (FromDateHiddenString), which is the parameter linked to your mdx query.

Unfortunately you cannot restrict the dates available via the calendar. but I've found that it drives the users more crazy to have to scroll through a massive list of dates, than to get no data when they choose an invalid date (after all, they generally know which dates they are interested in!)

i am also in HKSAR struggling to learn SSRS/SSAS/SSIS/MDX .. let me know if you want ot chat offline.

tx,

JG

|||Dear JG,

I've found this trick in somewhere else before. This seems nice but it can't suit my need. The main problem is I can't guarantee the date dimension is continuous. If the user pick that missing date from the date picker unluckily, they will not understand what's happening... after that, complain may come saying the report doesn't working. So, my current approach is creating another aspx and use the ReportViewer control. Then, I can have full control over the date selection. More than that, users will be happier to have a similar web interface to access their reports.

Regards,
Alex