Filter Data on SQL Widgets other options

Brian_Wiest

Currently there is the @createdFilter option. 
Are we limited to just this option or can we create more?

Brett_Moffett

At the moment you are limited to just this one and this one is only available on work items.
In future versions we will hopefully add more.

If there is one in particular that you would like I encourage you to create a feature request and get it voted up.

Brett_Moffett

I've raised this as a feature request....
https://community.cireson.com/discussion/2168/other-options-for-filtering-data-on-sql-widgets/p1
Vote it up and it just might happen

Geoff_Ross

Hi @Roland_Kind

This is possible since v8 of the Cireson Portal.

Use a token in your SQL Query with {{token}} and then provide the value to the token by the name name (AND CASE) in the request.

So, if your Query is

SELECT * FROM WorkItem WHERE Priority = {{priority}}

then you can use

$.getJSON('/api/v3/Dashboard/GetDashboardDataById', 
   { 
   "dateFilterType": "NoFilter",
   "queryId": "1FE7DECA-XXXX-YYYY-ZZZZ-1F7EF7604D62"   
   "priority": 1   
   },

to return all P1 IRs.

You can use multiple tokens or even the same token more than once.

SELECT * FROM WorkItem
WHERE Priority = {{priority}}
AND (
    AssignedUser like '%{{Assigned}}%'
    OR AffectedUser like '%{{Assigned}}%'
)

Good luck, happy interfacing!

Geoff

Brett_Moffett

At the moment you are limited to just this one and this one is only available on work items.
In future versions we will hopefully add more.

If there is one in particular that you would like I encourage you to create a feature request and get it voted up.

Brian_Wiest

Options based on other enumerations

support group
resolution category
classification
priority
impact
to just run off a few

Chris_Stelzer

Totally agree with Brian here, these are the most common types of slicing my users want to do within cube based reports in Excel. Would make total sense to introduce these other options to assist with this.

Brett_Moffett

I've raised this as a feature request....
https://community.cireson.com/discussion/2168/other-options-for-filtering-data-on-sql-widgets/p1
Vote it up and it just might happen

Roland_Kind

It would be really great if there is a possibility to set additional SQL variables which can be used as a filter - similiar to @UserId or @IsScoped . e.g. define  @filter variable in the JQuery call like as 

$.getJSON('/api/v3/Dashboard/GetDashboardDataById',
   {
   "dateFilterType": "NoFilter",
   "queryId": "1FE7DECA-XXXX-YYYY-ZZZZ-1F7EF7604D62"   
   "customerVariables":   { list of varname: varvalue}   
   },

or

Changing  the "/Dashboard/GetDashboardQueryData" API call from "get" to "post" allowing that a comprehensive SQL query can be submitted - in this case you can define whatever variable you need

Geoff_Ross

Hi @Roland_Kind

This is possible since v8 of the Cireson Portal.

Use a token in your SQL Query with {{token}} and then provide the value to the token by the name name (AND CASE) in the request.

So, if your Query is

SELECT * FROM WorkItem WHERE Priority = {{priority}}

then you can use

$.getJSON('/api/v3/Dashboard/GetDashboardDataById', 
   { 
   "dateFilterType": "NoFilter",
   "queryId": "1FE7DECA-XXXX-YYYY-ZZZZ-1F7EF7604D62"   
   "priority": 1   
   },

to return all P1 IRs.

You can use multiple tokens or even the same token more than once.

SELECT * FROM WorkItem
WHERE Priority = {{priority}}
AND (
    AssignedUser like '%{{Assigned}}%'
    OR AffectedUser like '%{{Assigned}}%'
)

Good luck, happy interfacing!

Geoff

Roland_Kind

@Geoff_Ross thats great :-) thanks a lot for the update !

Stephane_Bouillon

Add this in the UI for the Dashboard widgets, with the option to have it visible or not. For example if I want to filter by "This Month" but I do not want the user to change it, I'd set it in the widget properties and keep it invisible. Same applies to the other requested slicers. This would make it easy to have a generic SQL Query that is then used with different filters on different Dashboard pages.

Zackary_Slates

@Geoff_Ross
I love the Idea, but am new an not familiar on where exactly to put the javascript. Can you explain that part a little more in detail?
Thank you in advance.
Zack

Jason_Meyer

Following the thread as we would like to do this for creating custom Analytics and Dashboard pages, or have the ability for users to put filters on them like other views.