We help IT Professionals succeed at work.
Get Started

Why is Visual Studio SRS Reporting yielding invalid results vs. running the query directly against SQL Management Studio.

499 Views
Last Modified: 2013-11-15
I have a query developed that I am using in a report against Project Server data.  When initially developing the query I run it via SQL Management Studio against the Reporting database and everything runs as expected yielding correct results.  When I put the same query into Visual Studio and replace standard date fields (ex: '8/1/09') with a data parameter field (ex: @LastWkStartSat) it yields incorrect results.  Unfortunately a column that uses the same parameters yields correct results.  And even the column that is yielding mostly invalid results has a few values that are accurate.  I'm completely at a loss, but this is causing the report to be unreliable.  Any assistance is greatly appreciated!

p.s. I'm including the section of SQL that is creating the columns I am referring to.  The LastWkHrs column is returning correct results.  The LastWkPlnHrs is returning WRONG results.  The Avg8WkHrs is returning correct results.  The commented out sections are where I just use the dates hardcoded in place of the SRS parameters.
(SELECT ProjectUID, SUM(AssignmentActualWork) AS LastWkHrs
  FROM          MSP_EpmAssignmentByDay AS AssnDly
  WHERE      (TimeByDay BETWEEN @LastWkStartSat AND @LastWkEndFri)
  --WHERE      (TimeByDay BETWEEN '8/15/09' AND '8/21/09')
  GROUP BY ProjectUID
  HAVING      (SUM(AssignmentActualWork) > 0)) AS lwact ON lwact.ProjectUID = proj.ProjectUID LEFT OUTER JOIN
(SELECT     ProjectUID, SUM(AssignmentBaselineWork) AS LastWkPlnHrs
  FROM          MSP_EpmAssignmentBaselineByDay AS AssnBslDly
  WHERE      (TimeByDay BETWEEN @LastWkStartSat AND @LastWkEndFri)
  -- WHERE      (TimeByDay BETWEEN '8/15/09' AND '8/21/09') and BaselineNumber = 0
  GROUP BY ProjectUID
  HAVING      (SUM(AssignmentBaselineWork) > 0)) AS lwpln ON lwpln.ProjectUID = proj.ProjectUID LEFT OUTER JOIN
(SELECT     ProjectUID, (SUM(AssignmentActualWork)/(8.0)) AS Avg8WkHrs
  FROM          MSP_EpmAssignmentByDay AS AssnDly
  WHERE      (TimeByDay BETWEEN @Last8Wks AND @LastWkEndFri)
   --WHERE      (TimeByDay BETWEEN '6/27/09' AND '8/21/09')
  GROUP BY ProjectUID
  HAVING      (SUM(AssignmentActualWork) > 0)) AS lwavg ON lwavg.ProjectUID = proj.ProjectUID

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE