In this blog we have already read and understood how to implement drill through :
https://www.helicalinsight.com/configuring-drill-through/
However there could be certain cases, especially in case of date/date time fields, wherein we want to extract some date/month/year etc from the date and then do drill through on top of it. For example we have a date field with full date month and year, and we want to extract week number from it and then pass that to drill through report. In such kind of use cases we have to follow 1-2 more steps. In this blog we have covered how the same can be achieved.
Example: We are taking a scenario that we have dates stored in the database in YYYY-MM-DD format the way it is shown below:
But in parent report we want to create report as month – year wise bar chart i.e. MM-YYYY format. For that we are clicking on “Functions” of that dragged column (Month_Year column) and applying:
Hence it creates a report like shown below.
You can see the screenshot above, where travel_date is modified in MM-YYYY format by applying relevant function on that column. So X axis of bar chart is representing in MM-YYYY format and this report we are considering as parent report.
Now, we have child report as well which looks like below:
Now if we will pass MM-YYYY value by clicking any bar from parent report then child report will not able to filter the data accordingly, because child report filter is expecting the data in YYYY-MM-DD format.
In order to make this scenario work, we need to apply the same relevant function which will convert this into MM-YYYY format so that parent report format (from where the value is being passed) and child report filter (which is receiving the value and filtering the data) will exactly match.
So, we will add that same date field (travel_date column) from metadata in rows/columns and then apply same format which extracts MM-YYYY from the date. Now the data is exactly in the format from which we are passing it from parent report. Now add this as a filter by clicking. Refer below image for more clarity:
Now in the parent report, enable interactivity and select the child report using Drill Through option:
Important step to note here: When you map parent report and child report, it will list the filters from child report. You need to map the column which is being passed on click.
In this case, since dates are natively stored in YYYY-MM-DD format inside database column “travel_date”. We should not map that column as it is. Rather after mapping it, we have to apply the same function on this column as well so that it gets converted into same format MM-YYYY. Then this mapping will able to pass the correct value and filter the child data accordingly.
You can see below, in the parent report we have bars which are month year. When we click on it and do drill through, it is opening the child report of that specific month only.
In above screenshots, we have passed 03-2015 from parent report (screenshot 1) and child report loaded with filtered data of 03-2015 (screenshot 2).
Feel free to reach out in case of any more questions on support@helicalinsight.com
