?
Solved

SQL Data Formatting

Posted on 2013-11-19
10
Medium Priority
?
274 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 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

807 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