Question: is it possible to implement the above in Omniscope perhaps using the Custom Script operation?]]>
For an input array, I would like to loop through one of the fields and do a calculation but I am falling over at the first hurdle.
How do I reference a field and record number of an input array for instance? Or return a full field from the input array? So I would try something like Input1[Baseball players] for example and hope to preview just that one field.
Apologies, I am a real novice when it comes to JavaScripting.
Thanks]]>
Any suggestions as to how to do this?]]>
Ideally I'd like to use the built in browser, but I can't get my D3-based JavaScript charts to show in the browser versions for mobile devices. As a result, I'm using the external browser to render the Content Views.
With the native browser I can get output to show in the classic interface by clicking on the view's toolbar and choosing 'view in native browser' from the tools menu. However, pressing F12 to create the browser version for mobile devices results in the lower right view working, but the upper right view not working.
I've cut both views down to bare bones implementations, and I'm not seeing any errors. Is there something obvious I'm missing here?]]>
Is there a way to get a screen size inside Content view with native browser settings enabled? I need to use those numbers to get the right size of my pictures. ]]>
Is there a way to get all colours assigned to the field into array inside content view?
Bart
]]>
I've been trying to find a way to automate copying values down from above if the row is null.
The problem I have is that you can't execute 'for' or 'while' loops in functions in the field organiser (short of scripting)
For example in a column I have:
[Fieldname]
Data1
null
null
null
Data2
null
null
etc..
(where the nulls are empty cells)
I know I can use something of the form:
IF([Fieldname]!=null,[Fieldname],
[Fieldname]=null,IF(
Cell([Fieldname],CURRENTROW()-1)!=null,Cell([Fieldname],CURRENTROW()-1),
Cell([Fieldname],CURRENTROW()-2)!=null,Cell([Fieldname],CURRENTROW()-2),
Cell([Fieldname],CURRENTROW()-3)!=null,Cell([Fieldname],CURRENTROW()-3),
Cell([Fieldname],CURRENTROW()-4)!=null,Cell([Fieldname],CURRENTROW()-4),
Cell([Fieldname],CURRENTROW()-5)!=null,Cell([Fieldname],CURRENTROW()-5),
"Error field may contain more than 6 empty rows"),"Error")
but the limitations of this are if the latest data is pulled in and has more than 6 nulls before a value is above then it will print the error. Obviously also it creates an unnecessary extra field and the old one needs to be deleted in a second Field Organiser. Obviously I could copy paste and edit it up to 100 or more but this isn't very elegant.
I know I can use the Custom Script block to write a JavaScript function and as such have been learning javascript but am having a hard time applying it to my problem.
My question is:
Is there an easy/elegant way of doing this in formulas that I have overlooked?
Or is it a case of using JavaScript to solve my problems?
Is it better done in a 'script' fomula or in a 'Custom script' block bearing in mind I want to do it for 3 columns and keep the rest of the data in line?
I've attached a sort of example of what I mean of some basic data and how I would and have solved it in the past but not copied the formula down long enough therefore it errors after 6 nulls.
Hopefully this makes some sort of sense to someone! Thanks in advance for absolutely any help!
Simon]]>
Edit: I've managed to figure out how to construct the dataTable to read data in from a for loop for 2 different array's of data and then plot it out on the scatter plot. The next step will be to link the Omniscope data into the arrays and see if it still works. Being able to do this will greatly increase the flexibility of what can be done on the front-end of the data explorer.
I've posted below the updated code that just demonstrates reading in a simple amount of data via a for-loop.:
function drawChart() {
var EventArray = [1000,1500];
var EventValueArray = [2000,2500];
var data = new google.visualization.DataTable();
data.addColumn('number','Test1');
data.addColumn('number','Test2');
for (var i=0;i<2;i++) {<br />data.addRows([[EventValueArray[i],EventArray[i]]]);
}
var options = {
title: 'Age vs. Weight comparison',
hAxis: {title: 'Age', minValue: 0, maxValue: 6000},
vAxis: {title: 'Weight', minValue: 0, maxValue: 6000},
legend: 'none'
};
var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
------------------
I will be playing around a bit more and then posting an update if I figure out where in the code to link up the Omniscope scripting calculations into the chart DataTable.]]>
I'd like to ask if it's possible to export a table produced by a Content View script (html like) to an Excel file (including or not including the Content View table formatting).
Thanks]]>
Thanks]]>
The data is a set of consecutive annual payments.
This is the same as the IRR function in Excel, so you can test against that for verification.
Further explanation:
http://office.microsoft.com/en-us/excel-help/irr-HP005209146.aspx
http://en.wikipedia.org/wiki/Internal_rate_of_return
Source code prior to adaptation:
http://code.google.com/p/npoi/source/browse/trunk/main/SS/Formula/Functions/Irr.cs?r=227]]>
For each outer loop element (ABICapoGruppo) the resulting table has to show:
- an header row
- a serie of detail rows
- a summary row.
The number of detail rows is different for each ABICapoGruppo element.
At the moment I've written the following script (I've cut out all the details of data calculations).
Only the outer loop works fine, printing the description of ABICapoGruppo but no details from inner loop are produced.
I think that the key point is the inner array variable declaration that, for each outer loop cicle, is based on the current value of the outer loop element.
<#/*TABLE HEADER ROW*/#> [......] <#<br />//START OF OUTER LOOPvar array = subset_uniquesList('ABICapoGruppo', SUBSET('ABICapoGruppo',null,'<>'));for (var i in array) { var val = array[i]; #> <#/*ABICapoGruppo HEADER ROW*/#> [......] <#<br /> //START OF INNER LOOP array2 = subset_uniquesList('ABIAppartenente',SUBSET('ABICapoGruppo', '" + val+"') ); for (var j in array2) { var val2 = array2[j]; #> <#/*REPEATED DATA ROW*/#> [......] <# } #> <#/*ABICapoGruppo SUMMARY ROW*/#> [......] <# } #>
Is this the right way to write this script?
Attached to this post a doc word with a more readable version of this script. - Thanks
]]>
SUBSET_UNIQUESLIST([Field 2], SUBSET([Field 2],NULL,'<>'))
and it works fine, returning the list of non-empty in [Field2].
However, when I tried it in a script defining an array:
var array = subset_uniquesList('ABICapoGruppo', SUBSET('ABICapoGruppo',NULL,'<>'));
it returns the message that NULL is not defined. Which is the way to express the NULL values in scripts?
Thanks
]]>
I'm trying to create a content with a table included using the following script:
//START OF LOOP
var array = subset_uniquesList("ABIAppartenente");
for (var i in array) {
var val = array[i];
#>
<#/*REPEATED ROW*/#>
<#=val#>
<#/* example of inserting a script expression */#>
<#= formula("SUBSET_SUM('Netto', SUBSET('ABIAppartenente', '" + val+"'))") #>
<#/* example of embedded formulas using the formula() function from within a script*/#>
<#= formula("RECORDCOUNT(SUBSET('ABIAppartenente', '" + val+"'))") #>
<#/* example of embedded formulas using the formula() function from within a script*/#>
{{RECORDCOUNT(SUBSET2('Dataregolamento','ABIAppartenente', '04/09/2012','" + val+"'))}}
<#/* example of embedded formulas using the formula() function from within a script*/#>
<#<br />}
The table is created on a loop on ABIAppartenente code and have different columns:
- first column is the ABIAppartenente code itself;
- second column is a total amount for each ABIAppartenente code;
- third column is the count that contributes for each code;
- fourth column counts record for each ABIAppartenente code for a fixed date.
Everything works fine except for last column that return always 0 (for each ABIAppartenente, also if there are records for each ABIAppartenente code and the indicated date Dataregolamento).
So I'd like to know if anyone have an idea about the problem in using the SUBSET2 as indicated - Thanks
]]>
This allows advanced users with JavaScript development skills to effectively build a custom view in Javascript which runs in your system browser, embedded inside the Omniscope Content View, with the page source editable in the Content View as usual.
This is very much work in progress, and subject to change following feedback before we release 2.8 in public beta. Please experiment and post below, but don't build any live reports that depend upon the new behaviour.
But can't I already use Javascript in the Content View?
The Content View already supports Javascript pre-processing. In your page source, you have special <# #> tags indicating the start and end of script fragments. Omniscope then, behind the scenes, executes the Javascript in a browser-less environment to produce the final page text.
This is directly equivalent to the way a PHP/JSP/ASP/etc. web server might "execute" a server-side page to produce the HTML document that is sent to the client. We chose to use Javascript on the "server" side, but note that there is no HTML DOM or browser Window object, for example; this is pure "headless" Javascript.
In this case, the "server" is the Omniscope Content View back-end, in-memory on your desktop, and the "client" is the page renderer, either Java within Omniscope, or a separate native browser process embedded in the Omniscope window.
New source editing enhancements
When editing in "source mode" in the Content View, we now have full syntax highlighting of your page source. This makes scripting and HTML coding much, much easier.
Note that if you're using script pre-processing, advanced formulae, or the native browser, you will only be allowed to use source mode, rather than "design mode" for editing your page.
New menu items in the view's Tools menu:
- "Use native browser"
This affects how the HTML page content is rendered, after it has been evaluated (e.g. applying any formula insertion or script pre-processing).
This is deselected by default, matching 2.7 behaviour, meaning the page is displayed using Omniscope's built-in rich text display (via Java, based on HTML 3.2, without any support for in-page Javascript).
When selected, the page is displayed by embedding the system web browser (typically IE on Windows), allowing "client-side" scripts to be executed in the browser's Javascript engine, with full access to the HTML DOM, etc. This allows you to build Javascript interactivity and do other native browser behaviours such as embed media. - "View in external browser"
Click this to open the same page in an external browser. This allows you to debug your page in a full separate browser; we recommend Google Chrome for its developer tools out-of-the-box. The embedded browser, if IE, has very limited support for debugging.
Note that Javascript native dialogs (alert/confirm/prompt) are disabled in the embedded browser, because it can often lead to a complete freeze of the application. You should use alternative Javascript frameworks instead. - "Refresh on change"
By default, the page content is rebuild and the browser refreshed when cell edits or filtering occur. Untick this option to disable. You will need to code in a different means of triggering updates to any data you may have loaded via API / AJAX calls.
Even if ticked, viewing in an external browser will never refresh automatically on change; you will need to manually reload the external browser.
AJAX requests
You can choose to use this native browser option to add some animations or general pizzazz to your Content Views, or more extensively by using the API to request and process data.
We currently provide two APIs. At a low level, an HTTP service is provided should you wish to roll your own AJAX calls and perhaps use different or incompatible JS libraries to those used by the high-level API.
The high-level API is enabled by inserting a pre-processing snippet into your page source in the HTML HEAD tag, and provides Javascript objects and methods to 'call' the enclosing Omniscope Content View.
Low-level API
The page itself is served up by "page.html" within an auto-generated unique URL, which changes each time the view is opened. Use "View in external browser" to see the URL. But you should not need to know this, since the other HTTP services are available in a relative URL to this page. For example, from within "page.html", you can use AJAX and hit the "meta.json" URL (without any further URL qualifiers).
All requests return HTTP status code 200 if successful.
- page.html
Returns the page content as text/html after applying pre-processing and evaluating formulas.
No request parameters. - eval.json
Evaluates a JS expression on the back-end, with access to formula functions.
Accepts a POST request with text/plain upload body (NOT regular key/value form data), containing a Javascript expression to evaluate against the Content View's dataset as per "script pre-processing". This can be used to query arbitrary formula functions such assubset_max("fieldname"), for example.
Returns JSON{ "result": value }where value is the result of the expression; can be a complex object or array. - meta.json
Retrieves metadata about the view's data.
No request parameters.
Returns JSON{ "fieldCount" : 13, "recordCount": 1052, "fieldNames": ["field1","field2",...], "fieldTypes": ["text","text","date","integer","decimal",...] } - cells.json
Retrieves one or more cell values.
Accepts a POST request with the following form key/value pairs:
"fields": a JSON array of field names; if omitted, all fields are retrieved
"firstRow": The first row to retrieve, 0-indexed, must be in-bounds; if omitted, zero is used
"numberOfRows": The number of rows to retrieve; if omitted, all remaining rows are retrieved; automatically capped by the last row in the dataset
Returns JSON{ "result": [ [cell00, cell10, cell20], [cell01, cell11, cell21], [cell20, cell21, cell22] ] }
(where the 2-dimensional array's dimensions reflect the filtered data results, doesn't contain a row for field names, and is indexed [column][row]). - edit.json
Writes back one or more cell edits to the data table.
Accepts a POST request with the same key-value pairs as "cells.json", plus:
"data": a JSON two-dimensional array, in the same format as returned by "cells.json" under the "result" property.
Returns the empty JSON{}to indicate success.
This will only work in licensed editions where editing cell data is permissioned - i.e. not in the free Viewer. It will propagate a change to all views, and you will immediately see the result. You will need to untick "Refresh on change" if you want to avoid the same Content View (which originated the call) from reloading your page.
]]>
This formula gives the same result as the NETWORKDAYS() function in Excel for all the dates I tested:
DECLARE(
ve,DECLARE(de,DATETOTEXT([End Date],'E'),IF(de='Mon',0,de='Tue',1,de='Wed',2,de='Thu',3,de='Fri',4,de='Sat',5,6)),
vs,DECLARE(ds,DATETOTEXT([Start Date],'E'),IF(ds='Mon',0,ds='Tue',1,ds='Wed',2,ds='Thu',3,ds='Fri',4,ds='Sat',5,6)),
5*INTFLOOR(DATEDIFF([Start Date],[End Date])/7) + VALUE(MID('1234555512344445123333451222234511112345001234550', 7*vs + ve + 1, 1))
)
Source of inspiration:
http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates
====== Old version, recommended against! ========
Passing dates as dates into a script can cause odd behaviour in JavaScript. This demonstrates passing them as text, then converting in JavaScript to circumvent those issues.
The result is a calculation of the number of business days between two dates.
The script itself is a copy of:
http://partialclass.blogspot.co.uk/2011/07/calculating-working-days-between-two.html]]>
You can use Javascript in Omniscope in the SCRIPT function in formulae, and to do HTML pre-processing in the Content view.
Syntax:
dataArray(fields, rowSubset, includeFieldNames)Argument "fields": An optional array of field names. If omitted, all fields are retrieved.
Argument "rowSubset": An optional data subset, as returned by the subset function.
Argument "includeFieldNames": if true, the first 'row' in the returned 2d array 'table' will be the field names.
Returns: A two-dimensional array of cell data, indexed as [column][row], with indexes numbered from 0 to n-1.
See attached example, using dataArray inside a Content View.]]>
For example, this allows you to repeat a section of your document for different subsets of the data.
Scroll down for an example IOK file.
Requires 2.7 b96 or later. You must use "View Source" mode (the "<>" button in the Content View toolbar).
Existing syntax for dynamic content - {{ }}
The existing dynamic content syntax is as follows. You might not use this, but if you have inserted dynamic content using the "f" button in the Content View, this is what happens in "view source" mode:
<p><b>Total:</b> {{ SUBSET_SUM("Coupon") }}</p>
This example inserts the dynamic result of the sum of the "Coupon" field according to whatever data results after applying your filters.
New syntax for scripting - <# #> and <#= #>
<#
if (x==5) {
y = 3;
} else {
y = 2;
}
out.println("Write some text into the document, programmatically");
var array = subset_uniquesList("Category");
for (var i in array) {
#>
<b>Some text and HTML with <#=array[i]#> (inserted values)</b>
<#
}
#>
This example demonstrates some JavaScript then uses a loop over unique values (after applying filters) in a field to repeat a section containing both static content and inserted script expressions.
See the attached file for a larger example including comments.
Functions available in script blocks
formula("IF(RAND<0.5, true, false)") Dynamically evaluates an arbitrary formula using normal Excel-like formula syntax (not script)
recordCount() or recordCount(subset)See http://www.visokio.com/kb/functions-guide#RECORDCOUNT
subset("Field name", "Field value") or subset("Field name", "Field value", "Operator")See http://www.visokio.com/kb/functions-guide#SUBSET
subset_sum("Field name") or subset_sum("Field name", subset)See http://www.visokio.com/kb/functions-guide#SUBSET_SUM
Note that all SUBSET_? functions are supported in JavaScript. The SUBSET_UNIQUESLIST function is slightly different in that subset_uniquesList() returns a JavaScript array rather than a single String containing a comma-separated list.
textvalue(value) or textvalue(value, pattern)See http://www.visokio.com/kb/functions-guide#TEXTVALUE
More to come.
Example IOK file
Open the attached file in 2.7 b96 or later. This shows a table whose rows come and go as you filter. Try filtering by the Category field. Also try dragging the date sliders to see the numbers update and the rows disappear.
Expand the toolbars (or choose Toolbars > View toolbars), then click the "<>" icon to view source. The source is well-commented and shows different ways of working with scripting.
]]>
The "Custom Script" operation allows you to execute arbitrary Javascript to transform one or more inputs into a new output.
The result of the Javascript must be a two-dimensional array, indexed as [column][row] (i.e. [field][record]). You can optionally choose to provide the field name as the first row in this 'table'.
The inputs are accessible via "input1", "input2", etc. and also via "inputs[0]", "inputs[1]". These objects provide access to all regular Omniscope Javascript functions, such as:
input1.recordCount()
input[0].subset_sum("Price")
Here is an example script which simply ignores inputs, and produces static data:
[
["1, 1", "1, 2", "1, 3"],
["2, 1", "2, 2", "2, 3"],
["3, 1", "3, 2", "3, 3"]
]
Here is another which returns a 2x1 table of metadata about two inputs:
[
[
input1.recordCount()
],
[
input2.recordCount()
]
]
Here is another which does a sideways concatenation of multiple inputs:
working = new Array();
for (i in inputs) {
var input = inputs[i].dataArray(null, null, true);
for (n in input) working.push(input[n]);
}
working;
See attached example. Requires 2.8 or later.]]>
Firstly, the use case to answer the question "Why would I want to do this?":
Omniscope users familiar with the formula language may well have come across the SEARCH and SEARCHREGEX functions, which return the position at which a particular character/phrase (in the case of SEARCH) or regular expression pattern (in the case of SEARCHREGEX). These functions are often used to extract elements of text - for example, extracting parts of a postcode; the UK postcode comprises 4 sections:
So, from a full postcode, we might want to extract the postcode area (first 1-2 characters) or the postcode district (all characters before the space). Finding the space, and extracting everything left of the space, is fairly easy using the SEARCH function, so I will focus on the trickier example of extracting the postcode area.
Using SEARCHREGEX, I can do this in Omniscope by detecting the first alpha character (i.e. A-Z) and the first numeric character (i.e. 0-9) and then extract the characters between these two extremes. To do this we would use:
DECLARE(
/* Store position of first alpha (A-Z) and first nuymeric (0-9) */
firstAZ, SEARCHREGEX("[A-Z]",[Postcode]),
first09, SEARCHREGEX("[0-9]",[Postcode]),
IF(
/* Check that we found both an alpha and a numeric, and that the numeric is after the alpha i.e. a valid postcode format */
AND(firstAZ > 0,first09 > firstAZ),
/* Extract the alpha characters, if valid */
MID([ Postcode], firstAZ, first09-firstAZ),
/* Return nothing if postcode invalid */
NULL
)
)However, regex extraction is simpler and (once we're confident in regex - lots of tutorials online) allows for far more complex extractions. So here is the same function using Javascript:
SCRIPT(`
// Declare the pattern we want to extract, and then apply it to our input text
var re = /([A-Z]*)/;
var m = re.exec(text1);
// We should have one (or more) groups of character sets now
if (m == null) {
// If not, then return "No"
'No'
} else {
// If we do have at least one character group, return the first (could return many, concatenated, if required)
m[0]
}
`, "text1", [Postcode])The structure of this is:
- Firstly we have the Javascript code itself, which includes the use of a variable called "text1"
- Then, in the last line, we have the declaration of "text1" (this can be called anything, and needs to be the same name here as inside the script itself, and passing the value of our [Postcode] field into this variable, for use in the script
This only scrapes the surface of this (Javascript regex) approach - there are many ways to use this as the basis for far more complex, and valuable, text extractions... but as Steve's original article demonstrates, there are many, many more uses for Javascript.
Happy scripting... ]]>