Value from Prompt

building reports webintelligence
1

Hi All

I am creating a report where the user enters a start date and an end date (months). I need to be able to show in the report the difference between values depending on the months entered.

e.g. If user enters Jan as start and July as end month, I need to be able show revenue for Feb (Feb-Jan value), March will be (March-Feb) and so on…

Any help greatly appreciated.
Banging my head off walls for last couple of weeks on this!

Thanks

zefer (BOB member since 2008-12-08)

2

If this can help you.

aniketp :uk: (BOB member since 2007-10-05)

3

Unfortuantely not

zefer (BOB member since 2008-12-08)

4

Hy,

I can suggest you something, but I’ll have to find the final solution.

You can create in the universe derived tables for each month.

–table January—
select revenue
from table
where month=‘January’.(‘February’,…)

So in the report you can use those values and the difference between them.
But in this case you will have to use all 12 objects (Jan-Feb, Feb-Mar). Maybe you can find a way to hide the columns that do not correspond to the prompt values.

Let’s say the users select January and February. You can use for “Feb-Jan” the formula: if(UserResponse in (January,February);[revenue for Feb]-[revenue for Jan]; “Not applicable”). This logic can be used for all columns. After that you can use an alerter for the column with value “Not applicable” and find a way to hide those columns(white background; min width…).

I know this look strange and I’m hoping somebody will give a better solution, but you can try it and if you want let me know how it worked for you.

biankutzu22 :romania: (BOB member since 2008-05-16)

5

Try this,

= "Data Between"&FormatDate(ToDate(UserResponse(DataProvider([Query Objects]),"StartDate Prompt Text"),"mm/dd/yyyy"),"Mmm")&" and "&FormatDate(ToDate(UserResponse(DataProvider([Query Objects]),"EndDate Prompt Text"),"mm/dd/yyyy"),"Mmm")

KhoushikTTT :us: (BOB member since 2005-02-24)

6

Just gives a #DATATYPE error…

zefer (BOB member since 2008-12-08)

7

What is the format of your prompt value. The definition i gave is for format “mm/dd/yyyy”

KhoushikTTT :us: (BOB member since 2005-02-24)

8

Can it be used for MSSQL database? :?:

yohmawar :malaysia: (BOB member since 2008-12-15)

9

The answer to your question is: YES

This will work with MS-SQL Database as well.

“mm/dd/yyyy” – you can also try if needed as “dd/mm/yyyy” or “dd/mmm/yyyy” or “dd/MMM/yyyy” or “dd/Mmm/yyyy” and so on for different output formats.

If you get the error try replacing “&” with “+” and check the results.

Regards,
~Harish

harish.malik :india: (BOB member since 2008-07-14)

10

thanx harish

I’m still new in WEBI.

I’ve tried the code but got an error message '[Query Object] does not exist in the report". Do I have to create it the universe or in WEBI?

Thanx in advance
:wink:

yohmawar :malaysia: (BOB member since 2008-12-15)

11

Hi,

The query object should be part of the report, if you are performing any operations on to it.

If the object is already there in universe, you can include in your query while making the report or else, you need to create the same at Universe Level, so that that same can be used in the report.

Regards,
~Harish

harish.malik :india: (BOB member since 2008-07-14)