how to implement prompts in free hand sql report

building reports desktopintelligence
1

Hi all,

Can you provide me the steps & Syntax for prompt in Free hand sql reports

How to use @prompt,@varible for prompt options in Free Hand SQL Report

1.how to implement prompts in free hand sql report.
2.how to implement prompts using @variable in free hand sql report.

Thanks & Regards,
James Charle

james.charle (BOB member since 2010-08-03)

2

Hi,

[quote:54a86b16d6=“james.charle”]1.how to implement prompts in free hand sql report.
[/quote]
An example of a query with @prompt() function:

select *
  from customers
 where customer_name in @prompt('select customer', 'A', {'John','Peter','Ken'}, multi, constrained)

[quote:54a86b16d6=“james.charle”]2.how to implement prompts using @variable in free hand sql report.
[/quote]
Do not use @variable() for prompts. @prompt() function should be used instead.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Marek, as I respect your input, please detail why not to use @variable in places where @prompt is used. Unfortunately, I do this, is this a performance concern or a standard you prefer? (Sorry if this is bad place to ask for clarification)

BO Bozo (BOB member since 2009-09-22)

4

Hi,

If you have a prompt then use @prompt() function - that’s what the function is for.

The @variable function is meant for getting a value of a system variable When you use an argument of the @variable() function that is not understood then it behaves as a prompt - it asks for a value of the system variable. But that’s not the purpose of the function. Furthermore, using @prompt() you have much better control of how it works.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Why are you using Freehand-SQL anyway? And why are you using the dead Deski tool instead of Webi or Crystal?

Andreas :de: (BOB member since 2002-06-20)

6

Hi I have a question here???

can we use @prompt and @ variable in derived tables in universe??

hellboy (BOB member since 2010-08-22)

7

Yes.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Not all Ad Hoc Reporting can wait on an available IT person to create an object/class for a ‘one off’ situation. Nor is this feasable…the universe would be as polluted as Earth.

Do not kid yourself:
1.) Thinking Deski is dead
nor
2.) That Webi is capable of all things Deski or vise versa

BO Bozo (BOB member since 2009-09-22)

9

It is not about what I like or not, but SAP stated that DESKI is EOL = End OF LIFE. One should to see the writing on the wall.
Webi offers many features as of XI 3.1 SP3, although not all Deski features are available in Webi, such as VBA macros. But with the scheduling feature built-in to the XI platform one area for which VBA macros were used is now covered. And how are VBA macros supposed to work in a server/browser environment anyway? Java/.NET is the key here.

If one needs more power than Webi offers SAP’s recommendation is to utilize Crystal Reports.

One does not have to like the message that Deski will be depreciated, but that seems to be a fact. And with that one should be prepared and not use an outdated tool, just so one can enjoy the seemless conversion :roll_eyes: to Webi in a follow-on project. And believe me, from my own experience, the automatic conversion is not seemless…

Now, regarding Freehand-SQL, the vast majority of clients I have been working with (Fortune 500 and mid-size companies) do mot use nor do they need Freehand-SQl. A universe is like a well designed data mart and should allow for all required analysis paths. If one has the time to write complex SQL, then one should have the time to adapt the universe as well IMHO. if new data structure become available, the data warehouse team should know and the BI team should know beforehand, so changes can be made in time.
Of course one should implement some emergency policy to cover exceptional circumstances!

Andreas :de: (BOB member since 2002-06-20)

10

Personally, I don’t think fundamentals like printing a report to fit a page wide should be beyond Webi’s capabilities.

The poster has already stated that there is a resource bottleneck within the designer area; if they don’t have the skills themselves, then freehand SQL reports are a perfectly valid option. What is important imho is that they raise this as an issue and request anything not available be incorporated in the universe as soon as possible. Real life gets in the way of the way things should be; for example, we’ve got a DBA leaving tomorrow so I’m having to step into that area to help out.

11

Marek or anyone can you guys help me out with my question below

I am trying to do the following query with bo using free hand sql… i have already create a odbc connection which i have been using for a couple of years now and i know it works fine… I am trying to do the sql below and when i paste that query in the sql server manager and run it there it runs fine no error

DECLARE @vv varchar(10);
SET @vv = ‘09/1/2014’;

WITH tt AS
(
SELECT *
FROM customSWMcalendar
WHERE datex <= convert(datetime, @vv)
)

select *
from tt

ultimately i want to put a @prompt for the variable @vv… i want the user to be able to pick whatever date they want. Hope anyone can help…

i get the following error in bo

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.State: 42000
banghead.gif

fdomond (BOB member since 2014-09-19)

12

It could have to do with date formats not ceonverting properly. Forget the SQL code and start building the prompt in the universe or the report.

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)