Drill-down then Link to URL

Larry Beasley shared this question 2 years ago
Answered

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?

Comments (5)

photo
1

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.

0070ba3f1952eecbf5011ec73a3aaa75

In this example the URL calculated field is simply:


  1. 'URL Prefix' + 'Athlete ID'

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).


  1. 'URL Prefix' + SUM('Athlete ID')

340bd3c6b49a210727a90d078ee6c340

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

photo
2

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.

ce8db73dd5bb14b59c9b4ee771d142da

Please let me know if these suggestions are at all relevant to your needs!

Regards,

Nathan

photo
1

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.

photo
1

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

photo
1

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