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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

Date Parameter (last day)


 
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
zahmed
Forum Member
Forum Member



Joined: 14 Jan 2018

Posts: 10



PostPosted: Wed Nov 28, 2018 8:47 am 
Post subject: Date Parameter (last day)

Hello ,

i need to calculate the last day of month,
This month i should retrieve from a parameter From user

so i need to a function dependent of a parameter to calculate the last day of entered month

as seen in attach file i need to make the 31 oct 2018 date dynamic and taken from a paramter thanks

In my universe i just have a Hiring date "dd/MM/YYYY"



icon_confused.gif icon_confused.gif


Last edited by zahmed on Thu Nov 29, 2018 1:38 pm, edited 1 time in total
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6464
Location: Connecticut, USA


flag
PostPosted: Thu Nov 29, 2018 8:02 am 
Post subject: Re: Date Parameter

What's the underlying database?
Back to top
zahmed
Forum Member
Forum Member



Joined: 14 Jan 2018

Posts: 10



PostPosted: Thu Nov 29, 2018 1:25 pm 
Post subject: Re: Date Parameter

Thanks for reply ... the db is SAP S4HANA Database .. any solution ?

in below picture you can see sample of the data
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6464
Location: Connecticut, USA


flag
PostPosted: Fri Nov 30, 2018 9:30 am 
Post subject: Re: Date Parameter

I don't know S4HANA SQL syntax, but I can try to give you a generic solution.

And to verify the requirement -- if the user enters 3/31/2018, you want all hiring dates in March 2018, correct? What if the user enters a date that is not end-of-month?
Back to top
zahmed
Forum Member
Forum Member



Joined: 14 Jan 2018

Posts: 10



PostPosted: Fri Nov 30, 2018 11:46 am 
Post subject: Re: Date Parameter

the user will just choose a Month and year, example :Year: 2018 Month :Jan , Feb etc ... and i should calculate it to last day of this month .

to make a filter in query that hiring date is less than this last day of month & year

Thats what i need to do .. any solutions ?
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6464
Location: Connecticut, USA


flag
PostPosted: Fri Nov 30, 2018 12:16 pm 
Post subject: Re: Date Parameter

If this was Oracle, I would use:
Code:
hire_date <= add_months(to_date(@Prompt('Enter year','N',,,) || @Prompt('Enter month','N',,,),'yyyymon'),1)-1


If you enter "2018" for year and "NOV" for month, then the above will resolve to:
Code:
hire_date <= add_months(to_date('2018' || 'NOV','N',,,),'yyyymon'),1)-1

which will result in 11/30/2018. Does this meet your need?
Back to top
zahmed
Forum Member
Forum Member



Joined: 14 Jan 2018

Posts: 10



PostPosted: Mon Dec 03, 2018 1:44 am 
Post subject: Re: Date Parameter

Thanks for your answer
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11131
Location: Manchester, UK


flag
PostPosted: Tue Dec 04, 2018 7:06 am 
Post subject: Re: Date Parameter (last day)

It's even easier in Webi - there's a LastDayOfMonth function. icon_smile.gif
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
zahmed
Forum Member
Forum Member



Joined: 14 Jan 2018

Posts: 10



PostPosted: Thu Dec 06, 2018 8:56 am 
Post subject: Re: Date Parameter (last day)

Thanks mark .. but i was using Xcelsisus Dashboard
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.0314 seconds using 17 queries. (SQL 0.0031 Parse 0.0009 Other 0.0273)
CCBot/2.0 (https://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