Importing file paths and turning them into hyperlinks

Posted by sw on January 6, 2009 under Corvu |

There is always a need to link some types of documents to your performance management portal (HyperVu), I like prefer the terminology called Information Portal as it covers a broader aspect of displaying analysis. Anyway moving on with the topic, adding links for one or two PDF’s is not a problem but imagine a folder of 100’s of files, it well may be a folder full of measure definitions of all your scorecards.

 

As a rule if number of documents rise above the scale of 10 and on ongoing basis there will be changes then you should start thinking for a robust solution.

 

Well that’s how I think it should be proceeded, create a batch file which lists all files with their paths, import this file into CorBusiness, turn imported information into hyperlinks (this can either be URL addresses or actual file paths), create a nice quick report where file names are searchable and then add the report to your HyperVu Portal.

 

Okay let’s get started then.

 

Create a batch file called filelisting.bat and add dos script which lists only a full path of the files within a folder, make sure you place the batch file outside the folder where the pdf’s reside.

 

For those whose dos skills are a bit rusty, here is the required script:

 

echo off

cd %1

dir /a-h-d /b /-p /o:gen /s >filelisting.txt

cd..

echo on

 

 

Now if you execute this batch file, it will automatically open a text file and give you some similar results.

 

C:\CORVUAPPS\CORVU\SHARED\Admin\filelisting.bat

C:\CORVUAPPS\CORVU\SHARED\Admin\filelisting.txt

C:\CORVUAPPS\CORVU\SHARED\Admin\ MeasureDef \BVPI299.pdf

C:\CORVUAPPS\CORVU\SHARED\Admin\ MeasureDef \BVPI170.pdf

 

Note: Please make sure that you don’t add your batch file in the same folder where the pdf reside but instead outside that folder. Make sure your PDF folder resides under CorVu Shared Folder, if you just want to use it in CorBusiness.

 

Now that you have the desired text file, open CorBusiness and import the text file. The whole path needs to be imported as one character field called Path, give a field width of 255 but scroll through to check if everything is coming through otherwise re-visit field width and increase size. Save the opened Query with a relevant name, I will be calling it Path.

 

In my case PDF names are the ones that I would like to display so I am going to concatenate file names excluding the extensions from the Path column.  Now add a character column called FileName width size 30, choose the formula option and add the following script.

 

Formula:

 

stdet = index(@[Path], “\\” ,5)+1;

st = to_number(stdet);

 

endet = index(@[Path], “\\”,5);

en = to_number(endet);

 

ret = en - st ;

len = length(@[Path]) - 4;

substr(@[Path], st , len)

 

 

Note: If you want to find out more about character search and concatenation then visit a previous blog about index function.  Click Here.

You may notice that there are two backslashes rather than one that’s due to a CorVu requirement, will discuss some other time but for now just follow the example.

 

After you have added Filename column it should give you just the filenames without the extension, if not then re-visit your formula.

 

Now convert this query to a quick report, group the report on Filename column and keep the Path field hidden.

 

Right Click on Filename Column, select Define Hyperlink Column.. , in the pop up window select Column Name Path and you will see that Measure Name have gone blue and underlined.

 

If you just want to display hyperlinks in CorBusiness then this should be adequate but if you want to display this report in HyperVu then check next week blog.

 

Although I am keeping this out of the scope of this topic but if your file names reference something in your system database e.g. CorStrategy Measures then you could write another query which has all necessary information from CorStrategy and then do a simple data join on Measure Code to provide a detailed report.

 

An example image will be provided next week, where users are prompted for a measure code, displays possible results and their current performance along with an option to show its definition PDF.

 

Shariq Wagener