Calendar Colors in the Cireson Portal

steve_tuel

Deep Dive Instructions are here: cireson.com/blog/calendar-colors-in-the-cireson-portal.

Peter_Settle

Excellent, just what I have been looking for.

Adrian_Paech

Hrmm for some reason I just get a blank page when I try and load this in v7.0.3 of the portal.

Is there a trick to get this working?

Regards,

Adrian

steve_tuel

There shouldn't be.  Sounds like maybe the files aren't loading correctly.  Do you get any errors when you go into Developer Tools (F12) and reload the page?  Can you post your files here and I'll check it out?

Adrian_Paech

I got it working in the end..

It was just a typo on my end.

Thanks again!

Adrian

Matthew_Cu

I am looking to add a few additions to this calendar. 

1) I want to set my color for the events based on the Category for the Change Request. I can't seem to find this information in the service management DB. The Service Manager DB holds this information in the [dbo].[MT_System$WorkItem$ChangeRequest] - Category_7B1892FE_108AEC85_064D_7815C2DFC442 field.

I noticed the Workitem table includes a category field however this is linked to the Area field on the portal (not what I want).

I attempted to use temporary tables and views however the API doesn't seem to like using these SQL abilities. I was able to make create new tables and update them with a trigger however this stops both Cireson and SCSM2012 from updating an existing CR.

2) I also want to include MA's which are a child for a change request even if they are also a child within a Parallel Activity at the same time. The WorkItem table includes a ParentWI field but for CR linked MA's this is empty. 

Would you have any ideas how I can achieve both of the above?

Regards,

Matt

Adrian_Paech

MAs would be doable, but tricky.
We had a requirement to identify what step in the Change Process the CR was in, e,g,: Pending QA, Pending Approval, Pending CAB, Pending Implementation etc.
To do this, we created additional statuses on the parent CR, and whenever the relevant MA went in progress, we would automatically update the CR with the corresponding status.
We then color coded all of our CR's based on the current status (rather then the category).
This prevented the need to display the related MA's in the callendar.

Regarding the ParentWI field. THis is only used for parent / child relationships with incidents, where you have a parent incident and related child incidents. It is not used for MAs / RAs etc.
In this scenario, we have created a separate table which is populated when Work Items are created, and Work Items are updated, which contains a bunch of additional fields which we oftern need to 'efficiently' reference without SQL Joins to be used for dashboards, reports, calendars etc. Just one of the fields in this table is the GUID of the Parent Work Item that the MA is related to.

Regarding the category field.. What do you currently use this field for, Minor / Major etc.?

Peter_Settle

@Adrian_Paech Am really interested in the CR progress displaying in the calendar. I know that we would need to amend the Change Status list to include the extra options i.e. "Pending Implementation" etc.

However how are you getting the status to change on the CR?

Adrian_Paech

We trigger workflows to run whenever an MA or RA status is changed to "in progress". Its at this point we run a powershell script which does a bunch of stuff, but one of the things it does is check to see if the MA / RA is part of a CR, and if it is, looks at the title of the MA / RA, and based the title, sets the CR status accordingly. 
We have consistent titles in our MA / RA for CR's so we dont ever get any issues with this.
I guess if someone wanted to mess with the system, they could change all of the titles of the up-and-coming MA's / RA's within a CR before they go in progress, and this would stop the process from working correctly, but we have not had an issue up until now.
Hope this helps.

Eric_Krasner

I have downloaded the files, followed the article and instructions and yet i get a blank calendar and cannot figure out why.  I run the sql query in mgmt studio and get the desired results. Yet after encoding and adding to the html file, i get nothing.  Any ideas?

steve_tuel

Can you validate if the API call is returning results?  Go into Developer Tools from a browser (F12), click the Network tab, and then navigate to the Calendar Page.  Find the "GetDashboardDataById?..." call and look to see if anything was returned in the response.

Justin_Clarke

Hi Steve,

Fantastic solution for change calendars. I am testing this and have come across a strange issue where if i set a risk on a change i get many duplicates on the calendar. Have you seen this before or have i missed something?



Thanks for your help

Marek_Lefek

This can be if you are using more languages than only English. Try run the SQL (var query) from file CustomCalendar.html, line 20 and it wil give you double entries this is it.

If you can display calendar only i one language, you need to add new line with condition for localization code:

<br>AND RiskDS.LocaleID ='PLK'

Just can copy encoded below to url. Only change PLK for your language code.

%0AAND%20RiskDS.LocaleID%20%3D%27PLK%27

If you use multiple languages this script need to be change to be able to  use @LanguageCode tag. I try it now, but it won't works.

Justin_Clarke

Fantastic work Marek, this was indeed my issue. Calendar working well. Much appreciated

Marek_Lefek

Thats great!

Brad_McKenna

Anyway or anyone had any luck in querying a different data source instead of ServiceManagement?
The reason being in that we would like to create another calendar using this easy approach that will sit along our existing calendars,  but contain data that we do not want to create tickets for. (Think a calendar of events/changes planned for the year, where we do not desire to create tickets ahead of time to skew our metrics)

steve_tuel

You should be able to create a new Dashboard Datasource through Admin Settings -> Data Source Settings in the Portal.  Then you can create a Dashboard Query that points to this Datasource through Dashboard Query Settings.  What this will do is create a new Dashboard Query that has an Id that can be used in an API call.  The GetDashboardDataById API call takes in an Id as the parameter.  You can find that Id by pulling up the Dashboard Query Settings Grid and then tell it to show the Id column (it is hidden by default).  The Calendar customization would look very similar but you would update the API calls to use this instead. 

steve_tuel

The other thing to validate is that your Portal Service Account has permissions to run that query on the new Datasource.

Brad_McKenna

Fantastic! I was reading over the API documentation before creating this post and Data Source/Query data with the hopes it would be this easy. Elated to hear that it actually is this easy, thank you @Steve_tuel, I appreciate the quick feedback and guidance. Much appreciated!

Brad_McKenna

Need some guidance on my customization if possible. I am able to validate the api call/array is being passed as I am dumping the array to the Dev console.  Although the array is being obtained, the Calendar remains blank/empty. The ID column was added to the SQL table to use for ID within the kendoui scheduler config.

Attached is the code we are using where we changed the code initially provided.
The array below shows data we made up to try out the concept.

Any help is appreciated.

steve_tuel

I'm kind of stumped on this one.  Everything looks correct in your HTML file.  The only different is that ID column and the fact that it isn't a string.  I'm not sure how that could/would effect it but that is the only difference I can see.

Dean_Lintern

Works fantastic with a few tweaks here and there for our environment. Thank you!

I have an issue with the click on a CR to open it. Its doing nothing, any ideas? (I have not changed any of the original code on this bit)

"One quick note, I added some additional logic that allows you to click on the CR and it will open it in a new window. Line 57-66 adds a click event to the scheduler that when clicked will call the openWI function (Line 67-69).

"

Dean_Lintern

We have resolved this with a slight change to the var id = title.split(": ");

steve_tuel

I just wanted to post an updated version of this that uses the "GetDashboardDataById" API call instead of the legacy "GetDashboardQueryData" API. The legacy API runs a SQL Query directly in the call which is seen as a Security Vulnerability. There is a setting in the Setting Items called "EnableDashboardLegacyAPI" that controls the use of the API. If your company requires this to be turned off or you just want to use a more secured approach, follow the steps below:

1. Extract and Copy the "ActiveChangeCalendar" folder into CustomSpace.

2. Run the Active Changes-INJECT.sql Query against your ServiceManagement Database.

3. Copy the "ActiveChangeCalendar.js" file into CustomSpace\views.

4. Copy the "ActiveChangeCalendarVP.js" file into CustomSpace\views\viewpanels.

5. Navigate to "http://yourportalserver/view/ActiveChangeCalendar"

ActiveChangeCalendar.zip

This approach will inject a SQL Dashboard Query in your ServiceManagement Database that contains a unique GUID. It then uses the newer API call which only uses the unique GUID.

Roelof_Luinge

Hi Steve,

In updated the ChangeCalendar. But now I see all the changes twice.

I made this modification in the ActivChangeCalendar.html:

any Ideas?

Regards,

Roelof

steve_tuel

The modification you made shouldn't have an effect on how often it shows the Change on the calendar. That just controls what it does when you click on it. If there was an issue with your modification above, you would just have issue when you try to click to open a Change Request.

When you run the SQL Query below against your ServiceManagement DB do you see these same duplicate records?

SELECT DisplayName, WorkItemId, RiskDS.DisplayString as Risk, ScheduledStartDate, ScheduledEndDate

FROM dbo.WorkItem WI




LEFT OUTER JOIN

dbo.DisplayString RiskDS ON

RiskDS.ElementID = WI.Risk




WHERE ClassId='e6c9cf6e-d7fe-1b5d-216c-c3f5d2c7670c' --CHANGE REQUEST

AND StatusId='6D6C64DD-07AC-AAF5-F812-6A7CCEB5154D' --IN PROGRESS

Roelof_Luinge

Hi Steve,

Yes, when I run the Query I also see duplicate records.

Risk in Englisch and Dutch language , so I have to filter that out.

Roelof_Luinge

Solved.

DELETE FROM DataSource

WHERE Id = 'C88C0530-AB3A-46BA-B87C-484FE7A35996' 

INSERT INTO DataSource

VALUES ('C88C0530-AB3A-46BA-B87C-484FE7A35996', 'Custom Calendar - Active Changes', NULL, 'SELECT DisplayName, WorkItemId, RiskDS.DisplayString as Risk, ScheduledStartDate, ScheduledEndDate

FROM dbo.WorkItem WI

LEFT OUTER JOIN

dbo.DisplayString RiskDS ON

RiskDS.ElementID = WI.Risk

WHERE ClassId=''e6c9cf6e-d7fe-1b5d-216c-c3f5d2c7670c''

AND StatusId=''6D6C64DD-07AC-AAF5-F812-6A7CCEB5154D''

AND LocaleID = ''ENU'' ', 1)

Roelof_Luinge

The Proces managers in our organization also wanted to see the Completed changes.

So I changed the Query. When the status is Completed, I write "Completed" in the Risk Table.

DELETE FROM DataSource

WHERE Id = 'C88C0530-AB3A-46BA-B87C-484FE7A35996' 

INSERT INTO DataSource

VALUES ('C88C0530-AB3A-46BA-B87C-484FE7A35996', 'Custom Calendar - Active Changes', NULL, '

SELECT DisplayName, 

WorkItemId, 

case when StatusId=''68277330-A0D3-CFDD-298D-D5C31D1D126F'' then ''Completed'' else RiskDS.DisplayString end as Risk, 

ScheduledStartDate, ScheduledEndDate, statusid

FROM dbo.WorkItem WI 

LEFT OUTER JOIN

dbo.DisplayString RiskDS ON

RiskDS.ElementID = WI.Risk

WHERE ClassId=''e6c9cf6e-d7fe-1b5d-216c-c3f5d2c7670c''

AND (StatusId=''6D6C64DD-07AC-AAF5-F812-6A7CCEB5154D'' OR StatusId=''68277330-A0D3-CFDD-298D-D5C31D1D126F'')

AND LocaleID = ''ENU''', 1)

In the ActiveChangeCalender.html we added:

<div id ="calendartypes">

<li><span class="k-scheduler-mark" style="background-color:limegreen"></span>Low</li>

<li><span class="k-scheduler-mark" style="background-color:orange"></span>Medium</li>

<li><span class="k-scheduler-mark" style="background-color:red"></span>High</li>

<li><span class="k-scheduler-mark" style="background-color:gray"></span>Completed</li>

resources: [

{

field: "Risk",

title: "Risk",

dataSource: [

{ text: "Low", value: "Low", color: "limegreen"},

{ text: "Medium", value: "Medium", color: "orange" },

{ text: "High", value: "High", color: "red" },

{ text: "Completed", value: "Completed", color: "gray" },

Regards Roelof

Roelof_Luinge

Hi,

Our portal Change Calendar suddenly stopped working. I can see the call is getting back all the workitems, but the workitems are not displayed in the grit. I also see an error in the debug console:

1: why looking in /platform/views ? Can't find this in the js

2: 454 workitems do I get back

3: is this the cause the workitems are not shown in the grit?

Thanks for responding

Regards Roelof

steve_tuel

If you open one of those 454 Work Items, what do the date fields look like?