Portal - Change Calendar

IT Monkey

Custom Calendars are possible using the dashboards calendar definition by following the below KB articles

https://support.cireson.com/KnowledgeBase/View/1129#/ 
https://support.cireson.com/KnowledgeBase/View/1106#/

This is an example of how to create a Change Calendar for your change managers in the Cireson Portal 





To install execute the below query against your ServiceManagement database:

--use a GUID generator to create some new guids for your view panel and navigation node.  NOTE: All guids must be completely lower case.
--you can run this query three times in SQL to generate the guids you need.
-- SELECT LOWER(CONVERT(nvarchar(39),NEWID()))
DECLARE @NavNodeGuid nvarchar(255)       = '4f962bc3-61ba-4b95-a311-fa00b2c61e30';
DECLARE @ViewPanelGuid nvarchar(255)     = '6de60ec7-9b5d-4aad-9da9-3c77b5ac0e6f';
DECLARE @DataSourceGuid nvarchar(255)    = 'f177dd61-5cd9-4f78-95c9-f8987a69edca';

--this query selects all the active Change Requests in the WorkItem table
DECLARE @Query nvarchar(max) = 
'DECLARE @SystemWorkItemChangeRequest uniqueidentifier = ''e6c9cf6e-d7fe-1b5d-216c-c3f5d2c7670c'';
 DECLARE @ChangeStatus_Cancelled uniqueidentifier = ''877defb6-0d21-7d19-89d5-a1107d621270'';
 DECLARE @ChangeStatus_Failed uniqueidentifier = ''85f00ead-2603-6c68-dfec-531c83bf900f'';
 DECLARE @ChangeStatus_Completed uniqueidentifier = ''68277330-a0d3-cfdd-298d-d5c31d1d126f'';
 DECLARE @ChangeStatus_Closed uniqueidentifier = ''f228d50b-2b5a-010f-b1a4-5c7d95703a9b'';
 SELECT w.Id, w.WorkItemId, w.WorkItemId+'' - ''+w.Title as Title, w.Description, w.ScheduledStartDate, w.ScheduledEndDate
   FROM [WorkItem] w
  WHERE w.ClassId = @SystemWorkItemChangeRequest AND w.StatusId NOT IN (@ChangeStatus_Cancelled, @ChangeStatus_Failed, @ChangeStatus_Completed, @ChangeStatus_Closed)';
--this is the text which will be shown in the portal menu
DECLARE @MenuTitle nvarchar(255)         = 'Change Request Calendar';
DECLARE @Locale nvarchar(3)              = 'ENU';                                                             

--this just deletes it if it already exists so that you can iteratively tweak it and recreate it easily
DELETE FROM DisplayString WHERE ElementID = @NavNodeGuid and LocaleID = @Locale;
DELETE FROM NavigationNode WHERE Id = @NavNodeGuid;
DELETE FROM ViewPanel WHERE id = @ViewPanelGuid;
DELETE FROM DataSource WHERE id = @DataSourceGuid;

--this creates the query which will be used as the data source for the calendar
INSERT INTO dbo.[DataSource] (Id, ConnectionString, Query)
VALUES ( @DataSourceGuid, NULL, @Query);

--this creates the navigation node display string
INSERT INTO [dbo].[DisplayString]  (ElementID, LocaleID, DisplayString) 
VALUES (@NavNodeGuid, @Locale, @MenuTitle);

--Create the navigation node.
INSERT INTO NavigationNode(Id, [Definition], Ordinal, Sealed, IsPublic, IsVisible, LicenseRequired, IconClass, ParentId, Location)
VALUES (
@NavNodeGuid,
'{"Id":"' + @NavNodeGuid+ '","layoutType":"full","view":{"header":{"title":"' + @MenuTitle + '","subTitle":""},"body":{"content":{"rows":[{"columns":[{"ColSpan":12,"type":"viewPanel","ViewPanelId":"' + @ViewPanelGuid + '"}]}]}}}}', 
2,0,0,0,'DASHBOARD', NULL, 'E99B6A93-E536-442E-839F-67A99B439297','6CFC4D00-D8EB-4F51-B63D-F776576CF25A');

--Create the view panel
INSERT INTO ViewPanel(Id, [Definition], TypeId)
VALUES (
@ViewPanelGuid,
'{"Id":"' + @ViewPanelGuid + '","type":"calendar","content":{"queryId":"' + @DataSourceGuid + '", "views":["day","week","month","agenda"],"id":"WorkItemId","title":"Title","description":"Description","startDate":"ScheduledStartDate","endDate":"ScheduledEndDate"}}',
'calendar'
);

Then enable the navigation node in the navigation settings of the portal and assign the the appropriate AD group:

Billy_Wilson

worked great! Thanks!

Candice_Yeudall

That is great that you can do this, but is all this custom coding going to stay around for the next version?

AJ_Wittenbrink

How do I show all changes, not just my own?

AJ_Wittenbrink

Ignore my comment, I was looking at my past failed attempts......

nothing to see here...

Joe_Burrows

Candice_Yeudall said:

That is great that you can do this, but is all this custom coding going to stay around for the next version?

It should do unless there is a serious change to the portal in a future version, our out of box dashboards are also configured this way.

Leigh_Kilday

This is very cool. I've got two questions.

1. Is there any way to pass a parameter to the query? I want to allow people to filter on Change Type and WI type values from an on-screen control (probably not possible in v5 anyway).
2. In v6, can the calendar view be given conditional formatting? I have added RR to the above solution and it would be easier to identify RR/CR if we could alter the colours of the Gantt bars.

Chris_Jordan

Is this broken already with 6.0.1? My calendar is empty.

Joe_Burrows

Chris_Jordan said:

Is this broken already with 6.0.1? My calendar is empty.

Hi Chris, its not broken and still works

I checked in my lab post upgrade after seeing this, im guessing the hot fix installer is removing the customization for some reason. I had to re-run the above query to get it back as a navigation node.

Let me open this as an incident on your behalf for the team to investigate. IR53815

Cheers
Joe

Chris_Jordan

I also had to re-run it, to get the nav node back.

Not showing any CRs on the calendar however.

Joe_Burrows

Chris_Jordan said:

I also had to re-run it, to get the nav node back.

Not showing any CRs on the calendar however.

Showing fine for me, check you have a scheduled start and end date on your CR's.

Chris_Jordan

Thanks for confirming that, got it working. I must have had a typo if the select query. (because I show completed and closed CRs as well)

carrie_medine

Hello,
The behavior of the navigation node being removed upon upgrade is by design as you have configured the navigation node with the sealed column = 1. The Sealed column is used for system defaults and should always be set to 0 for custom navigation nodes. If you modify the Sealed column on your navigation node to 0 it will not be removed upon upgrade.

I did update our KB article with this information as well as it had an incorrect example that populated Sealed as 1. @Joe_Burrows please update the example above.

Thanks, Carrie

Joe_Burrows

carrie_medine said:

Hello,
The behavior of the navigation node being removed upon upgrade is by design as you have configured the navigation node with the sealed column = 1. The Sealed column is used for system defaults and should always be set to 0 for custom navigation nodes. If you modify the Sealed column on your navigation node to 0 it will not be removed upon upgrade.

I did update our KB article with this information as well as it had an incorrect example that populated Sealed as 1. @Joe_Burrows please update the example above.

Thanks, Carrie

Thanks Carrie for confirming this, have updated

Chris_Keander

Does this require a full-blown Dashboard license?

Joe_Burrows

Chris_Keander said:

Does this require a full-blown Dashboard license?

Hey Chris - a dashboard key is required for any custom dashboard and the dashboard designer functionality.

Chris_Keander

OK.  We are in communication with Cireson Sales now about changing our current licensing model - so hopefully this will be coming soon for us

Aaron_Smith

I'd love to know if there would be away to add Color coding based on what activity a Change is at in the workflow. For instance. We would want CRs to show Red if they haven't arrived at the CAB approval activity and Green if they have. Thoughts?

Joe_Burrows

Aaron_Smith said:

I'd love to know if there would be away to add Color coding based on what activity a Change is at in the workflow. For instance. We would want CRs to show Red if they haven't arrived at the CAB approval activity and Green if they have. Thoughts?

Im not aware of any way to color code, I agree though and think the ability to color code any of the dashboards would be very helpful

Thomas_Stromberg

Is there some kind of delay when using this calandar? I changed the title on a Change and the title was not updated in the calander to the new one. Perhaps it will take some time? But i don't see why it should take some time. 

Rudy_Michiels

Is there a way to introduce the 'My Own' concept? In other words, how can I get the current portal user in the TSQL?

AJ_Wittenbrink

Joe_Burrows said:

Aaron_Smith said:

I'd love to know if there would be away to add Color coding based on what activity a Change is at in the workflow. For instance. We would want CRs to show Red if they haven't arrived at the CAB approval activity and Green if they have. Thoughts?

Im not aware of any way to color code, I agree though and think the ability to color code any of the dashboards would be very helpful

There is with the Kendo UI Scheduler.  In fact i wish the Timeline view was available.

http://demos.telerik.com/kendo-ui/scheduler/index

Specifically under the resources area 
 resources: [

            {
                field: "ownerId",
                title: "Owner",
                dataSource: [
                    { text: "Alex", value: 1, color: "#f8a398" },
                    { text: "Bob", value: 2, color: "#51a0ed" },
                    { text: "Charlie", value: 3, color: "#56ca85" }
                ]
            }

I am not a good programmer though, so no idea on how to implement it

Magnus_Lundgren1

Is there anyway to randomize the color scheme on the boxes? As in the console version of the change calendar

Magnus_Lundgren1

i changed the default to week view, but how would i change so that the default is to only show business hours?

David_Darling

Hi, since applying 6.0.3 (we think) we're running into some funny behavior with the Change Calendar's "Agenda" view.  If we select a day in the "Month" view that contains Change Requests, and then click the "Agenda" button to move to the Agenda view, the Agenda view opens, but so do the Change Requests from that date in new tabs.  Any idea what's happening?  Do you also have this issue on 6.0.3?

Dennis_de_Jager

I don't have this behaviour on 6.0.3, I have however created an Request Offering that was 1 big hyperlink when I used an <href a> tag in the Instructions Box without closing it. It could be something like that.

I have altered this code to make a downtime calendar, which works perfect. Since all work items have the scheduled downtime date fields, this is a great way to communicate this to your end users and or colleagues. Making sure those fields are available on the portal forms when you want to apply this calendar.

This code isn't perfect, since the SQL query allows for all kinds of Work Items, but I have only defined the statuses for Change and Service Request. I don't know if those GUIDS will work in your environment as well, so please check if this works for you:

<div>DECLARE @NavNodeGuid nvarchar(255) &nbsp; &nbsp; &nbsp; = 'b2b3fa54-1000-4cff-a488-40457aa82395';</div><div>DECLARE @ViewPanelGuid nvarchar(255) &nbsp; &nbsp; = 'aa30bc75-1000-47c1-849c-b6667efb477e';</div><div>DECLARE @DataSourceGuid nvarchar(255) &nbsp; &nbsp;= '84c40afb-1000-4d7b-bdcd-7b252f21eade';</div><div><br></div><div>--this query selects all the active Change Requests in the WorkItem table</div><div>DECLARE @Query nvarchar(max) =&nbsp;</div><div>'DECLARE @SystemWorkItemChangeRequest uniqueidentifier = ''e6c9cf6e-d7fe-1b5d-216c-c3f5d2c7670c'';</div><div>&nbsp;DECLARE @ChangeStatus_Cancelled uniqueidentifier = ''877defb6-0d21-7d19-89d5-a1107d621270'';</div><div>&nbsp;DECLARE @ChangeStatus_Failed uniqueidentifier = ''85f00ead-2603-6c68-dfec-531c83bf900f'';</div><div>&nbsp;DECLARE @ChangeStatus_Completed uniqueidentifier = ''68277330-a0d3-cfdd-298d-d5c31d1d126f'';</div><div>&nbsp;DECLARE @ChangeStatus_Closed uniqueidentifier = ''f228d50b-2b5a-010f-b1a4-5c7d95703a9b'';</div><div>&nbsp;DECLARE @SystemWorkItemServiceRequest uniqueidentifier = ''04B69835-6343-4DE2-4B19-6BE08C612989'';</div><div>&nbsp;DECLARE @ServiceStatus_Cancelled uniqueidentifier = ''674e87e4-a58e-eab0-9a05-b48881de784c'';</div><div>&nbsp;DECLARE @ServiceStatus_Failed uniqueidentifier = ''21DBFCB4-05F3-FCC0-A58E-A9C48CDE3B0E'';</div><div>&nbsp;DECLARE @ServiceStatus_Completed uniqueidentifier = ''b026fdfd-89bd-490b-e1fd-a599c78d440f'';</div><div>&nbsp;DECLARE @ServiceStatus_Closed uniqueidentifier = ''c7b65747-f99e-c108-1e17-3c1062138fc4'';</div><div><br></div><div>&nbsp;SELECT w.Id, w.WorkItemId, w.WorkItemId+'' - ''+w.Title+'' - ''+w.AssignedUser as Title, w.Description, w.ScheduledDowntimeStartDate, w.ScheduledDowntimeEndDate</div><div>&nbsp; &nbsp;FROM [WorkItem] w</div><div>&nbsp; WHERE w.StatusId NOT IN (@ChangeStatus_Cancelled, @ChangeStatus_Failed, @ChangeStatus_Completed, @ChangeStatus_Closed,@ServiceStatus_Cancelled, @ServiceStatus_Failed, @ServiceStatus_Completed, @ServiceStatus_Closed)';</div><div>&nbsp; --this is the text which will be shown in the portal menu</div><div>DECLARE @MenuTitle nvarchar(255) &nbsp; &nbsp; &nbsp; &nbsp; = 'Maintenance Calendar';</div><div>DECLARE @Locale nvarchar(3) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= 'ENU'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</div><div><br></div><div>--this just deletes it if it already exists so that you can iteratively tweak it and recreate it easily</div><div>DELETE FROM DisplayString WHERE ElementID = @NavNodeGuid and LocaleID = @Locale;</div><div>DELETE FROM NavigationNode WHERE Id = @NavNodeGuid;</div><div>DELETE FROM ViewPanel WHERE id = @ViewPanelGuid;</div><div>DELETE FROM DataSource WHERE id = @DataSourceGuid;</div><div><br></div><div>--this creates the query which will be used as the data source for the calendar</div><div>INSERT INTO dbo.[DataSource] (Id, ConnectionString, Query)</div><div>VALUES ( @DataSourceGuid, NULL, @Query);</div><div><br></div><div>--this creates the navigation node display string</div><div>INSERT INTO [dbo].[DisplayString] &nbsp;(ElementID, LocaleID, DisplayString)&nbsp;</div><div>VALUES (@NavNodeGuid, @Locale, @MenuTitle);</div><div><br></div><div>--Create the navigation node.</div><div>INSERT INTO NavigationNode(Id, [Definition], Ordinal, Sealed, IsPublic, IsVisible, LicenseRequired, IconClass, ParentId, Location)</div><div>VALUES (</div><div>@NavNodeGuid,</div><div>'{"Id":"' + @NavNodeGuid+ '","layoutType":"full","view":{"header":{"title":"' + @MenuTitle + '","subTitle":""},"body":{"content":{"rows":[{"columns":[{"ColSpan":12,"type":"viewPanel","ViewPanelId":"' + @ViewPanelGuid + '"}]}]}}}}',&nbsp;</div><div>2,0,0,0,'DASHBOARD', NULL, 'E99B6A93-E536-442E-839F-67A99B439297','6CFC4D00-D8EB-4F51-B63D-F776576CF25A');</div><div><br></div><div>--Create the view panel</div><div>INSERT INTO ViewPanel(Id, [Definition], TypeId)</div><div>VALUES (</div><div>@ViewPanelGuid,</div><div>'{"Id":"' + @ViewPanelGuid + '","type":"calendar","content":{"queryId":"' + @DataSourceGuid + '", "views":["day","week","month","agenda"],"id":"WorkItemId","title":"Title","description":"Description","startDate":"ScheduledDowntimeStartDate","endDate":"ScheduledDowntimeEndDate"}}',</div><div>'calendar'</div><div>);</div>

Agenda view looks like this on mobile:
And as you can see, this includes the Assigned User in the Title, when there is no User Assigned, the Work Item does not appear on the Calendar. 

Dennis_de_Jager

Apologies for the Code layout, it goes in Neat and comes out like this.
It doesn't get any better when pasting it back in Notepad ++ either.

See attached txt

Eugen_Mueller

1. Is it possible to change the language for today, day, week and so on???

2. Completed CR aren't show at the calendar? Possible to Change, too?

Magnus_Lundgren1

Dont know about language but if you want to show more statuses change this line or remove it to show all.

 AND w.StatusId NOT IN (@ChangeStatus_Cancelled, @ChangeStatus_Failed, @ChangeStatus_Completed, @ChangeStatus_Closed)

Eugen_Mueller

THX, works fine.

Additionally questions:

1. How to change the colors for the different status of CR?  For example completed = green, in Progress = red .....

2. We have a calendar with all active CR (see first post). We want no additional calendar with all available status. So is it possible to have one calendar with one (for all status) or more (for each status) checkboxes to show it dynamic?

Magnus_Lundgren1

ive been looking into that with the colors aswell, but im not a dev so i didnt get very far