Using a command in CR 10 and want to use One Report Input Parameter

[shadabrauf]

Hi

I am using CR 10 and I am working on a Balance sheet, This report is a one page report and It has 20 Columns, For each column I have written a Command (seperate query). Now my issue is I want when I will run this report It should as a Parameter "As of Date" default would be current date, And I want to pass this parameter in all command queries for data extraction in where clause.

Please suggest how to do this, If need more explanation please let me know.

Shadab

 

[synapsevampire]

Crystal doesn't allow for different data sources in a single report so your architecture is suspect.

As for a parameter, when you create a Command object you create parameters and insert them at that stage. Set the default value to something like 1/1/1970, and then code for it within your command object if your databases SQL allows for it.

Otherwise you might consider a strored procedure.

I would consider an SP because it sounds like ou need a series of UNION ALL statements with embedded parameters.

-k

 

[shadabrauf]

I have one data source but having 20 command object and each command object is running a sql query (it is same with different where clauses for every command).

Can you give me an idea If write a SP then how it will work? I am using Oracle database so the way u suggest work with oracle for parameter?

shadab

 

[synapsevampire]

Are these command objects joined? If not, you have different data sources.

You could merge them all in one command object by using a UINION ALL between them (make sure that you return the same number and type of fields, and create an explicit field to demonstate the source of each).

select 'data source 1' as FromData, field1, field2
from table
where field1 = 'blah'
union all
select 'data source 2' as FromData, field1, field2
from table
where field1 = 'no blah'
union all
select 'data source 3' as FromData, field1, field2
from table
where field1 = 'a lil blah'
...etc...

In order to supply more meaningful suggestions, you'll need to supply technical information:

Example data
Expected output

And yes, an SP can have parameter(s), and Crystal will use them.

Here's the whitepaper:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_oracle_stored_procedures.pdf.asp

-k