Prompt to get data for previous 5 days

[rishabh_singhania]

Hi All,

I am woking on BO XI 3.1 with informix database.

Requirement: To get the data for previous 5 days including the day user selected in the prompt.

Example: Suppose we have an object named "sys date" and we have created the prompt on sys date , now when the user select any value (let's say 15/09/2014), then the output should contain the records for previous 5 days(i.e from 10/9/2015 to 15/09/2014)

Please Suggest.

Thanks in advance

Regards,

Rishabh

[former_member198611 · Accepted solution]

Hi,

try this logic:

(1) you can use Userreponse() function in obtaining the user data

(2) Use the RelativeDate() function in obtaining the past 5 days data (i.e) =RelativeDate('16-09-2014', 5) where 5 is the last five dates data.

Regards,

Bansi

[rishabh_singhania · Accepted solution]

Hi Bansi,

The method you have suggested workes at report level.

That means i have to pull all the records and at report level I will filter the data for 5 days, but if the records are more it will be aprobleme.

Can you suggest some thing which we can apply at universe level.

Thansk and Regards,

Rishabh

[former_member201488 · Accepted solution]

You may want to identify the backend database you're using as the exact SQL statement will differ depending upon the database.

Edit: My bad, Informix. Try this.

HTH

NMG

[Former Member · Accepted solution]

hello bhansi..i need your help in the same issue  i am facing..i need todays data and yesterdays data,when user selects any date.

[rishabh_singhania · Accepted solution]

Hi Suhas,

You can check this link below might be helpful to solve you issue

http://www.dagira.com/2007/08/22/dynamic-dates-part-i-yesterday-and-today/

Regards,

Rishabh

[former_member201488 · Accepted solution]

Here's one way to do it; this example is for SQL Server, you'll need to amend per your backend database SQL functions (e.g. it will be different for Informix, Oracle, etc.)

Create a Universe filter. In the WHERE condition use the following:

@Select(Your Date Object) between dateadd(d,-5,@Prompt('Date:','D',,mono, free)) and @Prompt('Date:','D',,mono, free)

The first part - @Select - you should select the date object you want to filter.

The next part - dateadd(d,-5,@Prompt('Date:','D',,mono, free)) - will prompt the user for a date and will subtract 5 days from it (for anything other than 5 days, amend the number. The 'd' represents days, this can be substituted with months and years - see this link)

Finally, we use the same prompt without the Dateadd function - which returns the same date as above.

Therefore, when the report is run, the user is prompted for a date, and the query is filtered to return only dates between the five days before the prompted date, and the prompted date itself.

HTH

NMG

[Former Member · Accepted solution]

mitchell,thanks for your answer,i am quite new to SAP BO,can you please say briefly.

I have class..objects are Ename,Elocation,Date.

my requirement is......when

user selects one date(any date),the repost should show that date data and previous day data.for this what i have to do .please explain.

[rishabh_singhania · Accepted solution]

Hi Neil,

Thanks for your reply.

I think this should work, will try implementing your option and update the thread.

Just one thing that I need to confirm is that will "dateadd" function will work if we have informix database.

Thanks and Regards,

Rishabh

[former_member201488 · Accepted solution]

Not sure, sorry. Give it a go!

[Former Member · Accepted solution]

tha prompt,that usaid is give errors. mine is desginer 3.x

database sql

[former_member201488 · Accepted solution]

Have you substituted your own date object in the @Select statement?

Please post the SQL statement you're using.

NMG

[Former Member · Accepted solution]

created one object named.....Previous day

in select clause i have selected .......date object name...dbo.test_date.Date

where clause ......@select (dbo.test_date.date) between dateadd(dd,-1,@prompt('select date','D','dbo.test_date.date',mono,free)) AND @prompt('select date','D',dbo.test_date.date',mono,free)

but user should get only one prompt,if selects one date..he will get that dates data and previous dates data.

[former_member201488 · Accepted solution]

Don't create an Object, create a Filter. Use this SQL in the Where clause of the Filter.

Edit: My bad - sorry. The @Select function should contain your date Object in Class/Object format, not the SQL name of the Object - e.g. @Select(Dates\Date Object)

@select(dbo.test_date.date) between dateadd(dd,-1,@prompt('select date','D',,mono,free)) AND @prompt('select date','D',,mono,free)

NMG

[rishabh_singhania · Accepted solution]

Hi Neil,

The dateadd function did not worked for informix 😞

Regards,

Rishabh

[former_member201488 · Accepted solution]

Check this page out:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls.htm

I believe the MDY function should let you accomplish what you need.

HTH

NMG

[rishabh_singhania · Accepted solution]

Hi Neil,

I tried with MDY and Extend function but I think i am missing out some thing in the syntax which is causing the error in parsing.

This is the syntax that I am using, please suggest if any changes required.

MDY(month(@Prompt('Entry Date:','D','class/object',mono,free)),day(@Prompt('Entry Date:','D',class/object',mono,free)),year(@Prompt('Entry Date:','D','class/object',mono,free))- 5 units day

Regards,

Rishabh

[former_member201488 · Accepted solution]

I'd remove the 'class/object' bits and try again.

I don't use Informix so I can't test it I'm afraid. I think you have the gist though.

Good luck!

[Former Member · Accepted solution]

hi NMG..i have tried your logic last night as you said.but its giving errors..if  ur using sql please check once and give me the correct one..

this is my prompt function

dbo.test_date.date between dateadd(dd,-1,@prompt('select date','D','test_date\date',mono,free))
AND @prompt(select date','D','test_date\date',mono,free)

its saying that conversion failed converting varchar type into date type gives you out of range values..

my date column datatype is varchar

[former_member201488 · Accepted solution]

Try:

dbo.test_date.date between dateadd(d,-1,@prompt('select date','D',,mono,free))
AND @prompt(select date','D',,mono,free)

Just tried this in my Universe (using SQL Server connection) - works fine for me.

[former_member201488 · Accepted solution]

...and your Date Column should be of the datatype DATE, or it won't work!

[Former Member · Accepted solution]

hi NMG,thnaks for ur patience and help towards me....i created a table in SQL.

Table..columns are Ename varchar(20),Elocation varchar(20),Sdate Date.

i just defined my date cloumns data type is Date.

but when its comes to the universe..its taking automatically Character..

Date object properties...in drop down list..its Character and i parsed..dbo.suhas_test.sdate...its ok.

but u said date column should be date..so i just changed that sdate object properties to Date and parsed...but its saying that..
the expression type is not compatible with object type

[former_member201488 · Accepted solution]

If your table date Column is a DATE and not a string, I don't understand why your universe will not parse it as a date? What values does it hold?

As a workaround, you can use CAST or CONVERT in your Date object SQL to convert this into a date. This page should assist you in doing so.

[Former Member · Accepted solution]

i have given date values in database as 17-09-2014.in this format..

but wehn its comes to the universe..table values its howing 2014-09-17 format.

when its comes to list of values in object Date..the format is 9/17/2014

[rishabh_singhania · Accepted solution]

Thanks everyone for the help.

@Neil: Thanks ... MYD function worked.

Here is the solution for those having the same requirement:

Created a conditional object with below syntax and placed it into the filter pane.

@Select(Class\Object) BETWEEN  MDY(month(@Prompt('Entry Date:','D','Class\Object',mono,free)),day(@Prompt('Entry Date:','D','Class\Object',mono,free)),year(@Prompt('Entry Date:','D','Class\Object',mono,free)))-5 UNITS DAY AND @Prompt('Entry Date:','D','Class\Object',mono,free)

Replace the 'Class\Object'  with your date object.

Thanks and Regards,

Rishabh Singhania

[former_member203850]

Create a object in universe 'Previous 5 days'

Definition :  trunc(sysdate-5)

And use this above object as a filter in report.

For example: 'Date object' is greater than or equal to ('Previous 5 days')

Regards,

Pranay

[rishabh_singhania]

Hi Pranay,

Thanks for replying.

Creating the object as you have suggested gives me syntex error while parsing.

Regards,

Rishabh

[former_member203850]

Yes . You will get an error 'Invalid table name' in universe beacuse sysdate is not available in any of the table .

Use this object in your report, it will work.

Regards,

Pranay

[Former Member]

Hi Rishabh,

Please use the formula: =RelativeDate(CurrentDate();-5).

Regards,

Ashvin

[rishabh_singhania]

Hi Pranay,

Can u please share how to create this object "last 5 days from now".

also the five days selected would be based on the user input.

if we select any previous date (say 15/01/2012)

then the output should be displayed for dates (10/01/2012   to 15/01/2012)

Thanks,

Rishabh

[former_member203850]

Lete consider an example :

Create date 5th Sept 2014

Sysdate : 16th sept 2014

As per the above condition Create date >= trunc(sysdate-5)

Will get the below records in report :

Please check .

Regards,

Pranay

[rishabh_singhania]

Hi Pranay,

Your logic is corerct, but I think that I could not clearly explain you the requirement.

Let me try to detail it.

Forget about the sysdate...let us take it as entry date.

Now user gets a prompt on entry date and can selet any value for this prompt.

and whatever date is selected we need to pull the data for previous  days based on the selection.

so now if user select any previous date 10/01/2010

will the logic you have sugested will work?

if yes can you guide me what will be the sintex for hte object that is needed to be created to get previous 5 days, as the one which you have shared earlier gives error when parsed

Thanks and Regards,

Rishabh

[former_member203850]

Yes. As i already mentioned that it will through an error in universe( Sysdate is not  part of any table) but it will work in report.

Attaching snapshot for your reference.

Please try the same logic in report .

Regards,

Pranay