Solved

Add Command - SQL query as Data Source for Report

Posted on 2008-10-06
5
665 Views
Last Modified: 2010-04-21
Hello,

I'm using the Add Command for an ODBC data source and building the query as follows:

select sum(Invoice_Detail.Amount) As Revenue
From Invoice_Detail
Inner Join Invoice_Header On Invoice_Detail.Document=Invoice_Header.Document
where Invoice_Detail.Job = 'REVENUE'

What I'd like to do is make the query dynamic and use a formula result that holds a shared variable from the main report instead of the Job value hard-coded above ('REVENUE').

Also, once I add this field onto my report, I can't figure out how to edit the query behind this field ??

Thanks,
JMO9966
0
Comment
Question by:JMO9966
  • 2
  • 2
5 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 22655083
2 ways, that I can think of.

Add the command without using the where clause.
When you add the command you can add a parameter to it.,
Then add the where clause using the parameter.

The other way would be to just add it as a command without the where clause then add a report parameter and use the select expertwith the parameter to filter the data.

mlmcc
0
 

Author Comment

by:JMO9966
ID: 22655156
Thanks again,

I tried adding a parameter to the sql query itself but it error'd when I hit OK.  I set it as a String type.

"To Few parameters, expected 1"

0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 22655437
After you add the parameter you have to add it to the query.

How are you adding it?

mlmcc
0
 
LVL 19

Accepted Solution

by:
GJParker earned 250 total points
ID: 22661032
I wouldn't use a command at all, create your subreport using tables and a normal paramter and then CR to sum the invoice_detail.amount

HTH
0
 

Author Closing Comment

by:JMO9966
ID: 31503646
Thanks guys!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…

809 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