Multiple values in a row

Hi, 
 
I'm still fumbling my way around e.Report Designer 8 SP1 (due to upgrade to 10 in a month or two) and am trying the following... 
 
I want a report that displays each client's details in a row like this (I've also listed the columns that are to appear): 
 
ID | Name | Activity | Plan

ASH_SUBJECT_ID | ASH_SUBJECT_NAME | AV_QUE_VALUE | AV_QUE_VALUE 
 
The SQL currently looks like this (it includes other things I want to report on, but aren't relevant to this post): 
 
SELECT ash_assessment_type, ash_subject_id, ash_subject_name, ash_team_name, asm_completed_by, asm_complete_datetime, av_que_value, per_id, per_first_names||' ' ||per_surname 
 
FROM o_assessment_header, o_assessments, o_avd_reporting_view, o_persons 
 
WHERE o_assessment_header.ash_asm_id = o_avd_reporting_view.av_asm_id 
 
AND o_assessment_header.ash_asm_id = o_assessments.asm_id 
AND o_assessments.asm_completed_by = o_persons.per_id 
AND (av_qst_id = 'J8526' OR av_qst_id = 'J8533') 
AND av_que_value IS NOT NULL 
AND asm_complete_datetime IS NOT NULL
 
How can I get the report to show the AV_QUE_VALUE of J8526 under Activity, and the AV_QUE_VALUE of J8532 under Plan? I was hoping something simple in the MyQuery or the expression builder could cope. 
 
Apologies if this is the wrong forum, or my question unbelievably simple.

Find more posts tagged with

Comments

Hans_vd

Hi LizBPRD, 
 
You have to select from the table that contains the column av_qst_id twice. 
So you add that same table to the from line a second time (use aliases), then join it to all the other tables and use one alias in the where clause to select 'J8526' and one alias to select 'J8533'. 
 
As I don't know your datamodel, here's a quick dummy example: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT dt1.select_field, dt2.select_field
FROM dummy_header dh, dummy_tab dt1, dummy_tab dt2
WHERE dh.join_field = dt1.join_field
AND dh.join_filed = dt2.join_field
AND dt1.av_qst_id = 'J8526'
AND dt2.av_qst_id = 'J8533'</pre>
 
Hope this helps 
Hans

hcy1986

Or like this:
[sql]SELECT ash_assessment_type, ash_subject_id, ash_subject_name, ash_team_name, asm_completed_by, asm_complete_datetime,
case when av_qst_id = 'J8526' then av_que_value end as Activity,
case when av_qst_id = 'J8533' then av_que_value end as Plan,
per_id, per_first_names||' ' ||per_surname

FROM o_assessment_header, o_assessments, o_avd_reporting_view, o_persons

WHERE o_assessment_header.ash_asm_id = o_avd_reporting_view.av_asm_id

AND o_assessment_header.ash_asm_id = o_assessments.asm_id
AND o_assessments.asm_completed_by = o_persons.per_id
AND (av_qst_id = 'J8526' OR av_qst_id = 'J8533')
AND av_que_value IS NOT NULL
AND asm_complete_datetime IS NOT NULL[/sql]

Hans_vd

hcy1986,

Your query will not give the desired result unless you add a GROUP BY clause.

Regards
Hans

LizBPRD

Thanks everyone, my report finally works! Now people will no doubt be asking for more reports.