Solved

Join issues with Crystal Report

Posted on 2016-08-03
10
46 Views
Last Modified: 2016-08-10
I have an excel spreadsheet that I am linking to our ERP system. The spreadsheet contains a forecast number for each product code for each customer (in a specific region) by quarter. The goal is to have the report display the forecasted number along with the actual quantity shipped for that quarter for that product for that customer.
     Product Code     Forecast Q1     Sold Q1
     Product Code     Forecast Q2     Sold Q2 (etc...)

 I was successful when creating a subreport for the quantity sold value and tying that (by a formula to return Q1 or Q2 or Q3 or Q4) to the same field in the forecast spreadsheet

The issue is with this that if there is nothing forecasted for a specific quarter in the excel spreadsheet (main report) it will not return a quantity shipped value if product was shipped in that quarter that was not forecasted (on the spreadsheet database). I can not figure out how to change the join of a subreport

Essentially, I only want records that have a value greater than 0 either for the forecasted amount or the actual quantity sold. If I use the tables that have the quantity sold information in them in the main report, I can tie the customer field and the product field but not the Quarter field.

Does this make any sense?
0
Comment
Question by:Joe Brown
  • 6
  • 3
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you show the data structure of the spreadsheet and the table?

mlmcc
0
 

Author Comment

by:Joe Brown
Comment Utility
Spreadsheet:
Division (example: 20)
Dist. Code (example: AE2912)
Distributor Name      (ABC Distributing)
Quarter      (example Q1)
Year      CODE (example: 2016)
ITEM Code (example: B1A005/010)
FORECASTED qty (example: 20)

There are a tables that are connected to provide the proper info. How best to transfer that information over to you?
0
 

Author Comment

by:Joe Brown
Comment Utility
All structure matches up except that the spreadsheet has the info by Quarter and the tables have a date. The reason I initially had a subreport was because I made a formula to display the quarter in the same fashion and then tied that field to the Quarter field in the spreadsheet. I'm basically trying to figure out how to write this so I have a UNION JOIN.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I don't think that you can get a Union result when you're using the tables "directly" (adding the individual tables to the report(s)).  If you want a Union of the data in the tables and the spreadsheet, you may have to create your own query.  A CR Command might be an option.  That's a query that you create in CR and it's stored in the report.  But I don't know if you can use two different datasources (tables and a spreadsheet) in a single CR Command.  If not, maybe you could use something like a stored procedure in the db.

 If your db has something like MS SQL linked servers, that could simplify things a bit.  You could create a linked server for the spreadsheet, which would let you use it like another table in the db (more or less).  You would still need to create a query to combine the two sets of data, but referencing the spreadsheet via a linked server would be simpler, and I think that should avoid the issues that are raised in CR when you use more than one datasource (since, as far as CR is concerned, the spreadsheet would be just another table in the db).

 James
0
 

Author Comment

by:Joe Brown
Comment Utility
Ok, I will try and see what I can do with your suggestion James.
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.

 

Author Comment

by:Joe Brown
Comment Utility
I have attempted to link the excel spreadsheet as a Linked Server on my SQL Database but I was not successful. I see it listed under the Linked Servers, however, there are no Tables or Fields shown
Doc1.doc
0
 

Author Comment

by:Joe Brown
Comment Utility
Is there a way to do a Full (Outer) Join in Crystal?
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
CR does seem to support Full Outer Joins, but I guess it depends on your datasource(s).  If it's not an option in this case, I would guess that it's because you're using an Excel file, or maybe because you're using two different datasources (tables and an Excel file).  But even if you could use a Full Outer Join, it might not work.  Under certain circumstances, CR will change an Outer Join to Inner.  There's a good chance that your Full Outer Join wouldn't stay Outer.  That's one advantage to creating your own query (eg. in a CR Command, or a stored procedure in the db).  You control the query and CR won't alter it (eg. change the joins).

 I guess you could try a Cross Join, where you don't link the Excel file to the other tables at all.  Then every record in the tables would be repeated for every row in the Excel file, so I guess you'd be getting everything from both sets of data, but with a ton of duplication.  It sounds really messy, at best, but maybe you could get it to work.

 As for the linked server, I don't know why yours isn't working, but if you create your own query (instead of trying to link the tables and Excel file in CR), then you could use something like OPENROWSET to read the Excel file.  Assuming, of course, that your db supported something like that.

 James
1
 

Author Closing Comment

by:Joe Brown
Comment Utility
Thank you for your assitance.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

10 Experts available now in Live!

Get 1:1 Help Now