Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: FORMULA AS A PARAMETER Post Reply Post New Topic
Author Message
ashishdtambe
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 6
Quote ashishdtambe Replybullet Topic: FORMULA AS A PARAMETER
     Posted: 11 Dec 2008 at 11:19pm


 DEAR ALL,

  I AM CREATING ONE REPORT IN WHICH I WANT TO USE A VALUE GENERATED BY A FORMULA TO BE USED AS A PARAMETER FOR MY SQL QUERY.

 IS IT POSSIBLE?


 REGARDS,

ASHISH TAMBE


Edited by ashishdtambe - 11 Dec 2008 at 11:52pm
Ashish Tambe
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 12 Dec 2008 at 3:05am
Hi
 
I don't think so , what excatly you are trying to do?
 
cheers
rahul
IP IP Logged
ashishdtambe
Newbie
Newbie
Avatar

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 6
Quote ashishdtambe Replybullet Posted: 12 Dec 2008 at 11:18am


 Hi Rehul,




  CREATE TABLE #TEMP(MON NUMERIC(19,6),ID NUMERIC(19,6),REVENUE NUMERIC(19,6))


INSERT INTO #TEMP (MON,ID,REVENUE)

SELECT distinct
MON =CASE
WHEN T0.[PredDate] BETWEEN '2008/10/01' AND '2008/10/31' THEN '1'
WHEN T0.[PredDate] BETWEEN '2008/11/01' AND '2009/01/31' THEN '2'
WHEN T0.[PredDate] BETWEEN '2009/02/01' AND '2009/07/31' THEN '3'
WHEN T0.[PredDate]>= '2009/08/01' THEN '4'
END,
T0.[OpprId], T0.[MaxSumLoc] FROM OOPR T0 
INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId WHERE T1.[U_StatusChange] ='EAF'
and T0.[Status]='O'

SELECT
PERIOD=CASE
WHEN MON='1' THEN '0-1 MONTH'
WHEN MON='2' THEN '2-3 MONTH'
WHEN MON='3' THEN '4,5,6 MONTH'
WHEN MON='4' THEN 'ABOVE 6 MONTH'
END,
COUNT (*) AS NUMBER,SUM(REVENUE) AS REV FROM #TEMP GROUP BY MON

DROP TABLE #TEMP




This is my query...i'll be asking user two inputs FROM DATE AND TO DATE....on the basis of these two parameter i'll be calculating dates in blue color in query which are actually parameters for th query....
to calculate these days i'll wite formulas...

so can i use the imput of those formulas in place of these dates?



Regards,
Ashish Tambe
Ashish Tambe
IP IP Logged
themessenger
Groupie
Groupie
Avatar

Joined: 15 Aug 2008
Location: United Kingdom
Online Status: Offline
Posts: 48
Quote themessenger Replybullet Posted: 18 Dec 2008 at 8:17am
I don't think you can use temporary tables directly in SQL - you may in a stored procedure, but in a Crystal Command you'd have to combine your queries, ie:

SELECT DISTINCT
(CASE
    WHEN T0.PredDate BETWEEN '2008-10-01' AND '2008-10-31' THEN '0-1 MONTH'
    WHEN T0.PredDate BETWEEN '2008-11-01' AND '2009-01-31' THEN '2-3 MONTH'
    WHEN T0.PredDate BETWEEN '2009-02-01' AND '2008-07-31' THEN '4,5,6 MONTH'
    WHEN T0.PredDate >= '2009-08-01' THEN 'ABOVE 6 MONTH'
END) AS MON, COUNT(T0.OpprId) AS NUMBER, SUM(MaxSumLoc) AS REV
FROM OOPR T0 INNER JOIN OPR1 T1 ON T0.OpprId = T1.OpprId WHERE T1.[U_StatusChange] ='EAF'
and T0.[Status]='O'
GROUP BY
(CASE
    WHEN T0.PredDate BETWEEN '2008-10-01' AND '2008-10-31' THEN '0-1 MONTH'
    WHEN T0.PredDate BETWEEN '2008-11-01' AND '2009-01-31' THEN '2-3 MONTH'
    WHEN T0.PredDate BETWEEN '2009-02-01' AND '2008-07-31' THEN '4,5,6 MONTH'
    WHEN T0.PredDate >= '2009-08-01' THEN 'ABOVE 6 MONTH'
END)

Then you should be able to set up your parameters as normal.

Hope this helps.


TheMessenger
Managing Director
www.allmymenus.com
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.