Passing parameter to Footnote in macro

[AMUMOR_SAS]

Hi. I am linking StPs, see the attachment which is just an example. When I output to HTML, link is working, but when I output to XLSX, the link i not working. However, if I copy the URL from XLSX to the addressfield in Internet Explorer, it works. What do I miss in the URL in XLSX?

To make i possible for the user to get an XLSX-output, I am trying to make a work around by placing the URL in a footnote, see the code below.. However, I am having trouble with passing parameters to the URL in the footnote. What am I doing wrong? I have debugged and I am certain of that the variables have values.

The code is below:

.
.
.
/*  This code is for HTML output */

	%Else %Do;

		%STPBEGIN;

/* These variables have values */
		%Put 'Dato:' &Dato.;
		%put 'Levaar:' &Levaar.;
		%Put 'Kansv_Inp:' &Kansv_Inp.;
		%Put 'Valg:' &Valg.;

		ODS escapechar='^';

/* Title1 and Title2 gets values for &Levaar., &Dato. and &Kansv_Title. Why does it work for the Titles, but not for Footnote? */

		Title1 "^{style [font_size=14pt] ^{style [font_weight=Bold] ^{style [color=green] Vareleveranse for år &Levaar. pr. &Dato.}}}";
		Title2 "^{style [font_size=11pt] ^{style [font_weight=Bold] ^{style [color=black] Oversikt over varemottakere for &Kansv_Title}}}";

/* The variables in Footnote1 are not replaced by values, but they are passed as their name. Why? */

		Footnote1 link="%nrstr(do?_program=%2FLandkreditt+Bank+Informasjonsplattform%2FRapportering%2FAdministrasjon%2FStored+Process%2FUnder+arbeid%2FVareleveranse_StP_Print_Excel&Dato=&Dato.+&Levaar=&Levaar.+&Kundeansv=&Kundeansv+&valg=Excel)" "Open in Excel";

		Proc report data=work.Grunnlag_Overordnet_url nowd
				style (header)=[background=aliceblue];
				column Navn_Varemottaker Varemottaker Navn Leveranseaar Sum_Salgsverdi Sum_Omsetningsverdi Sum_Fratrekk;

/* Viktig: Kolonne Navn_Varemottaker benyttes aom input parameter til neste StP, men er ikke vist i rapport fordi den inneholder UTF-8-tegn */
			define Navn_Varemottaker /display Noprint;
			define Varemottaker /display;
/*			define Navn_Varemottaker /display;
			define Leveranseaar /display;*/

			compute Varemottaker;
				/*urlstring='https://www.google.no/?q='!!strip(varemottaker)!!trim(left(Navn));*/

/* This is the URL which is working in HTML, but not in XLSX. However, if I copy the URL from XLSX to the addressfield i Internet Explorer, it works.  */

urlstring = 'http://fdpsas001test.landkreditt.local/SASStoredProcess/do?_action=form%2Cproperties%2Cexecute%2Cnobanner%2Cnewwindow&_program=%2FLandkreditt+Bank+Informasjonsplattform%2FRapportering%2FAdministrasjon%2FStored+Process%2FUnder+arbeid%2FVareleveranse_StP_Print_Kansv&Dato='!!"&Dato."!!'+&Levaar='!!"&Levaar."!!'+&Aar_Ifj='!!"&Aar_Ifj."!!'+&Fra_Aar='!!"&Aar_Fra."!!'+&Navn_Mottaker='!!strip(Navn_Varemottaker)!!'+&Mottaker='!!strip(varemottaker)!!'+&Kundeansv='!!&Kansv_Inp!!'+&valg='!!"&Valg.";

call define ('varemottaker','URL',urlstring);

endcomp;

		Run;

		%STPEND;
	%End;

Regards

Morten Fredrik

[Kurt_Bremser · Accepted solution]

%nrstr is  a macro function for masking special characters and therefore prevents resolution of macro variables and macro calls.

[AMUMOR_SAS]

Thank you. I'll try without %nrstr.

Regards

Morten Fredrik