Solved

subreport in crystal reports

Posted on 2014-11-12
14
338 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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. …
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

17 Experts available now in Live!

Get 1:1 Help Now