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.]]>
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]]>
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
<?xml version="1.0" encoding="UTF-8"?>
<schedulerFileAction source="/location-of-your-iok-file/file.iok" logTimings="false">
<subActions>
<schedulerFileActionSetDataManagerParameterValue parameterName="Userid">
<binding>
<manualparambinding type="INTEGER" publishedPublicly="false">
<values>
<e>
<v type="java.lang.Long" value="5" />
</e>
</values>
</manualparambinding>
</binding>
</schedulerFileActionSetDataManagerParameterValue>
<schedulerFileActionRefreshFromSource clearCacheBefore="true" clearCacheAfter="true" failOnError="false" />
<schedulerFileActionSaveIok location="/location-of-our-new-dashboard/12345.iok" />
</subActions>
</schedulerFileAction>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.
]]>
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!]]>
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]]>
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]]>