Solved

SQL Data Formatting

Posted on 2013-11-19
10
239 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 100 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 200 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 200 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
 
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 200 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 200 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
using t-sql EXISTS 8 23
GRANT, REVOKE, DENY 4 20
SQL Server Reporting Services Service Start Timeout 4 21
Sql query 34 22
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now