This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Business hours reporting in the Web Based Report Writer

As many folks have found, there is not currently a native method in the web report writer to allow for business hours reporting.

This does not mean however that it cannot be done.

In order to achieve a custom table with business hours filters, we must first start in the legacy Orion Report Writer.

Your business hours report can begin as any standard report.  The process of filtering to business hours involves adding 4 filters.

- Day of week greater or equal to Monday

- Day of week less or equal to Friday

- Hour of day greater or equal to 7

- Hour of day less or equal to 18

In my example, I am assuming business hours to be 7AM to 7PM Monday thru Friday.

Once you have applied the filter you will find you can produce the report in Report Writer quite easily.

The process of converting this report into a Web Based report is very simple.

First - use the Show SQL option on the Report Menu within Report Writer.

Select the entire SQL statement and copy it.

For the report timeframe, you will notice that report writer uses a date math shortcut of converting the 'last x amount of time' (last 7 days for example) to a statement like this:

( DateTime BETWEEN 42472 AND 42479.75 )

As you might guess, this report will ALWAYS and ONLY be for those specific decimal dates, so we need to correct this in order for our report to work in the Web Based Report Writer as we intend it to.

We will replace this line with the following:

( DateTime BETWEEN (DateAdd(dd, -7, GetDate()) AND GetDate())

This statement ensures that our report always runs for the intended time frame, but looking at a delta from today's date in order to determine last 7 days.  You can modify the DateAdd parameters in order to get different time frames.  See DATEADD (Transact-SQL) for more details on working with the DateAdd function.

Beyond that, no other changes are necessary, our query can be copied and pasted into the Custom SQL UI in the Web Based Report Writer and you can then configure your report columns as desired to achieve the finished result.

Here is a complete query which returns "Top 10 Interfaces by Transmit bps for the last 7 days, during business hours."

SELECT TOP 10
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)


WHERE
( DateTime BETWEEN (DateAdd(dd,-7,GetDate())) AND GetDate())
AND 
(
  (DATEPART(weekday, DateTime) >= 2) AND
  (DATEPART(weekday, DateTime) <= 6) AND
  (DatePart(Hour,DateTime) >= 7) AND
  (DatePart(Hour,DateTime) <= 18)
)


GROUP BY Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon


ORDER BY 8 DESC

Happy business hours filtering!

Parents
  • I am also having problems locating the Timestamp field.  Also, I wanted to know if this allows us to exclude (or include) certain days of the week.  For example, we want our utilization reports to be from 7 am to 7 pm but only Monday - Friday, since our offices are closed during weekends. 


    Can you enlighten me on the above?  Thanks!  emoticons_cool.png

Reply
  • I am also having problems locating the Timestamp field.  Also, I wanted to know if this allows us to exclude (or include) certain days of the week.  For example, we want our utilization reports to be from 7 am to 7 pm but only Monday - Friday, since our offices are closed during weekends. 


    Can you enlighten me on the above?  Thanks!  emoticons_cool.png

Children
No Data