Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Custom graph X axis date ...
I'd like to create a custom (bar chart) graph showing specific test activity over time. Number of test runs passed/failed are counted per day.
Since I'm filtering for custom fields, I cannot use one of the standard graphs in Spiraplan.
I have it working so far, except one thing: the standard graphs account for 'empty' days (with no activity). It appears this is not handled by the graph control itself and smart placement on the X-axis, but the 'empty' days are present in the underlying data table. I'd like the bars in my graph to represent their actual 'placement' in the date range.
Since I'm not an (E)SQL wizard, I would really like to know how to produce a range of dates that could be used to JOIN my existing table with, so I can have all dates in my range, including the ones without activity.An example would be most welcome!
I am not sure that is possible using just ESQL (or SQL) since queries will need a reference range to use. Inside the standard graphs we're using C# code to do this, not SQL for that reason.
The only way it would work is if you had a table of dates to join against. It could be done in the physical database using a stored procedure to create a table that has a list of dates dynamically.
In the future it will be possible when we open up SpiraApps to third-party development as then you could use JavaScript code to create an array of dates and populate the data.
@dl.pie do you mind sharing the equal that you have working (I realize it will not have empty spots with dates with no activity).
Thank you!
(this is for last month's tests only)
select concat(cast(Day(TestTime) as Edm.String), concat('/', concat(cast(Month(TestTime) as Edm.String), concat('/',right(cast(Year(TestTime) as Edm.String),2))))) as Time, Sum(CASE WHEN R.TEST_RUN_TYPE_NAME='Manual' and R.CUST_06='Y' THEN 1 ELSE 0 END) as ManualSystemTests, Sum(CASE WHEN R.TEST_RUN_TYPE_NAME='Manual' and R.CUST_06='N' THEN 1 ELSE 0 END) as ManualNonSystemTests, Sum(CASE WHEN R.TEST_RUN_TYPE_NAME='Automated' THEN 1 ELSE 0 END) as AutomaticTests from SpiraTestEntities.R_TestRuns as R WHERE R.PROJECT_ID=${ProjectId} and DiffDays(R.START_DATE, CurrentDateTime()) <32GROUP BY TruncateTime(R.START_DATE) as TestTime
And if you have any questions, please email or call us at +1 (202) 558-6885
