Solved

Add Command - SQL query as Data Source for Report

Posted on 2008-10-06
5
663 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now