IDT - Creating time and day variables in the Business Layer or WEBI Report?

[Former Member]

I want to demo a simple report below by utilizing the IDT tool. I want to create a report that looks at how many transactions a day a customer service handles per 15 minutes. The report would look something like this:

		12/26	12/25	12/24	12/23	12/22
Employee Name	8:00 – 8:15	10	4	8	7	6
	8:15 – 8:30	2	2	4	3	1
	8:30 – 8:45	4	4	6	5	6
	8:45 – 9:00	9	10	11	10	11
	9:00 – 9:15	22	11	24	23	24
	9:15 – 9:30	33	12	35	34	40
	9:30 – 9:45	33	12	37	36	39
….	….	….	….	….	….	….

I have the following two tables from the database to build this Universe:

Employee Table
Name	String
Employee ID	String

Example values from Employee Table

Name	Employee ID
James Doe	X11111
Jenny Smith	X22222
…	…

Transaction Table
Employee ID	String
Transaction ID	Numeric
Start Transaction	DateTime
Stop Transaction	DateTime

Example values from Transaction Table

Employee ID	Transaction ID	Start Transaction	Stop Transaction
X11111	123456789	2013-10-11 12:03:56.0	2013-10-11 12:06:19.0
X22222	987654321	2013-10-11 14:54:52.0	2013-10-11 14:58:58.0
…..	…..	…..	…..

I can easily do a row count to find out the number of transactions and create a measure for that.  Where Im running into the biggest problem is trying to figure out how to create a date variable by day, and a time variable for every 15 minutes. For the time variable, I only have to look at the “Stop Transaction” time, and see where that falls in the 15 minute increment. Is this possible in the Business Layer, or should I try to calculate this on the report side? I would like to accomplish on the business layer side so a user can drag it right into the report.

Your advice to solve this problem would be greatly appreciated, and I hope I simplified this as much as possible.

[Former Member · Accepted solution]

I figured this out. Thanks everyone