locked
column change on group by RRS feed

  • Question

  • User-914991384 posted

    I have a field called staff type in db it can hold three values(1,2,3) like 1=Employee or 2=Contractor or 3=Consultant.

    I can group by staff name and filter value by condition like if stafftype = 1 then 'employee'  that will appear on group by name. but here depend on the staff type column name differs.

    If staff type = 3 not there in db it should not show the Consultant records at all.

    It should show report like this.

    Employee

    Name    Dept    Age      HireDate         Grade

    ab        fin        hp       nov-2-2010      5 

    cd         hr       ibm      dec-2-2011      6  

    Contractor

    Name    Company    Dept            Status

    ab         hp               IT             Active

    cd         ibm              Finance      InActive

    Consultant

    Name    Dept    Vendor   HireDate            Phone

    ab        fin        hp         nov-2-2010        6563557

    cd          hr       ibm        dec-2-2011        8899899

    Please let me know how can i acheive this in crystal report

     

    Thursday, February 16, 2012 2:07 AM

All replies

  • User1829879277 posted

    You need to run three queries separately based on the type.

    Thursday, February 16, 2012 2:21 AM
  • User-914991384 posted

    please explain in detail

    Thursday, February 16, 2012 2:33 AM
  • User1829879277 posted

    Write a SP to run one of the three queries depending on the parameter passed.

    For example:

    IF (@param == 1) -- Employee

      BEGIN

        SELECT Name, Dept, Age, HireDate, Grade FROM [Table_Name] WHERE StaffType = 1

      END

    ELSE IF (@param == 2) -- Contractor

      BEGIN

        SELECT Name, Company, Dept, Status FROM [Table_Name] WHERE StaffType = 2

      END

    ELSE IF (@param == 3) -- Consultant

      BEGIN

        SELECT Name, Dept, Vendor, HireDate, Phone FROM [Table_Name] WHERE StaffType = 3

      END

    Thursday, February 16, 2012 3:32 AM
  • User-914991384 posted

    my SP return all the columns of the three staff types as Database fields so drag in to reports.

    but how do i design like that?

     

    Thursday, February 16, 2012 3:37 AM
  • User1829879277 posted

    Do you want to fetch all fields from the DataBase but display only particular fields based on type?

    Are you using Reports or GridView or any other control to show the output?

    Thursday, February 16, 2012 4:24 AM