Can we Pass One command object columns to another command object ?

[Former Member]

Hi Guys,

I have a report contains 2 command objects.But as per my requirement i need to pass one query columns as a columns of another query.

I have max time and min time in one query.It should passed as from time and to time of another query.

Like below

1st Query:

where from time =(min time of 2nd query)  and to time =(max time of 2nd query).

How can we do this,any other way like sub reports.Any ideas would be appreciated .Please suggest me

[abhilash_kumar · Accepted solution]

Hi Francis,

Yes you can. Add a Subreport and choose the 1st query as its datasource. I hope the 1st query has a two command level parameters that can be used in the where clause. If you don't then you need to create them.

Then, right-click the Subreport > Change Subreport Links > Move the Min Time Field to the Fields to link to panel on the right > From the drop-down where it says "Subreport Parameter Field to use", choose the Min parameter. Do the same thing for the Max parameter as well.

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thanks for quick reply.My query is like this

where HOUR((TIME(ccsr.transaction_time))) >= (?) AND HOUR((TIME(ccsr.transaction_time)))< (?)

Here i need to replace those? with columns of another query.It should pass from the 2nd query internally.When we run the report it should not promoted for parameters.

The output is like this.

Fromtime        Totime            Sale

6                    7                       34000          

7                    8                       12056

8                    9                        54243

[abhilash_kumar · Accepted solution]

Ok, then you create two separate formulas that find out the Min and Max times with this code:

//Formula for Min Time

Minimum({Fromtime})

//Formula for Max time

Maximum({Totime})

Then follow the steps I posted above to pass these formulas to the Subreport parameters.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

As suggested by you i created a report with 1st command object and i created 2 formulas .

Minimum({Fromtime})

Maximum({Totime})

Now i have taken 2nd query and pasted in command object,In the query i have two parameters from time and to time as ? symbol

Now i need to replace those ? with the formulas i created in another report.Here i am facing problem.How to pass 1st report  formulas to parameters of 2nd command object.

PFA

Please suggest

[Former Member · Accepted solution]

Hi All,

Can any one please resolve this.

Thanks,

Francis

[abhilash_kumar · Accepted solution]

Hi Francis,

Right-click the Subreport > Change Subreport links > Move the Min Time formula to the Fields to link to panel on the right > From the drop-down where it says "Subreport Parameter Field to use", choose the Min parameter that you created in the Subreport's Command Object.

Do the same thing for the Max parameter as well.

P.S; Why have you named your Command Object parameters as '?' ? How do you know which one of those question marks in Min or Max?

-Abhilash

[Former Member · Accepted solution]

HI Abhilash,

Thank you,

The query is already existed.It is written by some one.

Now i am in some confusion,Please clarify this

Now i pasted the query in command object,but it is asking to give the parameter values.But how we can pass the parameters from sub report without going inside of report.(we need to fill the parameter values of 2nd query before entering into report).I need to replace those parameters with min time and max time.When i run the report it should not ask for entering time parameters.

[abhilash_kumar · Accepted solution]

Hi Francis,

Did you follow the steps I posted above? If you go to Change Subreport links and link the formulas to the Subreport parameters you should not be prompted for the parameters unless you have another set of parameters in the Main Report.

Also make sure the datatype of the formula and the parameter is same, otherwise you will not be able to link them up.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

One last question ,In below query i need to replace ? symbol   with Minimum({Fromtime}) and Maximum({Totime}) formulas  when i pasted in command object.

where HOUR((TIME(ccsr.transaction_time))) >= (?) AND HOUR((TIME(ccsr.transaction_time)))< (?)

Please clarify.

[abhilash_kumar · Accepted solution]

Hi Francis,

That '?' could actually be the parameter you've created in the Command Object. Open the query that has a Command Level parameter and rename them.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash i think i made a mistake,Please clarify this,

I replaced parameters like this.

where HOUR((TIME(ccsr.transaction_time))) >= '{From time}' AND HOUR((TIME(ccsr.transaction_time)))< '{to time}'

From time and to time are formulas i created in another sub report with 1st query

In where condition of parameters what type of formula name i have to give.I mean Formula name with single quotes or which brackets.

Suppose if i created a formula called min time .I need to replace ? with  {min time} or (min time) or

({min time}) or {@min time}

Which syntax i have to use,Please suggest

where HOUR((TIME(ccsr.transaction_time))) >= (?) AND HOUR((TIME(ccsr.transaction_time)))< (?)

[Former Member · Accepted solution]

Hi Abhilash,

I am struck here .Can you please guide me

Thanks in Advance,

Francis