Drill-down then Link to URL
I have an almost perfect drill-down report. The final thing I want the user to be able to do is go directly to the customer's home page from the report. This is how it works:
1. I created a field in my SQL view :
Select
'All Customers' [All Customers],
customer.id [Customer Id]
From Customer
Then I set the hierarchy as [All Customers] > [Customer Id]
Now, when I create a drill-down report I can start with one line and I can go from there. The dilemma happens when I expand [All Customers] and get a list of customer Id's. I want to use the customer Id in a URL using https://customer.example.com/customer_id=## but at this point I cannot. In the draft report if I change format to Link to URL it breaks the drill down as the link overrides the drill down function and is replaced with https://customer.example.com/customer_id=All+Customers which is obviously wrong. I've tried to create the URL in SQL but that displays the HTML instead and doesn't work due to the report not wanting HTML injection. Any suggestions?
The same question
Hi Larry,
Thank you for getting in touch!
Unfortunately, in the end, your URL will be a field unique to the customer_id. I would suggest one of two approaches:
1. Drill-through:
In the primary report you could show the information relevant to "All Customers" (excluding the URL), and linking the "All Customers" field to a secondary report.
The secondary report would display individual Customer IDs as well as a calculated field to concatenate Customer ID with your URL prefix, which can then be formatted to be a URL hyperlink.
In this example the URL calculated field is simply:
The advantage of this approach is that you will not have to display an invalid link at the primary report level.
2. Sum(Customer ID)
By creating a calculated field where the Customer ID summed, you will be able to aggregate the URL at the higher hierarchy level (but creating a completely invalid link).
In this example "Age Group at Camp" drills down into "Athlete ID" . The advantage of this approach is that you will be able to maintain your drill down functionality.
If this is not relevant to your issue, It would be helpful if you could provide me screenshots of where your report is currently at so that I can provide more case-specific suggestions.
Regards,
Nathan
Hi Larry,
Thank you for getting in touch!
Unfortunately, in the end, your URL will be a field unique to the customer_id. I would suggest one of two approaches:
1. Drill-through:
In the primary report you could show the information relevant to "All Customers" (excluding the URL), and linking the "All Customers" field to a secondary report.
The secondary report would display individual Customer IDs as well as a calculated field to concatenate Customer ID with your URL prefix, which can then be formatted to be a URL hyperlink.
In this example the URL calculated field is simply:
The advantage of this approach is that you will not have to display an invalid link at the primary report level.
2. Sum(Customer ID)
By creating a calculated field where the Customer ID summed, you will be able to aggregate the URL at the higher hierarchy level (but creating a completely invalid link).
In this example "Age Group at Camp" drills down into "Athlete ID" . The advantage of this approach is that you will be able to maintain your drill down functionality.
If this is not relevant to your issue, It would be helpful if you could provide me screenshots of where your report is currently at so that I can provide more case-specific suggestions.
Regards,
Nathan
Hi Larry,
I had another thought on this.
As a better alternative to my second suggestion, you could expand that calculated field to account for the aggregation. (This is assuming your customer ID is an integer).
We can include a case statement in the calculation, so that when the sum of the ID's are greater than the maximum possible ID value, the field is set to be blank. Now when the customer ids are aggregated in the highest level of the drill down, the URL field will be empty, only populated when you drill in.
Please let me know if these suggestions are at all relevant to your needs!
Regards,
Nathan
Hi Larry,
I had another thought on this.
As a better alternative to my second suggestion, you could expand that calculated field to account for the aggregation. (This is assuming your customer ID is an integer).
We can include a case statement in the calculation, so that when the sum of the ID's are greater than the maximum possible ID value, the field is set to be blank. Now when the customer ids are aggregated in the highest level of the drill down, the URL field will be empty, only populated when you drill in.
Please let me know if these suggestions are at all relevant to your needs!
Regards,
Nathan
Option 2 looks good but I'm really looking for a way to mask the URL. I'm using multiple drill downs so the drill through report won't work. If somehow it will accept HTML I would be happy because I could either have the column data <a href="http://www.test.com/?cusomter_id=12345">12345</a> which would simply display 12345. I'll have to make the report requestors accept the URL calculated field work-around. Easier said then done. :)
Can you take a look at my screenshot and see if there are any other possible solutions? Much appreciated.
Option 2 looks good but I'm really looking for a way to mask the URL. I'm using multiple drill downs so the drill through report won't work. If somehow it will accept HTML I would be happy because I could either have the column data <a href="http://www.test.com/?cusomter_id=12345">12345</a> which would simply display 12345. I'll have to make the report requestors accept the URL calculated field work-around. Easier said then done. :)
Can you take a look at my screenshot and see if there are any other possible solutions? Much appreciated.
Hi Larry,
I apologize for the delay in response, it appears that I missed the notification of your reply.
Unfortunately, it is not possible to dynamically hide columns. However, it is possible to have multiple drill through child reports within the same parent report, so your "Programs" "Clients" "Locations" fields could all drill in to separate reports, tailored for their individual data. I am not sure if this will meet your needs, but this would allow you to hide the URL field until necessary.
Also, I am a bit confused as to what you mean by "If somehow it will accept HTML I would be happy". Could you elaborate a bit on this?
Regards,
Nathan
Hi Larry,
I apologize for the delay in response, it appears that I missed the notification of your reply.
Unfortunately, it is not possible to dynamically hide columns. However, it is possible to have multiple drill through child reports within the same parent report, so your "Programs" "Clients" "Locations" fields could all drill in to separate reports, tailored for their individual data. I am not sure if this will meet your needs, but this would allow you to hide the URL field until necessary.
Also, I am a bit confused as to what you mean by "If somehow it will accept HTML I would be happy". Could you elaborate a bit on this?
Regards,
Nathan
Hi Larry,
You okay with me closing this off? If you have any more questions on this one, just reply and it will open back up.
Regards,
Nathan
Hi Larry,
You okay with me closing this off? If you have any more questions on this one, just reply and it will open back up.
Regards,
Nathan
Replies have been locked on this page!