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

Read More

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

http://localhost/analytics/saw.dll?Go&Path=/shared/inSight+SMB/Administration/Audit+Log/Audit+Log+Report&Action=Navigate&P0=2&p1=eq&p2="Audit%20Log".Component&p3="IS_SMB_NODE_AGG_F"&P4=bet&p5="Audit%20Log"."Event%20Time"&P6=2+"03/10/2013 1:52:45 AM"+"03/12/2013 1:52:45 AM"&NQUser=Administrator&NQPassword=justforyou

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:

http://localhost/analytics2/saw.dll?Dashboard&Action=navigate&P0=1&p1=eq&p2=Date."Day%20Date"&p3="2013-03-12"&Page=Overview&PortalPath=%2Fshared%2FinSight%20SMB%2F_portal%2FNetwork+Configuration+Analysis&NQUser=Administrator&NQPassword=justforyou

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.

http://localhost/analytics2/saw.dll?Dashboard&Action=navigate&P0=1&p1=eq&p2=LSP."LSP%20Name"&p3="43to41_13"&Page=Overview&PortalPath=%2Fshared%2FinSight%20SMB%2F_portal%2FNetwork+Configuration+Analysis&NQUser=Administrator&NQPassword=justforyou

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

Read More