Join issues with Crystal Report

Posted on 2016-08-03
Medium Priority
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?
Question by:Joe Brown
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
LVL 101

Expert Comment

ID: 41741648
Can you show the data structure of the spreadsheet and the table?


Author Comment

by:Joe Brown
ID: 41744680
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?

Author Comment

by:Joe Brown
ID: 41744683
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.
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

LVL 35

Expert Comment

ID: 41745065
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).


Author Comment

by:Joe Brown
ID: 41747620
Ok, I will try and see what I can do with your suggestion James.

Author Comment

by:Joe Brown
ID: 41747770
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

Author Comment

by:Joe Brown
ID: 41747775
Is there a way to do a Full (Outer) Join in Crystal?
LVL 35

Accepted Solution

James0628 earned 2000 total points
ID: 41748037
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.


Author Closing Comment

by:Joe Brown
ID: 41749645
Thank you for your assitance.
LVL 35

Expert Comment

ID: 41750754
You're welcome.  Glad I could help.


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

752 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