SyteLine – Visualize data with Graphs

Recently, I was asked to create a chart to visualize report data to the user directly in SyteLine, skipping the cumborsome report fetching tools like Crystal Report or Report Builder. The featured picture of this post shows example charts. I was anyway engaged to have an insight into this feature as I want to ease the work of the users and show the data directly to them.
It is quite straightforward to use once you had a look at the examples in the several home forms, e.g. [Controller Home] or [Customer Service Home]. Nevertheless, it required some prerequisites to finally create my own chart.

The basic ingredients are:

• Custom Load Method (CLM) on an IDO
• An IDO to bind the CLM to
• The CLM itself

As you can see, you have to add a CLM. As CLMs are always more difficult to keep track of when you customize, I decided to comprise them into a single new IDO for graphs. This is also enables maximum reusability all over the forms, as you simply have to add the new IDO.
In the following I will list the customizations I have applied.

1. Create a plain IDO table.
   
   Transact-SQL

   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ccs_graphs]( [NoteExistsFlag] [dbo].[FlagNyType] NOT NULL, [RecordDate] [dbo].[CurrentDateType] NOT NULL, [RowPointer] [dbo].[RowPointerType] NOT NULL, [CreatedBy] [dbo].[UsernameType] NOT NULL, [UpdatedBy] [dbo].[UsernameType] NOT NULL, [CreateDate] [dbo].[CurrentDateType] NOT NULL, [InWorkflow] [dbo].[FlagNyType] NOT NULL, CONSTRAINT [PK_ccs_graphs] PRIMARY KEY CLUSTERED ( [RowPointer] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   CREATE TABLE [dbo].[ccs_graphs]( [NoteExistsFlag] [dbo].[FlagNyType] NOT NULL, [RecordDate] [dbo].[CurrentDateType] NOT NULL, [RowPointer] [dbo].[RowPointerType] NOT NULL, [CreatedBy] [dbo].[UsernameType] NOT NULL, [UpdatedBy] [dbo].[UsernameType] NOT NULL, [CreateDate] [dbo].[CurrentDateType] NOT NULL, [InWorkflow] [dbo].[FlagNyType] NOT NULL, CONSTRAINT [PK_ccs_graphs] PRIMARY KEY CLUSTERED ( [RowPointer] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO

2. Create the IDO from the plain table. No need yet to check the IDO in.
   
3. Create your Stored Procedure with the graph data as returns values. As a hint, I would just copy a plain one from SyteLine itself like Home_MetricCashImpactSp . As an example, I will provide the rough outline of my SP:
   
   Transact-SQL

   ALTER PROCEDURE [dbo].[CCSMetricITOSp] AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @ResultTable TABLE ( ito_1 AmtTotType ,ito_2 AmtTotType ,date DateTimeType ,date_label AS CONVERT(NVARCHAR, date, 101) --DateType (this needs to be nvarchar so displays properly on graph) ) --populate the result table with the basic data INSERT INTO @ResultTable ( ito_1 ,ito_2 ,date ) SELECT ito ,0 ,dbo.MidnightOf(date) FROM ccs_ito_history [...] SELECT ito_1 ,date_label FROM @ResultTable

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   ALTER PROCEDURE [dbo].[CCSMetricITOSp] AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   DECLARE @ResultTable TABLE ( ito_1 AmtTotType ,ito_2 AmtTotType ,date DateTimeType ,date_label AS CONVERT(NVARCHAR, date, 101)  --DateType (this needs to be nvarchar so displays properly on graph) )   --populate the result table with the basic data INSERT INTO @ResultTable ( ito_1 ,ito_2 ,date ) SELECT ito ,0 ,dbo.MidnightOf(date) FROM ccs_ito_history   [...]   SELECT ito_1 ,date_label FROM @ResultTable

4. Now you can return to the newly created IDO and add the properties you will need. As the IDO will be used in a highly generic way, you should have have no too specific property names. As an example I have added the following four properties – in addition to the default SL properties – all in read-only mode:
5. After adding you can add the CLM and define the return assignments.
   
6. Check the IDO in and clear the IDO cache.
7. Now go to the form you want to add the graph onto. In my case it’s the [Controller Home]. I have added my newly IDO. In addition, I have set the standart operations Graphs to True . I have to admit, that the help does not tell me enough about it, but I just think it might be useful when being set.
8. One trick, before you desperatly think that you failed somewhere else: We used a CLM. Thus, you have either to refresh the collection yourself to call the CLM, use a form event handler or use the initial command of the form to refresh. I first was confused that I had no data returned from the CLM…
9. Now one more trick: You may will see no data in the end, if you have not bound the properties to fields. So, I recommend to add a grid with columns of all the values you want to visualize. Only properties which are bound to fields will be shown!
10. Here is an example depict of the Graph setup: