Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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?
Vendor-Issues---Summary.rpt
Vendor-Spend-Subreport.rpt
Sample.docx
0
ITworks
Asked:
ITworks
  • 6
  • 5
  • 3
2 Solutions
 
mlmccCommented:
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
 
ITworksAuthor 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?
screenshot.docx
0
 
mlmccCommented:
Looks exactly like what I did

SHowing data
mlmcc
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
ITworksAuthor Commented:
Do you have certain sections suppressed or hidden?screenshotscreenshot
0
 
mlmccCommented:
That is what mine looks like.

I added the subreport to this version

mlmcc
Vendor-Issues---Summarywith-subreport.rp
0
 
ITworksAuthor Commented:
I'm an idiot.  How do I download this and save it as *.rpt?
0
 
ITworksAuthor 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.
0
 
mlmccCommented:
Can you refresh the report and save it with data.  Then post the report back here.

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

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now