Dynamically Adding Default Values to Dashbaord Prompt

Here is the way to automatically add the default dates of current month in the dashboard prompt.

undergiven code can be used for setting up the day first of the current month in the dasboard prompt automatically.

For this purpose, goto the prompt and under "Default to", select "SQL Results" and add following code;

For the first day of the current month add following code;

 

SELECT   CASE

            WHEN 1 = 0

            THEN

               "Office Visit"."Visit Dt"

            ELSE

               TIMESTAMPADD (SQL_TSI_DAY,

                             (DAYOFMONTH (CURRENT_DATE) * -1) + 1,

                             CURRENT_DATE)

         END

  FROM   Payments 

For the Last day of the current month add following code;

SELECT   CASE

            WHEN 1 = 0

            THEN

               "Office Visit"."Visit Dt"

            ELSE

               TIMESTAMPADD (

                  SQL_TSI_DAY,

                  -1,

                  TIMESTAMPADD (

                     SQL_TSI_MONTH,

                     1,

                     TIMESTAMPADD (

                        SQL_TSI_DAY,

                        1,

                        TIMESTAMPADD (SQL_TSI_DAY,

                                      DAYOFMONTH (CURRENT_DATE) * -1,

                                      CURRENT_DATE)

                     )

                  )

               )

         END

  FROM   Payments

In Between operation both cases can be used combined.

Cheers ;)

 

Dashboard in Drop Down

There is a parameter you specify in your instanceconfig.xml file that controls this features.

<DashboardMaxBeforeMenu>2</DashboardMaxBeforeMenu>

The above code says once two or more dashboards are in the same folder, then they will be stored in a dropdown menu titled after the folder. I.e. suppose you have Dashboard 1 and Dashboard 2 in Folder A. Then after you put the above code in you will see Folder A across the top of your screen. Once you click on Folder A, you get a drop down for Dashboard 1 and Dashboard 2.

Once you change the instanceconfig.xml file, you'll need to restart presentation services for the change to take place.

Dashboard Alerts & Guided Analytics

• Enables content and layout of dashboard to change based on information analyzed
• Sections or links can be defined to only appear when there is “interesting” information in the data

Types of Guided Analytics


1.Adding a link to a dashboard, that only appears if a condition is met

2.Making reports appear on the dashboard, when a condition is met, Requires two things

• A report to run, that determines whether the event has happened
• A report, or link, that you want to appear

Step 1 : Create a “Trigger” Report


• Create a report that checks for your condition
• Has a branch fell below its sales threshold?
• Has a product’s year on year sales fallen?
• Use filters to check for the condition
• If the report returns rows, the trigger will be met
• Save to the Web Catalog

Step 2 : Add Guided Analytics Link to Dashboard


• Drag and drop from Dashboard Objects palette
• Add to dashboard, just under the report to which it is related

Step 3 : Edit the Guided Analytics Link Properties


• Select the report that is run to determine whether the link is displayed
• Select whether any rows, or no rows, is the trigger
• Enter link text
• Select report, dashboard or URL to link to

Step 4 : View Link in Dashboard (if Triggered)


• Link only appears if condition is met
• Guides user to the next action they should take

Making Sections Display Conditionally


• Individual sections can be displayed conditionally
• Select “Guided Navigation” from the section properties
• Pick the report to run

Cache Management & Overcoming Cache Problems

It is possible that reports that you run after the load process will reuse the cache that existed prior to the load process. This can result in inconsistencies between reports.


Following are some workarounds to avoid such inconsistencies.

I.

If you want to use the cache and you have a regular load of data into your source for OBIEE, then you just need to ensure that the cache is cleared after your load. There is an ODBC command which will clear it:

Call SAPurgeAllCache();

You can put this in a sql file and then call it from a .cmd file:

nqcmd -d "AnalyticsWeb" -u Administrator -p <password> -s D:\OracleBI\server\Scripts\Common\purge_cache.sql -o D:\OracleBI\server\Scripts\Common\purge_cache.out

When you run this .cmd file it will clear the cache, and you can also schedule the command at regular intervals.


II.

If your data is changing all the time and you just don't want caching on then Turn off caching in the NSQConfig.ini (and restart the server service) or at the physical table level in the .rpd, to ensure you get the un-cached results, and can therefore see the SQL being generated each run, or drill down in Answers.

1. Locate this configuration file: <root
directory>\OracleBI\server\Config\NQSConfig.INI.
2. In the Query Result Cache Section, change the [ CACHE ] setting from ENABLE =YES; to ENABLE = NO;.
3. Save the NQSConfig.INI configuration file and restart the Oracle BI Server service.
4. After running an Answers report, check the SQL NQQUERY.log (on Windows, usually located in, :\OracleBI\Server\log\)


III.

Third one is that check wether Table is cacheable (table properties) in the physical layer, you can turn on/off it per table or can set a schedule to expire and refresh using "Cache Persistence Time".


IV.

Bypass Web Server Cache feature in Advanced tab of the corresponding reports is also a resolution.

Here are the steps to implement By Pass Server Cache;

1 Build Report with required fields,
2 Go to Advance SQL tab check the Check Box ' Bypass Oracle BI Presentation Services Cache'
3 Goto bottom of page look for 'Prefix' in text box type 'SET VARIABLE DISABLE_CACHE_HIT=1;'
4 Save the report

v.

Here is another one, If you always want to bypass the Presentation server cache add this to the instanceconfig file of the development presentation server:

add below lines in instanceconfig.xml  file then restart all bi services.


<WebConfig>
<ServerInstance>
<ForceRefresh>TRUE</ForceRefresh>
</ServerInstance>
</WebConfig>


VI.

You can manually purge all the cache entries:
1. Open the Oracle BI Administration Tool in online mode.
2. Click Manage, Cache to access the Cache Manager page and select all cache entries.
3. Click Action, Purge.


Cheers ;)

Referencing session variables in report title

currentTime => @{system.currentTime}
system.productVersion => @{system.productVersion}
session.locale => @{session.locale} = en-gb
session.language => @{session.language} = en
rtl =>@{session.rtl} = false
session.loginTime => @{session.loginTime}
session.logoutTime =>@{session.logoutTime }
session.lastAccessTime =>@{session.lastAccessTime}
session.currentUser => @{session.currentUser.id} = administrator
user.homeDirectory => @{user.homeDirectory} = /users/administrator
user.id => @{user.id} = administrator
user.displayName => @{user.displayName} = administrator

DUMMY Column with NULL Value

Sometimes you need a dummy column in Union (ALL) query's with a NULL value.

This could help in this situation;

CAST(NULL as double)