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:
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.