Use of in_range in filter

[thomasbk]

Hi

I hope someone can help me with this:

This works

[Sales Statistics].[Calendar (Invoice Date)].[Year (Invoice Date)] between [QPeriods].[LastYear] and [QPeriods].[ThisYear]


[Sales Statistics].[Calendar (Invoice Date)].[Year (Invoice Date)] in( [QPeriods].[LastYear];[QPeriods].[ThisYear])


This does not work

[Sales Statistics].[Calendar (Invoice Date)].[Year (Invoice Date)] in_range {[QPeriods].[LastYear] : [QPeriods].[ThisYear]}

Can someone explain to me why the in_range example doesn't work and how to solve it?
Let me know if you need further information.

Best Regards
Thomas

[MFGF]

Hi

I hope someone can help me with this:

This works

[Sales Statistics].[Calendar (Invoice Date)].[Year (Invoice Date)] between [QPeriods].[LastYear] and [QPeriods].[ThisYear]


[Sales Statistics].[Calendar (Invoice Date)].[Year (Invoice Date)] in( [QPeriods].[LastYear];[QPeriods].[ThisYear])


This does not work

[Sales Statistics].[Calendar (Invoice Date)].[Year (Invoice Date)] in_range {[QPeriods].[LastYear] : [QPeriods].[ThisYear]}

Can someone explain to me why the in_range example doesn't work and how to solve it?
Let me know if you need further information.

Best Regards
Thomas

Hi Thomas,

Unfortunately you didn't specify what "It does not work" actually means. Does it generate an error? No results? The wrong results? Something else?

What do you see if you view the generated SQL for the report in each of the three scenarios?

Taking them in the order you posted them, and using the sample GO Data Warehouse (Query) sample package and two year values, I see the following predicate generated in the native SQL:

where "GO_TIME_DIM"."CURRENT_YEAR" between 2012 and 2013

where "GO_TIME_DIM"."CURRENT_YEAR" in (2012, 2013)

where "GO_TIME_DIM"."CURRENT_YEAR" between 2012 and 2013

For me, they all work. You can see that the in_range example is resolving to a "between" in the native SQL. I'm using Cognos 10.2.2 and SQL Server 2012.

Cheers!

MF.

[thomasbk]

Hi

When running the report with a data item using the two first formulas (between and in), the report works fine.

When running the report with a data item using the in_range calculation method instead of between or in, the report fails.
So i think that the in_range formula are missing something or (hopefully not) that the in_range function can't be used in this context.

So I'm hoping that someone can troubleshoot the third formula, knowing that the two first formulas works fine.

[schrotty]

Hi,

I suspect the 'in_range'-function can only handle with constants in the expression.

Exression-information in cognos:

expression1 in_range { constant : constant [ , constant : constant ] }

Schrotty

[Lynn]

Hi

When running the report with a data item using the two first formulas (between and in), the report works fine.

When running the report with a data item using the in_range calculation method instead of between or in, the report fails.
So i think that the in_range formula are missing something or (hopefully not) that the in_range function can't be used in this context.

So I'm hoping that someone can troubleshoot the third formula, knowing that the two first formulas works fine.

I don't see why you wouldn't just use "between" syntax since it resolves to the same query syntax anyway. If you need multiple ranges you can use separate expressions combined with "or". If you can clarify what you are trying to achieve you might get alternative suggestions.

It works fine using a parameter.

[Sales (query)].[Time].[Year] in_range ?SelectYears?

It works fine using constant values.

[Sales (query)].[Time].[Year] in_range {2015:2016}

I get a parsing error trying reference a query item.

QE-DEF-0261 QFWP - Parsing text: [Sales (query)].[Time].[Year] in_range {[Last Year]:[This Year]}
QE-DEF-0260 Parsing error before or near position: 52 of: "[Sales (query)].[Time].[Year] in_range {[Last Year]"

[MFGF]

When running the report with a data item using the in_range calculation method instead of between or in, the report fails.

As before, what exactly does "the report fails" mean? We can't see your screen. Does it generate an error? No results? The wrong results? Something else? If an error, what is the error?

Did you look at the SQL as I suggested? What did you see?

MF.

[thomasbk]

Hi

I get the same parsing error as two posts above.

Since in_range ?date? works, i hoped it would also work with a query item.

The reason for using in_range instead of between is to avoid any confusion about whether or not the the start and values are included in the end result.

I have gone with the between formula so i can complete the report.