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

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Case statement for universe object (SQL server)


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
rw4705
Forum Member
Forum Member



Joined: 20 Jan 2004

Posts: 14



PostPosted: Tue Jan 05, 2010 4:22 pm 
Post subject: Case statement for universe object (SQL server)

Hello,

I'm having issues creating an object that is built using a case statement.

I have a metric object (Amount) and a dimension object (ReportMain Header) and need to build several objects that reflect the certain values associated with the ReportmainHeader object.

In the example below, the object I'm creating should only bring back a numeric value only if the ReportMainHeader object meets the criteria:

"CASE WHEN dbo.T_DIM_PRODUCT.ReportMainHeader = 'Domestic On-Balance Sheet' THEN dbo.T_FNL_CMR.Amount
ELSE 0 END"

I'm using SQL Server as my database and building objects in my universe for use by end-users.

Any help would be appreciated.

Thanks
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17885
Location: Bratislava


flag
PostPosted: Tue Jan 05, 2010 4:45 pm 
Post subject: Re: Case statement for universe object (SQL server)

Hi,
rw4705 wrote:
I'm having issues creating an object that is built using a case statement.
And what are those issues? You have not mentioned what the problem is - parsing problems? problems with the results? problems with the syntax? something else?
_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
rw4705
Forum Member
Forum Member



Joined: 20 Jan 2004

Posts: 14



PostPosted: Tue Jan 05, 2010 5:23 pm 
Post subject: Re: Case statement for universe object (SQL server)

Hi Marek,

Sorry about not stating the actual issue. The issue is parsing. I get
the following parsing error message:


Parse failed: Exception: DBD [Microsoft] [ODBC SQL Server Driver] [SQL server] Statement(s) could not be prepared.State: 42000
Back to top
rw4705
Forum Member
Forum Member



Joined: 20 Jan 2004

Posts: 14



PostPosted: Tue Jan 05, 2010 5:32 pm 
Post subject: Re: Case statement for universe object (SQL server)

Forgot to mention that it looks like it has to do with my syntax--can create other case statements but this is I can't get to parse.
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17885
Location: Bratislava


flag
PostPosted: Tue Jan 05, 2010 5:48 pm 
Post subject: Re: Case statement for universe object (SQL server)

Hi,

Can you first try this more simple syntax whether it parses or not?
Code:
CASE WHEN dbo.T_DIM_PRODUCT.ReportMainHeader = 'Domestic On-Balance Sheet'
          THEN 1
     ELSE 0
END

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
rw4705
Forum Member
Forum Member



Joined: 20 Jan 2004

Posts: 14



PostPosted: Tue Jan 05, 2010 6:21 pm 
Post subject: Re: Case statement for universe object (SQL server)

Hi Marek,

The case statement you provided had no issues with parsing--worked fine.

So looks like you may be on to something here............
Back to top
highandstoned
Forum Enthusiast
Forum Enthusiast



Joined: 01 Aug 2005

Posts: 1428
Location: Netherlands


flag
PostPosted: Wed Jan 06, 2010 2:44 am 
Post subject: Re: Case statement for universe object (SQL server)

looks like it doesn't include the "dbo.T_FNL_CMR" table when parsing.
Is the table in the universe ?
(check the "Tables" button when editing the object in designer, see if the table is slected there)

_________________
Press F1, then RTM, then SEARCH......and then post a question!

PS:If the suggestions made above solved or did not solve your issue, or if you solved it by yourself, please have the courtesy to provide the feedback/solution by coming back to the thread. That will help other board members to learn about it.... Let us share and help each other...on Bicon_mrgreen.gifB
Back to top
Mark P
Forum Groupie
Forum Groupie



Joined: 03 Feb 2003

Posts: 8404
Location: Manchester, UK


flag
PostPosted: Wed Jan 06, 2010 4:27 am 
Post subject: Re: Case statement for universe object (SQL server)

rw4705 wrote:
Hello,

I'm having issues creating an object that is built using a case statement.

I have a metric object (Amount) and a dimension object (ReportMain Header) and need to build several objects that reflect the certain values associated with the ReportmainHeader object.

In the example below, the object I'm creating should only bring back a numeric value only if the ReportMainHeader object meets the criteria:

"CASE WHEN dbo.T_DIM_PRODUCT.ReportMainHeader = 'Domestic On-Balance Sheet' THEN dbo.T_FNL_CMR.Amount
ELSE 0 END"

I'm using SQL Server as my database and building objects in my universe for use by end-users.

Any help would be appreciated.

Thanks


If it's a measure object, then I'd suggest treating it as one:

Code:
sum(CASE WHEN dbo.T_DIM_PRODUCT.ReportMainHeader = 'Domestic On-Balance Sheet'  THEN dbo.T_FNL_CMR.Amount
ELSE 0 END)


Your code is in principle correct, but I'd look into what highandstoned said about the tables being in the universe, especially if you've typed the whole code. Try building the statement using the formula builder to avoid typos in the table.column names.

Regards,
Mark

_________________
Available from October 2014 for contract/consultancy work throughout the UK

BusinessObjects - versions 3, 4, 5, 6, XI, XIr2, XI3.1, 4.1, SAP Integration
Data Warehousing, SQL Server 2000/2005/2008 (inc. SSIS, DTS, linked servers, stored procs), Oracle 7.3-11g, Netezza, Sybase, Informix, DB2, Progress, Ingres
______________________________________
Democracy: Where two idiots outvote a genius
Back to top
rw4705
Forum Member
Forum Member



Joined: 20 Jan 2004

Posts: 14



PostPosted: Wed Jan 06, 2010 3:59 pm 
Post subject: Re: Case statement for universe object (SQL server)

Thanks for all of your help Mark P. It works the way I need it to. Also, thanks to Marek and highandstoned for your help. seeya.gif
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  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.0473 seconds using 17 queries. (SQL 0.0031 Parse 0.0348 Other 0.0094)
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