Query regarding search archiver in the portal

Sean_Terry

I'm know things are on the roadmap to look at searching the archiver via the portal, but in the meantime, I wondered if there was another way, so I was playing around with the query below to list items in a table.

SELECT TOP (1000) [WorkItemId]
   ,[Title]
   ,[Created]
   ,[CreatedByUser] AS 'Creator'
   ,[Status]
   ,[Priority]
   ,[Risk]
   ,[AssignedUser]
   ,[CreatedByUser]
   ,[ActualStartDate]
   ,[ActualEndDate]
 FROM [CiresonAnalyticsAcct].[dbo].[SM_WorkItem_Change]
 WHERE Status = 'Closed'

I've looked at the @createdFilter but not sure it will help, and I have been looking at tokens to try and understand them better.

I guess the perfect solution is if it could generate a search box (similar to OData) where we could put the ID in, and it searches the 1000 items and beyond. Is there any way to achieve this or is this pointing at a feature request?

Simon_Zeinhofer

One thing to add maybe: When you have lots of work items in your archive table, and you have no filter added, the searchFilter token seems to be an empty string and is still applied to the query (the LIKE '%%' condition is like select *).

So when users open this page, it might need a long time to load or stays unresponsive as there are so many results to load.

So what you can do is, that you apply e.g. an Incident ID, which is for sure not in this table and add it as defualt inside the page settings.

So everytime the page gets loaded, the default filter is set. So the site is responsive and people might set their filter immediately. When you set the new filter via the page filter widget, the default filter gets replaced and the results are shown.

@Geoff_Ross Please correct me if I am wrong :)

Simon_Zeinhofer

Just delete the "TOP (1000)" in your query and all results are shown. ;-)

Sean_Terry

@Simon_Zeinhofer Thanks. Do you know of any other way of filtering the results other than @createdFilter? Just looking for a way to reduce the results rather than trying to explain to users how to filter by the column headers. It may not be possible yet.

Simon_Zeinhofer

There is an add in you could use for that:

https://cireson.com/blog/data-analysis-page-filter-widget-add-in/

Users can write the Name o fthe incident into the search field, a description or whatsoever - whatever you want them to be able to search for. In this article you find a short manual about the usage.

We use the add in already and it works really well.

Geoff_Ross

Wow @Simon_Zeinhofer , I came here to say this and you have beaten me to it.

Its great to have such awesome advocates in our customer base. Thank you.

@Sean_Terry Please let to community know how you got on or if you need more help as you have to understand the tokens concept well to use this Add-In.

Adam_Dzyacky

Great work @Simon_Zeinhofer!

Sean_Terry

https://community.cireson.com/discussion/comment/24318#Comment_24318

Hi, yeah. I need to adjust the queries so tokens are a new thing to understand. Not there yet.

Simon_Zeinhofer

@Sean_Terry As i have the same situation with our current Archive and also with the archive from our old deprecated system, I adapted our archive search with your idea.

Here is the query:

SELECT 
       [WorkItemId]
	  ,[Title]
      ,[Status]
      ,[AffectedUser]
	  ,[Tier] as SupportGroup
	  ,[AssignedUser] as AssignedTo
,Created



 
  FROM [CiresonAnalytics].[dbo].[SM_WorkItem_Incident]




  where (WorkItemId like '%{{searchFilter}}%'
  or Title like '%{{searchFilter}}%'
  or AssignedUser like '%{{searchFilter}}%')

Now what you have to do is:

Add the page Filter widget to your site, where the dashboard with this query is and set the settings to the following:

The Parameter Display Name is just the one i chose, this is up to you. But the Parameter Name HAS TO BE the token inside the query.

Afterwards you have to save the site.

I have to add, when I saved the pageFilter and saved the site it didn't work. Because when I edited the pageFilter, the Paramater Name and Parameter Display Name were empty, so I had to edit it again. After that I saved it and now it works.

Please just tell me iif it worked or if you need help with that :)

Simon_Zeinhofer

One thing to add maybe: When you have lots of work items in your archive table, and you have no filter added, the searchFilter token seems to be an empty string and is still applied to the query (the LIKE '%%' condition is like select *).

So when users open this page, it might need a long time to load or stays unresponsive as there are so many results to load.

So what you can do is, that you apply e.g. an Incident ID, which is for sure not in this table and add it as defualt inside the page settings.

So everytime the page gets loaded, the default filter is set. So the site is responsive and people might set their filter immediately. When you set the new filter via the page filter widget, the default filter gets replaced and the results are shown.

@Geoff_Ross Please correct me if I am wrong :)

Geoff_Ross

Once again @Simon_Zeinhofer You are completely right. Great idea!

Sean_Terry

https://community.cireson.com/discussion/comment/24336#Comment_24336

Thank you for your help with this @Simon_Zeinhofer , especially with the tips for the searchFilter and Parameter Names disappearing. They would have definitely caught me out 😁

I'll have a play and see how we can use it. Thanks again!!!