Date/Time Stamp: filter

Showing posts with label filter. Show all posts

Thursday, March 29, 2012

Date Range Problem

I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ONcolumns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

oncolumnsfrom [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.sql

Date Range for Report built against cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Thanks

MDX sets do not really work like this, they are made up of sets of discrete members and while they are ordered and you can get ranges of members, the idea of a between predicate does not really map well. I usually try to constrain my report parameter so that users cannot pick values outside of the valid member range.

If you can't do this you could probably use the filter function, provided that you had the actual date value stored somewhere (the MemberValue property is ideal for this). Which would make your range statement something like this:

filter( <date dim>.<hierarchy>.<date level>.Members

, <date dim>.<hierarchy>.MemberValue >= <fromDate>

and <date dim>.<hierarchy>.MemberValue <= <toDate>))

But this is going to be a lot slower than directly specifying a start and end member directly.|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

|||Yeah, that's what I do aswell. And 5 years is only 1500 members which is not big as dimensions go.

Date Range for Report built against a cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Many Thanks

Absolutely it is possible. Have you tried removing the CONSTRAINED flag? Also do a search of this forum, i have posted several items regarding using dates and MDX within reports.

|||

Hi,

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

Sunday, March 25, 2012

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

Thanks,

Ranjay

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

sql

Thursday, March 22, 2012

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! Please help!

There is not a Period column to reference the fields to and I am not sure how to
use a date range and apply it to one parameter. Is there a way to use
the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?

Basically here is what I am trying to do...

Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.

I did get some advice on using an if statement to reference the parameter but I receive the error message that I must declar the scalar value @.Period.

Here is the simple query I used to just see if the query would run based on the parameter(be nice...I am a newbie to SQL and RS)

In my Data tab:
IF @.Period = 1 SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN '03/06/2006' AND '04/02/2006'

For the report parameter:
Label Value
Period 1 1

I have used the IIF expression and such but this is just a different situation and I am pulling my hair out trying to find an answer.
Any help is greatly appreciated!

I'm sure there are a few different ways to approach this, but this could be one.

1) Create a parameter called @.PeriodStart like you did. Make this a datetime parameter. The label for this parameter could be a number (1, 2, 3.. n) and the value would be a date. You could hard-code the periods in through the Available Values section and select non-queried. For label you could put 1 and for value '1/1/2006' and 2 then 1/7/2006... or whatever you wanted to define for your period start dates. (If you wanted to make this more dynamic, you could create a dataset that somehow used sql functions to get these dates... You could create a DateDimension table or a Period table..)

2) Create a dataset called something like PeriodEndDataSet. The query could be something like this: SELECT DATEADD(MONTH, 1, @.PeriodStart) AS PeriodEndDate. (you can make the 1 month be anything you wanted).

3) Create a second parameter called @.PeriodEnd. You would want this to be a hidden parameter. In the Parameter editor Select Queried from available values section. Select your dataset and label & value fields. Also set the default value to come from the same query.

4) In the query for your report (the main data set), you can create your query to do this:

SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN @.PeriodStart AND @.PeriodEnd

That seems like a lot of work... I would almost recommend creating a lookup/dimension table to store this period information for you. You could then use this across many reports.

Regards,

Dan

sql

Sunday, March 11, 2012

Date function Question

Hi everone,
I want to add a criteria to filter out the Sales Order date.
For example, today is 10 Oct 2005, the default range that I want for the
user like this:
Start Order Date: 10 Sep 2005
End Order Date: 10 Oct 2005
I don't know how to set in parameter for the user! Can I use the following
function (like VB)?
=dateadd('m', -1, Globals!ExecutionTime) ==> for Start Order Date
Please help!
TonyDear all,
I can fix the problem, the correct one should be:
=dateadd("m", -1, Globals!ExecutionTime)
For your reference!!!!!
Tony
"Tony" wrote:
> Hi everone,
> I want to add a criteria to filter out the Sales Order date.
> For example, today is 10 Oct 2005, the default range that I want for the
> user like this:
> Start Order Date: 10 Sep 2005
> End Order Date: 10 Oct 2005
> I don't know how to set in parameter for the user! Can I use the following
> function (like VB)?
> =dateadd('m', -1, Globals!ExecutionTime) ==> for Start Order Date
> Please help!
> Tony

Thursday, March 8, 2012

Date Format problem for drill down

Please help me.
I have two reports(lets say Report1 and Report2) and both have filter
parameters as start date and end date.
Report1 has drilldown ability and opens up Report2 using date parameters
from Report1. Upto here it works fine no problems with date format because
when I pass dates from Report1 to Report2, I format them into "dd-MMM-yyyy"
format.
Now, problem happens when I open Report2 from Report1 and then hit refresh
on Report2 , its changes the date parameter format on report2 as
"MM/dd/yyyy". Which results in wrong data on report2 or gives an error(if day
is greater than 12 in the month).
Keep in mind that I am running these report in IE from a client machine
which has Australian Regional Settings and the server where both reports are
installed has US Regional Settings. (NOTE: if I change both server and client
to same regional settings then it works fine)
I hope, this is enough information for this problem.
Please help me as soon as possible, I need this fixed as early as possible.
Thanks,
HarryJust format the date when you pass it as a parameter, the way the new report
wants it..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harry" <Harry@.discussions.microsoft.com> wrote in message
news:294301DB-F414-4C9C-B29B-3538FFDD32FA@.microsoft.com...
> Please help me.
> I have two reports(lets say Report1 and Report2) and both have filter
> parameters as start date and end date.
> Report1 has drilldown ability and opens up Report2 using date parameters
> from Report1. Upto here it works fine no problems with date format because
> when I pass dates from Report1 to Report2, I format them into
> "dd-MMM-yyyy"
> format.
> Now, problem happens when I open Report2 from Report1 and then hit refresh
> on Report2 , its changes the date parameter format on report2 as
> "MM/dd/yyyy". Which results in wrong data on report2 or gives an error(if
> day
> is greater than 12 in the month).
> Keep in mind that I am running these report in IE from a client machine
> which has Australian Regional Settings and the server where both reports
> are
> installed has US Regional Settings. (NOTE: if I change both server and
> client
> to same regional settings then it works fine)
> I hope, this is enough information for this problem.
> Please help me as soon as possible, I need this fixed as early as
> possible.
> Thanks,
> Harry|||How do I format date, I cannot find the place. I think its all happening
inside Reporting Services Service.
Or I think you did not understand the problem properly.
I am not passing any dates, all I am doing is on Report2 (when opened
through Report1 as drill down it works fine) as you hit refresh it gives
error "The value provided for the report parameter 'dtToDate' is not valid
for its type".
for ref please read the previous post by me
Thanks,
Harry
"Wayne Snyder" wrote:
> Just format the date when you pass it as a parameter, the way the new report
> wants it..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harry" <Harry@.discussions.microsoft.com> wrote in message
> news:294301DB-F414-4C9C-B29B-3538FFDD32FA@.microsoft.com...
> > Please help me.
> >
> > I have two reports(lets say Report1 and Report2) and both have filter
> > parameters as start date and end date.
> >
> > Report1 has drilldown ability and opens up Report2 using date parameters
> > from Report1. Upto here it works fine no problems with date format because
> > when I pass dates from Report1 to Report2, I format them into
> > "dd-MMM-yyyy"
> > format.
> >
> > Now, problem happens when I open Report2 from Report1 and then hit refresh
> > on Report2 , its changes the date parameter format on report2 as
> > "MM/dd/yyyy". Which results in wrong data on report2 or gives an error(if
> > day
> > is greater than 12 in the month).
> >
> > Keep in mind that I am running these report in IE from a client machine
> > which has Australian Regional Settings and the server where both reports
> > are
> > installed has US Regional Settings. (NOTE: if I change both server and
> > client
> > to same regional settings then it works fine)
> >
> > I hope, this is enough information for this problem.
> > Please help me as soon as possible, I need this fixed as early as
> > possible.
> >
> > Thanks,
> > Harry
>
>

Saturday, February 25, 2012

Date Filter in Report Builder

The "On or Before" filter does not work with the 'On' part of the
filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
but does show 29/11/2006.
Is this a bug or maybe i'm missing something.
Regards
TobiI haven't used Report Builder that much, but its possible that Report
Builder is using the format of 30/11/2006 12:00:00 (12:00 am), and your
data from the db is 30/11/2006 09:15:00 (9:15 am).
Tobi wrote:
> The "On or Before" filter does not work with the 'On' part of the
> filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
> but does show 29/11/2006.
> Is this a bug or maybe i'm missing something.
>
> Regards
> Tobi|||I agree with Topher's assessment. If this was an actual report, I would
suggest creating an internal report parameter that adds one day and
then subtracts one second from the original parameter value. You would
then change your query parameter to use the new, internal parameter
value.
However, this is Report Builder, which I have not used much. Try to do
something like I just described.
Just had another thought... In your model, try to strip the times out.
If you aren't using them anyways, then you don't need them. If
everything has a time of 00:00:00 (12:00:00 AM), then your formula
should work just fine.
HTH,
Josh
Topher wrote:
> I haven't used Report Builder that much, but its possible that Report
> Builder is using the format of 30/11/2006 12:00:00 (12:00 am), and your
> data from the db is 30/11/2006 09:15:00 (9:15 am).
>
> Tobi wrote:
> > The "On or Before" filter does not work with the 'On' part of the
> > filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
> > but does show 29/11/2006.
> >
> > Is this a bug or maybe i'm missing something.
> >
> >
> > Regards
> >
> > Tobi

Date filter in MDX

Hi guys,

I have a cube where I want to make filter based on date range. I've created a cube with appropriate measures and I have Dimension with date field ( I want to filter on that field).

I've found two solutions:

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= "2/6/2007" --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= "2/7/2007" --'2/7/2007'

but this stopped working after I applied SP2 - i just get null values for all measures

and this one

[Reservation Search Log].[Simple Insert Date].&[February 1, 2007] :
[Reservation Search Log].[Simple Insert Date].&[February 10, 2007]

but in I would like to pass these dates as parameters. and if both dates are outside the range I get no results. In real case I should get all rows like this
data in SSAS February 1, 2007 to February 10, 2007
I pass January 1, 2007 to March 10, 2007
and no result are returned

If there is no appropriate records for both date parameters no rows are returned...

Any ideas are highly appreciated.Filter is not really an efficient way to do this, if possible I usually try to populate drop down lists of members in reports, but sometimes this is not always practical. Have you tried converting your comparison values to dates as well?

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= CDate("2/6/2007") --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= CDate("2/7/2007") --'2/7/2007'|||Hi Darren,

Thank very much for helping me with this....

I don't get what do you mean with converting comparison values to dates...

the example I posted is from real MDX query in which I got that doesn't work on SP2 and both sides are converted using CDate before comparison is made

Any other ideas? What is the best way to achieve such goal?|||

The sample you posted only had one side converted to a date, the other was a string value. The example you posted has

>= "2/6/2007"

as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like:

>= CDATE("2/6/2007")

I tested this in SP2 and it works for me.

|||

this is interesting...

I had this filter clause in where clause and I had the (null) problem...

I moved filter clause in select in subquery and everything is fine now...

Sorry for missing the changes in your post. I've made so many tests and I thought I posted same as you

Thanks a lot, Darren

Date Filter

I have a report in Visual Studio that I want to limit to just the activity for today. Normally I would add a WHERE stmt to the sql script that says docdate=getdate(). However this does not return any data to me in VS. I have tried many different syntax. Does anyone have the correct syntax or filter value that works in Visual Studio? Thank you.

getdate() returns a datetime, not just a date, which is why your sql query is failing. For a really helpful list of date formatting in SQLServer check this url: http://www.sql-server-helper.com/tips/date-formats.aspx

You have two options for selecting your activity records:

- do it purely in sql: use getdate to get an initial date, CONVERT it to get rid of the time component and store it as your start date, use DATEADD on it to add 24 hours and store this as the end date, then use a BETWEEN in the WHERE clause

- pass a date (as a string) through from the report parameters (could be a hidden parameter with a default value of =Format(Now, "yyyy/MM/dd")), then CAST it as a datetime in sql, and compare your activity records to it in the WHERE clause.

Friday, February 17, 2012

date comparison and filtering

Im creating a stored proc where i need to filter an Orders table
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W

, This Month.
This W

:
Do I have to calculate the start of w

and end of w

and then use
a between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w

, you have to decide what day of the w

you
want to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w

)
And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:

> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W

, This Month.
> This W

:
> Do I have to calculate the start of w

and end of w

and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w

, you have to decide what day of the w

yo
u
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w

)
> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w

where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W

, This Month.
> This W

:
> Do I have to calculate the start of w

and end of w

Thursday, March 29, 2012

Date Range Problem

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

Date Range for Report built against cube using MDX query

Date Range for Report built against a cube using MDX query

Date Range filter

Date Range filter

Date Range filter

Sunday, March 25, 2012

date parmeter converts into drop down

date parmeter converts into drop down

Thursday, March 22, 2012

Date Parameter used in filter

Date Parameter Problem! Please help!

Sunday, March 11, 2012

Date function Question

Thursday, March 8, 2012

Date Format problem for drill down

Saturday, February 25, 2012

Date Filter in Report Builder

Date filter in MDX

Date Filter

Friday, February 17, 2012

date comparison and filtering

Date/Time Stamp

Blog Archive

About Me