GetDataSetbyQuery Puzzle

Classic Servoy
1

Two methods; one which sets the value of the CompanyID into a global and then runs the second method on a different form:

Method 1

globals.companyid = companyid;
forms.Sales_Report.Sales_Report_Year();

Method 2

//Get a dataset based on query
var theStr = ‘’+
‘\n’+
‘\n’+
‘\n .text’+
‘\n {’+
'\n font-family: ’ + globals.gFmt_Font + ‘;’+
'\n font-size: ’ + globals.gFmt_Size + ‘pt;’+
‘\n }’+
‘\n .title’+
‘\n {’+
'\n font-family: ’ + globals.gFmt_TitleFont + ‘;’+
'\n font-size: ’ + globals.gFmt_TitleSize + ‘pt;’+
‘\n }’+
‘\n’+
‘\n\n’+
‘\n

’

var maxReturedRows = 100;//useful to limit number of rows
var company = globals.companyid
var query = 'SELECT T5.CompanyName, T2.SortName, sum(T1.RoomTotal) AS Total, count(T1.ResRoomID) AS Rooms
FROM tblReservationRoom T1
LEFT JOIN tblLocation T2 ON T1.LocationID = T2.LocationID
LEFT JOIN tblReservation T3 ON T1.ReservationID = T3.ReservationID
LEFT JOIN tblMembers T4 ON T3.MemberID = T4.MemberID
LEFT JOIN tblCompany T5 ON T4.CompanyID = T5.CompanyID
WHERE T3.WebsiteID = 1

So far so good but in this next section:

AND T5.CompanyID = globals.companyid

does not work. If however, I set T5.CompanyID to a specific value, i.e., the ID for a company, as in:

AND T5.CompanyID = 3445

then it works perfectly.

What am I doing wrong?

AND Datepart(yyyy,T1.Indate) = Datepart(yyyy,DATEADD(YEAR, 0, getdate()))

which displays results for the current year. If I change the Year from 0 to 1, it will display results for next year and if I change it to -1, it will display results from last year.

How would I rewrite the query so that it shows me values for the three years in 3 separate but adjoining columns?

GROUP BY T5.CompanyName, T2.SortName ORDER BY sum(T1.RoomTotal) DESC’;

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);

globals.htmlreport = theStr += ‘

\n\n’ + dataset.getAsHTML() + ‘\n\n
\n’;
globals.global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
globals.global_total = globals.global_total + dataset[3];
}
forms.Companies.elements.tabpanel.tabIndex = 8
[/quote]

Hope this all makes sense. TIA.

2

rochard:

AND T5.CompanyID = globals.companyid

does not work. If however, I set T5.CompanyID to a specific value, i.e., the ID for a company, as in:

AND T5.CompanyID = 3445

then it works perfectly.

What am I doing wrong?

You need to separate the SQL query STRING from the value. Like this:

"SELECT.... WHERE T5.CompanyID = " + globals.companyid

rochard:
AND Datepart(yyyy,T1.Indate) = Datepart(yyyy,DATEADD(YEAR, 0, getdate()))

which displays results for the current year. If I change the Year from 0 to 1, it will display results for next year and if I change it to -1, it will display results from last year.

How would I rewrite the query so that it shows me values for the three years in 3 separate but adjoining columns?

This is VERY difficult (SQL-wise). You’re better off doing 3 queries and then using arrays to display the values.

Hope this helps,

Bob Cusick

3

Bob, thanks for the solution to the first part of this problem. It is now working seamlessly.

As to the second part of the puzzle, ‘running multiple queries and putting the results into an array’, wouldn’t this make a great tutorial ‘movie’!

4

Michael,

Thanks for the tutorial idea. I DO have a new series on “Creating a HTML Report Using SQL” coming out in the next day or two. That should give you a start…

:D

Cheers,

Bob