Solved

SQL Data Formatting

Posted on 2013-11-19
10
242 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS Access Remotely 5 55
Report Builder 3.0 3 26
Upgrading Integration Services 3 28
SSRS Dynamic Column Names - Last 8 Weeks 3 13
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

22 Experts available now in Live!

Get 1:1 Help Now