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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

Looks Like hint in SQL but not sure

Goto page 1, 2  Next
 
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
Fiona
Principal Member
Principal Member



Joined: 16 Dec 2002

Posts: 399
Location: Cornwall


flag
PostPosted: Mon Oct 22, 2007 11:20 am 
Post subject: Looks Like hint in SQL but not sure

I'm building a report off a universe that I didnt build, I noticed that when I checked the SQL I could see this at the bottom of the SQL

/*Universe: Cars, UserID: @variable('BOUSER') */

I know what the @VARIABLE('BOUSER') does as Ive used it myself but I dont understand how its being used in the above format, is this something new in XI or does it just look like they wanted to add a comment to the SQL

Thanks
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Mon Oct 22, 2007 11:34 am 
Post subject: Re: Looks Like hint in SQL but not sure

That is coming from the "End SQL" option in the parameter list. Open your universe, click File and then Parameters, and then look at the Parameters tab of that dialog box. Scroll down to the END_SQL item and you will see where that tag is coming from.

This is a way to "tag" the query automatically with the universe name and the user running the report.

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Fri Oct 26, 2007 4:14 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Someone at the conference last week said they had a variable in this parameter that showed the report name. I have tried all of the obvious (to me) choices like @Variable('REPORTNAME') and REPORT_NAME and BOREPORT so on, and it's not working. icon_smile.gif The documentation that I have looked at does not provide a report name variable. Anyone know if this does, in fact, work, and if so, what the value is?
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19316
Location: Bratislava


flag
PostPosted: Fri Oct 26, 2007 4:43 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Hi Dave,

From the Designer guide I have that @variable is used to call the value assigned to one of two types of variables:
1) BO system variables
2) personal text file variable

The BO system variables and their values can be listed for instance using this VBA procedure:
Code:
Sub application_variables()
  For i = 1 To Application.Variables.Count
    MsgBox i & "/" & Application.Variables.Count & ": " & _
           Application.Variables.Item(i).Name & " = " & _
           Application.Variables.Item(i).Value
  Next i
End Sub

All of the variable names can be used as an input parameter for @variable function (it is case sensitive). As you can see none of them relates to the name of BO document (at least in my BO 6.5, maybe it is different in BO XI) nonod.gif

Regarding personal text file variables - I am not sure if it was possible to set a text file that would contain either all document names or some general variable that would give the name of the currently opened/used document crazy.gif

So I am really wondering too how it is possible to get BO document name using @variable. yesnod.gif

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX 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
Tom Thompson
Principal Member
Principal Member



Joined: 04 Jun 2003

Posts: 299
Location: San Francisco, CA


flag
PostPosted: Fri Oct 26, 2007 5:03 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Dave Rathbun wrote:
Someone at the conference last week said they had a variable in this parameter that showed the report name.


That would be a useful feature. Knowing the @BOUSER behind a problem query tells me who to blame, but knowing the report would let me know what to fix icon_biggrin.gif

_________________
Tom Thompson
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Fri Oct 26, 2007 7:09 pm 
Post subject: Re: Looks Like hint in SQL but not sure

The problem was, I heard this only in passing. I was not part of the conversation. It was taking place while I was answering questions after one of my presentations, so I was not able to jump in to the conversation. I would have least liked to get a business card or email address to follow-up on later. <sigh>
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19316
Location: Bratislava


flag
PostPosted: Thu Nov 08, 2007 6:27 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Dave, Tom,

Have a look at this post. The syntax is supposed to be
Code:
@variable('DOCNAME')

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX 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
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Thu Nov 08, 2007 8:51 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Thank you! I will try it out. Hope it works... that would be great.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
zack
Forum Enthusiast
Forum Enthusiast



Joined: 02 Aug 2007

Posts: 1266
Location: Living In The Spirit Of America


flag
PostPosted: Sat Nov 10, 2007 10:57 pm 
Post subject: Re: Looks Like hint in SQL but not sure

I tried it Marek, its working.
Thanks
Faisal
Back to top
rodlangham
Senior Member
Senior Member



Joined: 13 Apr 2004

Posts: 39


flag
PostPosted: Sat Sep 10, 2011 10:12 am 
Post subject: Re: Looks Like hint in SQL but not sure

Question really aimed at Dave Rathbun in this thread:

In a posting on Dallas Marks excellent blog, at http://www.dallasmarks.org/blog/2008/01/tips-and-tricks-identifying-business-objects-queries-using-end_sql/ you indicated that you had problems getting END_SQL to work correctly with Teradata (because the TD driver was stripping out comments before submitting the SQL to TD). Did you ever get this resolved?

My interest is that this would be a useful mechanism to use on my current client site, using TD.
Thanx
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Sat Sep 10, 2011 1:28 pm 
Post subject: Re: Looks Like hint in SQL but not sure

We use the Connect_Init() option to pass in the username, but it doesn't work for the document name because it isn't known at the time the connection is initiated. I posted about using Connect_Init() on my own blog.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
rajani
Principal Member
Principal Member



Joined: 17 Oct 2005

Posts: 278



PostPosted: Tue Sep 03, 2013 1:07 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Hello All,

We are on XI R2. I have used the following in my END_SQL.
Code:

/* CSM Units.unv - @Variable('BOUSER') - @Variable('DOCNAME')*/


It is showing up in the generated SQL as the same. Where do i see the translated version? meaning,
Code:

/* CSM Units.unv - 'Rajani' - CSM Trade Values.wid*/


Thank You!
Rajani
Back to top
Nick Daniels
Forum Aficionado
Forum Aficionado



Joined: 15 Aug 2002

Posts: 14237
Location: England


flag
PostPosted: Tue Sep 03, 2013 1:24 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Run the query, then go back and look at the SQL.
Back to top
rajani
Principal Member
Principal Member



Joined: 17 Oct 2005

Posts: 278



PostPosted: Tue Sep 03, 2013 2:19 pm 
Post subject: Re: Looks Like hint in SQL but not sure

going back means, going back to the Query Panel and look at the generater SQL?
Back to top
Nick Daniels
Forum Aficionado
Forum Aficionado



Joined: 15 Aug 2002

Posts: 14237
Location: England


flag
PostPosted: Tue Sep 03, 2013 3:17 pm 
Post subject: Re: Looks Like hint in SQL but not sure

Yes. Or use Auditor. Or the data provider SQL function.
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 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
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.0923 seconds using 17 queries. (SQL 0.0033 Parse 0.0605 Other 0.0285)
CCBot/2.0 (https://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