Archive

Archive for May, 2012

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
%d bloggers like this: