Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

Filtering the change date in historical trends reports

Questions
Tags
Users
Badges
Omar Khemoudj
(23●3●14) edited
Mar 19 '19, 6:57 a.m.

 Hello Everyone,


I am experiencing an issue when using the report builder for historical trends.

I am getting the limitation warning saying the report results exceeded the 3000 items limit.

By check the table results, I figured out that in the change date column, the updates are happening each day and every 30 mins, that is why the results exceed by far the 3000 limit. See below example:


Change Date (Custom Trends for Work Items)  Actual Work (Custom Trends for Work Items)   Custom Trends for Work Items: Count total number of artifact...   Work Item: Count total number of artifacts  
10/28/18 2:08 AM 28,800 1 1
12/14/18 1:07 AM 28,800 1 1
1/10/19 1:07 AM 28,800 1 1
1/14/19 9:30 AM 28,800 1 1
1/23/19 12:00 PM 28,800 1 1
9/19/18 12:17 AM 28,800 1 1
9/19/18 12:47 AM 28,800 1 1
9/19/18 1:17 AM 28,800 1 1
9/19/18 1:47 AM 28,800 1 1
9/19/18 2:17 AM 28,800 1 1
9/19/18 2:47 AM 28,800 1 1
9/19/18 3:17 AM 28,800 1 1
9/19/18 3:47 AM 28,800 1 1
9/19/18 4:18 AM 28,800 1 1
9/19/18 4:48 AM 28,800 1 1

I would like to filter the request by just taking the information at end of day, let's say between 11pm and midnight for example. Is it possible to do it from the SQL query for the variable  T3.REC_DATETIME:

SELECT T3.REC_DATETIME AS DATETIME,
       T1.ACTUAL_WORK AS ACTUAL_WORK1,
       COUNT( DISTINCT T1.REFERENCE_ID) AS REFERENCE_ID,
       COUNT( DISTINCT T2.REFERENCE_ID) AS REFERENCE_ID1
FROM RICALM.VW_RQST_HISTORY T1
INNER JOIN RIDW.VW_REQUEST T2
ON (T2.REQUEST_ID = T1.REQUEST_ID) AND T2.PROJECT_ID = 27  AND T2.ISSOFTDELETED = 0
  INNER JOIN RICALM.VW_RQST_BASELINE T3
  ON T1.REQUEST_HISTORY_ID=T3.REQUEST_HISTORY_ID
WHERE T1.PROJECT_ID = 27  AND
(  T1.REQUEST_TYPE = 'Task' AND
  MONTHS_BETWEEN(CAST(CURRENT_TIMESTAMP AS DATE), CAST(T3.REC_DATETIME AS DATE)) <= 6 AND
  CAST(CAST(CURRENT_TIMESTAMP  AS DATE) - CAST(T3.REC_DATETIME AS DATE) AS INTEGER) >= 0 AND
  ( T1.REFERENCE_ID = '20479' ) 
) AND
T1.ISSOFTDELETED = 0 AND
(T1.REQUEST_HISTORY_ID <> -1 AND T1.REQUEST_HISTORY_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL)GROUP BY T3.REC_DATETIME,
         T1.ACTUAL_WORK


Is there any other possibility?

Thank you very much for your support.
Omar



0 votes

4 answers
2,907 views
0 votes

4 answers

Permanent link
Rafik Jaouani
JAZZ DEVELOPER (5.0k●1●6) Mar 19 '19, 9:41 a.m.

Hi Omar, from looking at the SQL, it does not seem to be a trend report. It seems to be fetching work item history. I think your report is probably invalid. I would suggest you contact IBM support or give a better explanation of what the report is trying to do.

0 votes

Comments
Omar Khemoudj
Mar 19 '19, 11:03 a.m.

Hello Rafik,


Thanks for the answer,

It is a trend as I have chosen Historical Trends (time series) option. Do I need to check anything else? 

Permanent link
Rafik Jaouani
JAZZ DEVELOPER (5.0k●1●6) Mar 19 '19, 11:30 a.m.

For ACTUAL_WORK, I would expect to see SUM(T1.ACTUAL_WORK ) in the select. No doing so will give you counts = 1.

0 votes

Permanent link
Omar Khemoudj
(23●3●14) edited
Mar 19 '19, 11:34 a.m.

 Hello,


Thank you very much for your replies, I was able to apply the filter as follows:

I have added the condition to pick up only the items from 1 to 2 AM: 
(CAST(T3.REC_DATETIME AS TIME) between '01:00:00' and '02:00:00')

Also I noticed that JRS only allows to limit the filter between 1AM and 12AM

I get now only around 300 items which is far below the 3000 limit

Below the query


SELECT T3.REC_DATETIME AS DATETIME,
       T1.ACTUAL_WORK AS ACTUAL_WORK1,
       COUNT( DISTINCT T1.REFERENCE_ID) AS REFERENCE_ID,
       COUNT( DISTINCT T2.REFERENCE_ID) AS REFERENCE_ID1
FROM RICALM.VW_RQST_HISTORY T1
INNER JOIN RIDW.VW_REQUEST T2
ON (T2.REQUEST_ID = T1.REQUEST_ID) AND T2.PROJECT_ID = 27  AND T2.ISSOFTDELETED = 0
  INNER JOIN RICALM.VW_RQST_BASELINE T3
  ON T1.REQUEST_HISTORY_ID=T3.REQUEST_HISTORY_ID
WHERE T1.PROJECT_ID = 27  AND
(  T1.REQUEST_TYPE = 'Task' AND
  MONTHS_BETWEEN(CAST(CURRENT_TIMESTAMP AS DATE), CAST(T3.REC_DATETIME AS DATE)) <= 18 AND
  CAST(CAST(CURRENT_TIMESTAMP  AS DATE) - CAST(T3.REC_DATETIME AS DATE) AS INTEGER) >= 0 AND
  ( T1.REFERENCE_ID = '20479')  
) AND
(CAST(T3.REC_DATETIME AS TIME) between '01:00:00' and '02:00:00') AND
T1.ISSOFTDELETED = 0 AND
(T1.REQUEST_HISTORY_ID <> -1 AND T1.REQUEST_HISTORY_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL)GROUP BY T3.REC_DATETIME,
         T1.ACTUAL_WORK

0 votes

Comments
Rafik Jaouani
JAZZ DEVELOPER Mar 19 '19, 11:47 a.m.

Report should be selecting the SUM of Actual work instead of the value it self. That is the problem.

Permanent link
misha rajpoot
(34●1) Mar 20 '19, 1:14 a.m.

 Historical trend reporting uses a special custom report type designed to highlight changes between five snapshot dates, such as five business days or five business weeks. You can visually represent the data changes in charts and on dashboards.

For organizations created in Winter ’14 and thereafter, historical trend reporting is activated by default. If your organization is older than that, you must activate historical trend reporting in the Setup menu.

Salesforce retains historical data for the previous three months, plus the current month. (The amount of historical data you can work with in practice depends on your organization’s data design and use patterns.) You can select up to five date snapshots in that span to compare, using up to four historical filters.

Longer durations than days or weeks are not recommended. They may result in reports timing out and not returning. For small organizations with fewer records, month-to-month trend reporting may work, but this is not what historical trend reporting is designed for.

0 votes

Your answer

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 12,019
× 360
× 230
× 2

Question asked: Mar 19 '19, 6:54 a.m.

Question was seen: 2,907 times

Last updated: Mar 20 '19, 1:14 a.m.

Confirmation Cancel Confirm