I cannot get this Script to Work Please help

[Former Member]

Hi everyone, I am brand new to Crystal reports which I am not sure how to make this script work. I was given this text file script to enter in the Add command for Crystal reports.: The benefit plan summary. Which I want to combine this other Script that would give me the at cost for my Frames which is the second file. Calle Frame Usage and Cost and Sales Price. How do I combine the pair?

Benefit Plan Summary.txt.zip

Frame Useage with Cost and Sales Price.txt.zip

[Former Member · Accepted solution]

Aurthur, 

You can only have one command ( or stored procedure ) per crystal report. 

so you then have a couple of options.  

You could rewrite the command into a single command.  ( How to do that is a different forum and I'm assuming that writing SQL code is beyond your current abilities )    or have whoever gave you the code do it.

....or  you could make the plan summary command drive the main report and than build a subreport using the frame useage command  and then you would need to add the subreport to the main report and link them based on a common field  (  Like Plan number ???   - I don't have the time to dive deeply into the code someone gave you.

[DellSC · Accepted solution]

Actually, technically, you can have more than one command or stored proc, but it can significantly slow down your report.  See my blog post for more information about using commands:  http://scn.sap.com/community/crystal-reports/blog/2015/04/01/best-practices-when-using-commands-with...

I've downloaded both of your attachments - let me see what I can do to combine them for you.

-Dell

[DellSC · Accepted solution]

Looking at the two queries, the Benefit Plan Summary is summarized at the order level.  However, it doesn't look like the frame cost is summarized.  You'll probably need to get someone who knows this data well to combine this information for you.

Also, you'll want to change the hard-coded DateTime values for the start and end dates to parameters so that you don't have to change the values every time you run the report.  See the link I provided above for more information about how to work with parameters in commands.

-Dell

[Former Member · Accepted solution]

Hey Dell,

I have been reading up on all the informtion you have provided so far. It has been really useful. So the Benefit Plan Summary is the main report that I want all my data to come out of. The Frame usage with cost and price has what I need for the at cost. Which in the Benefit Plan summary has a piece of the Frame price retail only it is missing the at cost piece which is where I am trying to figure out how do I "Cut" and paste that small piece of information to the main Benefit Plan Summary. I know it should be pretty simpte for the SAP people to see it that I am not getting so far. Also I did change the dates both txt.file to have the same date as 10/01/2015-10/31/2015. So they are both matching the correct dates now.

[DellSC · Accepted solution]

The challenge is that you have to combine the queries in such a way that you have the cost as well as the price in the same query.  I'm not sure that it will be a simple task to do this and you would be better off getting the person who sent you the queries to do the combination for you.

-Dell

[Former Member]

Hello Dell

hope things are well for the Holidays. I took your advise and tried to track down the person who created these querys. Of course they no longer work for the company, so I am back to square one. These queries I sent you I feel as though should not be hard to put together by someone who has more experience in Crystal Reports. I tried putting a piece together myself the problem I seem to be experiencing to me is that there are soo many pieces of selections on the back side when I start a new report, I tried placing them together, with no success so far. How about you? How did you get really good at doing reports? I have a book at home on how to do Crystal Reports for Dummies. I even paid for two classes at New Horizons, but still pretty lost to understanding how this all works.

[DellSC]

It's not Crystal that's the issue, it's knowing where the data is in your database.  I write a LOT of queries and I know a lot of tricks for writing efficient SQL, but both of the queries are complex.

I have been using Crystal for @19 years - since version 4.5 - so I've written a lot of reports.  I also have written a lot of SQL queries in my work as a software developer.  The only time that I don't use a Command in Crystal is when I'm pulling data from only one or two tables.  Generally, I can write better SQL than Crystal can, so that's what I do.  I also answer a lot of questions about Crystal online - I learn from trying to figure the answers to things that I don't know how to do.  It just takes time and patience.

-Dell

[Former Member]

Arthur,

As Dell suggests,  leaning how to use Crystal to display the results is only part of the solution to your problem.   Crystal is a powerful tool and it can actually do a lot of data translation and processing.  But there are some things that can only be done effectively and efficiently on the query side. 

There is a book ( one of many available that I found very helpful in getting started that I can recommend.   It starts at the very beginning assuming you have no prior knowledge of how to put together a query and it's general enough so it doesn't really matter which particular "Flavor" of SQL you are dealing with.  

"SQL Queries for Mere Mortals"     by Hernandez & Viescas   ( search for it on amazon or ebay )

That should get you far enough so that you can "read" and understand the queries that were already written.       

Ted

[Former Member]

Hi Ted,

great advise and helpful information about the book. "SQL Queries for Mere Mortals". I just purchased my book from Barnes and Nobles. Should be arriving this Friday Nov 27! Of course I have to hit the hardest part of getting Data from Crystal for a beginner. I would like to ask you something else. On my computer desk top I noticed an application called Teradata SQL assistant. Do you know what that is used for?