Newbie question - creating report definition/report from SQL - how to add proper object selection to FILTER in CUIC

I created a Real Time report which returns data.

My issue is that I do not get the normal object selection in the Basic Filter section.

Imported reports have the option to select the "Key Criteria Field" using left/right arrows similar to the old WebView selection process. For historical reports, it also allows both Fixed Date and Relative Date selection.

In my case, I created an Enterprise Service Real Time report from scratch (as there were no templates which I could import).

I selected "EnterpriseServiceID" as the "Key Criteria Field" in the properties of the Report Definition.

However, when I run a report against the Report Definition, I get a static field in the Basic Filter, where by if I enter a valid value, it will work. I would rather however the end user be able to use the left/right arrow buttons, rather than typing in a value.

As a comparison, I checked an Enterprise Service Historical template that I imported.

I have examined the WHERE clause in the SQL for the Enterprise Service Historical All Fields report that I imported.

The SQL for WHERE clause in the report definition is as follows:

WHERE

Enterprise_Service.EnterpriseServiceID = Enterprise_Service_Member.EnterpriseServiceID

  and Enterprise_Service_Member.SkillTargetID = Service_Interval.SkillTargetID

  and Enterprise_Service_Member.SkillTargetID = Service.SkillTargetID

After selecting Date Range and Enterprise Services in CUIC FILTER and running the report, after viewing the SQL from the running report, the WHERE clause was altered as follows:

WHERE

(DATEPART(dw, Service_Interval.DateTime) in(2,3,4,5,6,7,1) and Service_Interval.DateTime between convert(DATETIME,'2015-06-17 00:00:00',21) and convert(DATETIME,'2015-06-17 23:59:59',21) and convert([char], Service_Interval.DateTime, 108) between '00:00:00' and '23:59:59') and (Enterprise_Service.EnterpriseServiceID IN (10018))

and

Enterprise_Service.EnterpriseServiceID = Enterprise_Service_Member.EnterpriseServiceID

  and Enterprise_Service_Member.SkillTargetID = Service_Interval.SkillTargetID

  and Enterprise_Service_Member.SkillTargetID = Service.SkillTargetID

In the CUIC report definition, EnterpriseServiceID (EnterpriseServiceID) is selected in the “Key Criteria Field”

Simply choosing the appropriate field as the “Key Criteria Field” is not enough however to add the date range and object selection within the FILTER section when running the CUIC report.

My question is How does one add the date range and proper object selection in the filter section which then gets inserted into the WHERE clause?

Please and thanks .. Lenny

One - you will need a value list for EnterpriseServiceID - I don't believe there is one.  In the Value List drawer crete a value list, there is a template where it does: SELECT [xxxID] AS ID, [EnterpriseName] AS VALUE FROM [Table].  You want to replace those three with appropriate column and table names.

Then in the report definition, under the "Fields" tab, you will want to select EnterpriseServiceID and then at the very bottom, select your new value list as the value list for the report.  At run time, the user will see the name associated with the value list, but it is the ID - or set of IDs that will be inserted into the SQL.

Let me know if that doesn't solve your problem.

Hi - this worked! Thanks!

If your SQL is Database Query you do not put the date in the SQL WHERE clause.

What you do is, in Properties Tab, use the Historical Key Field

to the date file.

And the Key Criteria Field to filter on another field you want to filter on.

If you use Anonymous Block you would parameter and you would use them in the SQL query.

start_date

end_date

agent_list (or call type whatever)

(all prefixed with the @ symbol), but prefixed with a ":" in the SQL query.

Checkout a stock report that uses anonymous block.

The start_date set the relative date range in the Parameter TAB for this specific parameter to "Start Date"

The end_date set the relative date range in the Parameter TAB for this specific parameter to "End Date"

Have the Display Name for both to "Date and Time"

Set agent_List (or calltype etc) to the correct value list

Regards,

Gerry