Posted on 2011-02-17
Medium Priority
Last Modified: 2012-05-11
I have a Crystal Report based on this SQL Command and uses 3 parameters.

BEcause I want to use dynamic parameters I want to create a VIEW in DB.

The problem is that I SUM quantity requested and quantity shipped for the date range given in the subquery.

If i use a view I would not be able to use this. Any ideas.

I do not want to try a 4 table join because items may not match.

select a.stock_number,a.description,a.category,vendor vendcode,
   (select decode(sum(quantity_requested),null,0,sum(quantity_requested))
   from stock_requested_item c, stock_request d
    where c.request_id=d.request_id and c.stock_number=a.stock_number
    and d.request_date between {?P_Begin_Date} and {?P_End_Date}
    and  d.ship_to_org like decode('{?P_Customer}','*All Customers*','%','{?P_Customer}' )
) Total_Ordered,
   (select decode(sum(quantity_shipped),null,0,sum(quantity_shipped)) from
     stock_shipped_item e, stock_shipment f
      where e.shipment_id=f.shipment_id and e.stock_number=a.stock_number
     and f.shipment_date between {?P_Begin_Date} and {?P_End_Date}
     and (e.dispcode is not null )
     and  f.ship_to_org like decode('{?P_Customer}','*All Customers*','%','{?P_Customer}' )  
) Total_Shipped
 (select name||', '||city||', '||state)
 from org where orgcd='{?P_Customer}'
) Customer
  from stock_item a, org b  
  where a.vendor=b.orgcd(+)
  and stock_type in ('P','C')
order by 1

Question by:sam15

Expert Comment

ID: 34923531
I don't see the problem? Does that SQL work and output what you want when you execute(obvioulsy when you put the date ranges in ect instead of the {?})?

Why can't you create and use that sql as a view?
LVL 17

Expert Comment

ID: 34923622
Create either a VIEW or even better use a Stored Proc as the datasource.

Just create a separate SQL Command to aggregate your SUMS based on your requirement, then link to your main data table. You may need to use the MIN() function to display the SUM amounts,...but if I'm understanding your question, it would not be a difficult workaround...

Hope it helps...
LVL 26

Accepted Solution

Kurt Reinhardt earned 2000 total points
ID: 34923837
You won't be able to create the SQL above as a View because you have parameters in the main SELECT clause of the SQL.  To Crystal Reports, a view is the same as a table.  You select fields from it and append values to a WHERE clause against it through your record selection criteria.  The parameters can't bed passed in to the view anywhere but the WHERE clause...

Creating a stored procedure will essentially be the same as the command you've created - you can pass a parameter value in to the body of the SQL, but you'll have the exact same limitations in Crystal Reports as you do with the command - you won't be able to use dynamic parameters. Dynamic parameters have to be built as a Crystal Reports feature, but command and proc parameters have to be built into the actual SQL and will be imported into Crystal Reports as soon as you use the object as a data source.

Using a SQL command in conjunction with any other data sources is a bad idea in terms of performance.  Commands must be executed in entirety before any joins or filters in Crystal Reports can be performed.  So, if you have a command joined to other tables, the command has to bring back 100% of it's records before it's ever joined to the other tables and any record selection criteria against those tables will now happen on the client, not on the database.  This can lead to very poor performance.

Personally, the first thing I'd would normally suggest is to use a SQL expression field to pull back the aggregate sums in the SELECT clause.  A SQL Expression is the Crystal Reports equivalent of a SQL subquery/subselect in the SELECT clause of a query.  The problem, however, is that you're using Oracle and you can't correlate data from a report into a full SELECT SQL Expression from Crystal Reports - you can against other databases like SQL Server and DB2, but not Oracle.  It sucks :(

So, if you want that SQL AND want dynamic parameters, what options do you have?

1)  Create an empty container report that only exists to populate some dynamic parameters
2)  Build a subreport with the SQL Command and the parameters inside the command
3)  Link the main report to the subreport based on the main report parameters to the subreport's command parameters.  Please note - when you attempt to link the main report to the subreport, it'll create a default subreport parameter starting with ?PM-  do NOT link to this parameter, because it exists outside the command.  Drill down into the list and select the actual matching parameter that's embedded inside the command.

I've used this technique for a number of reports and it works well.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 34926960
Let me confirm your solution for main report and subreport.

I think i would leave my current report as as is now and just link it to a new report with dynamic parameters?

Does this solution take a long time to implement. I have done a few subreports a few years back and i recall it takes some time. That seems the only way though for dynamic parameters.

If i leave the parameter as static i guess i have to keep refreshing the list manually. right?
LVL 101

Expert Comment

ID: 34927303
Yes, if they remain static you have to refresh the list periodically or when the values change.

A subreport is just a report that you embed inside a report.

SInce you have the basic report built you could make a copy of the main report then eliminate everything you don't need in it except the details you want displayed.


Author Closing Comment

ID: 34935229

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

615 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