ORACLE BUSINESS INTELLIGENCE WORLD

Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, A passionate BI Consultant working on Oracle BI (OBIEE) Technology. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects..

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles






  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 75 other followers

  • Blog Hits Since Nov 2010

    • 318,558 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Dynamic Variables for Previous Date Period

Posted by Santosh Kumar Gidadmani on November 13, 2010

One of my client was looking at a report which would provide data from first day of last month to current date, so that he can compare sales trend for the last month and plan for the current month.

Best way to quickly work on it is to create variable and use it on dashboard prompt as default to provide first day of last month to current date.

To demonstrate the steps, I have created an excel database to work on the example.

Firstly, create an initialization block and dynamic variables called previous months

Click Manage > Variables to open the Variable Manager.

Click Repository > Initialization Blocks.

Right-click in the white space on the right and select New Initialization Block.

 
 Name the initialization block as Previous_MonthsClick Edit Data Source to open the Repository Variable Init Block Data Source dialog box.

Click the Browse button to open the Select Connection Pool dialog box.

Select any connection pool associated with database but not excel sheet. (on the above screen shot, emp is excel database.

Double-click the HR > Connection Pool object to add it to the Connection Pool field in the Repository

In the Default Initialization String field, type the following SQL

SELECT

ADD_MONTHS(TRUNC(SYSDATE,’MM’),-1),

SYSDATE AS CURRENT_DATE

FROM DUAL

Click OK to close the Repository Variable Init Block Data Source dialog box. The connection pool and initialization string are added to the Repository Variable Init Block dialog box

Click Edit Data Target to open the Repository Variable Init Block Variable Target dialog box.

Use the New button to create TWO variables as FIRST_DAY_LAST_MONTH and create another as CURRENT_DATE

Click OK to close the Repository Variable Init Block Variable Target dialog box. The variables appear in the Variable Target field in the Repository Variable Init Block dialog box

Click Test and verify you get the results in the picture.

Click OK to close the Repository Variable Init Block Data Source dialog box.

Click Action > Close to close the Variable Manager

Check in changes.

Check Global Consistency. If the Consistency Check Manager displays any errors, edit the repository to correct the errors before continuing. If there are no error messages, close the Consistency Check Manager.

Save the repository.

Logon to Answers

You can use these variable anywhere in the reports like on the filters as well as on prompts.

Create any report which provides data by date range

Build the following query

1. Pull the date field with other fields

2. Add filters on the date field

Click on the results, it will pull only data within the date range. This date range will dynamically change every month to provide data from first day of previous month to current date.

You use the variables in the Date Prompt also as below

Similarly, you can you these variables on the prompt as well.

Go to prompt and pull the columns including date column

On the default to section, select server variable and type the variables as

Save the report. The report will filter date with the above date range by default.

– Santosh

Advertisements

One Response to “Dynamic Variables for Previous Date Period”

  1. sowjanya said

    Your Blog is Very helpful for OBIEE beginners,
    Thank you

    Like

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: