In DataExplorer, you can add a DataManager view and position it so only the left sidebar of the DM view is shown amongst all your regular DataExplorer views. In Omniscope, the DataManager tab is just a preconfigured tab; you can mix up views as you like.

You can then define parameters in the "Parameters" tab, and use those parameters inside your SQL. If you have parameters defined, the database block should explain how to format them.

You then configure "Data > Auto refresh > ... and apply when idle" (please experiment).

In this way, users can enter different parameter values, interactively, and after a refresh has finished, explore the data in the same file.

Alternatively, a more powerful solution involves using two Omniscope windows which communicate to each other. The first shows summary data, aggregations of the full dataset, exposing only fields users will want to filter by. The second shows the full granular data but only for filtering/selection made in the first Omniscope.

For more information, see:
http://www.visokio.com/kb/empowered-refresh
http://forums.visokio.com/discussion/980/use-of-parameters-in-dm-formula
http://forums.visokio.com/discussion/comment/2188/#Comment_2188
Client use case video

Thanks. I did as you suggested and placed the DataManager within my DataExplorer and am showing the left sidebar of the DM. I am able to filter using parameters. I have 2 other related questions:

1- Is there a way to show the current values of one or more parameter in a Content View? Or for that matter access these values anywhere else in the report outside the DataManager.

2- How can I hide the "Rename" and "Remove" buttons from the left sidebar of the DM?

1. Not directly, but you can use Field Organiser to create a field containing solely the parameter value using the "default value" option. Then in Content View use "singleton value" to display it.

2. Not currently. Please also be aware that this configuration - parameters in DM - won't work in the free Viewer, since the DataManager view is only available in Omniscope DataManager, Serve and ServerPlus editions.

If the free Viewer issue isn't a concern, we can look at providing a customisation option to hide those buttons.

Did you look at the other approach, involving two Omniscope windows? This is very powerful, since you simply select data in one window, and see the granular data near-immediately in the other.

We also have external remote clients for which we want to expose their own specific data. That means that there is a need for different "Database Table" source for each client containing custom SQL scripts filtering for that particular client. I have seen references to using the "Generator" service. What are the options to make this work? Can parameters be used and passed in to generate a report .IOK file?

You should be able to use a different IOK file for each client, using the above approach.

If you wish to automate, you can use Omniscope Server to automate the production of IOK file variants with either entirely different sources, or with data refreshed using different DataManager parameters. To explore this, you will need a trial license of Server edition - please ask if needed.

Another method of automation is "Batch output". You define a series of "commands" in an IOK file with a predefined structure, and "Batch output" executes those commands. Once such command might mean "Email template 'x.iok' to user 'joe@xyz.com' after filtering to only include records with Country=France". In DataManager, choose "Batch output", generate a command file, and edit it. You'll need Server edition to use more than 3 commands. See this video:
http://tc.visokio.com/videos/?name=DataManagerBatchOutput

The benefit of automation approaches like these is they work with the free Viewer since they produce end-user tailored IOK files which don't need to access the database. But in the free viewer it doesn't allow the end-user to change database parameters from within Omniscope.

The clients are added dynamically in the A-Check system. So the reporting should be flexible enough and not require hard coded client specific parameters. So the batch output seems to require a prior knowledge of a fixed number of clients that need to be configured in the DM is that correct?

You also mentioned the capability of automating the production of IOK file variants with data refreshed using different DataManager parameters. This methodology is the most attractive to us. Coult you please elaborate or guide us on how to do that?

The Batch Output method "command file" can be dynamically generated if needed. For example, you might use a Database Source block retrieving client data, transform using Field Organiser into the correct field structure, and use a File Output to dynamically create the command file.
The Scheduler (part of Omniscope Server) would be used to automate first the production of the command file, then executing of the Batch Output block which used that command file.
This approach is very flexible but a little complex.

The Scheduler, an application and/or service which is included with Omniscope Server, works with XML Actions to automate tasks. You can define actions using the UI and schedule them on a recurring basis within the Scheduler. Or you can write programs which generate XML Action files with program-originating custom values, drop the XML file in the Scheduler "watch folder" and execute the action on-demand, e.g. from a dynamic web server upon user request, or from an external scheduling system.

We'll send you a trial key of Omniscope Server.

We don't have any video tutorials for the Scheduler, but the following DataManager videos are relevant:
http://tc.visokio.com/videos/?name=DataManagerBatchOutput
http://tc.visokio.com/videos/?name=DataManagerDatabaseTable
http://tc.visokio.com/videos/?name=DataManagerFieldOrganiser
http://tc.visokio.com/videos/?name=DataManagerFileURLOutput
http://tc.visokio.com/videos/?name=ArielCohenUsingParameterPassingAtInvestec&width=720&height=480

Scheduler & XML actions resources:
http://www.visokio.com/scheduler-help
http://www.visokio.com/kb/xml-actions
http://www.visokio.com/kb/windows-service

Steve, I think now I understance the idea of using the XML Action file that can be created dynamically and placed in the watch folder. I assume many XML Action files can be created by a number of clients using a dynamic web server and the scheduler is able to queue them and process these files as they get generated. For example each Action file can contain client specific parameters that get fed into a template file which in turn generates client specific IOK files using the Batch Output. Does this makes of a good design?

The other question is related to large data. As you have suggested, using 2 IOK files with one containing summary data that is linked through parameters to a second file that will contain more granular data is a good approach for scalability. What I don't understand is the deployment using a Web Server using this scheme. Does the user need to select the summary report first then select the second detailed report to obtain 2 open Omniscope windows?

How new data is fetched by a remote client in an extranet using Database Table Source block in the detailed report for a new selection in the summary report with security considerations in mind? (limitation on ports etc...)

Your understanding of XML Actions is correct, and we have a number of clients using this approach, although you can choose whether to edit a command file and execute it using Batch Output, or to define each output as a separate block and execute them individually using the XML Action "Publish DataManager output block".

Regarding large data:

Omniscope is designed primarily to be an in-memory data visualisation solution. Going beyond has some complexity and is a relatively under developed area.

The 2-Omniscope solution, with a "query" IOK file showing overview data, and a "granular" IOK file showing a subset of granular data, is relatively easy to set up when you have a secure and fast connection to the database and have an Omniscope license.

If your database is remote, you will be able to secure a connection to it using a VPN or SSH tunnel, but this will need to be established separately to Omniscope on the client PC, and you will need good bandwidth.

The free viewer is not permitted to access databases directly (nor edit DataManager parameters, nor employ the 2-Omniscope approach). Only licensed editions can. Instead it can only open IOK files. If the IOK file was created by a ServerPlus license, it can also perform background refreshes from online IOK files while it is being viewed. Note that IOK files are typically at least 10x smaller than the equivalent database table results in terms of bandwidth, often even smaller.

So, a solution as above but for free viewers will need:
- a ServerPlus license, to allow the free viewer to do background refresh
- a webserver which provides parameterised creation of IOK files via a Web control panel, using Omniscope Server as described above; you will need to develop this; with HTTP authentication
- an IOK file configuration which includes a Web View displaying a control panel from your site which allows them to change the database query.

The user experience would be:
- open IOK file from your site (with HTTP authentication)
- explore data
- inside Omniscope Web view using control panel hosted by you, change database query parameters (your server updates the source IOK)
- Omniscope detects and downloads the updated data
- Omniscope refreshes the display for the new data.

If you only want this solution for licensed installations, you will need a Server license, rather than ServerPlus. The simplest and most powerful solution is the 2-Omniscope solution with a direct database connection, since it does not require any server (other than the database) nor development.

Incidentally, what data volumes are you working with?

Thanks Steve.
We have potentially millions of records in the queries.
Also I would like to know what is the best approach for passing multiple selections as a parameter to a stored procedure. For a single selection of parameter {{param}} there is no problem.

You're probably using this:
SELECT * FROM [AdventureWorks].[Person].[Address] WHERE [City]={{Param}}

Instead use the following syntax:
SELECT * FROM [AdventureWorks].[Person].[Address] WHERE [City] IN ({{Param}})

But you may run up against a database driver limit for the number of parameter values - keep me posted how this works out.

I did use your second construct for queries not invoked through a stored procedure. However, the problem is specifically for passing in the Stored Procedure parameter for example:

exec MyStoredProc {{Param}}

causes a problem. I tried to declare a varchar and pre-pend '(' and append ')' and that gives me a problem. I used the profiler to see what is being sent to SQL and it seems the comma separated string that gets created is translated into individual parameters for the stored procedure at the SQL level which causes me to get an error related to too many parameters for the stored procedure.

You could try using a Database Output block to upload selected records to a temporary table, then use that table in an SQL query.

What database vendor? It may be that there are ways of configuring a different limit.

I am using SQL Server 2008

I have 2 reports (a summary and a detailed). In the summary report I need to pick a start and end date that I pass to the detailed report as parameters along with other parameters from the summary date. The summary data does not contain date information so I need to create a form of calendar or date picker. I did try the google analytic to import a range of dates. Is there any other built-in approach that I am missing. Also how to combine the summary data and the date related data (possibly append)?

Let me clarify what you mean:

You have a summary table which is small, and a detailed table which is huge and beyond the capacity of your clients.

The summary table isn't summarised by intervals in time. Perhaps it's a geographic or categorical breakdown of data.

As well as, or instead of, allowing the user to select by summary data and see the relevant detailed data, you want the user to pick a date range, in some way.

Correct?

Can you introduce date intervals into your summary table? This can be done either server-side in SQL or client-side in DataManager. You would extract the full date range from the detailed data as a single field, aggregate it to produce the intervals you want the user to be able to choose, and merge it with the summary table to produce a larger summary table.

Or, you have two summary tables, the second being purely for choosing data by date interval.

If you want arbitrary date(/time) values to be configured by the user, you could do this using two DataManager parameters. Currently we only have a "Text" data type for manual-entry parameters, but you would be able to use this (with an SQL text-to-date conversion) for testing, and introducing a date picker to the DM parameter panel would be relatively trivial.

To append the data from both summary and detailed sources, see here:

http://tc.visokio.com/videos/?name=MixingDatasets&title=Mixing+unrelated+datasets

http://forums.visokio.com/discussion/97/can-i-put-multiple-unrelated-data-sets-into-one-.iok-files/p1

That is correct. The date range is unrelated to other data in the summary file.
I did make it to work with parameters. Although as you said the user needs to enter the date in text format. I have the following questions
1- I can see your suggestion with pulling the range of dates as a single field and appending to the other unrelated data. I did try this but was not able to set my parameters which include start and end dates and the other unrelated parameter at the same time. They seem to be mutually exclusive. I tried the options specified in the MixingDatasets video but if I select data not related to date then my start and end date parameters are blank and vice versa. This causes my detailed report not to function properly. Should I use 2 different tabs or can all be under the same tab but I am missing something?

2- How can I introduce a date picker to the DM panel?

1 - you would need to create 1 summary table combining all permutations of dates and non-date summary criteria, rather than have 2 summary tables

2 - you can't. We would need to add this, which we could do early in 2.8 if your final solution needed it.

So it seems to me the best option, given my scenario, is to allow the user to set the start and end date parameters directly in the DM as text. All permutations of date and non-date can grow to be too large.

Yes - so they would select in the summary data AND enter min and max date parameters.

A date picker will be available in the next version of Omniscope (2.8).