Skip to Content
author's profile photo Former Member
Former Member

Data Selection for report based upon a 'Prompt Value'

I want to report information in my report based upon a 'user input prompt value'

for example:

'Enter Shareholder Selection - A-Active, I-Inactive, B-Both Active and Inactive'

if the user enters 'A', the report selects only active shareholders

if the user enters 'I', the report selects only inactive shareholders

if the user enters 'B' the report selects all shareholders, active and inactive

the field in the database that this based upon is their total share value.

if this field is greater than zero (>0) they are considerd 'active'

if this field is equal to zero (=0) they are considered 'inactive'.

I have tried creating some type of filter, but am not having any luck.

I saw a few examples within the forums that I have tried without any luck....unfortunately most of the examples I've seen are base one only two choices.

I'm sure I need to create some type of 'independant varible' but am not sure how to do that either.

Any suggestions would be appreciated.

Thanks.

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 12, 2009 at 06:15 PM

    Daryl,

    the field in the database that this based upon is their total share value.

    if this field is greater than zero (>0) they are considerd 'active'

    if this field is equal to zero (=0) they are considered 'inactive'.

    It seems from a data reporting perspective that you have two choices:

    1) massage the data using a reporting tool (such as Desktop or Designer)

    2) set the data at the time of insertion to either an "A", or "I"

    If you are writing reports off of an instance of the data, meaning you're using something like DI or other ETL type of tool, then I would suggest using option 2 and thus the data is available to any reporting tool that you use to connect into your database for generating reports. If you must go with option 1, then I would suggest doing the "massage" in Designer, hence you build an object that all BO reports can use, versus massage at the report level where you have to attack the data with each report you construct.

    Thanks,

    John

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Alan,

      I think your solution is more straightforward and will work better for Daryl. One item to note is in the beginning of your code where you have

      [Any_Dimension] = case 

      . I think that this should just be

      case

      . At least when I've worked with Designer (and I'm using MS SQL Server), the Designer did not syntax correctly when I included the construct you proposed, however by going to just case it did syntax. If you were running SQL as custom SQL in BO or just running SQL in Analyzer or something, it would be good SQL syntax, however, in Desiger, it's a no-go, as per my experience.

      Daryl: good luck and let us know how you make out.

      Thanks,

      John

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2009 at 01:56 PM

    Hi Daryl,

    I Tried this unsuccessfully in DESKI . We can't Eliminate Rows having Empty Measure Values or Measure with 0 as values using Table Level Filter as FIlter can't FIlter rows based on Prompt value selection dynamically. Filters filter rows at a time and not based on 3 condition as Active, Inactive and Both. thus filters are of no use.

    I Tried this in WEBI, and it is working perfectly you donu2019t have to create any Object in Universe, you can do it using function UserResponse() at report level.

    Hence if you are comfortable using WEBI for Generating this report then Follow the steps.

    1. Create Report With Name and Shares Object. It will display all Shareholder Names and No.of shares they hold.

    2. Use Status Object in Query filter, use condition as u201CEqual Tou201D and Select prompt. It contains Active, Inactive and Both as values.

    3. Report will Display all Shareholder names and No. of shares like 45, 789, 0, 4562 where 0 is inactive Shareholder and all other are active shareholder.

    4. Create Variable using Formula.

    =If(UserResponse("Enter Status:")="Active" And [Shares]>0;[Shares];If(UserResponse("Enter Status:")="Inactive" And [Shares]<=0;[Shares];If(UserResponse("Enter Status:")="Both";[Shares])))

    5. Remove Shares Object from the report and Put Variable created with Names of Shareholders.

    6. Select Table-> Properties-> Display-> Uncheck the Option u201CShow Rows with Empty Measure Valuesu201D

    7. Report will display Value correctly as per your Prompt value selection.

    I Hope this Helpsu2026

    Thanksu2026

    Pratik

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Pratik and thank you for your input,

      I am not familiar with WEBI. Unfortunately I've only been working with Business Objects (Deski in particular) for approximately 3 months.

      I have converted over two dozen reports from our legacy systems on the AS400 (I Series) so am becoming more and more familiar, but am still quite a novice in that respect.

      I know we have some reports created in WEBI, but again, unfortunately not by myself.

      I had originally created these reports (three in all - one with the Active Shareholders, one with the Inactive Shareholders and one with both Active and Inactive).....individually and also within one exectution with 'tabbed' occurances). However, my supervisor would prefer one report with 'option' to run the one 'of choice' at the user 'requests'...to eliminate excess pages.

      But I digress......is WEBI similar to DESKI?

      Do I have any other options that you can think of?

      Thanks again....daryl

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2009 at 03:51 PM

    Hi Daryl,

    Creating a report in WEBI is Very Much Similar to DESKI. (95% Same)

    If you are comfortable to create report in WEBI here are the steps to work with WEBI.

    1. Select Business Objects Enterprise Java Infoview instead of Desktop Intelligence.

    2. It will ask you Your Login Credentials similar to DESKI.

    3. At left hand side you will see New Button click on WEB Intelligence Document.

    4. WEBI will prompt you to select Universe. Select The Universe.

    5. it will open Query panel select the Objects I mentioned in the previous post.

    6. Run the report (Run Button at the top)

    7. Follow the Steps as I mentioned (Creating Variable is similar to DESKI just use the formula I pasted. All Operation are same as DESKI.

    After Table Selection Table Properties are at the Left of Window Beside Data tab that contain objects that you have taken on the report.

    I Tried all in DESKI but of no Use so I at the end I tried WEBI.

    I am sure; this solves your issue very Easily with Perfection (Exactly as per your Requirement)

    Try these steps in WEBI it is Very Easy. Process of Creating Report is same for WEBI and DESKI. Just Interface is Different.

    Let Me Know, if you face Some Problem with these steps while working with WEBI.

    Thanksu2026

    Regards...

    Pratik

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 12, 2009 at 09:39 PM

    Thanks to you both.....that should do it if I can get my syntax correct.....I will give it a try and let you know.....

    daryl

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2009 at 01:58 PM

    Hi Guys.....unfortunately I could not get either to work.....I've tried reformatting the statement to no avail.

    I keep getting 'Invalid Definition (UNV0023)'

    Is there a definition of this message that would explain why?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2009 at 09:43 PM

    Hi all.....I finally got my issue resolved, and Pratik I'm sorry to say, I didn't do it with WEBI.....unfortunately we don't use that much in our environment therefore they preferred it be resolved in DESKI.

    What I ended up doing, and I believe this is where John and Allan were attempting to direct me...

    I created an object in my Universe 'Shareholder Status' - using the following case statement to set the initial value of each.

    CASE

    WHEN [Share Amount] > 0

    THEN 'Active'

    ELSE

    'Inactive'

    END

    Within DESKI in my report... I set my 'condition' within my query to :

    Shareholder Status INLIST prompt 'Enter Shareholder Status'

    the user must then 'enter or select' , Active, Inactive, Active;Inactive

    I could also change my 'report number' and 'heading' based upon these selections.

    Thank you all for input....I very much appreciate it!! A total learning experience!!

    Thanks Again!

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.