My query works in query analyser but freezes when i preview a report  
  Index ‹ Visual Studio ‹ Visual Studio Report Controls
Author Message
Carel Greaves
PostPosted: Visual Studio Report Controls, My query works in query analyser but freezes when i preview a report Top
Hi guys/girls

I've got a problem with RS2000, i've generated a report, but my code freezes in RS2000 although when i run my code in Query Analyser then it works fine.

What i am trying to do is add the previous months data to the current month. when i run the query it works, but when i preview my report it just inserts the first value for all the rows

Anyway here is the code, if someone knows how to simplify it for me then please, i would be greatful or perhaps offer an alternative solution:

-- Create Table that will hold the values for the running total
declare @MyTable TABLE (UID INT IDENTITY(1,1), CurrentMonth VARCHAR(20), GPYTDValue FLOAT, RunningValueGP FLOAT)
INSERT INTO @MyTable (CurrentMonth, GPYTDValue)

SELECT dt.[month_name], (sum(ft.price)- sum(ft.cost)) as turnover2005
FROM dim_time dt, fact_transaction ft, dim_Client dc
WHERE dt.time_key = ft.time_key
AND dt.[year] IN (2005)
AND dc.client_name like '%medicare%'
GROUP BY dt.[year], dt.[Month], dt.[Month_Name]
ORDER BY dt.[Year], dt.[month], dt.[month_name],sum(ft.cost)

UPDATE @MyTable SET RunningValueGP = GPYTDValue WHERE UID = 1

DECLARE @iStart INT
DECLARE @iEnd INT

DECLARE @RunningTotalGP FLOAT

SELECT @iStart = MIN(UID) FROM @MyTable
SELECT @iEnd = MAX(UID) FROM @MyTable

SELECT @RunningTotalGP = RunningValueGP FROM @MyTable WHERE UID = @iStart

SELECT @iStart = @iStart + 1

WHILE (@iStart <= @iEnd)
BEGIN
SELECT @RunningTotalGP = @RunningTotalGP + GPYTDValue FROM @MyTable WHERE UID = @iStart
UPDATE @MyTable SET RunningValueGP = @RunningTotalGP WHERE UID = @iStart
SELECT @iStart = @iStart + 1
END

SELECT * FROM @MyTable

Kind Regards

Visual Studio22  
 
 
Eagle1984
PostPosted: Visual Studio Report Controls, My query works in query analyser but freezes when i preview a report Top
I came right, thanks anyway.

If anyone is battling with the same problem, there is a RunningValue() Function in SQL Server that does the whole thing for you to add the previous months values to the currents or for any other purpose u might want to use it for. I'de finished writing the whole function myself when i found that it was already there, Ha Ha, anyway.

All i did to stop the freezing was close VS and restart it.

Kind Regards
Carel Greaves
 
 
 
Index ‹ Visual Studio ‹ Visual Studio Report Controls