Crystal Report Subreport Link.

I have a crystal report with a subreport. The database contains data:
Shift
A
A
A
B
B
A
C
...
A
G
The main report displays the all data from the Shift column,
and I want to link the subreport to display only distinct data from the column Shift.
My Idea is: Create a parameter to contain all distinct data from the Shift column, how can I do that?
Please help me if you have any idea.
JameMeckAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
GJParker is right.  You won't be able to use a shared variable in a record selection formula.  I didn't think about that when looking at mlmcc's formula.

 However, there is a simple solution for that.  Pass the value as a parameter, instead of using a shared variable.

 Create a string parameter in the subreport.  Let's call it List.  Use that parameter in the subreport's record selection formula, instead of the variable:

InStr({ShiftField}, {?List}) > 0


 Create a new formula in the main report that just says:

Shared StringVar ShiftList

 That new formula will just output the values accumulated in the ShiftList variable.


 Use "Change Subreport Links" and link the new formula in the main report to the parameter in the subreport.

 James
0
 
mlmccCommented:
What is the other data?

What do you want the subreport to display?

What would be the distinct data?

what do you want to do with it?

mlmcc
0
 
JameMeckAuthor Commented:
Thank you very much, please take a look the picture below.
CrystalReport.PNG
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
JameMeckAuthor Commented:
Sorry, I forgot, the shift data from the subreport appears only 1 time.
0
 
mlmccCommented:
Is the subreport using a different table?

If not simply use the same selection filter as the main report.

Try this idea
In the main report report header add a formula
WhilePrintingRecords;
Shared StringVar ShiftList;
""

In the detail section
WhilePrintingRecords;
Shared StringVar ShiftList;
If InStr({ShiftField},ShiftList) = 0 then
    ShiftList := ShiftList & {ShiftField};
""

In the subreport use this as the selection formula
WhilePrintingrecords;
Shared StringVar ShiftList;
InStr({ShiftField},ShiftList} > 0

mlmcc
0
 
JameMeckAuthor Commented:
Thank you very much, I have done what you said.
But I can't understand, please help me more.
Fist, for the main report, I created 2 formulars
+   SharedShiftList: WhilePrintingRecords;
Shared StringVar ShiftList;
+   DetailSharedShiftList: WhilePrintingRecords;
Shared StringVar ShiftList;
If InStr({ShiftField},ShiftList) = 0 then
    ShiftList := ShiftList & {ShiftField};
Then for the subreport I created a new formular:
+ SelectionFormular: WhilePrintingrecords;
Shared StringVar ShiftList;
InStr({ShiftField},ShiftList) > 0


So what should I do next? I tried to use "Change Subreport Links" function, but nothing was displayed on the subreport.

Please helo me more.
0
 
James0628Commented:
> Then for the subreport I created a new formular:

 You're not creating a new formula in the subreport.  You're entering that as the record selection formula in the subreport.  Open the subreport then go to Report > "Selection Formulas" > Record and enter that last formula there.

 If you handle it this way you won't be using the subreport links.  The formulas in the main report put the Shift values in a shared variable named ShiftList.  Then the record selection formula in the subreport looks in that variable to see which Shift values to include.

 James
0
 
JameMeckAuthor Commented:
Thanks! I do what you said, but when I press the button "Save and Close" , it shows error.
0
 
James0628Commented:
What kind of error?  What does it say?

 James
0
 
JameMeckAuthor Commented:
"The formula cannot be evaluated at the time specified"
And please tell me more, did I create these:
+   SharedShiftList: WhilePrintingRecords;
Shared StringVar ShiftList;
+   DetailSharedShiftList: WhilePrintingRecords;
Shared StringVar ShiftList;
If InStr({ShiftField},ShiftList) = 0 then
    ShiftList := ShiftList & {ShiftField};

By Formula Field. Is it correct?
Or do I have to create it by Section Formulas?

Please help me, I am new for Crystal Report.
0
 
GJParkerCommented:
You won't be able to use shared variables as part of your record selection formula because tehse aren't evaluated until print time.

Can we go back to basics here and explain a little more about your report design and why you need a subreport. is the data acoming from a separate source / table etc.

What is the layout of teh data in teh subreport and how do you want it displayed on teh main report ?
0
 
GJParkerCommented:
James0628 do I really need to point out the flaws in your suggestion ?

SHARED stringvar ?
0
 
mlmccConnect With a Mentor Commented:
Try it this way

Change the SHARED to GLOBAL

Delete the formula from the subreport

Add a formula to the main report
WhilePrintingRecords;
Global StringVar ShiftList;
ShiftList

Use that formula to link to the subreport field

Edit the subreport
Change the record selection to (use the names from your report)
InStr({ShiftField}, {?PM_List}) > 0

mlmcc
0
 
James0628Commented:
GJParker,

 I could be wrong, but I'm pretty sure that there's no problem there.  At least, not as I understand the situation.  The variable is shared in the main report, but the value is passed to the subreport via a parameter, so the subreport is not using a shared variable and the parameter should work fine in the record selection in the subreport.

 James
0
 
James0628Commented:
Argh.  I meant to include this and forgot.

 As mlmcc mentioned, if you use the subreport parameter, you could change the variable in the main report from shared to global, but I really don't think it's necessary.  I don't think it matters one way or the other.  The variable doesn't _need_ to be shared, since the value is being passed via a parameter instead, but I don't think there's any harm in it being shared, for the same reason.

 James
0
 
JameMeckAuthor Commented:
Dear Mr. James0628,
As you said: "Create a new formula in the main report that just says:

Shared StringVar ShiftList

 That new formula will just output the values accumulated in the ShiftList variable."

Where does the  ShiftList variable get data?

and I did what you said: Use "Change Subreport Links" and link the new formula in the main report to the parameter in the subreport.

But the subreport show everything in my Table2 even there is nothing in the Table1.

Thanks to Mr. mlmcc! I did what you said, but the result is same, the subreport show everything in my Table2 even there is nothing in the Table1.

Please help me.


0
 
JameMeckAuthor Commented:
I am sending my file, please take a look for me.
My job: I have 2 tables:
Table1: Data about Shift Name and total quantity, this data is displayed in the main report by graph.
Table2: Data about Shift Name and picture of each Shift.
When I select data from the Table1, if there is only shift L in the Main Report --> The subreport shows a pictue of shift L only, if the Main Report shows 3 Shift L, N, J --> The subreport displays the pictures of all 3 shifts.


Thanks!
Test-DailyReport3Graph.rpt
0
 
GJParkerConnect With a Mentor Commented:
Why can't just add the table HRTShiftPicture in to your main report and link on
HRTDataDetail.Shift -> HRTShiftPicture.ShiftName and do away with the subreport all together
0
 
JameMeckAuthor Commented:
Thanks GJParker!
But how can I show the pictures only 1 time.
Please take a look the file below.
Test-DailyReport3Graph.pdf
0
 
James0628Commented:
 > Where does the  ShiftList variable get data?

 You would use the first 2 formulas that mlmcc posted back in message 33573979.  You use those formulas to put the values in the variable.  But then instead of using the last formula in that message for the record selection in the subreport (which won't work because of the shared variable), you're using this new formula (that just outputs the value in the variable) in the main report, and linking that new formula to the new parameter in the subreport.  The record selection in the subreport uses that parameter.


 > ... if the Main Report shows 3 Shift L, N, J --> The
 > subreport displays the pictures of all 3 shifts.

 OK, I think something may have been lost in translation.  It sounds like you're saying that that is wrong, but I thought that that was what you wanted.  Going back to your original post:

 > The main report displays the all data from the Shift column,
 > and I want to link the subreport to display only distinct data
 > from the column Shift.

 So, if the main report shows shifts L, N and J, don't you want the subreport to show the pictures for all 3 of those shifts?  If not, then what do you want?

 Assuming that you do actually want the subreport to produce the pictures for all of the shifts in the main report, there is a basic problem with the report that you posted (the actual RPT file, not the PDF file).

 You have the subreport in the report header.  That's not going to work.  What mlmcc was trying to do is store the shift values in a variable.  Those will be stored as the records are read, so the list won't be complete until the report footer.  In the report header, you might have the value from the very first record, but that would be all.

 If you can move the subreport to the report footer, and add the other 2 formulas that mlmcc posted (one in the report header section and one in the detail section), this should work.

 James
0
 
James0628Commented:
Oops.  I just realized that mlmcc got the arguments to InStr backwards.  After posting all of that, I finally tried actually testing the changes I suggested, and it still wasn't working.  I was very confused until I finally figured that out.  :-)

 The detail section formula should be:

WhilePrintingRecords;
Shared StringVar ShiftList;
If InStr(ShiftList, {HRTDataDetail.HRTShift}) = 0 then
    ShiftList := ShiftList & {HRTDataDetail.HRTShift};
""


 ShiftList is now the first argument to InStr, instead of the second.


 Likewise, the record selection formula in the subreport needs to be changed.  It should be:

InStr({?PM_List}, {HRTShiftPicture.Shift Name}) > 0


 I can't really test this, because when I change the record selection in the subreport and try to view the report, CR wants to connect to the db.

 James
0
 
GJParkerCommented:
You uill probably need to create a group in the main report for shift and place the images in there and that will ensuer the image is only displayed once. You shoulkd then be able to format the group with multiple columns to get the images side by side.

If you are stuck the attach a copy of the report with the 2 tavbles linked in the main reportwith saved data.
0
 
JameMeckAuthor Commented:
Thank you very much!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.