[OBIEE 10g] Using Outerjoins

 

OBIEE using Outerjoins

This is how to use Outer Joins in Obiee

This works fine until you add a filter :

From the log:

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
having 440000 < sum(T31.F_FACT_VAL)
order by c1

This "kills" the outerjoin…. I hear think why not add "OR IS NULL". Let’s do that:

From the log:

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
having 440000 < sum(T31.F_FACT_VAL) or sum(T31.F_FACT_VAL) is null
order by c1

We still  are missing a couple off years.

Let try an in view filter: change F_FACT_VAL to:

CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end

This give us all the years back:

From the log:

SET VARIABLE QUERY_SRC_CD=’Report’;SELECT DIM_YEAR_MONTH_DAY.D_YEAR saw_0, CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end saw_1 FROM BM_OUTER_JOIN ORDER BY saw_0

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c4
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
order by c1

As you can see OBIEE did the case when logic internally.

[OBIEE 10g] Making A Column Selector In A Prompt

 

OBIEE Making a column selector in a prompt 

First we made a simple report based on the columns Year, Year Ago Dollars and Dollars. For debugging purpose we added the Logical SQL view. 

On the year column we added the following filter based on a presentation variable.

No switch to the Advanced tab:

 
Have a close look at the request XML especially the filter part: 

Periods."Year"
FILTER01_LEFT
Now edit the FILTER XML to:
@{FILTER01_LEFT}{’1′}
@{FILTER01_RIGHT}{’1′}

   

Don’t forget the single quotes (’). 

Press the set XML button:   and switch back to your criteria view:

 
Save the report! 




If you run the report and have a look at the logical SQL: 

you will see that the filter have evaluated into 1=1, which is always TRUE. It’s a good practice not to let it evaluate to NULL = NULL since not every database will give back the same result. 

Now create a new prompt called FILTER01_LEFT as a dropdown list based on SQL: 

 

In the SQL statement you put: 


SELECT case when 0=1 then Markets.Region else ‘1′ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Markets."Total US"’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Markets.Region’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Markets.District’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Markets.Market’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products."Total Product"’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products.Type’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products.Brand’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products.UPC’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products.Color’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products.Finish’ end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else ‘Products."Size X"’ end FROM Paint 





 
Test the result: 

Add the second prompt FILTER01_RIGHT as edit box prompt:

 

Test the result: 

Save the prompt and open a new dashboard page, put the prompt and the report on the dashboard page:

 
Test the result 

[OBIEE 10g] Google Charts

 

OBIEE Google Charts

This is how to work  with real venn diagrams:

First get your data:

Add an extra column with a row count:

Hide it:

Add an narrative view:

<img src="" id="venn_chart@9"/>  <!– creates an unique img tag –>
<script type="text/javascript">
var chartURL = "http://chart.apis.google.com/chart?cht=v&chs=200×200&chd=t:@2,@3,@4,@5,@6,@7,@8&chtt=@1&chdl=a|b|c&chdlp=b" ;
/* cht = chart type
   chs = chart size
  chd = chart data
  chtt = chart title
  chdl = chart legend
  chdlp= chart legend position
  more info http://code.google.com/intl/nl/apis/chart/ */ 

/* get the chart */
document.getElementById(’venn_chart@9′).src = chartURL;
</script>

Put all together:

[OBIEE 10g] Dynamic Prompt Content

OBIEE Dynamic prompt content 

This is  to show one of the ways to create dynamic prompt content. First create a small dropdown prompt which contains a FilterName LOV:

SELECT case when 1=0 then "D2 Market"."M01  Market"  else ‘Market’ end FROM "Sample Sales" Union all SELECT case when 1=0 then "D2 Market"."M01  Market"  else ‘Area’ end FROM "Sample Sales" Union all SELECT case when 1=0 then "D2 Market"."M01  Market"  else ‘District’ end FROM "Sample Sales" 

 

Put the result in a presentation called "filtername"

Create a new promt for the dynamic content in the column put:

case when ‘@{filtername}{Market1}’= ‘Market’ then "D2 Market"."M01  Market" else case when ‘@{filtername}{Market1}’   = ‘Area’ then "D2 Market"."M02  Area" else  case when ‘@{filtername}{Market1}’= ‘District’ then "D2 Market"."M03  District" else  "D2 Market"."M03  District" end end end

It’s very sensitive on the place of the single quotes!

Add a dynymic filter to your report: