How to use date range in grid containing summary data?

[WindForce]

Hi
I have this scenario in most of my apps, i would like to implement it using grids.

Basically, i would like to have a grid showing summary info grouped by customers, for a specific date range, i.e:

CustomerID | CustomerName | TotalOrders | TotalAmount
1           | Customer1     | 20          | 1200$
2           | Customer2     | 24          | 2300$

Then what i need is:
1) How can i put the date range for the above grid in the search part? Currently, i added the date range as arguments in the sql query, i don't see how to use the search part instead

2) I would like to use a row expander on the above grid. Expanding the row 1 above for instance would bring me a grid with those 20 orders belonging to Customer1. I link to the Orders table using the customerid, but how do i "pass-through" the date range to filter the linked grid?

Thanks
Jaime

[pcaspa]

Re: How to use date range in grid containing summary data?

Hi,

You can select "Range Search" for a field in the Search section of a grid

If you summaries the data in the initial Grid then I don't see why you need to "pass-through" the date range however check out Server side event - "cansearch" template (e.SearchDataSubmitted ) for details. Maybe put the details in a session variable that you can use for a filter in linked grid.

I hope this helps

Cheers
Peter

[WindForce]

Re: How to use date range in grid containing summary data?

Thanks Peter.
Take a look at the original sql query i used for the summary:

SELECT customers.CustomerID, Customers.Name as CustomerName, Count(Orders.OrderID)  AS TotalOrders,  Sum(Orders.Amount)  AS TotalAmount 
FROM (customers customers	 INNER JOIN orders orders  ON  customers.CustomerID = Orders.CustomerID )
WHERE Orders.Date BETWEEN :FromDate AND :ToDate 
GROUP BY customers.CustomerID, customers.Name ORDER BY customers.Name

As you can see, i used the :FromDate and :ToDate as arguments for the sql. I know how to put a date range in the search part, my question in no.1 was how to 'link' the results of that range in the search part to the arguments in the sql.

As for the question in no.2, the only linkable field available is the CustomerID. How can i tell the grid in the row expander to filter according to the date range from search part of 'parent' grid?

Many thanks for your help!
Jaime

[WindForce]

Re: How to use date range in grid containing summary data?

After playing with this a bit, i came to the conclusion there is no way i can use the search part of the grid in order to filter by date range the summary grid.
Instead, i will try embedding the grid into an UX, and then setting the date range in the UX and from there try to fill in those values into the embedded's grid arguments.
I wonder if in the latter scenario, the date fields set up in the UX are also available to filter the embedded grid's child ( in the row expander )