Tagged with parameters - Visokio Forums

Idea: Parameter passing -Dynamic queries for user drilldowns?

acohen — Thu, 15 Jan 2015 09:41:22 -0500

I know this idea has come up in several other forms already, primarily via threads tagged/labelled 'parameter passing'. Essentially, it is often not practical to bring in all of the data one might need to see into an instance of Omniscope and so the data is aggregated at some level.

However a user needs the functionality to be able to see further detail in some instances. If the user could trigger a parametrised SQL query to reload/refresh the data with one or more parameters set, it would enable an additional, more granular and personalised view of that data that would be ideal.

For instance, the user is looking at monthly data of some sort. Daily data would be unmanageable and unnecessary as the dataset would contain tens of millions of rows. The idea is the user can click on a particular record/set a parameter, say the month and then behind the scenes a dynamic query is done to bring up the daily data which can be viewed in a seperate view/file.

Import: Parametric SQL 'WHERE' clause with Excel?

DominicChan — Tue, 19 Aug 2014 06:47:37 -0400

Hi....Maybe it is already available?
I would like to read an Excel file, say containing a field with 300 IDs, and use these 300 IDs as the SQL's 'Where' clause in conjunction with {{Parameter}} (or other ways?)
Is this possible?
Thanks
Dom

Idea: Personalised User Data based on Field Filters

OliverB — Mon, 10 Aug 2015 08:30:22 -0400

Hi,

One of our clients is looking at the possibility of opening up access to Omniscope Mobile reports to a wider group of users. Their business is split into a number of different Business Areas, and every report has the ability to filter to just show data for a specified Business Area.

The current users are all authorised to view data for the entire business. However, the users that they want to open the reports up to will only be authorised to see the data for the business area that they belong to. All access is currently via the Liferay Omniscope portlet. Has anyone done anything similar before, and what would be the best way to go about implementing this?

Import: SQL view as a parameter in DataManager?

carlosmartinmari — Wed, 21 Jan 2015 07:04:24 -0500

Hi,

I'm trying to introduce a parameter, say "Sqlview" that is being used in the DataManager database source block to call a custom SQL view as follows:

select * from p = {{Sqlview}}

The problem we have is that when you give the variable type to the parameter and set it as text, in DM the variable is automatically written as:

select * from "my_table_name"

which returns an error because it should look like that:

select * from `my_table_name`

Introducing the correct symbols doesn't help (parameter enter as "`my_table_name`").

Is there any way to do that?

Thank you in advance,
Carlos

Parameters: On-demand parametric data set delivery

Veaceslav — Fri, 12 Dec 2014 06:55:27 -0500

Generate custom dashboards on demand

Scenario/Problem

You have an Omniscope server (2.9 or higher) installed and publicly available at your-omniscope.your-domain.com

In addition, you have another web server (Apache, Jetty, etc) providing services or any useful information to your customers/users. For the purpose of this post I'll consider that you have an apache web server at your-apache.your-domain.com, even though you can achieve this with most web servers.

On a particular page on your Apache web server you have a list of links that you want to open different dashboards based on some particular parameter (e.g. user id)
A link might look like this: http://your-apache.your-domain.com/users?userid=5

Clicking that link the user should see a dashboard page having data that concerns him/her, the dashboard would be generated on the fly.

You have a dashboard/IOK file configured to load data from a particular SQL database.

You want to load data that matches a parameter for a particular user. E.g.
SELECT * FROM [dbo].[bigtable] WHERE [somefield]={{Userid}}

Solution

IOK File

1. Open the IOK file, DataManager tab and add a parameter. E.g. Name: Userid, Type: Fixed value, Data type: Integer, Value: 3

2. Open database source block and add your custom SQL query. E.g.
SELECT * FROM [dbo].[bigtable] WHERE [Liq Cat]={{Userid}}

3. Save your file

Your apache web server

1. Implement a request handler to deal with your special GET requests http://your-apache.your-domain.com/users?userid=5 and read user id value. The server will not send a response unless all next steps finish executing (either successfully or not)

2. Generate an action file (e.g. Action.xml) that:

Sets data manager parameter value (for Userid parameter)
Refreshes from source, this sub-action will execute the SQL query again and load records matching the new user id
Save IOK file to a new unique location in mobile folder. E.g. mobile/custom-dashboards/12345.iok

3. Execute the new action
E.g. On windows (same parameters on Linux -executeAction /some-path/Action.xml)
OmniscopeEnterprise.exe -executeAction "some-path\Action.xml"

4. Once the scheduler action is executed successfully, your Apache web server will return a redirect response to the client

The client/browser will be redirected to your-omniscope.your-domain.com/custom-dashboards/12345.iok

You may want to set up a proper back-end configuration, hardware load balancer + software load balancer, caching requests, etc. Also, you may want to make sure your paths are randomly generated, to prevent other users from viewing other dashboards.

Integration: Central list of Parameters in a file?

bfromson1 — Thu, 20 Nov 2014 10:43:17 -0500

Is it possible to have a list of parameters in a file which can be read by an Omniscope instance and the parameters then used.

Specific application is to have a list of server names in one place, then individual reports can use this list to open database connections. If server is ever changed then only one change in one file would be required.

Sources: FTP with changing file names?

ChristianGroupM — Fri, 28 Feb 2014 10:01:21 -0500

Hi Visokio-Team,

I would like to use the FTP Data Source block to import my reports to Omniscope, but what to do when the target data file name is post-pended every day with a changing date?

For example:
repot_14586_02262014
repot_14586_02272014
repot_14586_02282014

How can I parameterise the source connection block so that I import only the newest version of the report into Omniscope automatically?

Best Regards, Christian

Idea: Passing Parameters/Variables inside URLs?

bfromson1 — Mon, 14 Oct 2013 06:40:54 -0400

As the web server evolves it would be great to have variables and parameters implemented in such a way that the values could be passed as part of the URL for the report page - that would open almost unlimited drill down and data flow possibilities.

At the moment we have implemented our first web report with a link to Microsoft SQL reporting services - we use the Omniscope report for top level view of a group of accounts then a linked field on the user account number to allow direct access to the SRSS report which shows all transactions on the account going back up to 10 years. Embedding report parameters in the URL effectively makes all data linkable within the scope of the designers imagination!

Workflow: Passing values between tabs/files?

tagarw — Thu, 07 Aug 2014 06:39:18 -0400

Hi All,

Can two Omniscope tabs communicate with each other? For example in Tab 1 we are selecting some value and we want to retrieve that value in Tab 2. Can we do that? If yes... how?? Please confirm.

Thanks..

Formulae: parameter/variable accessible to end user?

earheart — Tue, 18 Feb 2014 14:32:59 -0500

I am trying to use a parameter as part of a formula (which is building a text field based off a lot of data elements and ideally this parameter). However, I can't use the parameter in the formula in a field organizer. I can use a variable, but the user is not prompted to change the variable.

So I need a way to use a parameter in the formula field or prompt a user to change a variable. Any ideas?

Parameters: Passing values between files

aknotts — Tue, 30 Nov 2010 11:17:07 -0500

Hi

I have an IOK file with aggregated data in it (due to file size/RAM limits). I have created a local link to another Omniscope IOK file which contains the details that I might need to see. Is there a way to configure the local link to pass a query to the other Omniscope file e.g. [Vendor Name] = 'Boots'

If this isn't possible on the link is there a way to do it?

Am interested to know the answer for both v2.5 and also v2.6 (as most of my customers are on 2.5 at the moment).

Many thanks

Andy

Sources: Dynamic Data Source names?

jshedley — Mon, 04 Mar 2013 02:32:11 -0500

Hi - Is it possible to have dynamic data sources? We have a system that has a new database created each month and we want to put this in without having to open Omniscope and manually edit the file.

Thanks, Jamie

Idea: Sources - Variable first row data location?

Master_Chief — Thu, 07 Mar 2013 07:05:30 -0500

I have a selection of source files which each hold similar data, however, the actual data itself starts at a different row in each file.

Rather than edit the source block each time I add a new one, is there a way to search the file and locate where the data starts based on a cell marker (a cell value which remains constant throughout all of the files) and use this as the new data table.

Previously I have used Excel macros to loop through columns to locate where data starts, but the aim is to move all of the VBA code into an Omniscope trial to be completely automated.

Idea: Parameter to be used in formulas in DataManager?

bfromson1 — Fri, 15 Feb 2013 04:57:43 -0500

Is it possible to use a DataManager import/filter parameter in a formula?

Example - I have defined a parameter in DataManager which is used in a record filter as part of the data loading process. In one of my tabs, I want to display the value of this parameter as part of the narrative in a Content View, so I need to add a formula which just takes this parameter value...

Idea: Database authentication details as parameters?

bfromson1 — Wed, 05 Dec 2012 07:14:33 -0500

In 2.8, the Database Source blocks now have the option to include various items as parameters.

Could this be extended to include the Username and Password for SQL Server authentication - so a single copy of Username and Password could be kept in one file and updated from there if a change is required?

Thanks

Integration: Launching Omniscope from other applications (2.7+)

steve — Fri, 09 Mar 2012 08:41:43 -0500

From tonight's build, 2.7 b289, a set of command-line arguments have been added, allowing Omniscope to integrate more easily with 3rd party desktop applications.

6 new options have been added:

dmParamName - the name of a parameter to customise
dmParamValue - the value for that parameter
dmParam2Name - another parameter
dmParam2Value
dmParam3Name - another parameter
dmParam3Value

When opening a file via the command line, by supplying the path to the IOK file, the above options are used to customise DataManager parameters. If the file is configured to "Refresh from source: On open", the data in the file will open up reflecting those parameters. You can use this in combination with database blocks, for example, to customise server-side queries according to user choices in the originating 3rd-party desktop application.

For example, the attached file has two DataManager parameters, "Ticker" and "Limit". Both are used in Record Filter blocks to filter the data in different ways.

Download the attached file to your desktop, and execute the following command in a command window:

"C:\PATH\TO\INSTALLATION\Omniscope.exe" "C:\Users\YOUR_NAME\Desktop\Dm params test.iok" -dmParamName=Ticker -dmParamValue=abbey -dmParam2Name=Limit -dmParam2Value=5

This launches Omniscope and opens the file, with the first 5 Abbey records from the Bond Prices demo dataset loaded into the Omniscope interface.

The path to installation is typically one of:
- C:\Users\YOUR_NAME\AppData\Local\Visokio Omniscope app
- C:\Program Files\Visokio Omniscope
- C:\Program Files (x86)\Visokio Omniscope

Idea: Integration - Calculated parameter values?

bfromson1 — Thu, 15 Nov 2012 05:51:04 -0500

It would be useful to be able to do some calculations on parameters:

1) To retrieve data from today and a previous date - have a single parameter that is "Current date" and a second parameter that is a function of the Current date parameter to get the historic date. This could be worked round in the SQL or other query, but is more effeicient to be able to have the query just select data from the two dates and the calculation done elsewhere

2) Embed the parameter in a filename to open a file - date or sequence number embedded in the filename or path. Just have the simple number as a parameter but be able to build it up into the full filepath and name for usage

Automation: Passing calculated parameters?

dnordhielm — Fri, 19 Oct 2012 16:53:24 -0400

I'm trying to have a value that is calculated based on a parameter and a database. I would like to use this value throughout the rest of my file.

As an example, suppose I have two tables, one where I have a list of people's names and genders. The name of the person that I'm interested in is stored in a parameter, but I'd like to be able to easily access that person's gender elsewhere in my workflow. Is there an easy way to do this? I included an example in the attachment with block notes describing my problem. Thanks!

Filtering: Record Filters using (variable) dates?

earheart — Thu, 16 Feb 2012 14:39:43 -0500

Is it possible to use a non-static date (today - 365 days) as a filter?

Integration: ILF link files with parameter overrides (2.8+)

steve — Mon, 19 Mar 2012 12:51:04 -0400

A previous blog described how a 3rd party application can launch Omniscope to open a file with customised DataManager parameters:

http://forums.visokio.com/discussion/1376/new-command-line-arguments-for-datamanager-parameters/p1

We have now extended this concept to ILF link files.

(An ILF file is like a shortcut, which doesn't contain any real data. Instead it instructs Omniscope to open a file from a URL written inside the ILF file. You would use ILF files typically if you want to force Omniscope to open an IOK file directly from the IOK file's URL, rather than allow a web browser upon click to download the IOK file to a temporary folder first. This would allow you to use domain locking, for example.)

Use the new Server Edition XML action "Create Link File" to create ILF files which customise DataManager parameters. As with command-line parameters from the above linked post, you can specify the name of DataManager parameters and the value to use, which overrides the current value in the file. Using this method, you can override an unlimited number of parameters.

For example, the attached two ILF files instruct Omniscope to open the IOK file attachment from the above linked post.
- The first overrides the parameters to show the first 5 "abbey" tickers.
- The second overrides the parameters to show the first 3 "hbos" tickers.

Filtering: Import with parametric custom SQL queries?

farboud — Fri, 17 Feb 2012 00:21:06 -0500

I am trying to convert an existing report from another system into Omniscope. There is an existing stored procedure with parameters that was used in the previous system to filter the data retrieved from an SQL database. In the DataManager I am now able to use the custom SQL in a database table source and pull data using the same stored procedure.

However, I am not certain what is the best way to provide the parameter values that can change dynamically from, say, within the DataExplorer. Otherwise I will have to import millions of data rows which is not practical. What is the best way to handle this situation? Are parameters useful here? Can parameters be seen from the DataExplorer?

Thanks, Kaveh

DataManager: New launch parameters (2.8+)

steve — Tue, 12 Jun 2012 07:34:19 -0400

Omniscope now supports DataManager parameters for:

File source: location path (2.8+)
URL source: URL address (2.8+)
Database block: database/instance/table names and host/port (2.8+); server-side filtering; within custom SQL
Access database: file path (2.8+)
Field organiser: default value, replace value
Record/field filters: match value
Input switch: input selection (accepts text, boolean, number)
Search/replace: search & replace values
List of values: the list itself

This means you can use a variety of mechanisms to dynamically update these form fields:

Linking two Omniscope session with different related dataset, such as stock metadata and historical prices; see: http://forums.visokio.com/discussion/comment/2188/#Comment_2188
http://forums.visokio.com/discussion/980/use-of-parameters-in-dm-formula
http://www.visokio.com/kb/empowered-refresh
http://tc.visokio.com/videos/?name=ArielCohenUsingParameterPassingAtInvestec&width=720&height=480
From Omniscope Server, using the "Set DataManager Parameter Value" XML action before refreshing from source; see http://www.visokio.com/kb/xml-actions
When launching Omniscope from a 3rd-party desktop application; see http://forums.visokio.com/discussion/1376/new-command-line-arguments-for-datamanager-parameters/p1
When launching via WebStart; see http://forums.visokio.com/discussion/1402/ilf-link-files-with-parameter-overrides-2.8-b10/p1

Parameters: Passing in external query parameters inside the link URL?

TomRiggs — Tue, 14 Oct 2014 09:53:52 -0400

I need to be able to pass a parameter from an external URL into the Omniscope 2.9 dashboard and then use that parameter value in the custom SQL query for the data for the dashboard. I can make this work between two Omniscope dashboards with no issue but there seems to be no way to dynamically make this happen. The parameter in the URL needs to be sent directly and used for the custom SQL query before the dashboard loads. It also needs to be adaptable as there will be many calls to this same dashboard from many users for different values.