We help IT Professionals succeed at work.
Get Started

Trying to correct a SQL query for SSRS report

161 Views
Last Modified: 2016-03-08
I have a query the produces this report in SSRS that I have attached.

SSRS Report
I am trying to bring in cc.valid_to so that I can change the expressions for a conditional formatting to reference against that but when I do it via SELECT it changes the results in the cell and does not show the "cell_value" for several records. How can I bring it in without affecting anything else?

`/******************************
** Obje: Query 
** Name: MSSC_StudentsProgressReport
** Desc: SQL Query to retrieve a list of contact and category records where the category is one of the following: 
**       'EDPROB','EDASSG','EDEXPX','EDEXPG','EDTGGD'  
**       This query will be used in the (SSRS) Students Progress Report
** Auth: Tunde Shaba (TS) - Advanced Business Solution
** Date: 28/07/2015
**************************
** Change History
**************************
** PR   Date        Author  Description 
** --   --------   -------  --------------------------------------
** 1    28/07/2015 TS       Query created and used in Students Progress Report v1
** 2    11/09/2015 TS       Query updated to allow table headers to be ordered as per PCR 001 and used in Students Progress Report v2.2
** 3    14/09/2015 TS       Query updated as per mail from Jayson to display expected grades under a single column and target grades under a single column (v2.3)
*******************************/

SELECT DISTINCT
     c.label_name
    ,cc.contact_number
    ,av.activity
    ,av.activity_value
    ,a.activity_desc
    ,activity_value_desc=--PR3: av.activity_value_desc
                        CASE
                            WHEN av.activity IN ('EDEXPG','EDTGGD') THEN 'Grade'
                            ELSE av.activity_value_desc
                        END
    ,cell_value=
                CASE 
                    WHEN cc.activity = av.activity AND cc.activity_value = av.activity_value AND cc.activity IN ('EDEXPG', 'EDTGGD') THEN cc.activity_value 
                    WHEN cc.activity = av.activity AND cc.activity_value = av.activity_value AND cc.activity NOT IN ('EDEXPG', 'EDTGGD') THEN CONVERT(VARCHAR(10),cc.activity_date,126)
                    ELSE NULL
                END
    , activity_sort=CASE av.activity --PR2: Used in report to sort activities column order on the report
                                        WHEN 'EDPROB' THEN 1
                                        WHEN 'EDASSG' THEN 2
                                        WHEN 'EDEXPX' THEN 3
                                        WHEN 'EDEXPG' THEN 4
                                        WHEN 'EDTGGD' THEN 5
                                    END
    ,activity_value_sort=CASE --PR2: Used in report to sort activity values column order on the report
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'ENQY' THEN 1
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'DIAG' THEN 2
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'INDC' THEN 3
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'PLNG' THEN 4
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'PRCS' THEN 5
                         END
FROM contact_categories cc
    INNER JOIN contacts c ON cc.contact_number = c.contact_number
    INNER JOIN activities a ON a.activity = cc.activity
        AND a.activity IN ('EDPROB','EDASSG','EDEXPX','EDEXPG','EDTGGD')
CROSS JOIN activity_values av --PR2: MSSC want the report to display all activity value column headers

WHERE av.activity IN ('EDPROB','EDASSG','EDEXPX','EDEXPG','EDTGGD')
    AND cc.valid_from <= CONVERT(DATE, GETDATE())
    AND cc.valid_to >= CONVERT(DATE, GETDATE())
ORDER BY 7 DESC, 5, 6`

Open in new window

Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE