Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Command object SQL statement with parameter Post Reply Post New Topic
Author Message
CRNewbie32
Newbie
Newbie
Avatar

Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
Quote CRNewbie32 Replybullet Topic: Command object SQL statement with parameter
    Posted: 21 Sep 2009 at 2:27pm
I have a report that uses a SQL Server stored procedure as the data source. The first parameter they enter is their company number (we have numerous clients using Lawson and access Crystal through LBI). The next parameter is the account number. I have a SQL statement written that gets distinct account numbers by company. When I create a Command object with the SQL statement, it doesn't allow me to use the company parameter.
 

SELECT DISTINCT GLC.ACCOUNT, GLC.ACCOUNT_DESC
FROM dbo.GLMASTER AS GLM
INNER JOIN DBO.GLCHARTDTL AS GLC
ON GLM.CHART_NAME = GLC.CHART_NAME
AND GLM.ACCOUNT = GLC.ACCOUNT
WHERE GLM.COMPANY = {?Company}
ORDER BY GLC.ACCOUNT

When I do Add Command in Database Expert and add the SQL above and name the parameter and try to Add, it says "Failed to retrieve data from the datase.
Details: ADO Error Code: 0x
Source: Microsoft OLE DB Provider for SQL Server
Description: No value given for one or more required parameters
 
Does anyone have a similar situation and a solution to this problem--either regarding my approach and error or a different approach to get the same thing?
 
Any input would be greatly appreciated!
TrytoworkwithSQLServerCrystal
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 21 Sep 2009 at 8:50pm
Is Company column string type?
then try like this
WHERE GLM.COMPANY = '{?Company}'

Jyothi
IP IP Logged
CRNewbie32
Newbie
Newbie
Avatar

Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
Quote CRNewbie32 Replybullet Posted: 22 Sep 2009 at 9:22am
That was very helpful.
 
I have another report where the Company is in the report's data source (not a parameter). What would be the syntax for that?
 
I've tried :
SELECT DISTINCT RIGHT('00000' + LTRIM(RTRIM(CAST(GLC1.ACCOUNT AS CHAR(5)))),5) +
  RIGHT('000' + LTRIM(RTRIM(CAST(GLC1.SUB_ACCOUNT AS CHAR(3)))),3) AS ACCOUNT2,GLC1.ACCOUNT_DESC
FROM GLCHARTDTL AS GLC1
WHERE RIGHT('00000' + LTRIM(RTRIM(CAST(ACCOUNT AS CHAR(5)))),5) +
  RIGHT('000' + LTRIM(RTRIM(CAST(SUB_ACCOUNT AS CHAR(3)))),3)  IN
(SELECT DISTINCT RIGHT('00000' + LTRIM(RTRIM(CAST(GLC.ACCOUNT AS CHAR(5)))),5) +
  RIGHT('000' + LTRIM(RTRIM(CAST(GLC.SUB_ACCOUNT AS CHAR(3)))),3)
FROM dbo.GLMASTER AS GLM
INNER JOIN DBO.GLCHARTDTL AS GLC
ON GLM.CHART_NAME = GLC.CHART_NAME
AND GLM.ACCOUNT = GLC.ACCOUNT
AND GLM.SUB_ACCOUNT = GLC.SUB_ACCOUNT
WHERE cast(GLM.COMPANY as varchar(4)) = '{COMPANY}')
TrytoworkwithSQLServerCrystal
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 22 Sep 2009 at 4:02pm
What exactly you require do with this query? explain your requirement.

Jyothi
IP IP Logged
CRNewbie32
Newbie
Newbie
Avatar

Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
Quote CRNewbie32 Replybullet Posted: 23 Sep 2009 at 7:09am
I'm trying to get a string combination of an account number and sub_account number in one column, and the description in a second column. I want to limit this to the chart of accounts for one specific company--the company that is in the Select Expert for the GLTRANS table which is the table for the report.
TrytoworkwithSQLServerCrystal
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 27 Sep 2009 at 9:01pm
write the Where condition part in record selection.

Jyothi
IP IP Logged
CRNewbie32
Newbie
Newbie
Avatar

Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
Quote CRNewbie32 Replybullet Posted: 01 Oct 2009 at 6:06am
I don't want to hard code the COMPANY field. We have 22 customers, all with different company numbers, running the same report. Once they're in the report it has their company number, so I would like this Command Object to be able to obtain it without hard-coding the company number.
TrytoworkwithSQLServerCrystal
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 01 Oct 2009 at 3:56pm
Create a parameter for company number and use in above SQL Command.
what is the problem with above query? its not working?

Jyothi
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.047 seconds.