Hi Experts,
I need to have a report break AFTER the PathwayId changes. I
have used 2 queries/reports to get the desired results:
For the Header/Footer of the report which shows the Title,
Description, Date and Score of each Pathway I used:
SELECT DISTINCT r.PathwayId, p.Title, p.Description, s.Score, CONVERT(Varchar(10), s.CreateDate, 101) AS [Date]
FROM ucare.Responses r
INNER JOIN ucare.RiskScores
s ON s.ScheduleId = r.ScheduleId
INNER JOIN ucare.Pathways p ON p.Id = r.PathwayId
WHERE r.ScheduleId = @Enc AND PathwayId IN (Select DISTINCT PathwayId FROM ucare.Responses
WHERE r.ScheduleId = @Enc )and Score = (SELECT MAX(Score) FROM ucare.RiskScores
WHERE ScheduleId = @Enc)
GROUP BY r.PathwayId, p.Title, p.Description, s.Score, s.CreateDate
ORDER BY r.PathwayId, p.Title
For the Detail section Sub-Report that will have the Questions and Answers per each Pathway I have used:
SELECT PathwayId, d.Label AS Question,
CASE WHEN Text = 'False' THEN 'No'
WHEN Text = 'True' THEN 'Yes'
ELSE Text END AS Answer, CONVERT(Varchar(10), EntryDate, 101) AS Entered
FROM ( SELECT p.PathwayId, p.Text, p.EntryDate, p.DecisionInputId,
MAX(p.EntryDate) over (PARTITION BY p.PathwayId) AS Entered
FROM ucare.Responses p
WHERE p.ScheduleId = @Enc) t
INNER JOIN ucare.DecisionInputs
d ON d.Id = t.DecisionInputId
WHERE EntryDate = Entered
ORDER BY PathwayId
I’ve placed this report in the detail section with approximately 50 other sub-reports linked with the report parameters of @CaseNum and/or @Enc.
The diagram below is showing what I
need this report to output:
Pathway_1_Title Date_Entered
Question_1 Answer_1
Question_2 Answer_2
Question_3 Answer_3
Pathway_1_Description Score
Pathway_2_Title Date_Entered
Question_1 Answer_1
Question_2 Answer_2
Question_3 Answer_3
Pathway_2_Description Score
Pathway_3_Title Date_Entered
Question_1 Answer_1
Question_2 Answer_2
Question_3 Answer_3
Pathway_3_Description Score
Any help or guidance will be appreciated. If you need anymore info please do not hesitate to ask.
Thank you,
Jack Willhoite