Solved

subreport in crystal reports

Posted on 2014-11-12
14
356 Views
Last Modified: 2014-11-13
I'm not sure if I'm choosing the  best way to create a report for my needs and would like your help.  
I'm using Crystal Reports 2008.

I need a report that is showing me the summary information for the amount of rejects by type and vendor for a given period and calculate the percentage based on the dollars spent.

I have one database that will have my rejection information and a separate database that has the vendor spend information.

I created a Vendor Issues Summary Report which uses the reject database.  Then I created a Vendor Spend Subreport that sums up the spend for a given period.

I tried to put the subreport in the main report and linked on VendorID but I don't get anything to display.  

Does this have something to do with where I have the summations? Or because I'm doing summaries?

After I get the total spend dollars I need to perform some calculations to display the impact %

I have attached both my main report and subreport.  I also attached a word document that shows that the outcome is supposed to display when the report is finalized.  (This was from an old program that we no longer use)




Is this the best way to handle this type of report?
Vendor-Issues---Summary.rpt
Vendor-Spend-Subreport.rpt
Sample.docx
0
Comment
Question by:ITworks
  • 6
  • 5
  • 3
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40438388
Where did you put the subreport?

I put the subreport in the vendor group header and linked on the vendor and got the data to display

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
ID: 40438548
Well I feel stupid.  I can't get it to work.  I have attached the screenshots of where I put the subreport  and how I have the link set.  Is this correct?
screenshot.docx
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40438772
Looks exactly like what I did

SHowing data
mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 4

Author Comment

by:ITworks
ID: 40439813
Do you have certain sections suppressed or hidden?screenshotscreenshot
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 40439916
That is what mine looks like.

I added the subreport to this version

mlmcc
Vendor-Issues---Summarywith-subreport.rp
0
 
LVL 4

Author Comment

by:ITworks
ID: 40440015
I'm an idiot.  How do I download this and save it as *.rpt?
0
 
LVL 4

Author Comment

by:ITworks
ID: 40440052
Nevermind.  I was able to open your report.

But I still don't see the subreport results!

I did nothing but enter in parameters.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40440599
Can you refresh the report and save it with data.  Then post the report back here.

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
ID: 40440778
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 40440902
There's something fairly sneaky going on that I believe is causing the problem.

 You have Vendor in the main report, which is a string, and vendor_id in the sub-report, which is a number.  The two fields do appear to both contain a vendor "number", but since they're different data types, you can't link them directly.  So mlmcc linked from Vendor in the main report to the group name in the subreport, which is also a string.

 The problem is that when CR converts the numeric vendor_id field to a string to create the group name, it uses the default format that you've selected for numbers (under File > Options, on the Fields tab).

 If your default format is no decimal places or thousands separator, then you get a group name like "12345", which will match the "12345" in the Vendor field in the main report.

 But if your default number format is different, you get a string like "12,345.00", which will _not_ match the "12345" that's in the main report, so you get no data in the subreport.

 I'm guessing that mlmcc has his default number format set so that he gets results like "12345", but your format is set so that you get something else, like "12,345.00".

 One way to "fix" it would be to change your default number format, but:
 a) You may not want that to be your default format.
 b) The report would rely on the default being set that way to work.

 You could try changing your default setting just to see if you get results, but I don't think it's a good long-term solution.

 A better solution would be to convert the numeric vendor_id to a string yourself, so that you can control the formatting.  Assuming that your Vendor field is always just the number, with no leading spaces or anything else, create a formula in the subreport similar to the following:

CStr ({Command.vendor_id}, "#")

 Then link Vendor in the main report to that formula in the subreport.  Then I believe you'll get results from the subreport.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 40440954
I forgot to mention, if you want to try changing your default number format to see if you get results, close the report and re-open it (or possibly refresh it) after you change the default.  Simply changing the default setting probably won't change what has already been displayed.  I couldn't refresh the report without your data, so I had to close the report and re-open it to get CR to re-convert the vendor_id values in the saved data to strings using the new format.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40441454
Good catch James.  My default for numbers is no decimals.  I wasn't aware of that so either that is the default for CR2008 or it got set that way at some point and I forgot.

mlmcc
0
 
LVL 4

Author Closing Comment

by:ITworks
ID: 40441461
I tested it as James suggested and that was the problem.  So I did create the formula to convert the vendor ID and all is working now.

Thanks guys!
0
 
LVL 34

Expert Comment

by:James0628
ID: 40441863
You're welcome.  That was a tricky one.  Those are always fun/interesting (when you figure them out :-).

 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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