Inflectra Customer Forums: Custom graph X axis date format? (Thread)

Custom graph X axis date format?

D L (dennis.lardenoye@pie.nl) — Wed, 17 Jan 2024 15:50:18 -0500

Id 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 Im 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. Id like the bars in my graph to represent their actual placement in the date range. Since Im 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 t

David J (adam.sandman+support@inflectra.com) — Tue, 23 Jan 2024 12:39:11 -0500

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 were 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

Cher Nicastro (Cher.nicastro@ecifm.com) — Fri, 15 Mar 2024 19:25:07 -0400

@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 months tests only) select concat(cast(Day(TestTime) as Edm.String), concat(/, co

D L (dennis.lardenoye@pie.nl) — Wed, 15 May 2024 14:06:11 -0400

(this is for last months 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()) <32 GROUP BY TruncateTime(R.START_DATE) as TestTime