Disclaimer

All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Tuesday, March 26, 2013

How to locate the reoccurring character in the string

Hello

Here is an interesting requirement. Look at the below report:




You notice that all of the PWE IDs have 3 '_'. Now I want to know the location of the last '_' in the string, what do I do?

OBIEE has locate and LocateN function at our disposal. However, the locateN function doesn't work that well, but the Locate Function does work.

So let's start with that:

Create a new column and enter: LOCATE('_', Pseudowire."PWE ID", -1)

the '-1' part is going to locate the '_' from the reverse side of the string, this is what we want


The resulting integers are the correct location of the last '_' if you count them :



Now, let's get rid of all of the strings after the last '_', maybe I will include the '_' as well.

Create another column in the report with the following formula:

Substring(Pseudowire."PWE ID" from 0 for LOCATE('_', Pseudowire."PWE ID", -1) -1)

Now we will use substring function. The 'for' part will be the location of the '_' that we located from the above step, and the 'from' part will be 0, which means we start from the beginning of the left side and we keep all of the strings up to the location of the last '_'. The last -1 in the formula is basically getting rid of the last '_' as well.




Now test:



Works perfectly.

Thanks

Until Next Time

Friday, March 22, 2013

Working with Go URL and passing parameters to dashboards and reports

Hello

It's been a while. Today, I am going to talk about GO URL and how to make it work. This can be very handy once you make it work. 

The idea of GO URL is that, it will be able to navigate to other BI Contents, such as reports and dashboards while passing certain parameters along. This can be very useful if you are coming from external application navigating into OBIEE. 

So let's look at a few URLs I wrote and we will break it down:




1. This one takes you to the report and passes 2 parameters, components and event time


So here lets look at the part 'Go&Path=' onward.

/shared/inSight+SMB/Administration/Audit+Log/Audit+Log+Report : This represent the location of the report that I want to get to. I am filling all of the spaces here with '+'.

'&Action=Navigate': This is the commend for navigation.


Following are the parameters which are represented by P0, P1, P2 etc.
P0 determines the number of parameters I am going to pass. In this case, it is 2: column 'Component' and column 'Event Time', which you will see later in the url.

P1 represents the operators. 'eq' means equal.

P2 represents the column name in the format of table name.column name. Here if the name contains space, then the whole name needs to be double quoted and space needs to be replaced with %20. Therefore, so Audit Log. Event Time becomes “Audit%20Log”. “Event%20Time”.

P3 Represents the value that you are going to pass, the hardcoded value needs to be double quoted as well.

When you want to pass more parameters, first change the value of P0 accordingly, in my case it is 2. You will then use P4, P5,P6 the same way as P1,P2,P3. This will go on until all of your parameters are defined.  The value of my P4 is 'bet', which means the operator for this one is 'between'. The way you define 2 values for 'between' is = 2+"value1"+"value2"

Last but not least, you attach '&NQUser=Administrator&NQPassword=justforyou', which is the username and password of OBIEE to your url.

That's it, now we can test it out in both OBIEE 10G and 11G:

10G:


11G:






This one takes you to the dashboard and passes 1 parameter Date Day = 2013-03-12:


Here, things are pretty much the same way as the above url, just a few different things. Here the 'Go&Path=' doesn't quite work, therefore we have to use  'Dashboard&Action=navigate' followed with parameters.. We are just passing 1 parameter in this case, which is date.

'&Page=' part defines the name of the dashboard page I am going.



 '&PortalPath=' This part is the path of the dashboard. Notice that every '/' has to be replaced by '%2F' and all of the spaces filled with '%20' or '+'.

Now let's test this url in both 10g and 11g:


10G:


11G:





This is pretty much it.

The third link does the same thing as the second link except the parameter is a little different, I will let you figure it out then.




Note:

Here are a list of operators and meanings from Oracle Documents.


Operator
Meaning
eq
Equal to or in.
neq
Not equal to or not in.
lt
Less than.
gt
Greater than.
ge
Greater than or equal to.
le
Less than or equal to.
bwith
Begins with.
ewith
Ends with.
cany
Contains any (of the values in &P3).
call
Contains all (of the values in &P3).
like
You need to type %25 in place of the usual % wildcard. See the examples that follow.
top
&P3 contains 1+n, where n is the number of top items to display.
bottom
&P3 contains 1+n, where n is the number of bottom items to display.
bet
Between (&P3 must have two values).
null
Is null (&P3 must be 0 or omitted).
nnul
Is not null (&P3 must be 0 or omitted).
&P2=ttt.ccc
In this parameter, ttt is the table name and ccc is the column name. If the table or column contains spaces, it must be quoted with double-quotes. Spaces should be escaped as %20, for example, Measures."Dollar%20Sales".
&P3=n+xxx+yyy+...+zzz
In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values.
Note: If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes. For example:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3="7West"



Thanks

Until Next time

Related Posts Plugin for WordPress, Blogger...