I have created three views on the SQL server 2005 database with following fields:

 

1. inst_view

Inst_code      Inst_desc

BNIT               Institution name

GCIT              Institution name

2. Inst_faculty_view

Faculty_code   Inst_code    Faculty_desc

ABS                  BNIT            Advanced Building skills...

AE                    GCIT           Childhood Stdudies

 

3. Inst_faculty_fund_view

fund_code                  Inst_code    Fund_desc                               xtra_code

0708_BR_ABS             BNIT             0708_BR_Advanced Building...      1015

0708FNY_AE_ADL_D   GCIT             0708FNY_Access Education......     4050

 

The requirement of my report is to group by Faculty in the layout like:

 

ABS     Advanced Building skills

          0708_BR_ABS                0708_BR_Advanced building skills    

          xxxxx_xx_ABS                xxxx_xx_Advanced building skills    

 

AE      Childhood Studies

           0708FNY_AE_ADL_D      0708FNY_Access Education...   

           xxxxxxxx_AE_XXXXX       xxxxxxxx_Access Education...

 

Note: All the fund code (part of the code) must match Faculty code. However there is no direct link between them except inst code. And there are same faculty codes as well as different lengh of code across all the institutions. I would like to find a way to group all the fund code under the faculty with specified institution.

I start with dynamic cascading parameter picklist based on the above three views. In the record selection I put some thing like:

faculty_code= right(fund_code, 3).

The result was not satisfactory: the records with 3 charcters length of fund_code are displayed in the report, while those the 2 characters length are not shown.

I would appreciate if anyone could advise on the above

      

 

0708FNY_AE_ADL_D ( This is one "AE" is in the middle of the string, while "ABS" is  at the end of the string as the above my examples shown 0708_BR_ABS). It seemed that I cannot use 'like' in compairing as:

faculty_code like '%fund_code'

any further help would be greatly appreciated.

I'm very sorry to reply so late because I was busy doing other tasks assigned.

There is already a link between faculty view and fund view on the Inst code. However when I select an institution(parameter) then select more  than one faculty assoicated with that institution, the fundcodes are displayed in mixed  manner, e.g

 

ABS   Advanced Building Skills

                                                   0708_BR_ABS        (this belongs to inst A)

                                                   0708_BN_ABS        (this belongs to inst B)

with the above I used "faculty_code= right(fund_code, 3)" in record selection

However, some institutions have a faculty code with only 2 characters, and what is more, the faculty code being searched is not alway at the end of that fund code string(which is not very standard way of entering date from data entry staff, I cannot do anything about it).