Command / Stored proc as datasource
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2684
Printed Date: 18 May 2024 at 2:13pm
Topic: Command / Stored proc as datasource
Posted By: mxr561
Subject: Command / Stored proc as datasource
Date Posted: 23 Mar 2008 at 2:54pm
I have a query like th following. After going thro' the doc I feel the best way is to go with either COMMAND or stored proc as datasource where the query can be entered as it is and parameters can be set. I would like to get Brian's opinion on this. thanks
SELECT COUNT(TABLE_NAME1.COLNAME6),
TABLE_NAME1.COLUMN_NAME1,
TABLE_NAME1.COLUMN_NAME2,
TABLE_NAME1.COLUMN_NAME4,
TABLE_NAME1.COLUMN_NAME5,
TABLE_NAME1.COLUMN_NAME3
FROM TABLE_NAME1
LEFT OUTER JOIN TABLE_NAME2
ON TABLE_NAME1.PAGE=TABLE_NAME2.ID
WHERE (TABLE_NAME1.COLUMN_NAME4='EN'
AND TABLE_NAME1.COLUMN_NAME5='ES'
AND TABLE_NAME1.COLUMN_NAME1=1
AND TABLE_NAME1.TIMESTAMP >='3/1/2008'
AND TABLE_NAME1.TIMESTAMP <='3/28/2008'
AND TABLE_NAME1.TYPE in ( 2 ,4,8,9,10 )
AND TABLE_NAME2.COLUMN1 LIKE '%URLSTR%'
or( TABLE_NAME1.COLUMN3 IS NULL
AND FFID = ( SELECT FILEID FROM TABLE_NAME4 FT, TABLE_NAME2 APG
WHERE FT.ID = FFID
AND FT.COLUMN3= APG.ID
and APG.URL LIKE '%URLSTR%'
)
)
)
GROUP BY TABLE_NAME1.COLUMN_NAME1,
TABLE_NAME1.COLUMN_NAME2,
TABLE_NAME1.COLUMN_NAME4,
TABLE_NAME1.COLUMN_NAME5,
TABLE_NAME1.COLUMN_NAME3
|
Replies:
Posted By: BrianBischof
Date Posted: 23 Mar 2008 at 11:08pm
Yes, I agree. You want the database to process as much of the information on the server as possible. Doing it as a stored procedure would be ideal because it can get the parameter values from the report and do all the work on the database server.
-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
Posted By: mxr561
Date Posted: 29 Mar 2008 at 2:43pm
Thanks Brian for your response.
Is it possible to do grouping and sub-grouping ( sub-totals and grand totas) when we use Stored procedures?
|
|