Link to home
Start Free TrialLog in
Avatar of JameMeck
JameMeckFlag for United States of America

asked on

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.
Avatar of Mike McCracken
Mike McCracken

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
Avatar of JameMeck

ASKER

Thank you very much, please take a look the picture below.
CrystalReport.PNG
Sorry, I forgot, the shift data from the subreport appears only 1 time.
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
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.
> 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
Thanks! I do what you said, but when I press the button "Save and Close" , it shows error.
What kind of error?  What does it say?

 James
"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.
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 ?
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
James0628 do I really need to point out the flaws in your suggestion ?

SHARED stringvar ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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.


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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks GJParker!
But how can I show the pictures only 1 time.
Please take a look the file below.
Test-DailyReport3Graph.pdf
 > 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
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
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.
Thank you very much!
You're welcome.  Glad I could help.

 James