• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:


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

1 Solution
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?
MIKESoftware Solutions ConsultantCommented:
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...
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sam15Author Commented:
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?
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.

sam15Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now