This tutoial applies to version 11 of PHPRunner and ASPRunner.NET.
Our plan is to build a dashboard with two elements, chart and a data grid. Both elements will be built on the top of the same SQL query and have the same set of fields. Basically two different views of the same data. The main goal is to implement a search panel that would allow to filter both views at the same time. Here is how it looks in the generated application.
- First we create a chart and a custom view. Both are built on the top of the same SQL query. For the chart we have also select an option to 'Limit data to first 10 rows'.
SELECT
c.Country,
OrderDate,
sum (Quantity*UnitPrice) as total
FROM
orders o
inner join `order details` od
on o.OrderID = od.OrderID
inner join customers c
on c.CustomerID = o.CustomerID
group by Country
order by sum (Quantity*UnitPrice) desc- Create a dashboard, add both chart and a custom view there. It makes more sense to create a layout like this:
- The element at the top is dashboard search. When you add this element to the dashboard, select Dashboard as the main element and Search as the page type.
- Now click anywhere on dashboard page in the Page Designer and then click green 'Search settings' button on the right side panel.
Drag fields into cells the way it is shown on the screenshot. The point is the make OrderDate and Country fields to search in both tables when we run the search.
Now we just need a couple more final touches. Select your custom view and proceed to Fields -> Search and Fiter settings. Under OrderDate options make sure that only Between option is selected.
And since OrderDate is a date field it would be convenient to add a datepicker to OrderDate field. Proceed to any page of this custom view in the Page Designer, double-click on OrderDate field and under 'Edit as' setings select 'Calendar control' option.
Happy dashboarding!
