Pass data to a sub report

[Former Member]

I have a report that produces differences between today and yesterday, and looks like this, just 3 fields.

Array Alias Growth Array serial#

Phx_t1_0343 1004.00 000192600343

phx_t2_2489 6144.00 000192602894

I want to use the values for "array name" as a selection criteria for a following

sub-report.

As a test i created a formula which created a shared variable :-

shared stringvar testname := "phx_t1_0343";

and placed in report header.

In the subreport, i created a formula :-

shared stringvar testname;

and placed in subreport report header.

It works fine, and displays "phx_t1_0343"

So i thought i would be able in the record selection of the sub-report

to specify :-

{SRMARRAY.ARRAYALIAS} = {?testname}

but i do not see the formula ?testname

Is this normal ? is there a better way to pass the "array names" to the subreport ?

Thanks in advance

[abhilash_kumar · Accepted solution]

Hi John,

Shared variables cannot be used in the Record Selection formula.

In this case, you need to create a string variable that will accumulate the 'Array Name' and pass this string variable as a parameter to the sub-report. You can then use this parameter in the sub-report's selection formula:

So, create a formula in the Main reoprt like this:

whileprintingrecords;
stringvar array_name;
array_name := array_name + {array_name_field} + ",";

Place this formula in the details section beside the array name. You can choose to suppress this formula.

Place the sub-report in the Report Footer and then right-click the sub-report and select Change Subreport Links > Move the formula to the 'Fields to link to' area and uncheck the option 'Select data in sub-report based on field'.

In the sub-report's selection formula use this formula:

{array_name_field} in {?parametername_from_mainreport}

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash, your suggestion works fine. It enables the subreport

to have a record selection.

Now another question ?

the subreport is trying to determine differences between "yesterday" and

"today". It uses a .xls file for both days. Same number of columns/data format.

I want to display what has changed between both files ?

The only way i can think to accomplish this via linking

is left outer join

Sheet1_.Run Date/Time 1/30/2012 9:36:47AM --> Sheet1__1.Run Date/Time 1/29/2012 7:26:40AM

to produce differences

and then left out join(in another subreport)

Sheet1__1.Run Date/Time 1/29/2012 7:26:40AM --> Sheet1_.Run Date/Time 1/30/2012 9:36:47AM

is there a more graceful way to compare two .xls files ? i understand they have no relationship(via keys)

or should i create a checksum(psuedo key for each column in the spreadsheet) that i can use to link with.

[abhilash_kumar · Accepted solution]

Hi John,

I don't think I quite understand the second query.

So, you have two tables with the same number of columns and you wish to show the difference?

Difference as in values that do not match?

Why don't you do an equi join and select '!=' (not equal) as the Link Type?

-Abhilash

[Former Member · Accepted solution]

Abhilash, it seems i spoke to soon about your suggestion working for passing data to a subreport

I have done what you suggested, but the subreport always asks for a parameter input, and seems to be ignoring the link i set up ?

in record selection in the subreport i have :-

{Yesterday.F6} in {?Pm-@arrays with change}

any other suggestions ?? thanks John

[abhilash_kumar · Accepted solution]

Hi John,

If you run the sub-report as a separate report it WIll prompt for the parameter. Could you check if you're refreshing the Main report and not the subreport?

-Abhilash

[Former Member · Accepted solution]

I tried both.

if i refresh main/subreport i get the message "main report and subreport data will all be refreshed", yet my subreport is blank.

if i click reresh in subreport i get prompted for a param value if i enter a value, subreport works.

Its behave like nothing is passed to the subreport.

[abhilash_kumar · Accepted solution]

Alright, so the sub-report returns data when it is run individually.

Could you see if the array variable is actually accumulating values or not?

Could you create another formula just to call the variable and place it on the same section as the sub-report, perhaps just above it.

whileprintingrecords;
stringvar array_name;

Could you check if this variable contains values or not?

And, check the linking again.

-Abhilash

[Former Member · Accepted solution]

ok i've found the problem, original formual in details sections was :-

whileprintingrecords;
stringvar array_name;
if {@Daily Difference} <> 0 then
array_name := array_name + {SRMARRAY.ARRAYSERIALNUMBER} + ",";

so array name was never populated if @daily difference was zero.

i changed the formula to :-

whileprintingrecords;
stringvar array_name;
if {@Daily Difference} <> 0 then
(
array_name := array_name + {SRMARRAY.ARRAYSERIALNUMBER} + ","
)
else
array_name;

now the link works fine. Thanks Abhilash

[abhilash_kumar · Accepted solution]

Oh Yes, Glad it works John!

-Abhilash