• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Reporting with questionnaires answers
#1
Hi all,
How can I make a reporting witk the result of a questionnaire (at least 2 questions).
I user this:
Code:
({# FILTER_DATE ('SD_REQUEST.SUBMIT_DATE_UT', 'WEEK') #}) AND (( (SD_QUESTION.QUESTION_$lng in ('Type de contrat', 'Demande')) ))

But in my result I have one row for each question so 2 lines for one demand.

Can somone help?

Thumbnail(s)
   
Jkonan, proud to be a member of EV CONNECT FORUM since May 2019.

#2
(12-27-2019, 10:07 AM)Jkonan Wrote: Hi all,
How can I make a reporting witk the result of a questionnaire (at least 2 questions).
I user this:
Code:
({# FILTER_DATE ('SD_REQUEST.SUBMIT_DATE_UT', 'WEEK') #}) AND (( (SD_QUESTION.QUESTION_$lng in ('Type de contrat', 'Demande')) ))

But in my result I have one row for each question so 2 lines for one demand.

Can somone help?

What i do is creating a SQL view with multiple left outer join :

exemple :

Code:
CREATE VIEW [50005].[E_xxxxx]
AS
SELECT
r.request_id as "request_id",
r.rfc_number as "NumDossier",
r.creation_date_ut as "DateCreation",
e.last_name as "Demandeur",
c.sd_catalog_path_fr as "Sujet",
q1.RESULT as "NomFournisseur",
q2.RESULT as "NomDeRecherche"

FROM sd_request as r
inner join sd_catalog_path as c on r.sd_catalog_id=c.sd_catalog_id
inner join am_employee as e on r.requestor_id=e.employee_id
left outer join SD_QUESTION_RESULT q1 on r.REQUEST_ID = q1.REQUEST_ID and q1.QUESTION_ID=(select QUESTION_ID from SD_QUESTION where QUESTION_CODE = 'xxx018')
left outer join SD_QUESTION_RESULT q2 on r.REQUEST_ID = q2.REQUEST_ID and q2.QUESTION_ID=(select QUESTION_ID from SD_QUESTION where QUESTION_CODE = 'xxx037')

Then create a new parent request linked with SQL view

P.ABBE
ABBE Philippe, proud to be a member of EV CONNECT FORUM since Nov 2015.






Users browsing this thread: 1 Guest(s)