Solved

Join issues with Crystal Report

Posted on 2016-08-03
10
86 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
[X]
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
10 Comments
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

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

 
LVL 35

Expert Comment

by:James0628
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).

 James
0
 

Author Comment

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

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
Doc1.doc
0
 

Author Comment

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

Accepted Solution

by:
James0628 earned 500 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.

 James
1
 

Author Closing Comment

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

Expert Comment

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

 James
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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