Custom CMS Report - Too Many Skills

[chrisaroundtown]

Hi,

I have about 100 skills I'd like to report on in the same report. The input field for split/skills only allows a limited number of characters. Problem is my skill numbers are not grouped together i.e. I can't just ask for skills 1-100, I need skills 45;67;38;145...etc.

Is there a way to have 2 input fields for skill numbers and write the WHERE clause to refer to all skills in either field? Has anyone done something like this before? I can't get it to work.

Chris

 

[Stinney]

You should be able to just add it to the where statement. I took the split/skill report and added a second input for split and changed the where statement to the following and it worked:

ACD=$acd and SPLIT = [Splits/Skills:] or SPLIT = [Splits/Skills(1):]


- Stinney

Quoting only proves you know how to cut and paste.

 

[chrisaroundtown]

Hi Stinney,

I have tried the following, but this returns all dates.

ACD=$acd AND SPLIT = [Skills1:] OR SPLIT = [Skills2:] AND ROW_DATE = [Date:] GROUP BY SPLIT, ROW_DATE

I have also tried it with brackets around the skills criteria, but this gives an error if I enter more than one skill in either skill input field. It works if I only put one skill number in each field.

ACD=$acd AND (SPLIT = [Skills1:] OR SPLIT = [Skills2:]) AND ROW_DATE = [Date:] GROUP BY SPLIT, ROW_DATE

Any thoughts?

Chris

 

[Stinney]

I'm not logged into the system to try it, but I believe your where statement needs to read:

ACD=$acd AND SPLIT = [Skills1:] AND ROW_DATE = [Date:] OR SPLIT = [Skills2:] AND ROW_DATE = [Date:] GROUP BY SPLIT, ROW_DATE




- Stinney

Quoting only proves you know how to cut and paste.

 

[chrisaroundtown]

Thanks Stinney. It works well.

Looks like the placement of the OR statement is the important piece. Kind of like there are brackets on the criteria either side although I;ve noticed CMS doesn;t do well with brackets in the WHERE clause.

Thanks again.

Chris

 

[Stinney]

Yeah, CMS' query interface is not the friendliest. It definitely doesn't let you do the more complicated queries you can create using other software (even MS Access).

If you find yourself in a situtation where you want to do more complex queries, look into using the ODBC drivers for connecting to the historical databases directly.

- Stinney

Quoting only proves you know how to cut and paste.