CODEWITHSUNDEEP
sqlreporting-services
Jay C
Jay C

Reputation: 872

Query working in SSMS but not in SSRS?

I am using SQL Server 2012 Visual Studio 2010.

I've written the following query

SELECT  g.membercode, l.Value as AccountNum, ll.Value as Custodian, MAX(r.positiondate) as PositionDate
  FROM [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] g
  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPositionRecon] r
  ON g.MemberID = r.PortfolioID
  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseCustomLabels] l
  on g.MemberID = l.PortfolioBaseID
    LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseCustomLabels] ll
  on g.MemberID = ll.PortfolioBaseID
  Where g.portfoliogroupcode = 'open'
  and PositionDate is NULL
  and l.label = '$numname'
  and ll.label = '$custdn'
  and ll.Value not in ('mbt', 'whittier')
  group by r.portfoliocode, g.membercode, l.Value, ll.Value
  order by ll.Value, g.MemberCode

In SQL Server Management Studio I get the following desired result set

membercode  AccountNum  Custodian   PositionDate
account1    123456      mssb       NULL
account2    78910       mssb       NULL
account3    11121314    mssb       NULL
account4    151617018   mssb       NULL
account5    19202122    mssb       NULL

However using the identical query in SSRS I am getting the following undesired result set where account name is showing up in the Custodian column and Member Code is 0

Member Code Account Number  Custodian   Position Date   Row Count
0           123456          account1                        1   
0           78910           account2                        2   
0           11121314        account3                        3   
0           151617018       account4                        4   
0           19202122        account5                        5

Any idea why this query works in SSMS but not SSRS?

Upvotes: 2

Views: 1159

Answers (1)

Sam
Sam

Reputation: 7303

I don't know the reason why your results are different. But I would suggest creating a sproc and using that.

Also, you may as well remove PositionDate from the select as you have the clause and PositionDate is NULL

Upvotes: 1

Related Questions