Creating a view from within Crystal reports

[aaronburro]

I'm a bit n00bish on SQL and how to utilize it, so I'm probably asking the dumbest question possible. Don't flame me too hard for it

Anywho, I'd like to create a view (I think) and then run the report on that view. How would I accomplish this? Do I create the view outside of Crystal or what?

 

[synapsevampire]

Please post some technical information:

Software version and edition (Crystal)
Database type and version

This is standard required to answer almost any question in any software forum. No worries, a common oversite by well seasoned coders.

Crystal has a thing called Business Views in the latest version, and most databases have Views which are in essence a SQL statement.

Rather than stating what you want ot create, you might also consider stating requirements and requesting that others assist you with the design phase, rather than stating that you've decided on an architecture and then asking people what it means and how to do it

-k

 

[aaronburro]

aight, here goes:

I'm trying to create a farily simple report that is little more than a drop ship form for our shippers to use. All I need to do is select one sales order from the database (keep it simple, initially). So, I create a parameter for the sales order in Crystal and adjust the record selection formula accordingly. When I run the report, instead of just grabbing one record from the database, Crystal grabs every stinking one. This is acceptable only if I don't need to link to any other tables. Unfortunately, I do, and this method is grabbing tons of records from the DB, when all is needed is probably ten or twelve records, tops. I've done all the optimizing I can on the selection to get it to pass through, but it simply won't do it. The formula is this:

{SO_03SOHistoryHeader.SalesOrderNumber} = RIGHT("0000000" & {?SalesOrderNumber}, 7)

That should be a constant expression, but Crystal won't pass it through.
After entering the parameter, the SQL query is as follows:

SELECT
SO_03SOHistoryHeader."SalesOrderNumber", SO_03SOHistoryHeader."OrderDate", SO_03SOHistoryHeader."CustomerNumber", SO_03SOHistoryHeader."BillToName",
SO_04SOHistoryDetail."LineIndex", SO_04SOHistoryDetail."ItemNumber", SO_04SOHistoryDetail."ItemDescription", SO_04SOHistoryDetail."OriginalOrderQuantity"
FROM
"SO_03SOHistoryHeader" SO_03SOHistoryHeader,
"SO_04SOHistoryDetail" SO_04SOHistoryDetail
WHERE
SO_03SOHistoryHeader."SalesOrderNumber" = SO_04SOHistoryDetail."SalesOrderNumber"

My guess at a solution is just to create a view from within Crystal so I will only have to run this on one record, instead of thousands.

Crystal version 8.5
Database is MAS200 provideX 3.7something

 

[synapsevampire]

There are no Views in Crystal 8.5

I tested this syntax in CR 9 and it worked fine, so you might consider rewriting a few things.

I'm not a MAS expert, but if you can create a SQL Expression to pad the order number in advance, it would make sense that it would more readily pass it.

You might also consider using something like:

{SO_03SOHistoryHeader.SalesOrderNumber} like "*"+{?SalesOrderNumber}

That should pass, the only issue is whether it will return the proper rows, which is based on the salesordernumber possibilities.

-k

 

[lbass]

If you use a SQL expression, it will pass to the SQL as SV mentioned, so couldn't you change the parameter to a number datatype and then create a SQL expression {%salesorderno}:

{fn convert({SO_03SOHistoryHeader.SalesOrderNumber}, sql_integer)}

Then use a record selection formula of:

{%salesorderno} = {?SalesOrderNo}

-LB

 

[aaronburro]

well, lbass, I tried that, and it is still pulling all the records.

the SQL generated is:

SELECT
SO_03SOHistoryHeader."SalesOrderNumber",
{fn convert(SO_03SOHistoryHeader.SalesOrderNumber, sql_integer)}
FROM
"SO_03SOHistoryHeader" SO_03SOHistoryHeader

In addition, I get a conversion error that pops up after it reads all the records as well :/

 

[aaronburro]

fixed the conversion error

 

[aaronburro]

sorry for the double post, but I neglected to mention that even w/ the conversion error out of the way, Crystal is still pulling all the records

 

[lbass]

Please show us your record selection statement (not the SQL query that is generated). When I test this, the converted value appears in the where clause set to the parameter.

-LB

 

[synapsevampire]

LB: It's likely the result of using CR 8.5 and MAS.

Did you try a LIKE predicate? Does that not work for you?

In LB's scenario, convert the parameter to a type number and your Record Selection should be:

{%MySQLExpression} = {?SalesOrderNumber}

-k

 

[aaronburro]

aight, record selection formula is:

{%SalesOrderNumber} = {?SalesOrderNumber}

SQL Query with that is:
SELECT
SO_03SOHistoryHeader."SalesOrderNumber",
{fn convert(SO_03SOHistoryHeader."SalesOrderNumber", sql_integer)}
FROM
"SO_03SOHistoryHeader" SO_03SOHistoryHeader

 

[aaronburro]

update:

I tried the same technique w/ a demo version of CR XI and it worked flawlessly. The SQL Query showed a WHERE statement asking for the specific record in the parameter. Interesting "feature" there in the software...

 

[synapsevampire]

...

I guess you'd prefer to not use a LIKE???? I think that you'll find that a LIKE passes it fine in CR 8.5.

I also have a whitepaper here in the FAQs which addresses SQL pass through. If you do decide that you'd like to try another approach, start a new thread as I won't reread this since you'd ignored my post.

-k

 

[aaronburro]

well, I couldn't get the LIKE to work with an integer. The scenario is that I'd like to make this as simple as possible for the people who will be using the report who, quite honestly, aren't the sharpest knives in the computer user drawer. Allowing them just to enter a number works great for that, instead of making them type in all the zeros before hand. I've found that I can't use a custom Crystal formula based on a Crystal parameter in 8.5 and have the results pass through to SQL.

For what its worth, I tried the "LIKE" method and that didn't pass through in 8.5

 

[synapsevampire]

It does pass in CR 8.5.

-k

 

[aaronburro]

could there maybe be a difference between the MAS CR 8.5 version and the "regular" CR 8.5?

 

[aaronburro]

well, this is rich. now CR XI doesn't pass the argument along to the SQL query, allegedly...

 

[aaronburro]

Update: Well, it seems that someone somewhere gave me some bad advice about the "Use Indexes or Server for Speed" checkbox and told me to uncheck it for my reports. Doing that has effectively disabled SQL passthrough. So, when I recheck the box, SQL passthrough works perfectly, or as perfect as Crystal can do it.

sorry for being a n00b, folks