IT Monkey:   Join the Cireson Community today for your chance to win $50!

Portal - Change Calendar

IT MonkeyIT Monkey O.G.
edited September 2016 in Cireson Uploads
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:


«1

Comments

  • Billy_WilsonBilly_Wilson Cireson Solution Architect Advanced IT Monkey ✭✭✭
    worked great! Thanks!
  • Candice_YeudallCandice_Yeudall Customer Advanced IT Monkey ✭✭✭
    That is great that you can do this, but is all this custom coding going to stay around for the next version?
  • AJ_WittenbrinkAJ_Wittenbrink Customer IT Monkey ✭
    How do I show all changes, not just my own?
  • AJ_WittenbrinkAJ_Wittenbrink Customer IT Monkey ✭
    Ignore my comment, I was looking at my past failed attempts......

    nothing to see here...
  • Joe_BurrowsJoe_Burrows Cireson Support Super IT Monkey ✭✭✭✭✭
    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_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭

    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_JordanChris_Jordan Customer IT Monkey ✭
    edited September 2016
    Is this broken already with 6.0.1? My calendar is empty.
  • Joe_BurrowsJoe_Burrows Cireson Support Super IT Monkey ✭✭✭✭✭
    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_JordanChris_Jordan Customer IT Monkey ✭
    I also had to re-run it, to get the nav node back.

    Not showing any CRs on the calendar however.
  • Joe_BurrowsJoe_Burrows Cireson Support Super IT Monkey ✭✭✭✭✭
    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_JordanChris_Jordan Customer IT Monkey ✭
    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_medinecarrie_medine Member Advanced IT Monkey ✭✭✭
    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_BurrowsJoe_Burrows Cireson Support Super IT Monkey ✭✭✭✭✭
    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_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    Does this require a full-blown Dashboard license?
  • Joe_BurrowsJoe_Burrows Cireson Support Super IT Monkey ✭✭✭✭✭
    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_KeanderChris_Keander Customer Advanced IT Monkey ✭✭✭
    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_SmithAaron_Smith Customer IT Monkey ✭
    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_BurrowsJoe_Burrows Cireson Support Super IT Monkey ✭✭✭✭✭
    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_StrombergThomas_Stromberg Premier Partner Advanced IT Monkey ✭✭✭
    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_MichielsRudy_Michiels Member IT Monkey ✭
    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_WittenbrinkAJ_Wittenbrink Customer IT Monkey ✭
    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_Lundgren1Magnus_Lundgren1 Customer IT Monkey ✭
    Is there anyway to randomize the color scheme on the boxes? As in the console version of the change calendar
  • Magnus_Lundgren1Magnus_Lundgren1 Customer IT Monkey ✭
    i changed the default to week view, but how would i change so that the default is to only show business hours?
  • David_DarlingDavid_Darling Customer IT Monkey ✭
    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_JagerDennis_de_Jager Customer IT Monkey ✭
    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:

    DECLARE @NavNodeGuid nvarchar(255)       = 'b2b3fa54-1000-4cff-a488-40457aa82395';DECLARE @ViewPanelGuid nvarchar(255)     = 'aa30bc75-1000-47c1-849c-b6667efb477e';DECLARE @DataSourceGuid nvarchar(255)    = '84c40afb-1000-4d7b-bdcd-7b252f21eade';
    --this query selects all the active Change Requests in the WorkItem tableDECLARE @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''; DECLARE @SystemWorkItemServiceRequest uniqueidentifier = ''04B69835-6343-4DE2-4B19-6BE08C612989''; DECLARE @ServiceStatus_Cancelled uniqueidentifier = ''674e87e4-a58e-eab0-9a05-b48881de784c''; DECLARE @ServiceStatus_Failed uniqueidentifier = ''21DBFCB4-05F3-FCC0-A58E-A9C48CDE3B0E''; DECLARE @ServiceStatus_Completed uniqueidentifier = ''b026fdfd-89bd-490b-e1fd-a599c78d440f''; DECLARE @ServiceStatus_Closed uniqueidentifier = ''c7b65747-f99e-c108-1e17-3c1062138fc4'';
     SELECT w.Id, w.WorkItemId, w.WorkItemId+'' - ''+w.Title+'' - ''+w.AssignedUser as Title, w.Description, w.ScheduledDowntimeStartDate, w.ScheduledDowntimeEndDate   FROM [WorkItem] w  WHERE w.StatusId NOT IN (@ChangeStatus_Cancelled, @ChangeStatus_Failed, @ChangeStatus_Completed, @ChangeStatus_Closed,@ServiceStatus_Cancelled, @ServiceStatus_Failed, @ServiceStatus_Completed, @ServiceStatus_Closed)';  --this is the text which will be shown in the portal menuDECLARE @MenuTitle nvarchar(255)         = 'Maintenance Calendar';DECLARE @Locale nvarchar(3)              = 'ENU';                                                             
    --this just deletes it if it already exists so that you can iteratively tweak it and recreate it easilyDELETE 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 calendarINSERT INTO dbo.[DataSource] (Id, ConnectionString, Query)VALUES ( @DataSourceGuid, NULL, @Query);
    --this creates the navigation node display stringINSERT 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 panelINSERT 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":"ScheduledDowntimeStartDate","endDate":"ScheduledDowntimeEndDate"}}','calendar');


    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_JagerDennis_de_Jager Customer IT Monkey ✭
    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_MuellerEugen_Mueller Customer IT Monkey ✭

    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_Lundgren1Magnus_Lundgren1 Customer IT Monkey ✭
    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_MuellerEugen_Mueller Customer IT Monkey ✭

    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_Lundgren1Magnus_Lundgren1 Customer IT Monkey ✭
    ive been looking into that with the colors aswell, but im not a dev so i didnt get very far :)
«1
Sign In or Register to comment.