1

I've recently uncovered that SSRS is doing a bizarre aggregation and I really don't understand why. In this report I'm building, as with other SQL queries I've built, I have a tendency to take preliminary results from an initial query, throw them into a temp table, and then perform another query and joining on that temp table to get my 'final' results I need to display. Here's an example:

--1. This query fetches all available rows based on the day (must be last day of month) 

SELECT DISTINCT Salesperson ,c.Cust_Alias ,cost ,eomonth(CreateDate) createdate ,FaxNumber
INTO #equip
FROM PDICompany_2049_01.dbo.Customers c
JOIN PDICompany_2049_01.dbo.Customer_Locations cl ON c.Cust_Key = cl.CustLoc_Cust_Key
JOIN ricocustom..Equipment_OLD e ON e.FaxNumber = c.Cust_ID + '/' + cl.CustLoc_ID
JOIN PDICompany_2049_01.dbo.Charges ch ON ch.Chg_CustLoc_Key = cl.CustLoc_Key
WHERE Salesperson = @Salesperson
   AND ch.Chg_Balance = 0

--2. This query fetches first result set, but filters further for matching date variable 

SELECT DISTINCT (cost) EquipCost ,Salesperson ,DATEPART(YEAR, CreateDate) YEAR
   ,DATEPART(MONTH, CreateDate) MONTH ,Cust_Alias ,FaxNumber
INTO #equipcost
FROM #equip
WHERE Salesperson = @Salesperson
   AND DATEPART(MONTH, CreateDate) = DATEPART(MONTH, @Start)
   AND DATEPART(year, CreateDate) = DATEPART(year, @Start)
ORDER BY Cust_Alias

--3. Finally, getting sum of the EquipCost, with other KPI's, to put into my final result set 

SELECT sum(EquipCost) EquipCost ,Salesperson ,YEAR ,MONTH ,Cust_Alias
INTO #temp_equipcost
FROM #equipcost
GROUP BY Salesperson ,year ,month ,Cust_Alias

Now I am aware that I could have easily reduced this to 2 queries instead of 3 in hindsight (and I have since gotten my results into a single query). But that's where I'm looking for the answer. In my GUI report, I had a row that was showing to have 180 for equipcost, but my query was showing 60. It wasn't until I altered my query to a single iteration (as opposed to the 3), and while I'm still getting the same result of 60, it now displays 60 in my GUI report.

I actually had this happen in another query as well, where I had 2 temp table result sets, but when I condensed it into one, my GUI report worked as expected.

Any ideas on why using multiple temp tables would affect my results via the GUI report in SQL Report Builder (NOT USING VB HERE!) but my SQL query within SSMS works as expected? And to be clear, only making the change described to the query and condensing it got my results, the GUI report in Report Builder is extremely basic, so nothing crazy regarding grouping, expressions, etc.

avery_larry
  • 2,069
  • 1
  • 5
  • 17
jw11432
  • 545
  • 2
  • 20
  • without looking at your data and your report layout.. it would hard to tell. – Harry Nov 07 '19 at 22:52
  • _e.FaxNumber = c.Cust_ID + '/' + cl.CustLoc_ID_ What? How is this correct? – SMor Nov 07 '19 at 23:52
  • Have you deleted the data file (`YourReport.rdl.data`) to make sure that you aren't using old data? https://stackoverflow.com/questions/18210078/why-is-my-ssrs-report-showing-old-data – Hannover Fist Nov 08 '19 at 16:59

1 Answers1

0

My best guess is that you accidentally had a situation where you did not properly clear the temp tables (or you populated the temp tables multiple times). As an alternative to temp tables, you could instead use table variables. Equally you could use a single query from the production tables -- using CTE if you want it to "feel" like 3 separate queries.

avery_larry
  • 2,069
  • 1
  • 5
  • 17