Siebel Analytics Web Administration Guide > Siebel Analytics Integration API >

Using the Go Command To Issue SQL and Pass Filters

This section explains how to use the GO command to issue SQL, and how to pass filters to be used for navigation.

Issuing SQL Using the Go Command

The Go command can be used to issue Siebel Analytics SQL. These forms of the Go command return tabular results. The basic options from &Style= and &Options= can be used here as well.

To issue Siebel Analytics's simplified SQL, include the escaped SQL as a parameter to the Go command. For example:

saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales

where the FROM clause is the name of the Subject Area to query.

Alternatively, the command IssueRawSQL can be used to bypass the Web processing and issue SQL directly against the Analytics Server.

Passing Filters to the Go Command Through a URL (Navigation)

The Go command can also be used to pass context such as filters to a destination request. This is done by adding additional parameters to the call. You need to make sure that any columns you are passing are set up in the destination with Is Prompted filters, or specific default filters.

Navigation Parameters

The basic syntax of the navigation command is the same as presented earlier, but with the addition of the Action=Navigate parameter, and then population of the P1 - Pn parameters, as necessary.

&Action=Navigate

&P0=n  where n is the number of columns you wish to filter, currently 1 - 6.

&P1=op  where op is one of the following operators.

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 will need to type %25 in place of the usual % wildcard. See the examples that follow.
top
&P3 should contain 1+n, where n is the number of top items you want to see.
bottom
&P3 should contain 1+n, where n is the number of bottom items you want to see.
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:  The settings for &P1,&P2, and &P3 are repeated for &P4-P6, &P7-P9, &P8-P10, &P11-P13, &P14-P16, and &P17-P19 as necessary, depending on the value of &P0.

Navigation Examples

This returns records for the East and Central regions:

Saw.dll?Go&Path=/Shared/Test/ SB2&Action=Navigate&P0=1&P1=eq&P2=Customers.Region&P3=2+Central+East

This returns records for like Regions E....t:

saw.dll?Go&Path=/Shared/Test/ SB2&Action=Navigate&P0=1&P1=like&P2=Customers.Region&P3=1+E%25t

This returns the top two regions by dollars sold:

saw.dll?Go&Path=/Shared/Test/ SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=1+2

This returns records with between 20,000,000 and 25,000,000 in sales:

saw.dll?Go&Path=/Shared/Test/ SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=2+20000000+2500000 0

This returns records for Regions beginning with the letter E:

saw.dll?Go&Path=vate&P0=1&P1=bwith&P2=Customers.Region&P3=1+E

This returns records for Regions containing the letter E and having more than 20 million in sales:

saw.dll?Go&Path=/Shared/Test/ SB2&Action=Navigate&P0=2&P1=cany&P2=Customers.Region&P3=1+e&P4=gt&P5="Sales%20Fa cts".Dollars&P6=1+20000000

Siebel Analytics Web navigation is currently supported from charts, table and pivot table views, HTML views, and external applications and Web pages. The destination search should have filters defined on columns for which it wants to receive context. These can be specific filters or, usually, the Is Prompted filter. In addition to the Table.Column value specifically referenced in the navigation call, all filters from the source report that have corresponding table.columns in the destination, will be applied to the destination. Therefore, the appropriate context from a source can be passed to the destination.

Navigation From Charts

From the chart properties page, check the Navigate radio button and enter the full path to the saved search or portal that you are interested in the field provided, for example /shared/topaz/performance/transaction details.

Navigation Using JavaScript

Navigation can currently be accomplished using the custom text/date formatter for a column. The central concept is that you add a column you want to navigate from to your search. You then choose Custom Text Format from the properties for the column, and enter HTML that calls one of the two provided JavaScript functions. This technique can be used to perform many actions, including sorting columns, calling custom JavaScript functions, and so on.

Siebel Analytics Web includes two JavaScript functions that enable navigation from Table and Pivot views: GoNav and PortalNav. (These functions are located in /res/mozilla/viewhelper.js if you are interested in seeing what they do.) The former handles navigation to a specific search. The latter handles navigation to a specific dashboard. A description of their syntax follows, along with example Custom Text formats that you can use to accomplish navigation.

NOTE:  Siebel Analytics Web is standardized on the class=Nav, in order to control the look of the navigable text using the style sheet.

function GoNav(sPath,sTbl,sCol,sVal,sTarget) where

sPath = the catalog path of the destination search.

sTbl = the logical table name to filter.

sCol = the logical column name to filter.

sVal = the value to filter by.

sTarget (optional) = "_blank" to open a new browser window with the results.

Sample Custom Text Format for GoNav Call (include the quotes):

"<font class=nav onclick=\"JavaScript:GoNav('/shared/topaz/ performance/transaction details','Transaction','Quality','"@"');\">"@"</font>";;@

function PortalNav(sPortal,sTbl,sCol,sVal)

sPortal = the catalog path of the destination portal.

sTbl = the logical table name to filter.

sCol = the logical column name to filter.

sVal = the value to filter by.

Sample Custom Text Format for PortalNav Call (include the quotes):

"<font class=nav onclick=\"JavaScript:PortalNav('/shared/topaz/ _portal/transaction analysis','Transaction','Type','"@"');\">"@"</ font>"

Navigation From HTML Results

This is the same as the proceeding section, but rather than a custom formatter, type in the HTML syntax with static values in place of the @ signs.


 Siebel Analytics Web Administration Guide 
 Published: 18 April 2003