Count the number of columns in a column group

Question

I need to report the average number of customer visits each engineer makes per month. My SQL query creates a temporary table of months between the start date and end date and left joins this to the main data table, to ensure rows are returned even for months where no visits were made.

So an example of the data returned from SQL may be:

My report has two column groups, one for year and one for month, and I have a row group for the engineer.

For this report, the date is always returned as the first of the month, even though the actual visit could be on any date.

At the end of each year there is a cell which contains Count(Customer) and totals the number of visits the engineer made in that year. I would also like to have a cell which displays the average number of visits made each month in that year.

For a complete year I could simply divide by 12. However for a partial year I need to count the number of month columns for that year.

I tried CountDistinct(Month) but this only counts months where at least one visit was made, making the monthly average incorrect.

How can I get a count of the number of columns in a column group, including columns with no data?

Thanks.

Alan Schofield Alan Schofield · Accepted Answer · 2020-02-26T20:43:26

The way I would do this would be to add a column into your temporary dates table that had the number of months selected in it.

You could do this by either counting the months in the temp table then appending the value to it or, if the dates table contains more than just months then work it out based on the parameters you pass in.

For example

SELECT *, DATEFIFF("m", @startDate, @endDate) as NoOfMonths
    INTO #myTempDateTable
    FROM myDateTable
    WHERE etc...

Then in SSRS you can simply divide your total by this number.

Count the number of columns in a column group

1 Answers