SSRS: Lookups Needed....Or, what is the remedy to eliminate the duplication?

Hello:

Attached is the screenshot of my matrix SSRS report.  There is duplication of vendor IDs.  The reason that this is happening is because I had to use a UNION ALL statement for my main report.  Some of the vendors come from one "area" of the UNION ALL statement, while the others come from other "areas" of the statement.

Conversely, although my subreport has a UNION ALL statement, it is only pulling from one "area".

OK.  The long and the short of it is that, when I say "area", I mean "database".  It's a long story.

In any case, someone once mentioned to me the possibility of using something in SSRS called "lookups" or "lookup values" or something.

In any case, I need to get rid of these duplicate vendors.  Otherwise, I guess I'll just have to tell the client that we can only pull from one, umm, "area".  :)

Thanks!  Much appreciated!

TBSupport
POOR.docx
POOR.rdl
POOR-Subreport.rdl
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Would using UNION instead of UNION ALL in this help?:

--   <DataSet Name="POOR_Vendors">
select VENDORID
from POP10100 where VENDORID IS NOT NULL
UNION
select VENDORID
from RCG.dbo.POContractHeader where VENDORID IS NOT NULL

-- & if using UNION you won't need 'distinct'

by the way; why "poor vendors", are they sad? without funds?

All of the following is of course untested, but I've tried to implement some suggestions in the queries. A couple of points first however:
You are using a good method for your date ranges - but please take care with the parentheses - they are working now and would continue to unless you introduce an OR in the filtering - then you could have unexpected results. The date range should be coupled together by parentheses to ensure they are evaluated together.
I :think: you could replace the largish case expression for column [Month] with a simpler concatenation
The largest of the queries "POOR_Matrix" I :think: could be nested and then apply a group by over that to simplify the results. I have also tried to replaced that case expression by a concatenation but that woud need to be verified. Along with that replacement I've substituted a date range filter on POP10110.REQDATE
Please refer to comments within the SQL below. Oh, and I had to replace &gt; with > and &lt; with < but don't think that will be a problem.
 -- #####################################################################################################################        

--<DataSet Name="POOR_RCG">
SELECT
        POContractHeader.VENDORID
      , POContractHeader.ContractNum   AS ContractNo
      , SUM(POContractDetail.EXTDCOST) AS Total
      , 'Month ' + convert(varchar,( DATEPART(m,POContractHeader.DOCDATE) - DATEPART(m, GETDATE()) +1 )) AS [Month]
      -- change from case expression to concatenation

FROM POContractHeader
INNER JOIN POContractDetail
        ON POContractHeader.ContractNum = POContractDetail.ContractNum
LEFT OUTER JOIN PODraws
        ON POContractHeader.ContractNum = PODraws.ContractNum
WHERE (     POContractHeader.DOCDATE >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
        AND POContractHeader.DOCDATE  < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 18, 0)
      )
-- change to parenthese on date range
GROUP BY
         POContractHeader.VENDORID
       , POContractHeader.ContractNum
       , POContractHeader.DOCDATE
ORDER BY
        ContractNo
;
 -- #####################################################################################################################        
 --    <DataSet Name="POOR_RCG_DETAIL">
SELECT DISTINCT
        PODraws.PONUMBER
      , POContractHeader.ContractNum
      , POContractHeader.DOCDATE
      , PODraws.DateReq
      , POContractDetail.VENDORID
      , POContractDetail.ITEMNMBR
      , POContractDetail.ITEMDESC
      , PODraws.OrderQty
      , POContractDetail.UofM
      , POContractDetail.UNITCOST
      , POContractDetail.EXTDCOST
      , POContractDetail.LOCNCODE
FROM POContractDetail
INNER JOIN POContractHeader
        ON POContractDetail.ContractNum = POContractHeader.ContractNum
INNER JOIN PODraws
        ON POContractDetail.ContractNum = PODraws.ContractNum
WHERE (     POContractHeader.DOCDATE >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
        AND POContractHeader.DOCDATE  < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 18, 0)
      )
-- change to parenthese on date range
;
 
 -- #####################################################################################################################        

--    <DataSet Name="POOR_Matrix">
SELECT
        VendorID
      , SUM(Total)    AS Total
      , Month
      , SUM(PastDue)  as PastDue
      , SUM(Released) AS Released
FROM (
        SELECT
                POP10100.VENDORID      AS VendorID
              , SUM(POP10100.SUBTOTAL) AS Total
              , 'Month ' + convert(varchar,( DATEPART(m,POP10110.REQDATE) - DATEPART(m, GETDATE()) +1 )) AS [Month]
              -- change from case expression to concatenation
        
              , 0                      AS PastDue
              , 0                      AS Released
        FROM POP10100
        INNER JOIN POP10110
                ON POP10100.PONUMBER = POP10110.PONUMBER AND POP10100.POTYPE = POP10110.POTYPE
        WHERE (POP10100.POSTATUS IN (1, 2, 3)
          AND (     POP10110.REQDATE >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0))
                AND POP10110.REQDATE  < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 18, 0)
              )
        -- replace case expression with date range
        
                AND DATEPART(yy, POP10110.REQDATE) = DATEPART(yy, GETDATE()))
        -- BUT, this existing limits possibles to current year only ???
        
                AND POP10100.PONUMBER NOT LIKE 'DRW%'
                AND POP10100.VENDORID IN (@Vendor)
        GROUP BY
                 POP10100.VENDORID
               , POP10110.REQDATE
        HAVING SUM(POP10100.SUBTOTAL) <> 0

UNION ALL

        SELECT
                RCG.dbo.POContractHeader.VENDORID      AS VendorID
              , SUM(RCG.dbo.POContractDetail.EXTDCOST) AS
                Total
              , 'Month ' + convert(varchar,( DATEPART(m,RCG.dbo.PODraws.DateReq) - DATEPART(m, GETDATE()) +1 )) AS [Month]
              -- change from case expression to concatenation

              , 0                                      AS PastDue
              , 0                                      AS Released
        FROM RCG.dbo.POContractHeader
        INNER JOIN RCG.dbo.POContractDetail
                ON RCG.dbo.POContractHeader.ContractNum = RCG.dbo.POContractDetail.ContractNum
        LEFT OUTER JOIN RCG.dbo.PODraws
                ON RCG.dbo.POContractHeader.ContractNum = RCG.dbo.PODraws.ContractNum
        WHERE (
                    RCG.dbo.PODraws.DateReq >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
                AND RCG.dbo.PODraws.DateReq <  DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 18, 0)
               )
                -- change to parenthese on date range
                AND RCG.dbo.POContractHeader.VENDORID IN (@Vendor)
        GROUP BY
                 RCG.dbo.POContractHeader.VENDORID
               , RCG.dbo.PODraws.DateReq
        HAVING SUM(RCG.dbo.POContractDetail.EXTDCOST) > 0

UNION ALL

        SELECT
                POP10100.VENDORID      AS VendorID
              , 0                      AS Total
              , ''                     AS Month
              , SUM(POP10100.SUBTOTAL) AS PastDue
              , 0                      AS Released
        FROM POP10100
        INNER JOIN POP10110
                ON POP10100.PONUMBER = POP10110.PONUMBER AND POP10100.POTYPE = POP10110.POTYPE
        WHERE (POP10100.POSTATUS IN (1, 2, 3)
                --AND (select CASE WHEN DATEPART(m, POP10110.REQDATE) - 
                --DATEPART(m, getdate())= 0 then 'Month 1'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 1 then 'Month 2'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 2 then 'Month 3'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 3 then 'Month 4'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 4 then 'Month 5'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 5 then 'Month 6'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 6 then 'Month 7'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 7 then 'Month 8'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 8 then 'Month 9'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 9 then 'Month 10'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 10 then 'Month 11'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 11 then 'Month 12'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 12 then 'Month 13'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 13 then 'Month 14'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 14 then 'Month 15'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 15 then 'Month 16'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 16 then 'Month 17'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 17 then 'Month 18'
                --else '' end) <> 0
                AND DATEPART(yy, POP10110.REQDATE) = DATEPART(yy, GETDATE()))
                --and POP10100.PONUMBER NOT LIKE 'DRW%'
                AND POP10100.VENDORID IN (@Vendor)
        GROUP BY POP10100.VENDORID
               , POP10110.REQDATE
        HAVING SUM(POP10100.SUBTOTAL) <> 0
        AND DATEPART(m, POP10110.REQDATE) - DATEPART(m, GETDATE()) < 0

UNION ALL

        SELECT
                POP10100.VENDORID                               AS VendorID
              , 0                                               AS Total
              , ''                                              AS Month
              , 0                                               AS PastDue
              , SUM(POP10100.SUBTOTAL) - SUM(POP10100.OREMSUBT) AS Released
        FROM POP10100
        --INNER JOIN
        --POP10110 ON POP10100.PONUMBER = POP10110.PONUMBER AND POP10100.POTYPE = POP10110.POTYPE
        WHERE POP10100.POSTATUS = 2
                --AND (select CASE WHEN DATEPART(m, POP10110.REQDATE) - 
                --DATEPART(m, getdate())= 0 then 'Month 1'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 1 then 'Month 2'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 2 then 'Month 3'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 3 then 'Month 4'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 4 then 'Month 5'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 5 then 'Month 6'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 6 then 'Month 7'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 7 then 'Month 8'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 8 then 'Month 9'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 9 then 'Month 10'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 10 then 'Month 11'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 11 then 'Month 12'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 12 then 'Month 13'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 13 then 'Month 14'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 14 then 'Month 15'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 15 then 'Month 16'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 16 then 'Month 17'
                --WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 17 then 'Month 18'
                --else '' end) <> 0
                --and DATEPART(yy, POP10110.REQDATE) = DATEPART(yy, getdate()))
                --and POP10100.PONUMBER NOT LIKE 'DRW%'
                AND POP10100.VENDORID IN (@Vendor)
        GROUP BY POP10100.VENDORID --, POP10110.REQDATE
        HAVING SUM(POP10100.SUBTOTAL) - SUM(POP10100.OREMSUBT) <> 0
       --and DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate()) < 0
     ) AS DERIVED
GROUP BY
          VendorID
        , Month
; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
I noticed your main report has got two Row Groups: VendorID and VendorID1, both grouping on VendorID.  I don't think this is the cause of your issue but in any case you should be able to remove one of them and still have a working matrix.  (Select the "Delete group only" radio button when deleting the row group.)
0
TBSupportAuthor Commented:
Thanks, PortletPaul!  I'll try some of these things and let you know.  I apologize, for not mentioning it earlier.  But, on the main report, I'm only using the POOR_MATRIX datasets.  I kept the other previously created ones "there", in case I need them.  Thanks, for looking those over.

ValentinoV:  I need that grouping, though.  You have to have a group, in order to allow for drill-downs.  That's why I have a subreport, in that matrix.  :)

TBSupport
0
ValentinoVBI ConsultantCommented:
Ah, you're using drilldown, hadn't noticed it when I posted my comment.  In that case you're right!
0
PortletPaulfreelancerCommented:
Thanks for the grading. Trust it's all working as needed now. Cheers, Paul.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.