Compare Trends with Advanced Reporting

0 Likes
over 5 years ago

As I mentioned in Introduction to Advanced Reporting, it's possible to create Listing, Distribution, Summary and Calendar reports against virtual auxiliary tables(Views). In this post I would try to explain how to create report will compare some trends.

Let's consider SBM sample database coming with installation. Let's say, I would like to compare, in how many items each user was a Team Lead (TS_TEAM_LEAD field in Issues table - it's ID is 67 which is important because we will use this value in SQL) during several periods. As a result, I would have a table, which shows me that Joe in period1 was Team Lead in XX items, and in period2 he was Team Lead in YY items, so I would like to see and compare those values.

This thing is not possible to do with SBM Trend reports, and we need to write appropriate SQL plays with historical values and returns table we can output through Distribution reports (which is available for Advanced Reporting).

At first, there is entire XML for putting to "ViewDefinitions" folder:

<views>
 <view dbname="TSV_USERS" name="Users" singleItemName="User" app="cea0a86c-5d74-4e12-b8d6-9d6b90186f1e" uuid="57107ecf-e74f-42fc-bc6f-a8d149884398">
   <sql>
      SELECT TS_ID, TS_UUID, TS_NAME, TS_EMAIL from TS_USERS where TS_NAMESPACEID={namespaceID}
    </sql>
    <valuedisplayformat>
      <format>({0})</format>
      <fields>EMAIL</fields>
    </valuedisplayformat>
    <fields>
      <field dbname="NAME" name="Name" type="text" size="64" syscode="16" />
   <field dbname="EMAIL" name="Email" type="text" size="256" syscode="16" />
    </fields>
 </view>
 <view dbname="TSV_PERIODS" name="Periods" singleItemName="Period" app="cea0a86c-5d74-4e12-b8d6-9d6b90186f1e" uuid="57107ecf-e74f-42fc-bc6f-a8d149884402">
   <sql>
      select periods.TS_ID, NEWID() TS_UUID, periods.startDate TS_STARTDATE, DATEADD(day, 1, periods.startDate) TS_ENDDATE
              from
              (select seq.TS_ID 1 TS_ID, DATEADD(day, seq.TS_ID, '2015-10-14 22:58:27.000') startDate
              from TS_INTEGERS seq where DATEADD(day, seq.TS_ID, '2015-10-14 22:58:27.000') &lt; '2015-10-28 00:52:26.000') periods
    </sql>
    <valuedisplayformat>
      <format>({0}-{1})</format>
      <fields>STARTDATE, ENDDATE</fields>
    </valuedisplayformat>
    <fields>
      <field dbname="STARTDATE" name="Start Date" type="datetime"/>
   <field dbname="ENDDATE" name="End Date" type="datetime"/>
    </fields>
 </view>
 <view dbname="RPT_PERIODS_TREND" name="Periods Trend" singleItemName="Trend" app="cea0a86c-5d74-4e12-b8d6-9d6b90186f1e" uuid="57107ecf-e74f-42fc-bc6f-a8d149884403">
    <sql>
      select c6.TS_ID TS_ID, NEWID() TS_UUID, c6.TS_ID TS_PERIODID, c6.newValue TS_USERID, MAX(c6.rn) TS_COUNT from (
select c5.TS_ID, c5.TS_STARTDATE, c5.TS_ENDDATE, c5.recid, c5.newValue, ROW_NUMBER() OVER(PARTITION BY c5.TS_ID, c5.newValue order by c5.TS_ID) AS rn from (
select distinct p.TS_ID, p.TS_STARTDATE, p.TS_ENDDATE, c4.recid, c4.newValue from
      (
              select periods.TS_ID, NEWID() TS_UUID, periods.startDate TS_STARTDATE, DATEADD(day, 1, periods.startDate) TS_ENDDATE
              from
              (select seq.TS_ID 1 TS_ID, DATEADD(day, seq.TS_ID, '2015-10-14 22:58:27.000') startDate
              from TS_INTEGERS seq where DATEADD(day, seq.TS_ID, '2015-10-14 22:58:27.000') &lt; '2015-10-28 00:52:26.000') periods                
      ) p
      right outer join (
                                          -- get the change actions we are interested in
                                          select c1.TS_CASEID recid, c1.TS_NEWINT newValue, c1.TS_TIME starttime, ISNULL(c3.TS_TIME,DATEDIFF(s,CONVERT(datetime, '1970-01-01', 120),'2015-10-28 00:52:26.000')) endtime
                                          from TS_CHANGES c1
                                          -- combine each change action with the previous change for this record, creating a single row with a start time and an end time
                                          outer apply (select top(1) c2.TS_TIME from TS_CHANGES c2 where c2.TS_CASEID=c1.TS_CASEID and c1.TS_FLDID=c2.TS_FLDID and c2.TS_TIME>c1.TS_TIME order by TS_TIME) c3                                                
                                          where c1.TS_FLDID=67                                         
                                          union all
                                          ( select
                                                  TS_ID recid,
                                                  CASE WHEN NOT EXISTS(select top(1) TS_ID from TS_CHANGES where i1.TS_ID = TS_CHANGES.TS_CASEID AND TS_CHANGES.TS_FLDID = 67 order by TS_TIME)
             THEN TS_TEAM_LEAD
                                                    ELSE (select top(1) TS_PRIORINT from TS_CHANGES where i1.TS_ID = TS_CHANGES.TS_CASEID AND TS_CHANGES.TS_FLDID = 67 order by TS_TIME)
                                                  END newValue,                                                  
                                                  CASE WHEN TS_SUBMITDATE &lt; '2015-10-14 22:58:27.000'
             THEN DATEDIFF(s,CONVERT(datetime, '1970-01-01', 120),'2015-10-14 22:58:27.000' )
             ELSE DATEDIFF(s,CONVERT(datetime, '1970-01-01', 120), TS_SUBMITDATE)
              END starttime,
                                                  CASE WHEN NOT EXISTS(select top(1) TS_ID from TS_CHANGES where i1.TS_ID = TS_CHANGES.TS_CASEID AND TS_CHANGES.TS_FLDID = 67 order by TS_TIME)
                                                    THEN DATEDIFF(s,CONVERT(datetime, '1970-01-01', 120),'2015-10-28 00:52:26.000')
                                                    ELSE (select top(1) TS_TIME from TS_CHANGES where i1.TS_ID = TS_CHANGES.TS_CASEID AND TS_CHANGES.TS_FLDID = 67 order by TS_TIME)
                                                  END endtime from UBG_ISSUES i1
                                          )
                                ) c4
              on ( p.TS_STARTDATE > 0 )
              where (
                        ((DATEADD(SECOND, c4.starttime, CONVERT(DATETIME, '1970-01-01', 120)) between p.TS_STARTDATE and p.TS_ENDDATE) or
                        (DATEADD(SECOND, c4.endtime, CONVERT(DATETIME, '1970-01-01', 120)) between p.TS_STARTDATE and p.TS_ENDDATE) or
                       (DATEADD(SECOND, c4.starttime, CONVERT(DATETIME, '1970-01-01', 120)) &lt;= p.TS_STARTDATE and DATEADD(SECOND, c4.endtime, CONVERT(DATETIME, '1970-01-01', 120)) >= p.TS_ENDDATE))
              )
            ) c5) c6 group by c6.TS_ID, c6.newValue
    </sql>
    <valuedisplayformat>
      <format>{0}: {1}: {2}</format>
      <fields>PERIODID,USERID,COUNT</fields>
    </valuedisplayformat>
    <fields>
      <field dbname="PERIODID" name="Period" type="relational" target="57107ecf-e74f-42fc-bc6f-a8d149884402"/>
   <field dbname="USERID" name="User" type="relational" target="57107ecf-e74f-42fc-bc6f-a8d149884398" syscode="26" />
   <field dbname="COUNT" name="Count" type="number-integer"/>        
    </fields>
  </view>
</views>
First View(TSV_USERS) is pretty simple, it's coming with previous examples and just needed to create relational field pointing to TSV_USERS table.

Second one(TSV_PERIODS) is table contains time intervals for which we would like to compare values. Those periods are generated with next SQL code:

It splits time interval between start date and end date, each interval is 1 day. In case you need others periods, you can use "month", "week", "year" SQL keywords. If you execute this SQL, it will bring next output:

We got 12 periods, 1 day each period. Notice that NEWID() TS_UUID column - it's UUID generating which is mandatory field for every View.

The most complicated is third View - RPT_PERIODS_TREND. It has complicated SQL statement which I would try to explain step by step.

Again, there are some hardcoded things - 67 is id of TS_TEAM_LEAD field in TS_FIELDS, also we must have same start/end dates in this statement, as we are using for TSV_PERIODS view.

Also you may find one more interesting date here - 1970-01-01. This is actually constant value, Unix start time using for converting date/time values to integer milliseconds and calculating dates differences.

And UBG_ISSUES is Issues table contains primary items we are interested in.

SQL starts with same statement as for TSV_PERIODS View and generates time periods. Then, we are doing outer join with table contains all values for TS_TEAM_LEAD field for all items, during all time since item has been submitted. It's achieved with next block of SQL:

As first step, it selects all changes for TS_TEAM_LEAD field, and in case there are several changes, create time intervals with each value for our field. Then we have union all block, which doing different things depends on which changes we have for particular item. In case there is no any change for field (item has been submitted, and TS_TEAM_LEAD field has not been changed, we create fake "change" record for entire timeframe - it again depends if start date is earlier/later than submit date, and end date for this "fake" change will be our end date we defined in the beginning).

Let's execute this part and take a look on results:

For example, we can say that item 71 has Team Lead with Id 16 during some time, and then Team Lead has been changed to 13, and remains 13 till the our end date.

After that we have "where" clause for join, which intended to determine, if particular user(newValue) was a TeamLead in particular period - for doing that we check if period start/end dates overlap with field value start/end date:

Having all this information, we just doing calculation how many items had user as team lead in each period, and we are done!

After get all this staff in database as virtual auxiliary tables, we can do distribution report against our Periods Trend View:

Select "Period" as a row, "User" as a column and Sum Totals By as "Count" and run report: 

Here you can see that some period total items count spikes to 101 and then in next period again goes down. Same period "Carmen" has 5 new items as team lead, and this mean, that it counts each item twice - because during period 2 users were Team Leads in those 5 items. In case you don't need it, and for example would like to have always only one value per item for period, you need to modify changes SQL part to adjust this behavior.

 

Comment List
Anonymous
Related Discussions
Recommended