Solved

Crystal Reports Subreport details

Posted on 2016-10-04
16
38 Views
Last Modified: 2016-10-06
Hi Experts -
Trying to get a subreport to show only those details which match details from the main report.
If I set a subreport link, it only grabs the value of the last item in the detail list... so, {Command.RESOURCE_ID} = {?Pm-Command.RESOURCE_ID} results in my subreport only showing the record for 'BRAKE135' and not bringing in data for '21' - both resources should be displayed in the subreport.
I'm probably missing something obvious, but my crystal reports frustration level is so high right now.

 would be nice if this worked
0
Comment
Question by:tkriegel
  • 7
  • 5
  • 4
16 Comments
 
LVL 1

Author Comment

by:tkriegel
ID: 41828765
Can you let me know if you can see my embedded picture?  I can never see these when I preview >:-\
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41829168
WHere is the subreport?

You probably need it to be in the detail section to get all the values.

If you are trying to show it in a group or report footer, you would need to collect the ids then pass it in a shared valriable to the report.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 41829663
Yes, I can see your image (and you presumably can, now that you've come back to this page), although I'm not sure what it's supposed to be showing us.  You have BRAKE135 and 21 in the top part, and then a boxed section below that that I would assume was the subreport, but you said that the subreport was only showing BRAKE135, and that section seems to include many different values.  I'm going to assume that that is the subreport without the link.


 I assume that BRAKE135 is RESOURCE_ID?  Obviously if you link on RESOURCE_ID, then the subreport will only include one RESOURCE_ID at a time (the value in the current record in the main report, which will depend on where you put the subreport).

 It seems that BRAKE135 and 21 are the values in a specific group (?) in the main report.  If so, then what you need to link on are the values that will give you that same group of items in the subreport.  For example, if those are the records for part # 5256764 between dates X and Y, then link the part # and dates X and Y to the subreport (and not RESOURCE_ID).  This is assuming that the data in the subreport includes the required fields (in my example, the part # and date).  If not, then, as mlmcc suggested, you may need to build a list of the desired RESOURCE_ID values and pass that to the subreport.  Whether or not that's even an option will depend on where the subreport is located.

 James
0
 
LVL 1

Author Comment

by:tkriegel
ID: 41829865
The subreport was located in the report footer, which was the root of the problem.  I've moved it to the Details B section, and it now brings up data for both resources 21 and BRAKE135.  While this is great, it's still not what I need.

I now have:

Main report, detail section , records for resource id 21
Subreport, detail records for resource id 21
Main report, detail section for BRAKE135
Subreport, detail records for BRAKE135

Is there a way to keep them separated? ...keep main report details together, keep subreport details together?

There is no grouping in the main report, the records are pulled based on a part number parameter.  The is grouping in the subreport, since I need to return summary totals based on the resource id.
Current links are part id and resource id.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 41829905
Yes there is a way to do it.

DO you only want 21 and Brake135 in the sub report?
Try this

IN the main report header add a formula

WhilePrintingRecords;
Shared StringVar Array ResourceList;
Global NumberVar ResourceCount;
''

Open in new window


In the detail section add a formula
WhilePrintingRecords;
Shared StringVar Array ResourceList;
Global NumberVar ResourceCount;
Local NumberVar Index;
Local BooleanVar ResourceFound;
Index := 0;
ResourceFound := False;

While (Index <=  ResourceCount) AND (Not ResourceFound) do
(
     Index := Index + 1;
     If {YourResourceFIeld} = ResourceList [Index] then
         ResourceFound := True;
);
If Not ResourceFound  then
(
    ResourceCount := ResourceCount + 1;
    ReDim Preserve ResourceList [ResourceCount];
    ResourceList [ResourceCount] := {YourResourceFIeld};
);
''

Open in new window


Add a formula to the report
Name - LinkList
WhilePrintingRecords;
Shared StringVar Array ResourceList;
Join(ResourceList,';');

Open in new window


Link the subreport using the formula to the Resource field in the subreport.

Edit the subreport
CHange the selection formula for the resources to

{YourResourceField} IN Split({?pm_ResourceParameter},';')

mlmcc
0
 
LVL 1

Author Comment

by:tkriegel
ID: 41829960
I'm getting an error "A subscript must be between 1 and the size of the array" which then takes me to this line:
Index := Index + 1;
     If {Command.RESOURCE_ID} = ResourceList [Index] then
         ResourceFound := True;
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41830201
Change the comparison in the IF to just < rather than <=

mlmcc
0
 
LVL 1

Author Comment

by:tkriegel
ID: 41830273
Changing the formula did prevent the error, but didn't change anything else and I've actually taken a step backwards.  It's now only displaying BRAKE135 again and a blank subreport under the resource 21 info:

sstar-snip.pdf

The bottom portion is obviously not Crystal, but my desired look once I've manipulated it in Excel.

Thanks!
0
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.

 
LVL 100

Expert Comment

by:mlmcc
ID: 41830577
You need to put the subreport in the report footer where you had it originally.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 41830585
Does the subreport data include the part number?  I suspect not, but you didn't say.  If it does, you can forget the formulas and just link the subreport on the part number, so that it can read the resources for the same part #.

 Assuming that that's not an option ...

 You apparently have the subreport in a detail section.  It should be in a later section now, like the report footer.  You want it somewhere after all of the detail records have been read.  The main formula is building a list of resources, and the list won't be complete until the last record has been read.

 But that doesn't explain why 21 isn't showing up.  With the subreport in a second detail section, the subreport after the first record should show the first resource, the subreport after the second record should show the resources from the first 2 records, and so on.  I'm not saying that that's what you want to see, but it's what you should see, if mlmcc's main formula is in the first detail section and the subreport is in another detail section under that.  There must be something else going on with where you placed mlmcc's formulas or something.

 If you would post the report (the actual .rpt file), we could check the formulas, etc.

 Also, FWIW, mlmcc's main formula could be simplified as follows:

WhilePrintingRecords;
Shared StringVar Array ResourceList;
Global NumberVar ResourceCount;

If not ({Command.RESOURCE_ID} in ResourceList) then
(
    ResourceCount := ResourceCount + 1;
    ReDim Preserve ResourceList [ResourceCount];
    ResourceList [ResourceCount] := {Command.RESOURCE_ID};
);
''

Open in new window


 He used a While loop to see if the current resource was already in the array, but you can just use In for that.  And if you get rid of the While loop, then you don't need the Index or ResourceFound variables either.

 James
0
 
LVL 1

Author Comment

by:tkriegel
ID: 41830675
I've tried the subreport in all sections to no avail.  
The reason that I need to limit the subreport in the manner that I'm trying is because there actually many resources that are tied to any given part number.  In the attached report, you'll see them all if you remove any subreport links that reference resource_id.  I need to limit records based on the main engineering master which is displayed in the details of the main report.
In my example, the master has only 21 and BRAKE135, so these are the only resources I want the subreport to show.
The other subreport, 'materials' is working fine.

Just a note - I cannot open ANY attachments, even my own that I've posted, so I won't be able to view any changes if you post them via attachment.  ( get this error:  the authenticity of the received data could not be verified)

Thank you all for your help - I really appreciate it!

Southstar-TRIAL.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41830747
You made the change to actuals subreport but not to the materials subreport.

Also remove the link you were using originally

mlmcc
0
 
LVL 1

Author Comment

by:tkriegel
ID: 41830798
Not sure I understand, mlmcc...  There are no changes that need to be made to the materials subreport, that is correctly pulling the bill of material based on the part id from the main report.

The only links I have in place for the actuals subreport are the part_id (workorder_base_id) and the linklist link.  The linklist link returns no records, so I probably reverted back to the old link of {Command.RESOURCE_ID} = {?Pm-Command.RESOURCE_ID} for the upload.
Am I misunderstanding what you're telling me?
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 41830849
mlmcc was probably just assuming that you were looking for the same things in the materials subreport.

 First, move the actuals subreport out of the page footer.  CR won't expand the page footer, so the subreport will be cut off (unless you expand the subreport object so that it's big enough to hold the entire subreport).  You can add another report footer section and put the actuals subreport there.

 The actuals subreport is empty because you didn't use the record selection test that mlmcc had at the end of his earlier post.  You have:

{Command.PART_ID} = {?Pm-Command.workorder_base_id} and
{Command.RESOURCE_ID} = {?Pm-@linklist}

 The problem is that {?Pm-@linklist} contains a string with a list of values, like "1;2;3", so {Command.RESOURCE_ID} = {?Pm-@linklist} generally won't work.  The only time it will work is when the list only contains one value (eg. "1"), which explains the results that you got earlier, when you had the subreport in a detail section (as shown in the PDF file that you posted).  After the first record, the list just contained "BRAKE135", so you got that record in the subreport.  But after the second record, the list contained "BRAKE135;21", which didn't match anything, so you got no records in the second subreport.

 Anyway, you need to split the list into separate values and check those, as in mlmcc's earlier post.  Change the record selection formula in the actuals subreport to:

{Command.PART_ID} = {?Pm-Command.workorder_base_id} and
{Command.RESOURCE_ID} IN Split({?Pm-@linklist},';')


 James
0
 
LVL 1

Author Comment

by:tkriegel
ID: 41831763
James - That was exactly the missing piece!  Report is working wonderfully now.  Thanks to both you and mlmcc for all your help!
0
 
LVL 34

Expert Comment

by:James0628
ID: 41832694
You're welcome.  Glad I could help.

 James
0

Featured Post

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.

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…

760 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

20 Experts available now in Live!

Get 1:1 Help Now