Objects in a where clause

[xlbo]

Hi all, using B/O 5.1 reporter. I'm trying to create a dynamic formula that looks at what year we are in. We have an object in the universe called "Current Year" but I can't seem to get B/O to allow anything but hardcoded values in where formulae (in the actual report, not the SQL). Anyone know how to get a where formula to allow objects rather than just hardcoded values ??

TIA
Geoff HTH
~Geoff~

 

[blom0344]

Hello xlbo,

Try using a subquery in your main query. The result of your subquery should retrieve the input for the main query (i.e. the current year value from your universe / database) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[xlbo]

Thanx for the response....problem is that I want 1 query to bring back multiple years worth of data ie sales data for 2001 and 2002. I then want to create a measure called Volume This Year and another called Volume Last Year
So....I thought I could bring through our "Current Year" dimension and use something like:
<Volume(Brls)> where(<FinancialYr>=<Current Year>)
and
<Volume(Brls)> where(<FinancialYr>=<Current Year> - 1)

for the previous year. BUT...I get a synatx error - seems like you can't put &quot;objects&quot; in Where clauses.I know I could just do this using 2 queries but there are many other parts of this report and to do it via seperate queries would mean at least 16 queries feeding into 1 report and I'd prefer to hit the server once with something big rather than many times. Also, there are a lot of degrees of hierachy involved and it would mean bringing through a lot of data that only needs to be brought through once.

TIA
Geoff HTH
~Geoff~

 

[blom0344]

Oke xlbo,

Your report looks a lot like the one I was working this morning. Why don't you try to retrieve the dataset as a whole from the database in 1 query and use local variable in your report to distinguish between years?

Something like:

YTD2002 : = Sum(<measure>) Where (<Year> = 2002)
YTD2001 : = Sum(<measure>) Where (<Year> = 2001)

This way I build a report showing the sales results from last month, the YTD from this year and corresponding YTD from last year (for reference purposes) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[xlbo]

Again, thanx for the quick response - What you have there is what I currently use but I wanted them to change dynamically so I didn't have to update all my reports at year end (we do a lot of this year vs last year type analysis).I've actually come up with an answer for what I wanted which was a dynamic Current Year Sales and Previous Year Sales measure.

Anyway, this is the solution I am using if anyone is interested:

Create a measure called ChkYr
ChkYr: =If(<FinancialYr>=<CurrentYr>) Then 1 else 0)

Sales Volume Current Year:
=Volume(Brls) where (<ChkYr> = 1)

Sales Volume Previous Year:
=Volume(Brls) where (<ChkYr> = 0)

Obviously, this particular If statement will only work for current and previous years but sure you could use multiple IFs to get the constants needed for more comparison

HTH
~Geoff~