OBIEE 11g: Table view Date Column controlled by Two Date Prompts

This page shows how you can setup up 2 dashboard prompts to restrict your data to show between a start date and an end date.
To accomplish this task is simply straight forward as the below 2 points:
Update two Presentation Variables say @{StartDate} and @{EndDate} with the Start Date and End Date prompts.
Then have a Table View with two filters on the Date Column i.e Date >= @{SartDate} and Date <= @{EndDate}.

We will be following the 2 above steps and also tweaking the prompts to overcome OBIEE’s default behavior to reach our goal.

  1. Goto the http://localhost:9704/analytics url, then select “New” -> “Dashboard Prompt” then the subject area “Sample Sales”
  2. Create a new “Column Prompt” by selecting “+” symbol, then in the “Select Column” window select “Time” -> “Per Name Month” then click the “OK” button.
  3. In the “New Prompt : Per Name Month” window, under “Options” section , for “Choice List Values” select “SQL Results” and add the below SQL statement:
  4. SELECT “Time”.”Per Name Month” MTH1 FROM “Sample Sales” ORDER BY MTH1.
  5. Then click the “Edit Formula” button for the “Prompt For Column” field.
  6. In the “Edit Column Formula” window enter ” ‘SDate’ ” for the “Column Formula”, the click the “OK” button.
  7. Uncheck the both “Enable user to select multiple values” and “Enable user to type values” checkboxes.
  8. Check the “Require user input” checkbox.
  9. Select “Presentation Variable” for the “Set a variable” field and in the below text box enter “StartDate”, then click the “OK” button.
  10. By following the above similar steps create a “New Prompt: ‘EDate’ entry as show below, then click the “OK” button.
  11. Make sure the “Presentation Variable” for the “Set a variable” field and in the below text box enter “EndDate”, then click the “OK” button.
  12. On the final “Definition” screen make sure to select “Row-based Layout” as show below and click on the “Save Prompt” icon to save.
  13. Save your Dashboard prompt under the “Shared Folders” -> “11g Shared” and name it as “DateDiffPrompt” and click the “OK” button.
  14. After creating the above two dashboard prompts. We now have to create our report or analysis to use the two date prompts.
  15. From the “Oracle BI Answers” web page select “New” -> “Analysis” -> “Sample Sales” to create a new report.
  16. Select the columns as shown below i.e. “Per Name Month” and “Revenue”.
  17. Select the “Per Name Month” options and select the “Filter” option.
  18. In the “New Filter” window select “is greater than or equal to” for the “Operator” field.
  19. Enter “StartDate” for the “Variable Expr” field, then click the “OK” button.
  20. for the same column “Per Name Month” select the “Filter” option and in the new “New Filter” window select “is less than or equal to” for the “Operator” field.
  21. Enter “EndDate” for the “Variable Expr” field, then click on the “OK” button.
  22. Make sure that both the columns and filters are created per shown in below snapshot.
  23. Click the “Save Analysis” icon and select the “Shared Folders” -> “11g Shared” folder and enter “DateDifference” for the “Name” field.
  24. Click the “OK” button to close the window.
  25. Goto the “Dashboards” in the Answers menu and select “My Dashboard” the on the top right select “Page Options” -> “Edit Dashboard”
  26. In the left side “Catalog” window drag the “Shared Folders” -> “11g Shared” -> “DateDifference report and drop it onto the right pane with “Drop Content Here” text.
  27. Drag the “DateDiffPrompt” prompt on top of the “DateDifference” section so it is placed in a new section as show in the below screenshot.
  28. Click the “Save” icon then click on the “Run” icon to show your dashboard.
  29. You should now see the two prompts with a blank report.
  30. Select a date from the “From” prompt say “2008 / 02” then a date from the “To” prompt say “2008 / 07” the click the “Apply” button
  31. You should now be able to see the selected months in your “DateDifference” Report table.

Note:
If you will like to test out the OBIEE default behavior change EDate to SDate in the “Prompt For Column” field in the “New Prompt” window in one of the previous steps, save and re-run your dashboard.

Summary:
Due to the default OBIEE behavior we had to keep two different names for the “Prompt For Column” i.e “SDate” and “EDate” to make the prompts have an effect on the “DateDifference” table.

OBIEE : Variables in Oracle OBIEE 11g

There are basically 4 different types of variables in OBIEE 11g.

Session Variables
Repository Variables
Presentation Variables
Request Variables.

Session Variables:

As the name suggests, session variables are created during the creation of session i.e., as soon as a user logs into the BI server. So, Every login has its own session variable. There are two types of session variables
System (which are defined by OBIEE and are reserved)
Non-System which are defined by developers.
Session Variables can be created only through Oracle BI Administration Tool.

Referencing session variable:

For displaying session variables, we should use @{biServer.variables[‘NQ_SESSION.VariableName’]} .

For using session variables in expression, we should use VALUEOF(NQ_SESSION.“VariableName”).

Repository Variables:

A repository variable is a variable that has a single value at any point in time. There are two types of repository variables
Static (which changes only if admin or developer changes it value) Dynamic ( value is refreshed using a query)
Repository variables can be created only through Oracle BI Administration Tool.

Referencing repository variable:

For displaying repository variables, we should use @{biServer.variables.VariableName} or @{biServer.variables[‘VariableName’]}.

For using repository variables in expression, we should use VALUEOF(“VariableName”) for static variable and for dynamic variable VALUEOF(“Dynamic Initialization Block Name”.“VariableName”).

Presentation Variable:

A presentation variable is a variable which can be created as a part of creation of dashboard prompts. Dashboard prompts must be either Column Prompt or Variable Prompt.
The value of presentation variable is set by the prompt for which it is created (upon user selection).

Referencing presentation variable:

For displaying presentation variables, we should use either

@{variables.VariableName}[Format]{DefaultValue} or
Format and DefaultValue are optional
Format is useful to format the data for e.g., for Date, format can be MM/DD/YYYY. Note: Default Value is not formatted. @{scope.variables[‘VariableName’]}.
Scope should be used if you create variables with same name. Scope can be analyses, dashboard etc.
Order of precedence is analyses, dashboard pages, dashboards.

For using presentation variables in expression, we should use @{“VariableName”}{DefaultValue}. Default value is optional.

Request Variable:

Request Variable is used to overwrite the value of session variable and it happens only during request initiation to the database from column prompt. Can be created only during the creation of column prompt.

Database Connection Failure while creating a “New Repository…” using the Administration Tool-Tnsnames.ora

  1. In the OBIEE 11g 11.1.1.X.0 “BI Administration Tool” when you try to create a “New Repository…” and the below screen shows your unable to connect to your Data Source Name, as show below:
  2. Even when you have configured your “System DSN”s correctly as show below:
  3. The solution would be to configure your tnsnames as described below:
  4. Goto the Windows 7 “Control Panel” -> “System and Security” -> “System”
  5. On the left open “Advanced system settings” and on the “System Properties” window select the “Advanced” tab.
  6. After select the “Environment Variables” button on the bottom:
  7. In the “Environment Variables” window under “System Variables” click the “New” button.
  8. then enter “TNS_ADMIN” for the “Variable Name” field and for the “Variable Value” enter the location of your OBIEE Home/oracle_common/network/admin e.g: C:OracleFMWoracle_commonnetworkadmin
  9. Click “OK” to close the “Edit System Variable” window, then “OK” to close the “Environment Variables” window and “OK” again to close the “System Properties” window.
  10. Then come back to your windows explorer and copy the tnsnames.ora from your Oracle Database Home/network/admin/ e.g: C:appsrikanthproduct11.2.0dbhome_1NETWORKADMIN to your OBIEE Installation Home/oracle_common/network/admin e.g: C:OracleFMWoracle_commonnetworkadmin
  11. You will need to create the “admin” folder then copy the tnsnames.ora to that location as show below:
  12. Now log-off as your user reboot and try creating your “New Repository…” in the “BI Administration Tool”.

how do i generate a tnsnames.ora file to connect to an oracle database

I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find.

Do I need to generate the tnsnames.ora file myself? If so, where do I place it? If not, how does Oracle generate it for me? If I do need to generate it, what is the appropriate syntax for the file?

5 Answers

You can easily create a tnsnames.ora [text] file. It should be in $ORACLE_HOME/network/admin/ and should look something like this:

 ORATST=
 (description=
   (address_list=
     (address = (protocol = TCP)(host = fu.bar)(port = 1521))
   )
 (connect_data =
   (service_name=oratst)
 )
)

The default directory for a tnsnames.ora file is

/u01/app/oracle/product/<version>/<dbname>/network/admin/tnsnames.ora

Contents:

<alias> = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <ip>)(PORT = <port>))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <dbname>)
    )
  )
  • alias: you can choose this and use it as connection string later on.
  • port: the default is 1521

f your OS is Windows 10, you can find tnsnames.ora file in the following unc path:

C:\app\myAccount\product\11.2.0\dbhome_1\NETWORK\ADMIN

Where myAccount is your Windows account name.