Hi All,
i am finding it strange to understand what i am doing wrong here
here’s what i am doing
Enviroment : BO XI R2 , SQL SERVER 2005
i am creating couple of filters in the universe to extract current year and current month .
here’s the syntax
Current month = @Select(view\Date)>=datepart(mm,getdate())
and
Current Year = @Select(view\Date)>=datepart(yy,getdate())
but when i run the report using these two filters i should be seeing the data from this month to all the data that exist beyond ( in our DW we have data for 2009 also) , but somehow i only see the data from this month and this year , seems like its not bringing 2009’s data.
i hope i am clear , if not let me know i will explain more in detail.
thanks
kol
Could you elaborate? This isn’t going to give you what you want, but the results should be different from what you describe.
Let me explain. You want to see only to see the data from the current month/year and future months/years (as far as there are any in your dimension table), but using an and statement you’ll only see November, December of 2008, 2009, etc.
i THINK you are half the way correct with my requirement , let me get into more detail here
My DW has data for all the month of 2009 .
when i apply the filters in the query i am expecting the results to be starting from current month to all the data that exist the DW ( even the data for 2009 )
but these filters are doing the half the job it brings all the data from current month to end of 2008 its not looking for 2009’s data
Current month = @Select(view\Date)>=datepart(mm,getdate())
and
Current Year = @Select(view\Date)>=datepart(yy,getdate())
Till how far in the future is your data? Like I said in my previous post, this way you’ll only get data for the months of November and December of the years to come, so if you’re data in 2009 is only till say July you won’t see any.
Ah , now i am trying to understand , thats right , so if then i have data for nov 2009 then this filters would work but for now i only have data for april 2009 ,
so whats the best solution would be then to achieve what i am trying.
thanks once again for looking into it
kol
Current month = @Select(view\Date)>=datepart(mm,getdate())
and
Current Year = @Select(view\Date)>=datepart(yy,getdate())
Or Next Year = @Select(view\Date)>=dateadd(yy,1,datepart(yy,getdate()))
Make an object that displays the the (current) year and the month YYYYMM and then apply the predefined condition >=datepart(yy,getdate())||datepart(mm,getdate())
Wahey,
i know its silly on my part to ask for this , well i have a date field object in the universe but i am not able to get the current month and current year , i was able to get just MM or YYYY part from the object but not able to get current MM or current YYYY.
I USED THIS FUNTIONS:
{fn month(@Select(view\Date))}
{fn Year(@Select(view\Date))}
or
datepart(MM,@Select(view\date))
datepart(YY,@Select(view\date))
Is there any other way to get Current MM or Current YYYY.
THANKS
KOL
By “current” do you mean related to what date it is right now? If so, replace whatever you’ve got in the @Select() with the function getdate() which will return the current date. At that point you can extract the current month and current year from that value.
If your definition of “current” is different, you’ll need to provide something more specific.
Dave,
you are right i want to get the current date and by date i mean current mm and current yyyy.
the getdate() function dont seeems to be working
can be bit more clear to me when you say replace @select with getdate funtion , what did was i tried to replace with the date object and applied getdate () function which doesnt parse , can you please tell me what i am doing wrong here?
thanks
kol
Thanks dave,
link did help me to get the current mm and current yyyy,
but i am stuck on how to create a filter ( condition) in the universe which will get all the data from current date to all the future months and years .
i tried something like this @Select(view\fDate_Year)>=datepart(yy,getdate())
@Select(view\Date_Month)>=datepart(MM,getdate())
but this doesnt work it brings all the past data too.
thanks once again for looking into it
kol
Please post the generated SQL that you are tring in your filter, not the @Select, something like this should work…
DatePart(mm,Tablename.YourDate)>=Datepart(mm,getdate())
and
DatePart(yy,Tablename.YourDate)>=Datepart(yy,getdate())
Or
DatePart(yy,Tablename.YourDate>=Dateadd(yy,1,datepart(yy,getdate()))
Dave,
i just tried what you said in your last post , but seems like its doing half the job,
its bringing all the data from today’s date to all the future data that exist in our DW, but its also not bringing the data that exist from 1st of NOV to Yesterday’s date
how to make that work to bring in all this months data?
thanks
kol
So you want past data but only to the beginning of the current month, and future data beyond that. It’s too bad you’re not on Oracle because the answer is really simple.
In Oracle it would be as simple as trunc(sysdate,‘MM’). A little bit of google returned this for SQL Server to return the first day of the current month:
KMB,
thanks a lot , your suggestion helped me cut down on all the past tries what i did ,
what i did was created a object which brings current date ( but it brings from first date of current month)
Object 1= dateadd(mm, datediff(mm, 0, getdate()), 0)
then the
Object 2 = @Select(view\date) >=@Select(Object1)
this should bring in all the data from starting current date of current month to all the future dates.
i checked the results and they are looking right , just wanted to check with you that i am doing it correctly
thanks once again
kol
(BOB member since 2007-05-31)
(BOB member since 2005-01-06)
(BOB member since 2002-06-06)
Just do this: