Analytics - Work Item Count Widget Questions

Karen_Bruster1

Good afternoon,

After upgrading our Portal to 8.1.2.2012, I started working down my "To Do List". I was playing around creating a few simple analytic pages and encountered this issue.

When creating a Service Request Work Item Count Widget using a saved search I get this on the display page.

{{vm.count}} (see attached search and display)

I have deleted both the widget and the search several times and it still is showing the same {{vm.count}}. I am able to use basically the same search and widget with Incidents no problems.

Has anyone got an idea what is causing this?

Brian_Wiest

The portal is a little funny when building new queries. 
If you add the code via the admin panel then go to add it to a page it will not show into the widget setup until you restart your browser session. (and I mean close all portal sessions)
After restarting the browser you will see the offering in the setup drop down. 

I prefer to add in queries when in the widget setup wizard but note it will remain blank in the queries window but will still save. 

Brian_Wiest

While the Widgets based on saved searches is a nice function, I personally find them to be a little pain. As you have to make sure the saved search is promoted to a location in the portal that is scoped to the audience that has access to the analytics page. (And if you update the search it moves back into your own scope so you have to promote it again)
I find the SQL widgets to be much more reliable for this functions.
This code should get you started.

Select Count(wi.id)

FROM WorkItem wi

LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 

LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID 

WHERE ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989'

AND DATEDIFF(dd,Created, GETDATE()) = 1

And dssupport.DisplayString in ('SupportGroup1', 'SupportGroup2')

Karen_Bruster1

@Brian_Wiest Do you have to save the SQL code some where so you can have it show up in the Query Name drop down? I tried creating new and it is not showing in the field to select.

Brian_Wiest

The portal is a little funny when building new queries. 
If you add the code via the admin panel then go to add it to a page it will not show into the widget setup until you restart your browser session. (and I mean close all portal sessions)
After restarting the browser you will see the offering in the setup drop down. 

I prefer to add in queries when in the widget setup wizard but note it will remain blank in the queries window but will still save. 

Karen_Bruster1

@brian_Wiest Thank you that worked and it looks great. Its just the start they want a bunch of stuff so I will probably be back......

Karen_Bruster1

@Brian_Wiest I have another question. Can you use wild cards in the support group names?

I actually have like a gazillion for the Software people, but they all start the same (SDT3)

And dssupport.DisplayString in ('SupportGroup1', 'SupportGroup2')

Brian_Wiest

If it works in SQL studio then I should work in the portal. 
Since I am out of the office for the week cannot review the library of queries I have designed.
It should be either
And
dssupport.DisplayString in ('%SupportGroup1', '%SupportGroup2')
dssupport.DisplayString like ('%SupportGroup1', '%SupportGroup2')

Karen_Bruster1

@Brian_Wiest Thanks have a good Holiday, I will give this a try and see what happens.

I will eventually learn enough SQL to work my way out of a bag.....