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. How to create a test report in Webi XIR2 versions..how to start with

18. 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

19. 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.

20. “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

21. 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.

22. 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

23. 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.

24. 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

25. 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.

26. 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

27. In Webi When do u go for a second query builder?
    In what situations??

28. 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.

29. 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.

30. 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.

31. Hi Julian.
    Ok. Thanks 🙂

32. 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

33. 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.

34. @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?

35. 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.

36. 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

37. 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.

38. 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

39. 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

40. 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.

41. 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

42. 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.

43. 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

44. You would need to change the SQL for your object. If your database were Oracle you would want something like: NVL(eventcode,'0')

    Read this short article for more information: http://www.techonthenet.com/oracle/functions/nvl.php

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

46. 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.

47. 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…

48. 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.

49. 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

50. 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.