RD PH/Cloud How-To: Add Excel Hyperlinks in Solver Portal

Summary

This article will describe the process to implement Hyperlinks in Solver Cloud. Hyperlinks can be used to reference internal and external data for Reporting. This process works well in Excel but requires certain syntax to work in both Excel and the web interface. The steps below assume familiarity with Report Designer and intermediate Excel formula experience.

Article sections:

• Method
  • Data Load
  • Configuration
    • Portal 
    • Excel

  • Combine and Display One Link to Users

• Properties

Method

There is special syntax required to ensure that the hyperlink feature will work across sheets and ensuring one report will work in both Excel and the portal.

Data Load

To load hyperlinks into Solver Cloud, the data will need to be imported in the following format:

• https://solverglobal.com|Solverglobal Site
  • HTTP or HTTPS is required, "www" syntax is not valid and will result in the URL simply being text.
  • The pipe ( | ) symbol splits the URL from the friendly name and is required for the hyperlink to work in drilldown.
    • The friendly name is what will appear in drilldown

When reporting off of this URL, to hide the |<friendlyName>, you can use a left formula: 
=LEFT(B5,FIND("|",B5)-1)

Configuration

The following exercise was created in the Solver corp demo. It will create hyperlinks between two worksheets with different entities on each sheet. Note that there are two sections below to ensure the link works within the portal or Excel. 

Portal

1. First, create a MATCH statement. The MATCH statement will look at the other worksheet and match the values and return a Row value. This formula was entered into cell A10:
   =MATCH(B10,INDIRECT("SUS!B:B"),0)
   
2. The MATCH looks at the field in B10 and then compares it to the values in column B using the INDIRECT Command. The 0 at the end will give an Exact Match. Run it to see the rows.
3. Next, add the special Hyperlink code. This combines the Excel HYPERLINK function with the special syntax needed to work in the cloud portal.
    =HYPERLINK("sjs://SUS!C"&MATCH(B8,INDIRECT("SUS!B:B"),0),B8)
   • HYPERLINK is the Excel Command.
   • The “sjs://” is the command that is necessary in the web portal.
   • SUS! is the worksheet that we want to link to.
   • C is the column that we want to combine with the MATCH statement to form the location to HYPERLINK to.
4. Save the template, go back to the Portal, and run the hyperlink.

Excel

The above hyperlink will only work in the Solver portal. To use Excel for hyperlinks, the standard Excel HYPERLINK function must be used.

1. First use the ADDRESS function with MATCH. MATCH works the same as above and the ADDRESS will give you the Cell Address:
   =ADDRESS(MATCH(B10,INDIRECT("SUS!B:B")),3,1,1)  
   
   • Match the D8 value with Worksheet SUS and C column
   • Goes to the 3^rd Column in the Worksheet
   • 1 is absolute value

2. The Result will look like the below
   
3. Next add your Hyperlink in Excel
   =HYPERLINK("[HyperLink 2.xlsx]SUS!"&ADDRESS(MATCH(B10,INDIRECT("SUS!B:B")),3,1,1),D8)
   • Add the Hyperlink with the Workbook Name xlsx
   • Sheet Name SUS
   • ! for separate sheet name from Address
   • ADDRESS formula from Step 1
   • Field to reference for Hyperlink format in this case the Account Field.

• Note this will work in Excel but not in the Portal.

Combine and Display One Link to Users

You now have formulas that work in Excel and in the web, but you want to have one report that works in both. You can also combine the two concepts that work together.

1. The first thing you need a true/false statement that will show if you are in Excel or the portal. This can be done with many formulas but in this exercise we will use the following statement in Cell A2.

 =LEFT(CELL("Filename"),10)  

• • This will show if you have a Filename. Now go to C8 and enter this formula =ISERROR($A$2)  This will show if the formula in A2 is valid or not.

4. Run the report in Excel and you will see a False or there is not an Error.
   
5. Next run the report in the Portal and you will see a True Statement.
   
6. Now we can do an IF statement with TRUE and FALSE. Combine the two statements.
   =IF(ISERROR($A$2),HYPERLINK("sjs://SUS!C"&MATCH(D8,INDIRECT("SUS!C:C"),0),D8),(HYPERLINK("[HyperLink 2.xlsx]SUS!"&ADDRESS(MATCH(D8,INDIRECT("SUS!C:C")),3,1,1),D8)))
   
7. In the Formula above we made a TRUE or FALSE statement. Now we make an IF statement that you have your ERROR statement $A$2.
   1. IF Statement is true you have your Portal Hyperlink
   2. IF Statement is False you use your Excel Hyperlink.

You can look at your Excel Function to make sure it is working correctly.  This completes the exercise in getting hyperlinks to work in Solver Cloud or Private Host.