Work items sort/group by Customer/Organization

Raffael_Jenzer

Hi

We're using the Cireson Portal with different Customers (Organizations). Our Analysts would like to sort/group work items by Customer/Organization.

Does anyone has a solution for this?

Thanks in advance,
Raffael

Geoff_Ross

Hi Raffael,

If you create a custom page with a SQL grid widget you can specify the SQL query to bring the data in. Just ensure to include whatever fields you are using for Customer / Organisation.
Then your analysts can filter and sort on this field.

Hope this helps, let me know if you need more info.

Geoff

Tom_Hendricks

If you are using views, there are a couple approaches that come to mind:

• A view for each organization, showing all work items
• A view for all work items, with the organization shown as a property to be filtered.

You could then use the View Builder to "promote" the views to the web portal.

In either approach, you would need to create a type projection (a.k.a. "Combination Class") in a management pack which represents the relationship(s) among two or more objects.  It is based on one class, then the other related class(es) is/are nested beneath it.  The relationships may also need to be created if they do not already exist (out of box or via your other customization).

I am not able to elaborate at the moment, but if this is something you are not familiar with I can add some how-to information later, if other members of the community do not beat me to it.  Creating type projections can seem intimidating at first, but is actually fairly straightforward once you get used to it and see them in action.

Geoff_Ross

Hi Raffael,

If you create a custom page with a SQL grid widget you can specify the SQL query to bring the data in. Just ensure to include whatever fields you are using for Customer / Organisation.
Then your analysts can filter and sort on this field.

Hope this helps, let me know if you need more info.

Geoff

Tom_Hendricks

If you are using views, there are a couple approaches that come to mind:

• A view for each organization, showing all work items
• A view for all work items, with the organization shown as a property to be filtered.

You could then use the View Builder to "promote" the views to the web portal.

In either approach, you would need to create a type projection (a.k.a. "Combination Class") in a management pack which represents the relationship(s) among two or more objects.  It is based on one class, then the other related class(es) is/are nested beneath it.  The relationships may also need to be created if they do not already exist (out of box or via your other customization).

I am not able to elaborate at the moment, but if this is something you are not familiar with I can add some how-to information later, if other members of the community do not beat me to it.  Creating type projections can seem intimidating at first, but is actually fairly straightforward once you get used to it and see them in action.

David_Allen

@Geoff_Ross Hijacking this thread a bit, but could we use the same SQL grid widget to display config item data?

Geoff_Ross

@David_Allen
Yes, I don't see why not. But the ServiceManagment DB has very little data on Config Items so you might need to reach into the ServiceManager DB to get the right data.

David_Allen

The SQL Table Widget only allows you to enter a query to SELECT content from the ServiceManagement database.  Please could you advise how I can use the widget to query the ServiceManager database?

Geoff_Ross

3 Ways:
1. Await the Cireson Data Analytics App which will allow you to provide a connection string for each SQL widget.
2. Use a view, which quiries another database, saved in ServiceManagment and call that.
3. Use the full 'Path' to the table in the SQL query 
eg

SELECT * FROM [ServiceManager].[dbo].[MT_Microsoft$SystemCenter$BusinessService]

Geoff

David_Allen

Thanks Geoff...

1. We can't wait (I'm sure you're surprised to hear that)

2. Like this idea, and asked a question around this just this morning (https://community.cireson.com/discussion/1474/adding-additional-tables-to-servicemanagement-databsase)

3.  The text when using the widget explicitly says "Use a SQL query to SELECT content from the ServiceManagement database.  Maybe that should be reworded if other sources can be queried?

Thanks again!!

Geoff_Ross

1. Great, ETA is of end of the year.
2. I saw that but will leave for Dev's to answer. I don't know if its supported but Views don't get removed during upgrade so tables most likely won't. Maybe with solution 3 you can just create a new DB anyway.
3. I don't know how intentional this 'feature' is. Its SQL right, so you can run a Query against one database to bring data from another as long as you specify. The hint maybe should read 'This SQL will run against ServiceManagement'

David_Allen

Testing out option 3 and I can successfully retrieve Service config items from the ServiceManager database and they are displayed nicely in a table.  The issue I have is that the first two columns get the theme shading, and each row is hyperlinked (as attached).  The issue with the hyperlink is that it just takes you to the Search page with no information.

I appreciate that the search won't return anything as it using data it knows nothing about.  So, would it be possible to remove the hyperlinking altogether?  If we can get rid of the shading over the first two columns too, that would be great, but is less of an issue?

Geoff_Ross

Probably with some custom JS targeting that page and grabbing the grid and playing with the Styling/ HTML.

Geoff_Ross

Totally off topic in this thread now.

David_Allen

It really has... posted as a new thread here, https://community.cireson.com/discussion/1486/sql-widget-shading-and-hyperlinks, if you would maybe like to provide some hints on the custom JS targeting?