Add Command - SQL query as Data Source for Report


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 ??

Who is Participating?
GJParkerConnect With a Mentor Commented:
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

mlmccConnect With a Mentor Commented:
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.

JMO9966Author Commented:
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"

mlmccConnect With a Mentor Commented:
After you add the parameter you have to add it to the query.

How are you adding it?

JMO9966Author Commented:
Thanks guys!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.