I have a requirement where I wanted to create object which should have values 1 to 100 (I’m aware that a custom object can be created and populated either from text or excel and the same to be located in bo server since my reports are WebI).
Once the object is created, I need to associate this object with my Revenue_Amount column in my fact table. Basically the user would select the value from this object(anything between 1 and 100) and based on the value, the revenue_amount value would be calculated.
Something like this…
Assume that the prompt value is 10 (This is not part of any table).
The revenue amount value would be =(Revenue_Amount * 10)
Since the object(Custom one) that I created is not part of any table, how do I pass this value to Revenue_Amount column? I’m not getting any clue on passing this value to the column.
Which database do you use? If it’s Oracle then you can create a derived table using this query:
select rownum as my_num_object
from user_objects
where rownum <= 100
Then create an object based on my_num_object column. Associate an LOV with the object. Then use the LOV in the @prompt() function. And then use the @prompt() in the definition of the revenue amount object.
Since the values are always 1 to 100 you could try to embed them within the prompt. It allows you to use a list that you provide rather than coming from an object. For example, the following will provide a list of “Yes” and “No” for the user to pick from.
@prompt('Enter Yes or No','A',{'Yes','No'},mono,constrained
But how would I parse my SQL since this is an custom LOV and I do not have any corresponding object in my table.
What I mean is,
???= @prompt(‘Enter Yes or No’,‘A’,{‘Yes’,‘No’},mono,constrained
Either “Yes” or “No” to be compared against a column on the Left hand side. Right? I do not have a column in my table to compare the custom LOV. However I have to use these value to calulate my revenue_amount.
You can define (in the select part) the revenue amount object as:
table_name.revenue_amount_column * @prompt('select a number','N',{'1','2','3','4'},mono,constrained)
The above is with hardcoded values of LOV.
If you want an LOV to be populated automatically, without hardcoding 100 values, then you can use the approach that I suggested above. Create a derived table that gives yoo 100 rows. However, I am not sure about an SQL syntax for the derived table for Teradata.
I understand what you meant. Thanks for sharing the approach.
However, we already have a case statement in the revenue amount column and adding @prompt would make my SQL become little complex as I’m sailing with the help of Teradata indexing mechanism.
My actual revenue amount column is as follows.
Sum(Case When Type =‘A’, Then Rev_Amt Else Rev_Amt * Rate End)
If I’m going to use prompt on top of this object, probably it would be like,
Sum(Case When Type =‘A’, Then Rev_Amt *@prompt(…) Else (Rev_Amt * Rate) *@prompt(…) End)
When there is no other option, Ofcourse I need to go by this way.
However, Is there anyway where we can use the prompt (Custom lov object) without bringing in actual SQL structure and use the same in the report by using UserResponse() function as this function captures the prompt value and I can create a variable to calculate the revenue amount? I’m not sure about this but just thought about it.
The reason for thinking like this is because I do not want to touch the Teradata Indexing part as using Prompt on the Revenue object changes the SQL construct and so many other users using the Revenue_Amount column. They would be little scared if Prompt appears to them.
Please share your ideas and let me also do the same if I have something strikes on my mind.
If the prompt for a number from the range 1…100 is needed only in 1 report then you can do it in the report by adding the 2nd dataprovider with a free-hand SQL of this type:
select @prompt('select a number','N',{'1','2','3','4'},mono,constrained) as number_entered_by_user
from dual
(the syntax that uses DUAL table is for Oracle)
And then capture the value that a report user entered in a prompt using UserResponse() function and use the value in the report as needed.
Just use the prompt as an object; it won’t parse but you can include it in a report as long as you select at least one “real” object that does reference a table. From there the value selected by the user will be visible to the UserResponse() function and you can use it in report formulas.
(BOB member since 2003-11-27)
(BOB member since 2002-06-06)