Filters and Variables - Hidden Prompt

We had one strange requirement from the client the other day. The client wanted to see the customer details on a dashboard page for a year month of the current year which has the maximum Actual Value Metric across different customer regions. Let us try to understand it in detail.


Have a look at the report shown below : 

Therefore as you can see here, June month of 2015 has the maximum actual value.

and thus customer details are to be  shown for 2015/06 on the dashboard page.

Now the first thing is - Get the logical SQL of this report.
And separate yearmonth part out of it.

Now create a dashboard prompt.
Go to Default selection. Choose SQL Results. 
Paste the modified SQL. 

Save the prompt at appropriate location.

Let us create the Customer Details report. 

Go to dashboard now, create one dashboard page (ex- Hidden Prompt) and open dashboard page properties.

Select the Hidden Prompt page.

Run the dashboard page. We should be able to see the customer details for 2015/06.

Thank you.

Advantage of limiting prompt values by Members of Groups option

Have a look at the values of Region column.

Create a dashboard prompt on Region column and do not include Missing, Not Applicable and UNKNOWN values.

We have used NOT IN clause to eliminated the above values from Region prompt.

Run the prompt.

Go to Search.. option and ensure those values are present or not.

Let us find a solution.

Create a Group.

Take a Region column in the report and go to Selection Steps.

Select Region column from the list.

Edit Member Group. 

Select Action as Remove.

Manually remove UNKNOWN, Missing and Not Applicable from the list.

Do not check override with prompt option.

Save the group at appropriate location in Shared folder.

Now go to Region prompt and include this group.

Perform both the checks and you will find the Missing, Not Applicable and UNKNOWN values completely being eliminated from the prompt.

Thank you.

Advanced Trellis View

Advanced Trellis View is widely used in OBIEE reporting applications. 

Let us look at how to create it.

Have 3 dimensions and 2 measures in a report.

For ex - Region | Sub Region | Year Month | Actual Value | Target Value

Filter condition : Year = 2015

It makes sense when an appropriate time dimension attribute such as Yearmonth is included in the report. 

Here we have put Region column in prompt section, Sub region in Rows section.

For both the measures (Actual Value and Target Value), we have selected the visualization type as Spark Bar.

Let us modify the graph properties.

Note that we have checked <include NULL rows> options.

Graph output - 

When you hover your mouse over one of the measures it shows the measure value for
Start  - In this case target value for the starting yearmonth in the recordset
End - In this case target value for the last yearmonth in the recordset
Min - In this case the minimum target value in the recordset
Max - In this case the maximum target value in the recordset

Yearmonth values are shown directly. If we can add yearmonth caption then it would be more meaningful.

Let us go to Yearmonth column and modify the column formula.

Hover you mouse again over the Target value column in the view.

Thank you.

Using clause SELECT_BUSINESS_MODEL in dashboard prompt

Issue :

A dashboard prompt is to be created on a certain column (ex - Payment Description). Not all the values it should display. And that it should display only those values where Payment Key is equal to 0 or -1 or -2. But note that here the Payment Key is NOT exposed to presentation layer. So how can we create a prompt ? 

Answer : Use SELECT_BUSINESS_MODEL clause.

Look at BMM and Presentation layer.

Payment key is not available in presentation layer.

  Please carefully look at the syntax of SELECT_BUSINESS_MODEL clause.

  SELECT_BUSINESS_MODEL 

  

  "<<Column name in BMM layer>>"

  FROM

  "<<Subject Area Name in BMM layer>>"."<<Table Name in BMM layer>>"

 WHERE

  "<<Column name in BMM layer>>" in ('0','-1','2')

SELECT_BUSINESS_MODEL

"Method Of Payment Desc"

FROM

"Enterprise - Billing Operations"."Dim - Method of Payment Previous"

where

"Method Of Payment Key" in ('-2','-1','0')

Look at the result

If anyone faces such type of issue, hopefully this blog post might prove useful.

Thanks.

Use XML and Modify Report

Requirement :

A Summary X report has 3 dimensions (A| B|C)and several metrics. (M1|M2|M3|M4|M5}M6)
One of the metrics (say metric M1) has 5 action links (drill down reports R1,R2,R3,R4,R5)
User wants to see the 5 action links on dimension C as well.

Search for the word Metric M1 in the XML , look for the tag as shown below -

<saw:actionLinks showPopupMenuForOneLink="true" appliesToTotal="false">

The above line is the starting of the tag Action Link (placed on Metric M1 column)

Copy the entire content between the starting and end of action link tag.

<saw:actionLinks showPopupMenuForOneLink="true" appliesToTotal="false">

------          <<Code>>       -----

-------    <<code>> ------------

</saw:actionLinks>

Now look for the word C (Dimension C)  in the same XML and carefully paste the above code between start and end of formatSpec tag.

This should look like this-

<saw:displayFormat>

<saw:formatSpec suppress="suppress" wrapText="true" interaction="action">

<saw:actionLinks showPopupMenuForOneLink="true" appliesToTotal="false">

------          <<Code>>       -----

-------    <<code>> ------------

</saw:actionLinks>

Just apply this new XML and you are done. You can find the 5 action links in column C as well.

Note –

Point No 1:

You have to put the action links on the VALUE part of the column, that is column formula. (And not the Header part)

Therefore you have to put the code between <DisplayFormat> tags for the column formula of Attribute C and not the column name header of C.

Point No 2:

Following tags must be present.

<saw:formatSpec suppress="suppress" wrapText="true" interaction="action"></saw:formatSpec>

If you find the formatSpec  with no interaction, you will have to change it.

Point No 3 :

It was found that after applying the changes,Data format of the column (here say D1) gets changed by itself to different format…as shown below