BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: Upcoming Events: BONYMAUG: May 14, BI2014 Nice, France: May 21.

WEBI variables


 
Search this topic... | Search WebIntelligence Version 6... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence Version 6  Previous TopicPrint TopicNext Topic
Author Message
Joanne Mellides
Forum Member
Forum Member



Joined: 29 Aug 2002

Posts: 13



PostPosted: Wed Jan 14, 2004 4:29 pm 
Post subject: WEBI variables

Hi

We are trying to create a WEBI report (6.1a) where we can bring in multiple years of sales data by product line. We would then like to create variables to display them separately (tough without the Sum/Where option icon_cry.gif ). We actually tried to create separate measure objects in our universe
Sales - Current Year and Sales - Prior Year
- but the SQL won't work - it uses an AND instead of OR - which won't work.

We created variables in WEBI using the IF function - but the amounts don't line up properly and if you take the year object out of the block - we get a MULTIVALUE icon_sad.gif

Does anybody know if this is possible to do in WEBI? Obviously no problem in FC....

Thanks in advance.....
Joanne
Back to top
pritzlb
Forum Member
Forum Member



Joined: 16 Jan 2004

Posts: 2
Location: Appleton WI USA



PostPosted: Fri Jan 16, 2004 11:58 am 
Post subject: Re: WEBI variables

Joanne; We have been dealing with the same issue. We really miss that function. Can you crosstab instead and put the year in the header?
_________________
Brian Pritzl
VersiFit Technologies LLC
pritzlb@versifit.com
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5607


flag
PostPosted: Fri Jan 16, 2004 12:39 pm 
Post subject: Re: WEBI variables

Joanne Mellides wrote:
We actually tried to create separate measure objects in our universe
Sales - Current Year and Sales - Prior Year
- but the SQL won't work - it uses an AND instead of OR - which won't work.


I don't quite understand what you mean but try reading this. You definitely can create the type of measure objects you've described and have them be compatible. I do it all the time. I have This Year YTD, Last Year YTD, etc type measures all over the place. They're much easier on the user.

_________________
Cindy Clayton Image link
Ask WHY until you understand!
Back to top
Joanne Mellides
Forum Member
Forum Member



Joined: 29 Aug 2002

Posts: 13



PostPosted: Fri Jan 16, 2004 2:09 pm 
Post subject: Re: WEBI variables

I don't particularly care for crosstabs. We need to create multiple columns and I need to create variances and variance %'s too.

I'm hoping the Case statement will work.....

Which creates my next question.... icon_smile.gif

We are working with a star schema - one fact table, with many 'Dates' - Invoice Date, Order Date etc. We have a Dates table which I have aliased for each 'Date' along with our other sales data dimensions - customer, item, etc.

If I want to create separate measure objects - Sales CYTD, Sales PYTD, Sales CMTD etc. is the case statement something like:

CASE WHEN
datepart(yyyy,SalesDataMart.dbo.Sales_Star_Company.DateFiscalYearBegins) =
datepart(yyyy,InvoiceDate.Dates) then sum(SalesDataMart.dbo.Sales_Star_Facts.ExtendedPrice) - 'For the current year *** "this is where I have a problem"

How do I state this for the current year of the invoice date? Do I need to alias my fact table?

Thanks - as you can see I am NOT a SQL DBA icon_cry.gif

Just trying to create a good user experience..

Joanne
Back to top
pritzlb
Forum Member
Forum Member



Joined: 16 Jan 2004

Posts: 2
Location: Appleton WI USA



PostPosted: Fri Jan 16, 2004 3:09 pm 
Post subject: Re: WEBI variables

We did make this work with separate objects in the Universe (use CASE statements) but these must be used carefully. Experienced user kind of stuff. If the user doesn't have the right conditions in the filters then these new objects in the Universe can come back empty, etc.
_________________
Brian Pritzl
VersiFit Technologies LLC
pritzlb@versifit.com
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence Version 6  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0516 seconds using 17 queries. (SQL 0.0030 Parse 0.0379 Other 0.0107)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo