Filtering the change date in historical trends reports
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:
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
|
4 answers
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. Comments
Omar Khemoudj
commented 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?
|
For ACTUAL_WORK, I would expect to see SUM(T1.ACTUAL_WORK ) in the select. No doing so will give you counts = 1. |
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. |
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.