Custom Reporting question

[vladpetra]

Greetings,

I am writing my first custom report for one of our customers. I wrote several lines, saved the function and decided to test it. I am able to CALL the function from my informix consul with "CALL my('2015')", but when I am trying to execute it on the CUIC, it alerts with "Dataset status is Failed (Database error)"

I am not sure if the value is correct when I am creating parameters in CUIC

My function is really basic. I am attaching a file with the function.

I am using a INT parameter, so the user will be able to input just a year, which based on the function will create the report.

I would be thankful for any advice

Best regards,

Vladislav

[jacparke]

1 - you should be using a procedure, not a function.

2 - -206 is "Table not found", it makes no sense to drop the temp table if that is the case.  Actually you don't really need a temp table, you can just do a select and RETURN WITH RESUME.

[vladpetra]

Thank you for your reply.

1 - I was based on the current situation in CUIC and UCCX. I started with function because all other default reports using function.

2 - Again, this is just in case of error, as I was able to understand. I've copied these lines from other function which comes with UCCX/CUIC by default.

The problem is that currently I am able to execute the function from my console. But when I am trying to execute it on CUIC, it doesn't go through and bring the error.

[jacparke]

I am not totally familiar with how UCCX does reporting, I am aware that all of their stock reports use procedures - or functions.  A function should be used when producing a single value - select MAX(some value) from table, for example.  A procedure should be used in the case where you are returning multiple rows from a dataset - totally besides the point.

You should be able to write your report without any middle-step.  In the Report Definition, make it a "Anonymous Block" and merely type in your query like:

		SELECT callednumber, startdatetime, enddatetime
		FROM ContactCallDetail
		WHERE YEAR(startdatetime) IN (:p_year);

press the "Create Parameters" button, it will default p_year to a STRING, change that to a decimal.  Then "Create Fields" and you should be off and running.

Regards,

Jack Parker

[vladpetra]

I am currently trying to make it with "Anonymous Block" as well, since I couldn't run the function correctly.

The problem is that the final "result" will have a lot of calculations. The function that I provided is just the first step.

[jacparke]

You are missing a semi-colon after the "END FOREACH", see if that solves your problem.

There is a lot of math you can do within CUIC, if you give me some idea of what your'e trying to accomplish, perhaps I can help.

Regards,

Jack Parker

[vladpetra]

I am trying to replicate the MTD TSF Summery Report. Our customer asked to include just Monday to Friday between 8:30 to 4:30. But he would like to have a report for answered calls within 30 seconds and another report for calls which had been answered after 30 seconds. Another report for calls which had not been answered.

[jacparke]

I assume MTD stands for Month To Date, what does TSF stand for?

Regards,

Jack Parker

[vladpetra]

I am not sure. This is one of the existing reports and it uses "sp_csq_interval" function

[jacparke]

I've got it.  You sure picked a doozy.

Regards,

Jack Parker

[vladpetra · Accepted solution]

You will never believe it. It seems that there is some kind of BUG. When I was entering 2015 to get information about this year, I was getting an error, the one that I've mentioned. But then I decided to check 2014, and guess what...it worked.

I've added a parameter for a month, since our client would like to do it per month.

I want to thank you for your help.

[jacparke]

I'll see if I can dig that up.

Regards,

Jack Parker

[vladpetra]

Thank you very much for your help.