cancel
Showing results for 
Search instead for 
Did you mean: 

universe with the SQLSERVER

Former Member
0 Kudos

Hi

I want to compare the datetime function that is returned by the SQLSERVER database with the string

'MM/DD/YYYY', please suggest what can be done for acheiving the same.

I am using the formula like:--

DT_QualtyObjects.EventReportedDate BETWEEN

(CASE WHEN

CONVERT(varchar,@Prompt('Enter From Occurrence Date','D',,mono,free),101) = 'MM/DD/YYYY'

THEN

(GETDATE()-7)

ELSE

@Prompt('Enter To Occurrence Date','D',,mono,free)

END)

AND

(CASE WHEN

CONVERT(varchar,@Prompt('Enter To Occurrence Date','D',,mono,free),101) = 'MM/DD/YYYY'

THEN

GETDATE()

ELSE

@Prompt('Enter To Occurrence Date','D',,mono,free)

END)

Which will finally return me the from date and to date of the user and if the user has not entered any date then the data should run for the from date=sysdate-7 and to date=sysdate.

At this point in time i am not able to compare the date returned with the "MM/DD/YYYY" string

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Rahul,

The format in which the date should be entered in SQL is 'YYYY-MM-DD' to match your comparison. Also you might want to unpack the @prompt by using substrings and covert back to datetime. The value that comes into the @prompt is a character type not a datetime type.

Regards

Jacques

Former Member
0 Kudos

Hi,

Please follow the steps below for MS SQL SERVER :-

1) Create an date type object "Object1' in the universe.

2) In the object use getdate() function in the Select clause which will fetch the curreny system date.

3) Now create a character type object 'Object2' which will use @prompt function in the Select clause-@Prompt('Enter Date','D','Emp\Object1',Mono,Free). 'Emp' is the class name in my example.

4) Save the universe and export it.

5) Create a webi document with this universe and select 'Object2' in the report.

6) When you will run the report you will recieve 'Enter Date' prompt where you can select the current date from the list of values. Refreshing the list will update the date.

In this manner you can create a report which uses current or 'todays' date prompt. Every time when the user will run the report he could select the current date by refreshing the list of values as there will be only a single current date value.

-


More you can customize it as per your requirement for example:

What you can do create an object in the universe which would contain the dates of the last 7 days in a yyyy-mm-dd string-format. Then add the values "yesterday", "today" and "last week" to that field. It's definition looks like this:

case

when datetm < getDate()-9 then 'last week'

when datetm < getDate()-8 then 'yesterday'

when datetm < getDate()-7 then 'today'

else convert(varchar(10),datetm,21)

end

datetm is the column holding the date of the transaction in the transaction table.

Now add another Object called "Date From" with this formula:

convert(

datetime,

case

when @Prompt('Enter Date to (yyyy-mm-dd)','C','Itccpr\Objekt3',Mono,Free) = 'today' then convert(varchar(10), getDate()+1, 21)

when @Prompt('Enter Date to (yyyy-mm-dd)','C','Itccpr\Objekt3',Mono,Free) = 'yesterday' then convert(varchar(10), getDate(), 21)

when @Prompt('Enter Date to (yyyy-mm-dd)','C','Itccpr\Objekt3',Mono,Free) = 'last week' then convert(varchar(10), getDate()-6, 21)

else @Prompt('Enter Date to (yyyy-mm-dd)','C','Itccpr\Objekt3',Mono,Free)

end

, 21)

Using that column in the condition-area of the WebI-Query, users can enter "today", "yesterday" or "last week" or alternatively enter any date in yyyy-mm-dd format. To achieve that the report will always open with the most recent data, I sheduled a job hourly that runs with the parameter from "yesterday" to "today", so the users don't even have to refresh the data to get the most recent transactions if they open the document via "View Last Instance".

Hope above will helps you to solve your query.

Cheers

Deepti Bajpai

Former Member
0 Kudos

Hi Deepti

First of all, thanks aloooott for taking out some time for my query which is still of importance for me and sorry for checking and getting back to you so late on this.

I was delighted to see your response.

You knw, what!...i created such things like object of Sysdate and also used it in prompt both using @prompt at universe level and at report level,

however faced some issue.

Here is what is get when i do the things as suggested by you:-

1) create an object Getdate()/sysdate().

2) create a prompt @Prompt('Enter Date','D','Class\Object',Mono,Free)

but the problem which i face is at universe level is;-

Both the objects(getdate and @prompt) doesn't parse at the universe level and gives the error:--"Incorrect Syntax near from state", may be because it is not

coming from any table OR not refering any table atall!

Even if i apply the formula and export it and then again use the same in the WEBI report, i get this error:-

"There is an error in the query and the field doesn't refer any table".

and also i noticed that the calendar anyway contains the 'TODAY' option at the bottom, so how does this work around will help in case if it work and

if it really works, please let me know the correct way!

I may be doing something wrong if you think that it works.

i hope you will not mind if i can have your email id, or official number when i may call you to discuss the same(if u in india!

Many Regards

Rahul

Former Member
0 Kudos

Hi Rahul,

Let say you have 2 objects

Object1 = getdate()

Object2 = table.country

At universe level, Object1 will not parse.

When you select only Object1 in WebI Query Panel it will give error u201CThe query does not reference any tableu201D.

For testing, select two or more object ex. Object1 and Object2 in WebI Query Panel and then the run the query.

Try same for @Prompt Object.

Thanks,

Sushil