Enabling 'Cross Product Allowed' option in Query!

[PRIT AMRIT]

Hi Gurus,

Require your valuable suggestions in the below scenario.

I have a Report, which is working fine now. But as per the 'Business' requirement got to add an additional filter for 'fiscal year'.

The value for this 'fiscal year' comes from another table. This table will always have 1 value, e.g. now it has FY=2011.

To be able to filter the existing report, I have to make a join between these tables and which i don't want, since there are lot of queries and calculation in the existing query. If i apply the join then have to change Queries and map report to the latest query after join.

Instead, can I just enable the 'Cross Product Allowed' option in the query and straight away filter the report?

e.g. [Query 1].[Year]=[Query 2].[FY]

Let me know if any other work around as well.

Thanks a lot

[FM]

You hardly ever want a cross join. A cross join will give you n*m where n is the number of tables and m is the number of rows per table. Now exactly what you want, eh?

How do that tables look from the database side? Also, if all we're talking about is year, why is it critical that it comes from query 2?

You're the report author, right? Filter on year, and to the consumer, it's the same, right? They'll get what they want with out horrid performance.

Or is there some specific reason you need to filter on Query 2?

[PRIT AMRIT]

Looks like u didn't understand my post. Well, let me brief you little more.

Say I have a report and which is build on Query 'A'. This Query requires a filter for Fiscal Year. Of course Query 'A' has a 'Year' column.

For some typical business requirement we are calculating the 'Fiscal Year' and storing the value in another table 'B'. The table looks like,

sys_param_cd        sys_param_value
---------------      -----------------
Fiscal Year                  2011

Now, I should filter the Query 'A' based on this table 'B' value. Got it?

So, in Query 'A' the filter would look like

[Query A].[Year]=[Query B].[Fiscal Year]

But, when you would try to do this, you would see error message since there is no relationship between these two tables. I mean Query A and Query B.

I know, I can have a straight forward join between these 2 queries, but if i do so, i have to change the query structure and map the reports to the after join queries. Which i don't want since i have lot of queries and calculations.

But there is another option in Query Properties called 'Cross Product Allowed'. I wonder if i can just enable that option to 'YES' and they perform the FILTER, will it cost me any performance or other issues. Since the 'Query B' got only 1 record.

Or any other workaround can do this?

Thanks

[nag.prasady@gmail.com]

Create Master detail relation ship between then and hide the master using box type none.It's idea only if it works let me know.
Regards,
Nagendra.

[PRIT AMRIT]

Good idea and seems to be working fine.

Thanks mate