BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: Upcoming Events: BONYMAUG: May 14, BI2014 Nice, France: May 21.

Custom relative dates for any date object


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
shump88
Forum Member
Forum Member



Joined: 06 Aug 2009

Posts: 3



PostPosted: Thu Aug 13, 2009 3:19 pm 
Post subject: Custom relative dates for any date object

In our application we allow users to define their own relative dates based on "core" relative dates. For example, a core relative date would be 'TODAY', and users create their own relative dates by adding/subtracting days, months, or years to 'TODAY'.

In SQL server we have one function, that calculates relative dates based on a specific relative date code.

I need a way to call this function in the where clause of the sql statement, i.e -

select * from
Activity
where
datestart >(select dbo.bhcfn_GetDateRelativeByCode('TODAY', getdate())

I have tried a few things options but I keep coming back to the fact that any derived table has to be join to a specific field. I want to avoid creating this strucute, were we have to create over 271 derived tables for each date object in each class.

I have tried creating a LOV that contains a prompt.

select dbo.bhcfn_GetDateRelativeByCode(@Prompt('Enter a relative date:','A', 'Calendar\Code',mono,constrained),getdate())

This basically prompts the user to enter a code, the function returns a date and that is the problem. I need the date to be created at run time. I don't want the date to be stored in the definition of the report.


Is there is prompt hack I could use to resolve this challenge ( calling a function in the where clause)?
Back to top
Debbie
Forum Enthusiast
Forum Enthusiast



Joined: 01 Mar 2005

Posts: 2192
Location: Cheshire, England


flag
PostPosted: Mon Aug 17, 2009 5:04 am 
Post subject: Re: Custom relative dates for any date object

Why don't you just create a set of relative date objects at universe level?

Eg: create a date object START_OF_YESTERDAY: dateadd(d,datediff(d,0,getdate())-1,0)

Then in your report, you'd have

where datestart> START_OF_YESTERDAY.

I have a whole "universe" of just date objects that don't reference a table. I link this into every universe I create, so that the set of 20 or so date objects are available to users, eg: start of month, start of year, start of financial year, end of last month etc etc

debbie
Back to top
rkluth
Senior Member
Senior Member



Joined: 26 Mar 2009

Posts: 31



PostPosted: Tue Sep 08, 2009 12:41 pm 
Post subject: Re: Custom relative dates for any date object

I find that START_OF_YESTERDAY: dateadd(d,datediff(d,0,getdate())-1,0)
will not parse. I'm using sql server. I made a new object in the universe and placed START_OF_YESTERDAY: dateadd(d,datediff(d,0,getdate())-1,0) in the box below Select: I made the type date. Thanks in advance for your help. icon_confused.gif
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17512
Location: Bratislava


flag
PostPosted: Tue Sep 08, 2009 12:58 pm 
Post subject: Re: Custom relative dates for any date object

Hi,

The object does not parse because it does not reference any real table. However, the object can be used in a report with any other object and it will work just fine.

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
shump88
Forum Member
Forum Member



Joined: 06 Aug 2009

Posts: 3



PostPosted: Wed Sep 09, 2009 2:22 pm 
Post subject: Re: Custom relative dates for any date object

Thanks for the update. This does work well, however, you can't prompt for for relative dates, so the user has to create a report for each relative date. I was hoping for solution that allow me to prompt the user for a relative date.

I did get a solution working in Deski....but it locks up in Webi. I created a object that calls a stored procedure:

Code:
dbo.bhcfn_GetDateRelativeByCode(@Prompt('Enter a relative date:','A', 'Calendar\Code',mono,constrained),getdate())


builds sql as such:

Code:
SELECT
  dbo.Activity.dateend
FROM
  dbo.Activity
WHERE
  (
  dbo.Activity.dateend  <  [b]dbo.bhcfn_GetDateRelativeByCode(@Prompt('Enter a relative date:','A', 'Calendar\Code',mono,constrained),getdate())[/b]
  )
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0465 seconds using 17 queries. (SQL 0.0031 Parse 0.0351 Other 0.0083)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo