1. 

   mysti9uemirage, Member

   Hello,
   I need a lot of assistance.

   Basically, I have data similar to the following site: http://www.sqlines.com/mysql/how-to/get_top_n_each_group
   I'll ask my questions based on their data.

   I would like the report to group by country and show the table for the country, difference between the min and max population
   These should be sortable
   Then, once the user clicks on the row, the details of the country/city/population shows up in a table
   Could I achieve the above with myDBR? If so, what topics should I be looking at?

   I know how to use dbr.min/dbr.max but I don't know how to not have it show up in the report and only use it to calculate the difference, then show this difference in the report (I'm thinking of using dbr.calc but not sure how to reference the result of the min/max)
   I also used dbr.hdr and dbr.collapse but it collapsed each row instead of the group even though the hdr allowed the min/max to be for the desired groups. Also, dbr.collapse shows the data in a list, not a table

   Please help. Thank you.
2. 

   myDBR Team, Key Master

   Hi,
   to get the list of the countries and the difference between population of largest and smallest cities, you would use a self join. To show the details of the selected city when clicking on it, you can use a linked report which shows the report inline, attaches itself to 'country'-column and takes the city id/name as parameter.

   
   
   select 'dbr.report', 'sp_DBR_country_detail', 'inline', '[country]', 'in_country=country';
   
   select cmin.country as 'Country[country]', max(cmax.population)-min(cmin.population) as 'Difference'
   
   from cities cmin
   
     join cities cmax on cmax.country = cmin.country
   
   group by cmin.country

   --
   myDBR Team
3. 

   mysti9uemirage, Member

   Thank you for your guidance.
   In summary, I had to create another report to show the details. I used a stored procedure to share code between the parent and detail report and inline the detail report in the parent's.
   Also, it seems like the rule of thumb is that when the calculation is not part of the main report, it should be done in a query instead of using the dbr commands.

Reply

You must log in to post.