Passing a parameter from lobby element to quick report

Hi @dfsjmarchant, This is probably because the RESOURCE_ID is a STRING (VARCHAR) which for comparisons are surrounded by ‘ ’, indicating the start and end of the value. 

Normally if you type in a url and use the ‘ character, they are automatically replaced by %27 . 

There seems to be a parameter (I'm on 23R2) on the lobby element page, which allows you to overrule the datatype.

Please check if that works. If not, you can leave that setting on and manually add the %27 like so: quickreport516536?AREA=%27$[RESOURCE_ID]%27

That’s interesting, we’re yet to take 23R2, so that will fix it I assume. 

I’ve actually combined your suggestion with the update to the query in the Quick Report to strip out the quotes, and that works,

 replace(&AREA, '''', '')

Thank you

@dfsjmarchant I am curious how/if you got this to work (we are on v22.2.21).  We are having the same issue as above and I have added the replace function to the parameter variable in the query (which is accepted as a valid query in the Quick Report design screen).  However, when I test passing a parameter surrounded by the single quotes (through the UI), it does not even attempt to run the query and instead gives a “malformed URI” error message.  

From what I can tell - even if I have the code to strip the single quotes from the parameter in the SQL code - the parameter is throwing an error at the time of the submission of the value to the query and never makes it to the SQL statement.  

Hi 

It’s not an ideal solution, it’s a bit of a compromise for us… but this is what I do…

Below is the Lobby Element, and the navigation link:

On clicking “Area 1” it takes you to the quick report.

Then on the quick report… within the query… 

WHERE lower(RESOURCE_GROUP) LIKE lower('%' || replace(&RESOURCE_GROUP, '''', '') || '%')

I’m not sure if that helps.

I’d really like to pass through a start date, but I’m struggling with that. Its frustrating that there isn’t a date picker within the quick report parameter list.

I’m doing my best to learn here, so hopefully it’s given you a bit of help.

Cheers

James

@dfsjmarchant This is strange because when I create the Navigation URL in the Lobby Element with the “%27” HTML encode characters - they do not get translated to “double-quotes” in the parameters screen of the quick report (like yours does).

Instead - this is what I see prepopulated in the parameters screen:

Somehow - yours shows double-quotes around the value.  

Which by the way still may be a problem b/c I tried manually putting double-quotes around the value (instead of single) and I still the get the malformed URI error - but that is a different challenge.  For some reason, you can get your query to work with these doubles being stripped - but I cannot.

Do you think it has anything to do with the datatype of the parameter? I.e. it’s a number, and not a string?

Just for reference, this is passed through in the URL

@dfsjmarchant Yeah - it is most likely related to the data type - which we have no control of overriding in our current version.  It looks like we just going to have to sit tight with what we have until we are upgraded to a more recent version that provides the “Ignore Data Types During URL Parameterization” function - and then test how that works.

Another problem I’m seeing, it comes through to the Quick Report OK, and the quick report displays fine. However… if you then try to Export the data to Excel, it causes an issue. 

@dfsjmarchant We have had this exact issue before as well - and it was solved by developing IAL (Information Access Layer) objects - which from what I understand are somewhat the equivalent of SQL Server views and/or summary tables.  They can be updated on certain intervals in the background or at run-time depending on your business requirements.

Information Access Layer (ifs.com)