| Author |
Message |
rw4705
Forum Member
Joined: 20 Jan 2004
Posts: 14
|
 Posted: 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
Joined: 27 Nov 2003
Posts: 16332
Location: Toronto
|
Posted: 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: XI 3 | 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-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
• 2012-04-09 All in an input control |
|
| Back to top |
|
|
rw4705
Forum Member
Joined: 20 Jan 2004
Posts: 14
|
Posted: 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
Joined: 20 Jan 2004
Posts: 14
|
Posted: 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
Joined: 27 Nov 2003
Posts: 16332
Location: Toronto
|
Posted: 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: XI 3 | 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-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
• 2012-04-09 All in an input control |
|
| Back to top |
|
|
rw4705
Forum Member
Joined: 20 Jan 2004
Posts: 14
|
Posted: 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
Joined: 01 Aug 2005
Posts: 1428
Location: Netherlands
|
Posted: 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 B B |
|
| Back to top |
|
|
Mark P
Forum Fanatic
Joined: 03 Feb 2003
Posts: 7376
Location: Manchester, UK
|
Posted: 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 1st July 2013 for contract/consultancy work throughout the UK
BusinessObjects - versions 3, 4, 5, 6, XI, XIr2, XI 3.1, 4.0, 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
Joined: 20 Jan 2004
Posts: 14
|
Posted: 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.  |
|
| Back to top |
|
|
|
