?
Solved

SQL Data Formatting

Posted on 2013-11-19
10
Medium Priority
?
268 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:LCNW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 300 total points
ID: 39661043
You need to provide us with the information about your raw data from which you want to generate the required report
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 600 total points
ID: 39661621
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 600 total points
ID: 39661734
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 1

Author Comment

by:LCNW
ID: 39664143
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 600 total points
ID: 39664291
Hi,

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

Regards
  David
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 600 total points
ID: 39665556
Just wondering (as you included the MS SQL Reporting topic), are you using SQL Server Reporting Services? (that's what my answer assumed)
0
 
LVL 1

Author Comment

by:LCNW
ID: 39669744
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
 
LVL 1

Author Comment

by:LCNW
ID: 39669892
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
 
LVL 1

Author Comment

by:LCNW
ID: 39670453
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
 
LVL 1

Author Closing Comment

by:LCNW
ID: 39672203
Thanks for helping me through the thought process.
0

Featured Post

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question