Solved

Crystall_View

Posted on 2011-02-17
6
466 Views
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

0
Comment
Question by:sam15
6 Comments
 
LVL 8

Expert Comment

by:kingjely
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?
0
 
LVL 17

Expert Comment

by:MIKE
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...
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 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.

~Kurt
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sam15
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?
0
 
LVL 100

Expert Comment

by:mlmcc
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.

mlmcc
0
 

Author Closing Comment

by:sam15
ID: 34935229
Excellent!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

803 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