Help with Business objects

Need your help with creating Business object variable
I have a date, called Billg  Dttm (this is my parameter) to retrieve the data.
So:
 if  today date is Monday then I need everything for the last three days : Sunday, Saturday and Friday
if today date is Tuesday thru Friday then I need everything for the previous date: if Tuesday need data for Monday and so on

thank you in advance
rfedorovAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Is this in Crystal?

If so then try

If DayOfWeek({Billg  Dttm}) = 2 then
     {YourDate Field} >= {Billg  Dttm} - 3
Else
     {YourDate Field} >= {Billg  Dttm} - 1

mlmcc
0
James0628Commented:
You said that "Billg Dttm" is a "parameter".  That would normally mean that you were going to enter a value for a parameter named "Billg Dttm" when you ran the report, and it would be compared with some field, to decide which data is included in the report.  The formula that mlmcc posted would work for that kind of situation.

 But then you said "if today date", implying that the report will be based on the current date, in which case I would assume that "Billg Dttm" is actually a date/datetime field.

 Is "Billg Dttm" a parameter, which will be compared with some date/datetime field?
 Or is "Billg Dttm" the date/datetime field, which needs to be compared to a date that's based on the current date?

 Or are you trying to do something else?

 James
0
rfedorovAuthor Commented:
thank you for your respond, probably i was not clear at all
First this is not Crystal report,  this is Business Object...-------->Help with Business objects
Second, i would like to automate the parameters query, i would like to ask you to help me with that.
The query should use "Billg Dttm" as a date/datetime field
if today's date is not Monday, so the Billg Dttm should Billg Dttm -1 (yesterday)
 and if today's date is Monday, the Billg Dttm should be a range from Friday thru Sunday
I hope it is clear now, thanks
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

mlmccCommented:
Which BO tool?

mlmcc
0
rfedorovAuthor Commented:
SAP Business Object
0
mlmccCommented:
SAP Business Objects is a suite of tools.  Are you using WebI, DeskI or one of the other tools?

mlmcc
0
rfedorovAuthor Commented:
WebI, sorry
0
mlmccCommented:
The exact code will depend on the database

In SQL Server it will be something like this

Case DatePart(WeekDay, GetDate())
   When 2 Then  GetDate() - 3
   Else  GetDate() - 1
 End Case
 

Open in new window


You can then use this as an object in the filter
0
rfedorovAuthor Commented:
thank you, Does not work
This is not what i need
i need to create the variable and that variable should take care of Monday/ not Monday case
The query should use "Billg Dttm" as a date/datetime field
if today's date is not Monday, so the Billg Dttm should Billg Dttm -1 (yesterday)
 and if today's date is Monday, the Billg Dttm should be a range from Friday thru Sunday
0
mlmccCommented:
If you want to filter the data the filter must be built in the universe unless you want to bring in all the data and apply a report filter.

mlmcc
0
mlmccCommented:
The code I provided above is for an object in the universe.  The universe object would then be compared in the filter to your date field in the database.

mlmcc
0
mlmccCommented:
You say you want a variable.  Variables can't be used in the filter for the query.  Are you trying to filter the data as it is read from the database or are you trying to apply the filter on the report?

mlmcc
0
rfedorovAuthor Commented:
Guys, i am confused now...
The query should use "Billg Dttm" as a date/datetime field.

if today's date is not Monday,  the the "Billg Dttm"  should get me everything for   Billg Dttm -1 (yesterday)
and if today's date is Monday, the         "Billg Dttm" should get me everything for a range from Friday thru Sunday
0
mlmccCommented:
Since you are using WebI as the reporting tool all you can do is drag an object into the filter section of the query then set the comparison and finally the value to compare to which could be an answer to a prompt.

Anything more complex than that must be done in the universe.

Can you make changes to the universe?

mlmcc
0
rfedorovAuthor Commented:
I do not understand what it has to do with Filter, I do not want any filters, i need help to create a variables which will help me to run the query.
Let say , the first  variable called " Day of the week", and second called "DateRelated"

So if today is Monday it should bring everything where "Billg Dttm"  from Friday to Sunday,
if not Monday  should bring everything only for yesterday,

may be i can not explain well,
0
mlmccCommented:
As I stated, variables CANNOT be used in the query.  Only objects in the universe can be used in the query.

Are you creating these variables in WEBI or in the universe?

mlmcc
0
rfedorovAuthor Commented:
and again, this is misunderstanding
i do not want to create a query, i even do not have rights to do so
i need to create those variables and use them as objects.
if monday then Billg Dttm from -3 to -1
else Billg Dttm=to -1
0
mlmccCommented:
Perhaps I am misunderstanding what you want to do.

Are you building a report that will show data for all week or month and want to show the Friday, Saturday, and Sunday transactions together?  The other days will be separate.

You have the ability to create a query if you have the ability to edit the report and create formulas.
Are you saying you cannot click the DATA tab then click EDIT to see/modify the query the report is built on?

You seem to want to limit the data in the report which is done in the query though it can be done on the report but it is much less efficient.

The formula you are wanting will be something like this.

=if (DayNumberOfWeek(CurrentDate()) = 1 ; RelativeDate(CurrentDate(); -3); RelativeDate(CurrentDate; -1))

Open in new window


That will give you the date of Friday for Monday or Yesterday for the rest of the week.

mlmcc
0
rfedorovAuthor Commented:
Thank you very much, I think i can use your template


the formula below is working fine
=If ( [WeekDay]<>"Monday"; RelativeDate([Billg Vldtn Que Commit Dttm]; -1); RelativeDate([Billg Vldtn Que Commit Dttm]; -2))
the logic is fine, i only need your help to finish the second part where Weekday is monday and i need to use the data between  RelativeDate-3 and  RelativeDate-1, not sure about the syntax
0
mlmccCommented:
What do you mean?

Are you trying to filter the report?

mlmcc
0
rfedorovAuthor Commented:
there is a lot of data
normally, i run the report based on [Billg Vldtn Que Commit Dttm]  i use as a parameter
if the day is not Monday, use [Billg Vldtn Que Commit Dttm]-1 (yesterday)
if the day is Monday, i use the range from -3 to -1, including Sunday, Saturday and Friday
now i want to automate the report, schedule  for example...
0
mlmccCommented:
You can automate the report but that filter will MUST be built in the universe or set it up to retrieve all the data for this year and filter out what you don't want at the report level using your variable.

The scheduler cannot look inside the report and use variables.  It only knows what is in the query.  You cannot schedule with a formula for the value of a parameter.

mlmcc
0
rfedorovAuthor Commented:
Based on the conditions i have specified, anything you can suggest me to do?
0
mlmccCommented:
If you can't change the universe or the report query then the only way to handle this would be to run it ad hoc every morning or schedule it each night to run the next morning and enter the appropriate date for the filter.

Does the report have a date filter on it already?

mlmcc
0
rfedorovAuthor Commented:
i used the previous date as a parameter
0
mlmccCommented:
Unfortunately that is all you can do.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rfedorovAuthor Commented:
ok, thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.