We have a development environment where my desktop version is 10 and the Server and Publisher are 9. We pulish our qvw files to a iFrame with AJAX for a SAAS software. I am still a novice to the whole process, but as I understand it, the QV Server houses a .qvw file then the Publisher transforms that file to a html rendered version along with the data and then AJAX picks up the html version and ports it to a iFrame in the application where the user interacts with the report's face without any of the QV controls.
A few revisions back on a file, the load statement was
SQL
//most recent mResult date
select custID, Max(RunDate) as Max_Run_Date from mResults
group by custID
;
SQL
//most recent mResult score
select custID, min(Score) as Max_Run_Date_Score from mResults
Where RunDate in (select max(RunDate) from mResults group by custid)
group by custid
;
SQL
//most recent mResult score with review tied to it
select m1.custID, min(Score) as Has_Review_Score from mResults m1
where rundate in (select max(m.rundate) from mResults m
join vw_review r on r.mResultsreviewid = m.mResultid
group by m.custid)
group by m1.custid
;
Now it is changed to below due to a changed table structure and more accurate results
SQL
WITH T1
AS
(
-- MostRecentScore
SELECT fs.FinstatementID,CASE WHEN YEAR(fs.[FinStatement.PeriodDATE]) = 1776 THEN NULL ELSE fs.[FinStatement.PeriodDATE] END AS PeriodDate,
m1.RiskLevel,m1.custID,modID,modResultID,RunDate,Min(Score) AS MinScore
FROM modResults m1 WITH (NOLOCK)
INNER JOIN (SELECT custID,MAX(RunDate)AS MaxDate FROM modResults m GROUP BY m.custID) m2
ON m1.custID = m2.custID AND m1.RunDate = m2.MaxDate
LEFT OUTER JOIN vw_FinStatement fs
ON m1.custID = fs.custID AND m1.FinStatementID = fs.FinStatementID
GROUP BY
fs.FinstatementID,fs.[FinStatement.PeriodDATE],m1.RiskLevel,m1.custID,modID,modResultID,RunDate
)
,T2
AS
(
--MostRecentReviewedScore
SELECT fs.FinstatementID,CASE WHEN YEAR(fs.[FinStatement.PeriodDATE]) = 1776 THEN NULL ELSE fs.[FinStatement.PeriodDATE] END AS PeriodDate,
m1.RiskLevel,m1.custID,modID,RunDate,MIN(Score) AS MinScore
FROM modResults m1 WITH (NOLOCK)
INNER JOIN (SELECT t.custID,Max(RunDate)AS MaxDate FROM modResults t JOIN vw_review r ON r.modresultsreviewid = t.modResultID GROUP BY t.custID) m2
ON m1.custID = m2.custID AND m1.RunDate = m2.MaxDate
LEFT OUTER JOIN vw_FinStatement fs
on m1.custID = fs.custID AND m1.FinStatementID = fs.FinStatementID
GROUP BY
fs.FinstatementID,fs.[FinStatement.PeriodDATE],m1.RiskLevel,m1.custID,modID,RunDate
)
,T3
AS
( --MostRecentScoredDate
SELECT c.custID,MAX(RunDATE) AS MAXDate
FROM modResults mr WITH (NOLOCK)
RIGHT OUTER JOIN vw_cust C
ON mr.custID = c.custID
GROUP BY c.custID
)
,T4
AS
( --MostRecentNONReviewedScore
SELECT fs.FinstatementID,CASE WHEN YEAR(fs.[FinStatement.PeriodDATE]) = 1776 THEN NULL ELSE fs.[FinStatement.PeriodDATE] END AS PeriodDate,
m1.RiskLevel,m1.custID,modID,RunDate,MIN(Score) AS MinScore
FROM modResults m1 WITH (NOLOCK)
INNER JOIN (SELECT t.custID,Max(RunDate)AS MaxDate FROM modResults t JOIN vw_modresultsnoreview r ON r.modresultsnoreviewid = t.modResultID GROUP BY t.custID) m2
ON m1.custID = m2.custID AND m1.RunDate = m2.MaxDate
LEFT OUTER JOIN vw_FinStatement fs
on m1.custID = fs.custID AND m1.FinStatementID = fs.FinStatementID
GROUP BY
fs.FinstatementID,fs.[FinStatement.PeriodDATE],m1.RiskLevel,m1.custID,modID,RunDate
)
SELECT
t3.custID,
t1.MinScore AS Max_Run_Date_Score,
t2.MinScore AS Has_Review_Score,
m.[mod.mod] AS mod_Name,
t3.MaxDate AS Max_Run_Date,
t4.PeriodDate AS Curr_FinState_Date,
t2.PeriodDate AS FinState_Date_Last_Reviewed,
t4.RiskLevel AS Curr_Risk_Level,
t2.RiskLevel AS Risk_Level_Last_Reviewed
FROM T1 t1
LEFT OUTER JOIN T2 t2
ON t1.custID = t2.custID AND t1.modID = t2.modID
RIGHT OUTER JOIN T3 t3
ON t1.custID = t3.custID
LEFT OUTER JOIN vw_mod m
ON t1.modID = m.modID
LEFT OUTER JOIN T4 t4
ON t1.custID = t4.custID AND t1.modID = t4.modID
;
The top statement runs and returns results in the report with no problem. The bottom statement runs with no error, loads records with no error, and will display results on my desktop even if I open the source file that the server is using. Also, it will open in the app, but with zero records. I have searched the logs, any log I could find, till I was blue and found nothing. Also when I open the document on the Server's desktop; which is version 9; all I get is a "failed to open document" close box with no further explaination as to why. Because we are on a deadline, I switched the load statement back to a more friendly query based code as the first above. My SA simply says its a compatibility issue, but I don't buy it because its the same file with only the load statement (and associated changes in the gui) is different. Does anyone know if the second statement is not compatible in ver 9? If so, what is the problem? If not, have you ever seen this type of behavior and what did you do?
Any help would be greatly appreciated as I am getting nowhere searching the community or documentation. I had to look for an hour and a half just to find the logs.
btw; both queries run just fine on SQL Server Management Studio on both the server and a local copy of the db on my local test server.
Also, the user account that accesses the SQL db is the same on both and has no trouble opening any other document on this server on this db.
