• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

Pass values to parameter field from another report.

Hi,
i have a report that that basically compare sales report for each customer for last year and this year, so report looks like this

Cust No    Sales Total (Year 07)   Sales Total (year 08)
1234           $560                             $980

now i have another report (different file) that takes in the "customer No" and tell me what each customer ordered this year vs last year. right now we have to manually type the "Cust No" is there a way to pass them to the 2nd file?

Thanks
0
Raul77
Asked:
Raul77
  • 14
  • 10
  • 5
  • +2
4 Solutions
 
crgary_txCommented:
make the 2nd report an on demand subreport. In the subreport create a parameter for Customer No and link the Customer NO field in the main report to the subreport parameter.

let us know if you need help further..
0
 
Raul77Author Commented:
thanks for the response, but i am totally new to CR can you give me some explanation on how to create a on demand subreport?
using CR V 9.0
0
 
watsonjCommented:
The way I would do this is use your second report which holds all the details only, then group by customer. You could then supress the details and drill down for them. If you need them for print though you could use the same report as two sub reports in one main report. There may be a better way todo both in one report, but I'd need to play with it, unfortunately my iPhone doesn't have cr on it...
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
watsonjCommented:
On demand is fine, as long as you don't need to print the report, so how are you planning on using it?
0
 
crgary_txCommented:
>go to Insert->Subreport
>Enable Choose an existing report (incase you have already built the 2nd report)
check 'On-demand subreport at the bottom
>Browe to your 2nd report
>Click on the link tab
choose the customer no field  from "Available Fields' and drop it in 'Fields to link to:'
At the bottom select your customer field parameter for 'subreport parameter to use:
>click ok and come out of the screen
>place the sub report in the section where you are displaying the customer no and summary
Now the subreport will be sitting as an hyperlink when you click on the hyperlink the customer no in that group is passed and the subreport runs for the particular customer no

hth
0
 
Raul77Author Commented:
thanks, before i try this, seems like i cant print on demand report? is that right? i definitely need to print this report.

Thanks
0
 
crgary_txCommented:
you can print the subreport if you can print the main report.
Now do you want to print the 1st report and the 2nd report in one click then you have to make the static subreport (not on-demand)t so that the subreport also printed when the report is run. But this will have an impact on the performance of the report
0
 
watsonjCommented:
Actually, use your second report but add another report header, in the top headed add a cross tab, they're a pain but you should get all the details from your first report summarized in there.
0
 
Raul77Author Commented:
mmm its not going to work, the reason is i use a program to generate these reports and the program doesnt like ondemand reports or hyperlink, so lets forget about that, if i could generate a field in FIRST report to just display all the customer No in a like separated with "," it would help, user can simply copy that line and past in the program to generate 2nd report, that would work 2, any easy way for me to modify the FIRST report to show whats it showing now as well as add a line to the bottom with all the customer No ... i need to be using running total since i am using suppress formula.
0
 
watsonjCommented:
Ok, back k to basics, how are you getting g the data, is it through a SQL query?
0
 
Raul77Author Commented:
yeah, we use a program , and someone made all this CR reports for it, now the program is great but the problem is user need to first generate report 1, then manually type in the program Cust No, and get report 2.i have modified report 1 using suppress forumal to NOT show me any customer that has NOT purchased any item in Year 08, and its working, now if i could add a line to list all the customer numbers that is is showing in one line, separated by "," user can simply copy that line and paste into program again to generate report 2.

appreciate the help.
0
 
crgary_txCommented:
do you want to pass all the customer Nos as a comma seperated values to the subreport and make subreport run for all the customer nos in the sting at once?

What is the data type for customer no? String or number: assuming its number do the following:

create a formula say customer_no in the main report:

whileprintingrecords;
stringvar cust;
cust:=cust+totext({Customer_no},0,"")+",";

The above formula gives you all the customer nos as a comma seperated: eg 1001,1002,

>insert the 2nd report as a subreport where ever you want on the main report.
>drop the formula you created (@customer_no) from available fields: to fields to link to:
>select ?Pm-@customer_no from the drop down for subreport parameter field to use
>disable the check box for 'Select data in subreport based on field'
>click ok and come out of the sub report screen

>Now right click on the subreport and edit subreport
>use the following record->Selection formula:

totext({table.customer_ ID},0,"") in split({?Pm-@customer_no},",")

now the subreport should run for all the customer ids populated in the main report.

hth..
Gary

0
 
Raul77Author Commented:
i actually DONT want to pass anything to 2nd report, lets forget the whole second report, all i want is a Field in FIRST report to list the reported cust no.

think of the report as this
Cust No    Sales Total (Year 07)   Sales Total (year 08)
1234           $560                             $980
2223          $233                             $567
1212         $556                             $566

"THIS IS wHAT I WANT" a box showing "1234,2223,1212"
i am going to try and see if the formula you gave will do the trick.
0
 
crgary_txCommented:
The formula should give you what you want: I went a step ahead to explain how that string can be used as parameter to filter the other report

"create a formula say customer_no in the main report:

whileprintingrecords;
stringvar cust;
cust:=cust+totext({Customer_no},0,"")+",";

The above formula gives you all the customer nos as a comma seperated: eg 1001,1002,"
0
 
crgary_txCommented:
you need to place the above formula in the detail b section and supress the detail b using the below formula:

Go to report->section expert
select details b, click x+2 tab next to supress and place the following formula:

not onlastrecord

this is because if you place the formula on the footer its gonna print just the last value..
0
 
mlmccCommented:
Can you join the 2 data files so it can all be in a single report?

mlmcc
0
 
Raul77Author Commented:
ok we are very close, there is one problem left ! using the formula and using the detail b to on the not onlastrecord,
it is displaying ALL THE cust no, but as i mentioned i am using a suppress forumula
"Sum ({sp.SalesP2}, {sp.Org1}) = 0" --> which will IGNORE the customer if their salesP2 is ZERO.

if we can make the field report only those customers we are done ... so right now the report shows

Cust No    Sales Total (Year 07)   Sales Total (year 08)
1234           $560                             $980
2223          $233                             $567
1212         $556                             $566

but i am getting here "1234,1234,2545,566,333,455 and so on" while it should only show me the 3 cust no. its showing everyone's.
0
 
crgary_txCommented:
i think you should try:

whileprintingrecords;
stringvar cust;
if salesp2 <>0 then
cust:=cust+totext({Customer_no},0,"")+",";
0
 
Raul77Author Commented:
All the formulas below return nothing:

whileprintingrecords;
stringvar cust;
if {sp.SalesP2}<> 0 then
cust:=cust+totext({sp.CustNo},0,"")+",";
-=-=-
whileprintingrecords;
stringvar cust;
if Sum ({sp.SalesP2}, {sp.Org1})> 0 then
cust:=cust+totext({sp.CustNo},0,"")+",";

***This will return ALL Cust No.***

whileprintingrecords;
stringvar cust;
if Sum ({sp.SalesP2}, {sp.Org1}) = 0 then
cust:=cust+totext({sp.CustNo},0,"")+",";

0
 
Raul77Author Commented:
i dont get it
this Returns ALL :
whileprintingrecords;
stringvar cust;
if (Sum ({sp.SalesP2}, {sp.Org1})) = 0 then
cust:=cust+totext({sp.CustNo},0,"")+",";

This return NOTHING

whileprintingrecords;
stringvar cust;
if (Sum ({sp.SalesP2}, {sp.Org1}))< 0 then
cust:=cust+totext({sp.CustNo},0,"")+",";

OR this

whileprintingrecords;
stringvar cust;
if (Sum ({sp.SalesP2}, {sp.Org1})) > 0 then
cust:=cust+totext({sp.CustNo},0,"")+","; .....
0
 
crgary_txCommented:
In what section are you placing your records ie:
Cust No    Sales Total (Year 07)   Sales Total (year 08)
1234           $560                             $980
2223          $233                             $567
1212         $556                             $566

looks like you are placing either in a group footer/group header. In that case you need to great GFb/GHb section by doing insert->section below. Not the detail b section. After creating the GFb section, place the below formula:

whileprintingrecords;
stringvar cust;
if  Sum ({sp.SalesP2}, {sp.Org1})<> then
cust:=cust+totext({sp.Custno},0,"")+",";

And then suprress the group b section as described in my above thread:

Go to report->section expert
select Group footer b/header b, click x+2 tab next to supress and place the following formula:

not onlastrecord

I tested on my machine and this worked for me!

Gary
0
 
Raul77Author Commented:
I think we are "almost" there... i did as you said
now it does this

Cust No    Sales Total (Year 07)   Sales Total (year 08)
1234           $560                             $980
1234,
2223          $233                             $567
1234,2223,
1212         $556                             $566
1234,2223,1212

how can i make it show only the last one? when i drag the formula to "report footer" it doesnt show anything, when its in Header B it does like above.

Thanks.
0
 
crgary_txCommented:
In what section are u placing the string? You need to supress that section conditionally so that i prints only for the last record. Here's how you do:

>Go to report->section expert
>select section in which you are placing the string.  click x+2 tab next to supress and type  the following formula:
not onlastrecord;
>click ok and come out of the screen

now it should show like this on your report:

1234           $560                             $980
2223          $233                             $567
1212         $556                             $566
1234,2223,1212,

0
 
Raul77Author Commented:
did that, same, if i supress using
not onlastrecord;
i get NOTHING, if i remove "not onlastrecord;"
i get like how i explained above. i have attached the image.
CR-Report.JPG
0
 
James0628Commented:
I was staring at this, trying to figure out what could possibly be wrong and drawing a complete blank, until it finally hit me.  You're testing for onlastrecord in the group header.  That's never going to be true, except maybe if it was the last group on the report and there was only one record in the group.

 Try creating a group footer 1b (or just use GF1, since you don't seem to have anything else in it) and moving crgary_tx's formula there, with the same suppression.  That should work.

 James
0
 
Raul77Author Commented:
same exact result James .... :(
with "not onlastrecord" nothing is shown

if i remove it, it does this:

Cust No    Sales Total (Year 07)   Sales Total (year 08)
1234           $560                             $980
1234,
2223          $233                             $567
1234,2223,
1212         $556                             $566
1234,2223,1212
0
 
Raul77Author Commented:
Update: i played with it more and this is intresting:
when i use this formula:

whileprintingrecords;
stringvar cust;
if  Sum ({sp.SalesP2}, {sp.Org1}) <> 0 then
cust:=cust+totext({sp.Custno},0,"")+",";

it doesnt show me anything if i suppress using "not onlastrecord;"

BUT if use this formula:

whileprintingrecords;
stringvar cust;
if  Sum ({sp.SalesP2}, {sp.Org1}) = 0 then
cust:=cust+totext({sp.Custno},0,"")+",";

and do Suppress using "not onlastrecord"
it gives me cust No of "ALL" customers on the last record. (which is we want (only on the last record))

so if we could somehow combine the 2, we are there !!!
0
 
Raul77Author Commented:
i think CR doesnt know when the last record is , thats why its causing problem, is it possible to perhaps store the string in Cust_result and just print that once in report footer?
0
 
James0628Commented:
Weird.  OnLastRecord does work in general.  I can't see any reason that it wouldn't work in this case (in the group footer).  While not knowing why it's not working does bug me , the quickest solution may be to just forget about it in this case and move on.

 You pretty much hit the nail on the head, but you don't need to store the string anywhere else.  You've already got the result in a variable and you can just use that in the report footer.  Just suppress the section that your current formula is in (just a simple suppression, not using a formula like "not OnLastRecord") and create a new formula in the report footer that just says:

whileprintingrecords;
stringvar cust;

cust


 That should do it.

 James
0
 
Raul77Author Commented:
Thank you guys, its working now :) It was really hard splitting the points since you all really helped me !
0
 
James0628Commented:
You're welcome.  Glad I could help.

 The OnLastRecord thing still bugs me, but I'm sure that I'll move on to the next mystery soon enough and forget about this one.  :-)

 James
0
 
crgary_txCommented:
The records on the report seen are not the detail records but the summary records with details supressed based on some condition. I believe the reason we are not getting the expected results is because Onlastrecord function works  for the detail record and not for the group record.
0
 
James0628Commented:
I tried using Not OnLastRecord to suppress a group footer and it worked.  I only saw the last group footer.  So, it does work for a group footer in general.  Whether the details are suppressed (for whatever reason) or not shouldn't matter.  The records are still being read.  If there were some additional conditions that could cause the group footer to be suppressed, that could be a problem, but if it was just Not OnLastRecord, it seems like it should have worked.  

 James
0
 
James0628Commented:
Hmm.  I suppose it might be something different about CR 9.  I'm using CR 10.

 James
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 10
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now