Solved

Crystal Report Subreport Link.

Posted on 2010-08-31
24
869 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:JameMeck
  • 9
  • 8
  • 4
  • +1
24 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:JameMeck
Comment Utility
Thank you very much, please take a look the picture below.
CrystalReport.PNG
0
 

Author Comment

by:JameMeck
Comment Utility
Sorry, I forgot, the shift data from the subreport appears only 1 time.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:JameMeck
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> 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
 

Author Comment

by:JameMeck
Comment Utility
Thanks! I do what you said, but when I press the button "Save and Close" , it shows error.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
What kind of error?  What does it say?

 James
0
 

Author Comment

by:JameMeck
Comment Utility
"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
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
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
 
LVL 34

Accepted Solution

by:
James0628 earned 167 total points
Comment Utility
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
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
James0628 do I really need to point out the flaws in your suggestion ?

SHARED stringvar ?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 

Author Comment

by:JameMeck
Comment Utility
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
 

Author Comment

by:JameMeck
Comment Utility
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
 
LVL 19

Assisted Solution

by:GJParker
GJParker earned 166 total points
Comment Utility
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
 

Author Comment

by:JameMeck
Comment Utility
Thanks GJParker!
But how can I show the pictures only 1 time.
Please take a look the file below.
Test-DailyReport3Graph.pdf
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
 > 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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
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
 

Author Closing Comment

by:JameMeck
Comment Utility
Thank you very much!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now