Joined: 06 May 2014
Online Status: Offline
Posts: 1
Topic: how to declare the variables -Please Help Posted: 06 May 2014 at 11:46am
Hi Experts,
I have a report parameter that captures the values the way users selects on the UI selection seperated by a comma as"abc:3:1000,xyz:4:3304,pqr:6:8900" and may have
possibilites of 8 string values ie. they can select upto 8
selected string values thru the UI and each of these string values have a look up
table column as view.lookup_column for those id's. example:
abc:3:1000=code1 xyz:4:3304=code2 pqr:6:8900=code3 and so on as the description
Now I have to display the result as
Expected result : code1,code2,code3
under the column named selected params thru UI.
How Can I achieve the expected result? , heard of variables but not sure how to use.
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Posted: 08 May 2014 at 5:12am
This is a tough one.
My go to solution is a stored procedure as you can break the comma delimited string into its parts and use the view to look and return the 'true' value...and not everyone has the ability to use stored procs for a variety of reasons.
The next thought that comes to mind is a subreport. Since there is a limit of 8 strings you could use the SPLIT function to create an array of selected codes and assign them formulas (which would be the variables...more later) and then you could link the formulas to the subreport as parameters and have the parameters link to the view, which would then display the 'correct' values for the report.
hopefully a quick example will be helpful:
//first formula in the main report to split the string and populate the variables
shared stringvar c1:="";
shared stringvar c2:="";
shared stringvar c3:=""; //continue for all 8
local stringvar array sp:=split({?parameter}, ",");
if ubound(sp)>0 then c1:=sp[1];
if ubound(sp)>1 then c2:=sp[2];
if ubound(sp)>2 then c3:=sp[3]; //etc...fill the shared variables
"" //will hide the output of the formula
several other formula will be created that look like:
//lets call the formula p1-p8
shared stringvar c1
then, I am hoping as this might not work, and you will know really quick...
create the subreport with 8 paramaters, call them say, p1-p8
Link the subreport to the formula with p1 in the main report linking to p1 in the subreport.
for complete simplicity, I would create 8 commands in the subreport like:
select * from table where lookup_column = {@p1}//changing the parameter for each parameter
it's worth try. I am not sure if it will work as the formulas in the main report are only 'initialized' at run time, which might be too late for CR.
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Posted: 08 May 2014 at 10:23am
Why does the user have to input 8 items separated by commas into one field? Couldn't there be 8 input parameter fields for input, then at report time, concatenate the fields with a comma in-between?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum