subreport in crystal reports

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?
Sue TaylorProject ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Sue TaylorProject ManagerAuthor Commented:
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?
Looks exactly like what I did

SHowing data
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Sue TaylorProject ManagerAuthor Commented:
Do you have certain sections suppressed or hidden?screenshotscreenshot
That is what mine looks like.

I added the subreport to this version

Sue TaylorProject ManagerAuthor Commented:
I'm an idiot.  How do I download this and save it as *.rpt?
Sue TaylorProject ManagerAuthor Commented:
Nevermind.  I was able to open your report.

But I still don't see the subreport results!

I did nothing but enter in parameters.
Can you refresh the report and save it with data.  Then post the report back here.

Sue TaylorProject ManagerAuthor Commented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

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.

Sue TaylorProject ManagerAuthor Commented:
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!
You're welcome.  That was a tricky one.  Those are always fun/interesting (when you figure them out :-).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.