Basic grid design ( with sql group by ) question

[WindForce]

Hi
I am trying to base my grid on something similar to the following :

select Customers.CustomerName, Customers.CustomerID, count(Orders.OrderID) as OrdersCount, sum(Orders.Amount) as TotAmount from orders join Customers on orders.customerid=customers.CustomerID order by Customers.CustomerName group by Customers.CustomerName, Customers.CustomerID

Now i would like to add a Search part to the grid, with a Dates range search ( or two date fields, fromDate and toDate ), and then on the search filter the above grid for Orders.Date between fromDate and toDate.

Saw possibilities to use arguments in the SQL but i need the search part functionality...

[lvasic]

Re: Basic grid design ( with sql group by ) question

I would probably use a subquery rather than group by and in a stored procedure.
Select
c.CustomerID,c.CustomerName,
OrdersCount = (Select count(OrderID) from orders where CustomerID = c.CustomerID and OrdersDate between @sDate and @eDate),
TotAmount = (Select sum(Amount) from orders where CustomerID = c.CustomerID and OrdersDate between @sDate and @eDate)
from Customers c
order by c.Name, c.ID

[WindForce]

Re: Basic grid design ( with sql group by ) question

Thank you Lee, i missed your answer. I don't have issue filtering by dates using group by or subqueries, i have issues trying to use a date range in the search part in the above scenario.
I also tried embedding the grid in a UX and trying to pass it the filtering dates, but no joy...

[CharlesParker]

Re: Basic grid design ( with sql group by ) question

You posted the following:

select Customers.CustomerName, Customers.CustomerID, count(Orders.OrderID) as OrdersCount, sum(Orders.Amount) as TotAmount from orders join Customers on orders.customerid=customers.CustomerID order by Customers.CustomerName group by Customers.CustomerName, Customers.CustomerID

Am I high or are you just leaving out the date field?
I know in a grid you just go to the search tab, select the date field and then click "date range" and you can set a default value...

I am going to assume, you just didn't include it in the post but did in the actual sql query and recommend you just use the built in AS feature to do that.

date-range.JPG

[WindForce]

Re: Basic grid design ( with sql group by ) question

Thanks Charles. You're ( sadly? ) not high :-) There is no date field in the sql results.
I've been bitten today, again, by this scenario.

I'd like to show in a grid the total orders and income, grouped by Customers. My client wants the ability to then modify the dates and refresh the grid. There is no date in the result, but rather in the WHERE clause in the query.
So far i managed to advance a little bit by using the following:
select Customers.CustomerName, Customers.CustomerID, count(Orders.OrderID) as OrdersCount, sum(Orders.Amount) as TotAmount, :FromDate as StartDate, :ToDate as EndDate from orders join Customers on orders.customerid=customers.CustomerID where Orders.Date between :FromDate and :ToDate order by Customers.CustomerName group by Customers.CustomerName, Customers.CustomerID
I defined to date arguments ( :FromDate and :ToDate ) which are set to get their initial value from session variables wich are declared in the onGridInitialize event.
The thing is i have no way to have the Search part of the grid set the session variables values and requery, so i created a UX, added two fields for the dates and embedded the grid.
Even then, i set up the button refreshing the grid to set the session variables with the values from the date fields from the UX, but it doesn't work ( the grid is always empty )
I've been looking for years for a solution to this very standard scenario, but can't get it to work as expected...

[CharlesParker]

Re: Basic grid design ( with sql group by ) question

Ok, but I am still unfamiliar with the idea of not including the date field in the select portion of the SQL query if you intend to filter it on that. Don't you have to include the date? I mean you can define an argument and give it any date value you want but without a date field in the query I don't see how you can filter on it...

I would just create a regular grid with the fields you need from the table as shown in a search section. You can use a view if there is more than one table involved.
Hope that helps, and please understand I am not some sorta SQL or AS expert so I am working within my limited experiences.
Cheers

[lvasic]

Re: Basic grid design ( with sql group by ) question

You could do something like this:

function OnSearchPartFilterCompute as v (e as p)

e.rtc.parameters_searchPart = e.SearchDataSubmitted.DateBeg +"|||D|DateBeg" + crlf() + e.SearchDataSubmitted.DateEnd+"|||D|DateEnd"
e.rtc.filter_SearchPart = "((dbo_Labor.DateBeg) BETWEEN :DateBeg AND :DateEnd)"

end function

[lvasic]

Re: Basic grid design ( with sql group by ) question

This is why I would use a stored procedure. You can group and also filter by date range. You pass the date range when you call the stored procedure.

[WindForce]

Re: Basic grid design ( with sql group by ) question

Dear Lee
Thanks for your help. I am having a hard time understanding how to setup this.
Do you mean i can avoid the UX embedding and have it all on my grid with just search part? That would be great. Could you please elaborate what to put exactly in the grid sql and in the search part?

[lvasic]

Re: Basic grid design ( with sql group by ) question

See if this video helps: https://www.screencast.com/t/igcU24BJMPg


Crosstab table, stored procedure, Group by

[WindForce]

Re: Basic grid design ( with sql group by ) question

Many thanks Lee.
I did exactly as in your video, but it took me nearly two days to have the grid run, because using a MariaDB stored procedure, and i had to search through many threads in order to just have the grid display the data.
I will now start entering the Dates filtering as per your method, will update. I'll have to play a bit with the search, because i want to give the user the possibility to filter through additional fields as well

[WindForce]

Re: Basic grid design ( with sql group by ) question

Played a few hours with the e.rtc.filter_searchpart but i couldn't have it filter by anything other than what we did with the dates...

[lvasic]

Re: Basic grid design ( with sql group by ) question

I updated the video. Watch the part about the SQLQuery. We are bypassing the filter part and building our own query. We grab the search criteria in load the session variables and then pass those as args to our stored procedure. Is your stored procedure set up to handle this?

Here is a new video with 3 search criteria: https://www.screencast.com/t/DoYMsoZ2bTVm

[WindForce]

Re: Basic grid design ( with sql group by ) question

Thanks Lee, brilliant and simple!
By the way i found a gazillion issues while implementing the use of stored procedures in grids. First, don't know for MS SQL, but for MariaDB it won't work if i don't set correctly all the server events ( onSQLCountQuery and onSQLSelectQuery) and also, i cannot use the e.cn connection that you use in your code, but rather manually open CN to my DB in each of those server side events.
Last (that's why i particularly needed to add more filtering options ) I cannot use Quick Search at all ( error with the field list )...
...and if not enough suffering, cannot export to excel (the whole purpose of this grid was to filter then export to excel) with Hebrew headers - it seems it works only with SQL *tables*...sigh