Home > Universe Designer > Universe Prompts using @prompt function

Universe Prompts using @prompt function


Let’s play with prompts

In this thread I am going to explain some frequently used prompts which are extensively used for getting input range in reports. The prompts which I am going to illustrate will fall under LOV Categories i.e. there will be manually generated LOV’s to get values from the database. The following examples in this thread are done using Oracle and SQL Server databases and you can change the syntax to appropriate databases like DB2 or any other supported databases.

Everyone who is handling BI will have some ideas in prompts, these are used to get input parameters from the users when the run a report. There are some time range prompts like “Enter Start Date:” and “Enter End Date:” where the users can enter a date value or select a date from calendar control (Web Intelligence) and report will be retrieved for the selection.

If report is in repository with necessary access privileges being given, the users will refresh on their own. By this, the above style of getting the dates from the users is really good but in some cases the reports will be scheduled to different clients in and around the organization, in this particular scenario the prompts should be static i.e. it cannot be changed from time to time. There will be request to from clients that they need report for one month, last month, last week, week before last week, yesterday …etc….

So now the report should work for both manual feed and fixed range. This can be achieved from writing prompt definitions from report or universe.

Following are some of the frequently used time ranges for which I have written some prompt definition,

TIME RANGE LOV

This prompt is written in universe as a where condition, so that whenever it is used in the report , it will act as the where condition of generated query set.

Now the same prompts can be used as manual time feed and as well as the manually generated LOV like “LAST WEEK”. It is pretty self explanatory query which is mentioned below, take a look and you will get the exact idea behind this short prompt definition. For example, the below prompt definition is written for a date field from AdventureWorks database tables, if the user refreshes the report the values of the dates will between Currentdate –7 to Currentdate.

 

   
AdventureWorks.Production.Product.SellStartDate      
between Case @Prompt('Enter start Date:','A',,MONO,FREE)       
when 'LAST WEEK' then getdate()-7       
when 'CURRENT MONTH' then DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)       
when 'LAST MONTH' then DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))       
when 'YESTERDAY' then DATEADD(d,-1,GETDATE())       
end       
and       
Case @Prompt('Enter End Date:','A',,MONO,FREE)       
when 'LAST WEEK' then getdate()       
when 'CURRENT MONTH' then  DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))  
when 'LAST MONTH' then DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))       
when 'YESTERDAY' then Getdate()       
end 

image

Advertisements
  1. User
    June 19, 2012 at 7:17 am

    can we create cascading LOV using deski ?

    Like

    • June 19, 2012 at 12:45 pm

      Yes probably we can because we are creating these cascading LOV’s from universe, so it should act on both web intelligence and desktop intelligence reports.

      Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: