Solved

Join issues with Crystal Report

Posted on 2016-08-03
10
62 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
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
 
LVL 34

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 34

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 34

Expert Comment

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

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Report Error 14 72
Combine and display field 5 73
String to Date Formula Failure 2 44
Crystal Reports Server, Unable to remove users 8 38
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. …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

19 Experts available now in Live!

Get 1:1 Help Now