Dynamic hyperlink syntax

[Naith]

Hey,

Can one of you kids verify the syntax of this hyperlink:

http://CrystalServerName/crystal/en...=Y&prompt3=Regulatory&prompt4=Date(2002,8,21)

The idea is that {Database.Field} should act as a dynamic populace for the link. i.e. click on the field when the value is 8 - then the same report executes again, but with a new value of 8 being passed in, instead of the previous 7, say.

Trouble is, the dynamic aspect is completely ignored. The link simply re-executes a second example of the original report, based on the original values. So I get outputs of 7 ad infinitum - regardless of the value of the hyperlink field.

The parameter's default value is null.

I'd appreciate any efforts to point me at enlightenment.

Thanks for your time,

Naith

 

[Naith]

Turns out Crystal say that the correct syntax is actually entered in the formula section as:

"[URL unfurl="true"]http://Crystal[/URL] ServerName/crystal/enterprise/ePortfolio/en/viewrpt.cwr?id=1110&apsuser=Administrator&apspassword=***&apsauthtype=secEnterprise&prompt0=2&prompt1=" & {Database.Field} & "prompt2=Y&prompt3=Regulatory&prompt4=Date(2002,8,21)"

This is at opposites with what their whitepaper says - and it still doesn't pass the value of the field to the url.

Doesn't anybody make use of this functionality?

Naith

 

[lyanch]

I use it. Here is one example from one of my reports.

"

http://server:84/report_center/view...&apspassword=&apsauthtype=secEnterprise"

+ "&promptex-catalogId=" + '"' + {catalog.catalogId} + '"'

You are using prompt1 instead of promptex with the prompt name, also your password looks incorrect. This example also shows how to add quotes around string paramters.

Lisa

 

[Naith]

Hi Lisa,

Thanks for your response.

My password was masked - but the aps details are okay.

Can I just ask if your example queries a stored procedure? Querying a database, the syntax is fine. But against a stored procedure, it's being naughty 100% of the time.

Could you also verify whether the "Prompt the user for new value(s) when viewing" option has to be checked on for this? I understand that if it's not activated, then the promptexes are ignored. But if they are on, they seem to physically prompt each runtime, and ignore the promptexes anyway.

Thanks for your time,

Naith

 

[lyanch]

That one wasn't, but I do have ones that do. One thing I do know, if my fields are enclosed in single quotes.. it doesn't work, also you need to include the @ in the promptex like "promptex-@ID=xxxxx"

I would start with what your currently getting.. then play around with the string till it works. Then go back to Crystal and make it match what works.

Lisa

P.S. it depends on the viewer as well.. none of my links work correctly from the DHTML viewer.

 

[lyanch]

I also forgot that it isn't just string paramters that need quotes around them, all paramters do.. they are initially sent as a string..

Lisa

 

[Naith]

Thanks for your response, Lisa. You've been very helpful.

I got caught out with the Prompt vs Promptex. I thought Promptex was simply the evolution of Prompt, but that Prompt was still supported. (at least, that's what the URL Commands white paper says.[

http://support.crystaldecisions.com/communityCS/TechnicalPapers/ce8_urlcommands.pdf

])

I switched to Prompt# because my url just barely scrapes 254 characters, so I'm trying to save space where possible. Switching back to Promptex# did the trick for numerical and string fields. (I'm just using syntax like: "&promptex1="+'"'+"Whatever". I must confess that I'm not sure what you mean when you say that you have to include the @. As far as I was aware, you only pass the @ when you're indicating a parameter that's based in a subreport - kinda like a database link.

What I'm wondering though - and perhaps you can help me out here - is how do you pass DateTime values to your stored procedures?

In the hyperlink window, you can just whack in "xxx=DateTime(x,x,x,x,x,x)", but I'm using the formula facility to pass the link - as you are too. Passing this way, the dates never make it into the report. They cause Enterprise to moan about 'The syntax for the value of prompt' being incorrect, or return a Page Not Found error.

This follows attempts to pass the DateTime as:

"&promptex4=" + '"' + {@Date} + '"'
[{@Date} = "DateTime("+CStr({?@BusinessDate},'yyyy,MM,dd,HH,mm,ss')+&quot"]
"&promptex4=" + '"' + "DateTime(2002,1,1,0,0,0)" + '"'

"&promptex4=" + '"' + "DateTime%282002,1,1,0,0,0%29" + '"'

...and the list goes on.

Have you any experiences of passing datetimes to stored procedures accurately?

Thanks,

Naith

 

[lyanch]

Here is the syntax for a link to a report with a stored proc using a parameter in the original report:

"

http://server/report_center/viewrpt...ord=&apsauthtype=secEnterprise&prompt0="

+ '"' + "B" + '"' + "&prompt1=" + '"' + "DateTime("+CStr({?dateparam},'yyyy,MM,dd,HH,mm,ss')+&quot" + '"'

For some reason Crystal didn't like it when I actually put this in a hyperlink field..

It kept truncating it and adding

http://"

to the start of it.. I think that may be another issue. If I actually copy the text output of this into a browser, I get the report as expected.

Hope this helps...

Lisa

 

[Naith]

Yeah, if I copy the url using the Cstr date conversion to IE, I come up with the trumps too. But that kind of defeats the whole purpose of using the hyperlink. It appears that it isn't possible to pass dates to stored procs - which it seems you've also encountered - unless I'm hearing you wrong.

I'm doing a little more investigation into these shenanigans, but if date passing to a sproc over a url is really non-deliverable, then that's a rather glaring "feature" on the application. If you or anyone finds otherwise, I'd really appreciate an update.

Thanks again for your time.

Naith

 

[Naith]

Commas don't show up on Crystals encoding white paper as a symbol which needs to be encoded [

http://support.crystaldecisions.com/library/kbase/articles/c2003259.asp

] - but they do need to be.

When passing a date or datetime value, it needs to be passed as DateTime(yyyy%2CMM%2c...etc) as opposed to DateTime(yyyy,MM,...etc)

The hex equivalent of commas works with hyperlinks.

Naith

 

[lyanch]

Thanks Naith, I know I would have had to have dealt with it for real as opposed to test reports in the future.. now I know the answer

Lisa

 

[MeDeeSa]

Lisa/Naith:

I noticed in this thread that you are very knowledgeable about passing parms in the URL. I was hoping you could help me out... How do you pass null values in the URL? It seems I've tried every combination from both CE and the CSP and nothing works... any suggestions?

Thanks!

Dee

 

[Naith]

If you think I'm knowledgable about URLs you must be reading my posts wrong.

Can you stipulate the syntax of how you're currently attempting to pass nulls, including what happens, and whether you're reporting against a stored procedure or directly against the database.

Naith

 

[MeDeeSa]

Naith:

Keeping in mind that I've already logged in to CE and don't have to pass credentials, here are a couple of the hyperlinks I've tried (promptex1 should be null)...

http://wccd-xdv-trw2/epmisrpts/view...ot;&promptex3="0"&init=JAVA:connect

http://wccd-xdv-trw2/epmisrpts/view...ot;&promptex3="0"&init=JAVA:connect

http://wccd-xdv-trw2/epmisrpts/view...ot;&promptex3="0"&init=JAVA:connect

On the CE side, I've tried all of the combinations of "set to null" and "Prompt for new values". None of the combinations work for passing nulls and passing actual values. If I "set to null" and don't "prompt", CE works with promptex= but ignores a value for promptex1. If I don't "set to null", CE doesn't show anything when I pass a null.

According to the ce8_urlcommands.pdf, the word null should work... it is actually in one of their examples (the one to show how to pass ranges).

Hope this isn't too confusing. Let me know if I can clear anything up...

Dee

 

[Naith]

Dee,

The correct way to pass nulls is (allegedly) passing the string null, with no quotes, or - which works in my experience - &promptex1=&promptex2=xxx.

I can see that you've tried both ways here. Could you confirm that if you pass a legitimate non-null value into promptex1 that you get a valid output?

Naith

 

[MeDeeSa]

Naith:

Here are the combos I've tried:

prompt1=
Set to Null - Yes
Prompt for value - Yes
Report does not use all parms so blank report displays

prompt1=409679
Set to Null = Yes
Prompt for value - Yes
Report is correct

prompt1=
Set to Null - Yes
Prompt for value - No
Report is correct

prompt1=409679
Set to Null - Yes
Prompt for value - No
Report ignores value as if a null was passed

prompt1=409679 or null
Set to Null - No
Prompt for value - No
Report ignores null and value and uses default value

Thanks,

Dee

 

[MeDeeSa]

Naith:

By the way, crystal enterprises is executing a Stored Procedure on the back-end. Does that make a difference?

Dee

 

[Naith]

Stored procedures don't make a difference. That's what I use.

The last scenario is what I was expecting to see. Try repeating the process from a couple of other clients with internet connections. If they behave differently, you may have to reinstall your browser, which, in my experience, is where the caching of the original instance is occuring.

Naith

 

[MeDeeSa]

Naith:

I'm working with Crystal Support - hopefully they will help resolve the issue and I'll let you know the outcome...

Thanks!

Dee

 

[MeDeeSa]

Naith:

Well, we finally figured out that the problem is with the Java viewer provided by Crystal. It doesn't work with the JAVA viewer....

Thanks!

Dee