SQL Data Formatting

I need to display some data in a layout similar to the hardcoded excel layout. I can't seem to figure it out. I need 0's used as placeholders, as well.

I've attached an image of the excel layout.
Capture.PNG
LVL 1
LCNWAsked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Looks like you'll need a Matrix.  In rough lines here's how that would work.  I'll assume your dataset contains the following fields:

Year
Category (your A/B/C... values)
Value

Add a Matrix to the design.  Put Year into Rows, Category into Columns and Value into Data.

The Value will be summed by default.  If you end up with empty cells (which is due to NULLs in your data), use an expression like the following:

=IIF(IsNothing(Sum(Fields!Value.Value)), 0, Sum(Fields!Value.Value))

Open in new window


To add the Totals column: right-click the grey top part of the right column and select Insert Column > Outside Group - Right.  Put the same expression as used for your Value into the new cell.
0
 
chaauConnect With a Mentor Commented:
You need to provide us with the information about your raw data from which you want to generate the required report
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Often the middle group is the most useful answer - that is, SQL gives a bit and Excel gives a bit, instead of trying to make SQL do something its not intended to.

Now yes, you may need placeholders.

Can you provide the query you've currently got, and the raw table as chaau asked?

Regards
  David
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
LCNWAuthor Commented:
Here's my query so far. It does what I need except it's duplicating the rows.

SELECT     DATEPART(yyyy, p.CrossClampedOn) AS Year, ISNULL(dbo.fx_OrgIsExport(ot.TransplantOrgName), 'Total') AS Facility,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Heart' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Heart,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Intestine' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Intestine,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Kidney' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Kidney,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Liver' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Liver,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Lung' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Lung,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Pancreas' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Pancreas,
                      p.CrossClampedOn
FROM         dbo.rpt_patient AS p INNER JOIN
                      dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
GROUP BY CUBE (DATEPART(yyyy, p.CrossClampedOn), dbo.fx_OrgIsExport(ot.TransplantOrgName), ot.DispositionOutcomeDesc), p.CrossClampedOn
HAVING      (DATEPART(yyyy, p.CrossClampedOn) >= 2009) AND (ot.DispositionOutcomeDesc = N'Transplanted')
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Do you have some test data that produces the duplicate rows? Please post as create table & insert statements.

Regards
  David
0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Just wondering (as you included the MS SQL Reporting topic), are you using SQL Server Reporting Services? (that's what my answer assumed)
0
 
LCNWAuthor Commented:
No. I'm using SQL but then a different product on the front end. It requires data to be formatted as much as possible before being used.
0
 
LCNWAuthor Commented:
To clarify, I need to show 0's and I need a Totals row for each year.

I'm having trouble explaining this. Let me try to simplify it. I run this query.

SELECT DATEPART(yyyy, p.CrossClampedOn) AS Year, ISNULL(dbo.fx_OrgIsExport(ot.TransplantOrgName), 'Total') AS Facility,
                      ISNULL(SUM(CASE WHEN dbo.fx_Organ_SimpleName(ot.OrganName) = 'Heart' THEN dbo.fx_Organ_Count(ot.OrganName) END), 0) AS Heart
                                         
FROM         dbo.rpt_patient AS p INNER JOIN
                      dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
GROUP BY CUBE (DATEPART(yyyy, p.CrossClampedOn), dbo.fx_OrgIsExport(ot.TransplantOrgName), ot.DispositionOutcomeDesc)
HAVING      (DATEPART(yyyy, p.CrossClampedOn) >= 2009) AND (ot.DispositionOutcomeDesc = N'Transplanted')

My results are in the attached image.

I would like to somehow further list a field called p.CrossClampedOn for each SUM of the hearts. So a subset of data.

When trying to add it to the group by of my original query, I would receive duplicates of each row. I could then make a view off of that and group by all columns to display the correct information.

I would like to achieve this all in one query, if possible. I thinking maybe I need to do a subquery in the FROM line somehow.

Thanks.
Results.PNG
0
 
LCNWAuthor Commented:
I've replaced everything with a PIVOT and now it works. Sorry I couldn't explain it better.

Here's my PIVOT:



WITH OrgansByTxC AS (
      SELECT
            p.CrossClampedOn,
            DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
            dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
            dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
            dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
            ot.DispositionOutcomeDesc AS 'Outcome'
            
      FROM
            LifeCenterReporting.dbo.rpt_patient p
      INNER JOIN
            LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
)
SELECT  p.CrossClampedOn, DATEPART(yyyy,p.CrossClampedOn) AS 'Year',Facility,
            ISNULL(Heart,0) AS 'Heart'
         ,ISNULL(Intestine,0) AS 'Intestine'
         ,ISNULL(Kidney,0) AS 'Kidney'
         ,ISNULL(Liver,0) AS 'Liver'
         ,ISNULL(Lung,0) AS 'Lung'
         ,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
      OrgansByTxC
      PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted'
;
0
 
LCNWAuthor Commented:
Thanks for helping me through the thought process.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.