Advanced Web Intelligence Reporting Questions, Randomly Selected

I appreciate the questions that are emailed directly to me; many of them are good ideas for articles that fill a topic gap on the site. Others deserve equal attention, but they don’t exactly require their own article. This article is intended to capture and answer such questions related to Web Intelligence reporting.

My report has multiple queries (data providers) that use the same prompts how can I present the user with only one instance of the prompt?

This is part of the magic of Business Objects. Since BO 5.0, and probably before it, BO has supported
@prompt consolidation. This is to say that if you have two prompts that are exactly identical (same characters, and same options) then the user will only be prompted one time, but their submitted value(s) will be sent to all @Prompt and @Variable occurrences in report’s query(s).

Note: this question has piqued my interest because it raises two three questions:

  1. What happens if the @Prompts or @Variables vary in case (upper or lower case) only?
    ANSWER: One prompt will be displayed to the user for each variation in case.
  2. What happens if the @Prompts or @Variables vary in options only (such as one has “constrained” and the other has “free”)?
    ANSWER: The first prompt (from top to bottom) will take the lead and drive the properties of the prompt displayed to the user. Interestingly, if the prompts vary in “mono” and “multi” settings then this will cause an error in most cases. WebI will not permit the prompt to be displayed and raise an error (see below) and Desktop Intelligence (DeskI) will allow it to pass, but if the result violates the SQL statement (multiple values sent to an equals operator, not an IN operator) than the user will get an error.
    Error received in WebI when using two prompts that vary only in mono/multi settings

    Error received in WebI when using two prompts that vary only in mono/multi settings

  3. What happens if the two Business Objects @Prompt vary in LOV (List of Values) only?
    ANSWER: This is peculiar. If the LOVs are custom, then the first one (from top to bottom) will drive the user displayed LOV; the LOVs will not be combined. If one of the LOVs is based on an object’s LOV then this will override all LOVs and only the objects LOVs will be displayed to the user.

IMPORTANT NOTE: I experimented with both WebI and DeskI. I found WebI to be quite robust at handling quick changes to the conditions/prompts of a query. On the other hand, DeskI produced strange results because it was constantly caching the previous behavior of the previous test. So each time I tested a new option I needed to create a new report. This is interesting because it points to possible problems when creating a DeskI report. Certain changes to the DeskI reports I was creating were not properly reflected without starting a new report or completely exiting DeskI and returning to the report.

SHARE YOUR FEEDBACK: on this topic and all others I do, of course, welcome anyone’s experimentation or prior knowledge if they can send it in form of a comment or email.

Where can I find truly advanced WebI or DeskI report building resource?

Most advanced training courses of which I am aware are really only “advanced” if you have just started using BO. They probably contain a few really good snippets, but most of the content is geared towards getting beginners to the next level.

Honestly, I have found much of the best content contained in online BO reporting forums (Web 2.0 at its best) and much of it comes from the very generous contributions Business Objects experts such as Dave Rathbun and Steve Krandel (both have personally helped me out and inspired me to give back through this web site). Uncovering this great stuff has been to result of thorough researching of specific topics (both in search engines and using the search functions of the respective online sites. Please see our list of useful “links” for a good start.

If you know of any comprehensive advanced training courses or books (that don’t mostly rehash the simple stuff) please share the wealth through comments here or email me directly if you prefer.

Enjoyed this post? Share it!

 

51 thoughts on “Advanced Web Intelligence Reporting Questions, Randomly Selected

  1. Hi Julian,

    I have a question regarding prompts in the reports. Everytime when we run a report with prompt, by default previous values selected will be selected in the prompt. How can we make sure that everytime we refresh we get blank prompt Window? Is there any option to change at report level or Universe level?

    Thanks
    Aravind

  2. Hello Aravind,

    Definately! I need to spend some time on the organization of the site to make it easier for people to find related articles. Right now, I don’t think anyone uses that fancy revolving tag cloud on the home pages or in the side bars.

    In that cloud is a link the the “Prompt” tag. From there you would see the article called “Extended @Prompt Syntax: Default Value, Purge Values“. This is actually the most popular article on the web site (according to my webmaster stats). Please check it out and leave a comment on at that page if you have a free moment (I know none of us really do these days).

    Thanks, Julian

  3. Hi Julian,

    Thanks for this. But my requirement is slightly different. My scenario is when we create a Deski or Webi report and create a prompt in the report level itself. How do we purge those values in the prompt when we run the report second time?

    regards

    Aravind

  4. Aravind,

    The problem may be that you have missed the “Prompt Properties” icon. It’s very easy to do.

    When you create a condition, and change the option to Prompt, a new icon should appear between the text box and the icon to select your object type. This icon will bring up the Prompt Properties dialog.

    In there, you should see an option for “Keep last values selected.” Toggle this off, and it should purge the values of the prompts between refreshes.

  5. Hello Marshal,

    Thanks for this. I could see that in Webi, but can we do it in Deski?

    Thanks and Regards

    Aravind

  6. Hmm, I have no idea. I would assume you can. But, I’ve never used DeskI, so I don’t know where the button would be to set that.

  7. I know that you can do it in DeskI if you edit the SQL, but if you do this you might as well make it a prompt in the universe (which is what BO wants you to do anyway).

  8. I guess Designer/ WebIntellinece Reporting solutions will be the right place for my question. And here it goes,

    We use a Dimensional data model which has about 15 different models based on Subject areas. Eg: Billing, Claims, Eligibility, etc. Each model has its own Fact table linked to Dimensions, some of which are Conformed dimensions which is present in multiple models. We want to build Universes on top of this model and expose it to the Business Users to create WebI reports through InfoView.

    When thinking of how we can build the Universes I have different options as below,

    1. Creating a Universe for each Subject area. With this we will have 15 Universes, each has 1 fact table and many Conformed dimensions with some junk dimensions. When a Report needs data from more than one Universe, we have to link the different Universe queries at Report.
    Major drawback with this approach is change management. As our data model will be expanded in future, which in turn makes me to update multiple Universes when, say a Conformed dimension changes; since the Conformed dimension table will be present in multiple Universes.
    2. Creating a master Universe for the Dimension tables(here there may be a effort to modify data model to suit linking Dimension tables together). Then to create derived Universes for each Fact table. These derived Universes will be linked back to common dimension Universe.
    Maintenance will be easier in this approach, as whenever a Dimension changes I need not update multiple Universes, but as I am linking Universes at Designer level as Master and derived Universes, I am concerned about the Report development if the report needs data from multiple Universes. Then I would be linking “multiple Linked Universe” queries at Report.
    3. The other option I have is to combine multiple dimension models(Subject areas) into one Universe. By this we will create minimal number of Universes as possible. May be end up creating 5 or 6 Universes, but we will have tough time in maintaining Security of data elements. For instance, at high level a Universe may have Billing and Eligibility data, where I have to maintain strict Security for the User groups, and let only specific users to see/ use all data elements (objects).

    Hope I have summarized my question well, any inputs from you on the approach you are aware of/ pro’s and con’s of it in terms of time it takes to build, the performance of Report(creating WebI reports through InfoView) is appreciated !!
    We want to see which approach makes it better when it reaches Business Users who has little patience waiting for a Report and needs best possible interface 🙂

    Thanks in advance for your help !!

  9. Julian,

    How about Prompt with same name and other options but different LOVs? This is kind of a wierd thing to do. But I have seen people using it.

    Regards,

    Devang

  10. I have updated this article with the results of my tests. Thanks Devang for the reminder and additional option to test. I think you all will find the results interesting. -Julian

  11. Hi Ram,

    I wish I had some perfect answer to give you. You really already have a good analysis of the pros/cons of each option. Let me share a little additional insight that I may have.

    If you had one big universe (or just a few) one problem I have seen is that end users will start creatively combining incompatible data elements in a single query. For example, they might try to create a single query for customers and vendors. This can often lead to frustration and extra support effort and hand-holding. Having these incompatible data elements in different universes prevents this.

    I agree that defining universes be security groups can be a good thing. It allows you to easily manage security, but it also could lead to repetitious classes in universes.

    Linking universes in BO XI R2 is generally unreliable and I do not recommend it. I can’t recall right now why I feel this way, but I always avoid this when possible.

    Best Regards, Julian

  12. Hi, I want to pass multiple parameters in my report.

    I have created on report with single parameter passing.
    the code is shown as below.

    =”“+[Category]+”

    How can i pass multiple parameters.

  13. I am creating a webi report with cascading drill filter…. but it is working from top to bottom. ( Study Id >> Country)

    A. One Study_Id is present in many countries.(One to Many)
    B. In a single country many Study_Id are there (One to Many)

    Its is like many to Many condition between Study_Id and Country

    how can i achieve the cascading Drill filter functionality which will will work from A to B and B to A.

    and in the universe i am not able to put the hierarchy from both the side.

    Please help

  14. I want to learn more about these. I really hope someone who knows the answer happens to read this and takes the time to post a share their knowledge.

  15. Hi Julian,

    We are using Business Objects Web intelligence for Reporting.

    We are facing some problems.

    1) From BO Universe, I am not able to extract the time from the datetime field. i.e. From “25/12/2009 14:00:00”, I need to extract only the time “14:00:00”.

    2) From web intelligence document, I am not able to subtract two dates.

    Can you please help me to proceed this.

    Thanks & Regards,
    Senthil

  16. Hi Senthil,

    1) Those date values are just character strings, right? If so then you could just parse out the hour with the right function. Otherwise you could convert it to a date data type and then use your database’s date functions to return just the hour. Either way, you gotta get into some functions.

    2) Since the dates are being provided in a character data type’d object then you will need to convert them to date data types for this kind of requirement. You can do it at the database level (in universe with database functions) or at the report level (WebI functions).

    So functions are your answers really. 🙂

  17. i want to create a sample report in Webi XIR3 versions.Can you guide me the necessary steps to follow.i have to export it to Dev domail as well

  18. Hi Praveena, your questions are not really related to “Advanced Web Intelligence Reporting”. I will take your request as a new article request and add it to my list. Thanks, Julian.

  19. “Linking universes in BO XI R2 is generally unreliable and I do not recommend it. I can’t recall right now why I feel this way, but I always avoid this when possible.”

    Julian,

    We have similar situation as Ram and at this point we are analyzing options for best way of supporting users with growing requirements. Your comment quoted above piqued my interest as we are using BO XIR2.
    Would you mind to elaborate your experience on linking universes in BO XI R2?

    Thanks in anticipation.

    Mahesh

  20. Honestly, I have always avoided linking universes as a best practice. I can become confusing and also make universes difficult to migrate or upgrade to newer versions of BO. I can’t really recall any personal experience with bugs or instability, but I have heard that many people just avoid this practice.

    Having said this, in reports I use multiple data sources from different universe all of the time.

  21. hi,

    When i run a query, there is no data for the particular value on that time we will get a message like no data to retrieved, But we will get the table structure like all columns names with no records.

    But my requirement is in this case it should not be display the table structure also ..

    how to do this?

    Pls help me

  22. In WebI there is a “Show when empty” property. Click the outside edge/border on the table and look at the “Display” properties. In DeskI I think you can create a logical expression (Boolean) to hide or show a table.

  23. Reg: Reasons for instance failing?

    When i schedule a report if that schedule is failed.
    what could be the reasons for its failure?
    (But that same report was running with out errors when tested after its creation.)

    Regards,
    Ramanathan

  24. Hi Ram, sometimes the error message will point you in the right direction. Without it you are looking at numerous points of failure, such as destinations, communication errors between servers, exceeded limits, etc. If you don’t see an error than I suggest you look up the failed instance using Query Builder. You may see something there that could help.

  25. Hi,

    when i am scheduling a report I got the error

    Unexpected exception caught. Reason: [java.lang.NullPointerException: i_statusInfo is null.]

    How to rectify this?

    Please advise

    Regards,
    Ramanathan

  26. Hi Ram,

    1) The dreaded “java.lang.NullPointerException” error is dreaded because it really tells you nothing. It could mean that you need to apply a patch if you are on XIR2 SP3 or lower. Sorry, I can’t really tell you much from that error and without getting my hands dirty.

    2) There is a HUGE difference between WebI’s Query Panel and the administration tool called “Query Builder”. I understand that you are asking, “Under what circumstances do I add a second query (a.k.a. data provider) to a WebI report?”. The answer is that this depends completely on your business requirements and data structure. Sometimes you do so because you want more than one data selection in the same report, even if on different tables or tabs. Other times you want to be able to combine the measures between two different time intervals without any restrictions. Query efficiency can be another reason, in some cases. There are so many reasons, but I would say experiment and learn to prefer fewer queries unless there are compelling reasons to have more.

  27. Hi, Julian
    I’ve Done Migration of some reports used in vesrion BO 6.5 to version BO XI R2.
    some reports use a univers work correctly Without probleme; but reports doesn’t use a univers, It’s just SQL QUeries using a DBlink to Database source.
    In the Rep 6.5, Reports work correctly, but in Rep XI the actualisation of report retuns a error like

    “(DMA0007 : error : inf ) data providers were not refreshed correctly”

    is THERr any probleme with Dblink in BO X R2. ?
    any idea will be useful.

    thanks in advance.

  28. Hi Youssef, I personally never use “DB link” reports. I believe these are not a best practice and I am not even certain if their migration is supported by BO.

  29. Hi,
    Im my universe I have three isolated joins.
    i.e. those three are irrelevant joins
    due to these joins many select queries are generated while report creation instead of a single query.
    already I have 7 contexts to resolve loops.
    Now how to over come these irrelevant joins?
    If i include these irrevelant joins in any of 7 contexts will universe again form a loop or traps????
    Please advise..
    Please help…

    Regards,
    Ramanathan

  30. I am going to let someone else take this one. I usually get to design my database as well as my universe, so I never end up with such issues. One dirty fix is to just create a universe that includes the joined tables that you need for your kind of reporting. Yes, it is great to have it all in one place, but a big ball of joined mess with lots of contexts is not helping you out either.

    Also, think about using more aliases (copies of tables) so that you can put an end to contexts. I have often eliminated contexts by adding one or two aliases. Some may not like this approach but it helps simplify things for me.

  31. @RAM Hello, I’m getting the same problem whe sending a report by Scheduling:

    Unexpected exception caught. Reason: [java.lang.NullPointerException: i_statusInfo is null.]

    Has someone found a solution for this?

  32. HI every body,
    sorry if I publish my probleme in this tip.

    my probleme is like that :
    I try to export unvers and reports in file save.BIAR from the qualification server, with Import wizard. every thing is ok, but in the end of exporattion, the file SAVE.BIAR is empty, nothing has been exported.

    version :BOXI R2
    os : windows XP.
    profile user systeme : administrator

    any idea plz.
    thanks for advance.

  33. Hi,

    I am wishing to have the prompt for the date range to automatically pick up data from the last 7 days instead of picking a date from and date to.

    Can you please advise how I can achieve this so I don’t need to update the date range each week.

    Thanks

  34. Hi Lauren, the only way to do that is to create a somewhat complex keyword word prompt. I thought I had written an article about this trick, but I can’t find it.

    Basically, the “keyword prompt” uses SQL logic to establish some conditional statements that will evaluate to “true” when their defined keyword is used. For example, you could create a date prompt accepting only “A” (string) datatypes. This prompt could accept dates as the string “yyyymmdd” or it could accept “Yesterday”, “Last Week”, or “Last Month”. If the string submitted matches any of those keywords then logic would be included in the SQL for the appropriate relative date. If none of the keywords were found then the string would be converted to date using the edit mask of “yyyyMMdd”.

    I really need to write an article about this. Please do let me know if you are interested.

  35. Hi Julian,

    Thanks for the reply, I would be very interested in finding out more about the keyword prompts as it will save me a lot of time each week if it is already set up.
    If you do find the article it woul be a great help.

    Thanks

  36. I created a webi report and it has a query filter which is selected as prompt.I want the list of options displayed must be in alphabetial order can any one help me in this

    eg: Region filter select as prompt i want the list of values displayed in that prompt as
    Africa
    America
    Brazil
    Chicago
    China
    India
    etc

  37. Hi Ganesh, in the universe, locate the object for “Region”, view the properties, find the tab with the List of values, edit the list of values query, add a sort as you desire, test it using the display button.

  38. Hai Experts,from universe there is one measure ie;EventCode having different types of codes like Enquiry,FollowUp,LostofSales,Contact,etc
    i make a formula to Count the EventCode and used in Webi report it shows the value for differnt Codes but zero values are not read it show null blank.I WANT TO GET THE ZERO VALUES FOR WHICH IT IS IN BLANK(NULL).
    PLEASE SUGGEST

  39. Hi Raj, “NULL” values require special handling in SQL. In Oracle, you can handle them with the NVL function. This will allow you to tell the query what to do when a NULL is encountered, such as return a 0 for all NULLs.

  40. thanks for ur reply,but i dint get ur answer,let me clear my dout,from universe table there is an eventcode having different types of codes like Enquiry,FollowUp,LostofSales,Contact,etc
    i make a measure that is from object properties formula count(Tablename.EventCode)save and export it,when i used this EventCode in Webireport,it show values for paricular EventCode,but zero values are not read it show null blank as below example .I WANT TO GET THE ZERO VALUES FOR WHICH IT IS IN BLANK(NULL).

    Enquiry,FollowUp,LostofSales,Contact
    10 20 15
    5 12 5
    6 4 3

    Can u please help me how to get get zero values for null,Formula

  41. i am using simple prompt in my report but it is not appearing in webintelligence where i need to check

  42. Hi Anis, one way this could happen is if your report uses custom SQL. I suggest you look at the report’s SQL and see if you see a “@Prompt” in the text of the SQL. If you don’t something is not working.

  43. Hi Julian,
    I have a webi report req like, Report should have 2 prompts for “Start Date:” and “End Date:” and in report we should have a drill flter like “Time Period”, Lovs of this filter wil be Daily, Weekly, Monthly, Quarterly and Yearly.. Based on the selected value in the filter, we need to disply the date in the cross tab report. Ex. If we select “Dialy” in the drill filter, the report should display the dialy wise data in the Cross Tab Report. and based the difference(no. od days) between “start Date: ” and “End Date:” Prompts we need to display(restrict) the lov’s of “Time Period” Object, which we are using for Drill Filter. Ex. If the diff between Start date and end date prompts is 50 days, then we need to display the values in the drill filter up to “Monthly”, If it is 365 days then Drill Filter should display up to ” Yearly”…(This is the most challenging for me…) pls help me out ASAP as its an urgent req… Thanks in Advance…

  44. Hi Kiran, I am not sure the requirement is possible without some compromise. I could see this accomplished with a very custom JSP page using Javascript, I am not sure you can do this with WebI. I suggest you post this on the BOB forum, please find the link here.

  45. Hi,
    I have a report which should run separatly for each country included in teh database.
    Is there a way to set the report so that he will automatically run for each country, or I have to schedule one by one separatly the report?
    Thank you in advance for the help,
    Johanna

  46. Hi Johanna, your requirements will be best met by using a publication with a filter/rule on country. I have not really used publications much so I can’t say more than this and I am not even sure about it.

Leave a comment

Your email address will not be published. Required fields are marked *