SSRS - How to do this sort of pivot?

I have following data when I get totals for area, group based on the open close items. The data needs to be reported as a pivot table. Area, groupnames are parameter filters , if they select just grpB, grpA should not be displayed in pivot table. I am not sure how this could be achieved. Can you please give me pointers how to resolve this?

area_name group_nameQuarter openclosed all

---------------------------------------------------------------

xx grpA 1 2 35

xx grpB 1 4 26

yy grpA 1 1 12

yy grpb 1 2 13

zz grpA 1 3 14

zz grpb 1 1 12



xx grpA 2 2 24

xx grpB 2 4 15

yy grpA 2 1 12

yy grpb 2 1 12

zz grpA 2 2 24

zz grpb 2 3 15











area_name QuartergrpA grpBtotal

open closeopen close

---------------------------------------------------------------------------





xx 1 23 4 2 11

yy 1 11 2 1 4

zz 1 31 1 1 6

---------------------------------------------------------------------

grand totals 16 4 7 4 21





xx 2 22 4 1 9

yy 2 11 1 1 4

zz 2 22 3 1 8

---------------------------------------------------------------------

grand totals 25 5 8 3 21

SSRS - How to do this sort of pivot?

Perhaps reading this article you get some idea

http://ift.tt/1CcjEy7

---

Best Regards,Uri Dimant SQL Server MVP, http://ift.tt/1iQ9JkR



MS SQL optimization: MS SQL Development and Optimization

MS SQL Consulting: Large scale of database and data cleansing

Remote DBA Services: Improves MS SQL Database Performance

SQL Server Integration Services: Business Intelligence

SharePoint View - 3pm yesterday to 3pm Today - Need Help

Hi,

Hope the below blogs help you

http://ift.tt/1CcjExW

---

Please remember to click 'Mark as Answer' on the answer if it helps you

.NET

You got your checkbook ready?

SCD TYPE 2 LOGIC

In addition to Vaibhah answer, Maybe you should put all THE restrictions in the "on" Clause. This way there Will only be an insert when All 4 fields in THE on clause do not match.

Only when matched normally you do an update. My advice would-be be if THE id is matched always do an update of THE remainng fields except id and an insert when THE id is not in THE target table.

SEe link for an example:

http://ift.tt/1vMP7Vh

CREATE TABLE #EMP1(
        [id] [int] IDENTITY(1,1) NOT NULL,
        [fname] [varchar](25) NULL,
        [lname] [varchar](25) NULL
)

CREATE TABLE #EMP2(
        [id] [int] IDENTITY(1,1) NOT NULL,
        [fname] [varchar](25) NULL,
        [lname] [varchar](25) NULL
)

INSERT INTO #EMP1 SELECT 'J','Smith'
INSERT INTO #EMP2 SELECT 'John','Smithson'

MERGE INTO #EMP1 AS TGT
USING #EMP2 AS SRC
        ON TGT.ID=SRC.ID
WHEN MATCHED THEN
        UPDATE SET
        TGT.FNAME=SRC.FNAME,
        TGT.LNAME=SRC.LNAME
WHEN NOT MATCHED THEN
        INSERT (FNAME,LNAME)
        VALUES(SRC.FNAME,SRC.LNAME);

Regards,

Reshma

---

Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered

How to get table result from XML

Hi I am new to XML, I was playing with node, but wasn't successfull

I have XML fragment

DECLARE @XML xml =

'
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
    <QUALF>002</QUALF>
    <IDTNR>A1</IDTNR>
    <KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
  </E1EDP19>
   <E1EDP19 SEGMENT="1">
    <QUALF>003</QUALF>
    <IDTNR>E1</IDTNR>
  </E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
    <QUALF>002</QUALF>
    <IDTNR>A2</IDTNR>
    <KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
  </E1EDP19>
   <E1EDP19 SEGMENT="1">
    <QUALF>003</QUALF>
    <IDTNR>E2</IDTNR>
  </E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
    <QUALF>002</QUALF>
    <IDTNR>A3</IDTNR>
    <KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
  </E1EDP19>
   <E1EDP19 SEGMENT="1">
    <QUALF>003</QUALF>
    <IDTNR>E3</IDTNR>
  </E1EDP19>
</E1EDP01>'

and I need to create query, which list all products and his two codes, result should be like this

ProductCode1	ProductCode2
A1	E1
A2	E2
A3	E3

It is visible from XML fragment, how those values are organised in segments......

I tried to use

select  PC1.query('./text()')

    AS PC1,

PC2.query('./text()')

    AS PC2

from @XML.nodes('/E1EDP01/E1EDP19[1]/IDTNR') as PC1(PC1)

CROSS APPLY @XML.nodes('/E1EDP01/E1EDP19[2]/IDTNR') as PC2(PC2)

But it gets all combinations of ProductCode1 and PRoductCode2, somtehing like cross join and result has 9 rows...

Could you help please

How to get table result from XML

Try this,

DECLARE @XML xml ='
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
    <QUALF>002</QUALF>
    <IDTNR>A1</IDTNR>
    <KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
  </E1EDP19>
   <E1EDP19 SEGMENT="1">
    <QUALF>003</QUALF>
    <IDTNR>E1</IDTNR>
  </E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
    <QUALF>002</QUALF>
    <IDTNR>A2</IDTNR>
    <KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
  </E1EDP19>
   <E1EDP19 SEGMENT="1">
    <QUALF>003</QUALF>
    <IDTNR>E2</IDTNR>
  </E1EDP19>
</E1EDP01>
<E1EDP01 SEGMENT="1">
<E1EDP19 SEGMENT="1">
    <QUALF>002</QUALF>
    <IDTNR>A3</IDTNR>
    <KTEXT>PRF NUDE MAGIQUE BB MEDIUM M-UP TST</KTEXT>
  </E1EDP19>
   <E1EDP19 SEGMENT="1">
    <QUALF>003</QUALF>
    <IDTNR>E3</IDTNR>
  </E1EDP19>
</E1EDP01>'
SET @XML = '<ROOT>' + CAST(@XML AS NVARCHAR(MAX)) + '</ROOT>'
SET @XML = CAST(@XML AS XML)

;WITH CTE AS (
        select t.u.value ('QUALF[1]','varchar(10)') as QUALF,
        t.u.value ('IDTNR[1]','varchar(30)') as IDTNR,
        t.u.value ('KTEXT[1]','varchar(30)') as KTEXT,
        t.u.value ('..','varchar(30)') as DESCR
        from @XML.nodes('/ROOT/E1EDP01/E1EDP19') t(u)
)

SELECT [002] AS ProductCode1,[003] AS ProductCode2 FROM (SELECT QUALF,IDTNR,DESCR FROM CTE) X
PIVOT(MAX(IDTNR) FOR QUALF IN ([002],[003])) PVT

---

Regards, RSingh