Nov 09 2009

Making Up Data Part I: Personal Data Providers

Setting Object Types

In order to move on I will click on each object and reset the qualifications. First the Month:

Next the Month Name:

It’s important to remember that every detail must have a parent dimension object or it won’t work properly. Here I am going to associate the name with the dimension I created a few steps ago:

These steps are a vital part to solving this problem. If I left the month number as a measure it would project (sum) on the report which doesn’t make sense at all. If I left the month name as a dimension rather than assigning it as a detail I could not use it as I intend to on the report. Once I make all of these changes I complete the new data process by answering the following question. This lets the client know that while I do want this data to be included in the report, I really don’t want (or need) to see it displayed in a separate report block.

Merging Data

I have talked about the merging process before, but I will go ahead and show it here as well. After invoking the merge process I click the month from each data provider and merge them.

Once I have my merged month dimension I can drag it over to my report block. I select the merged month and drag it on top of the month that was already in the block in order to replace it. Here is what I see after that step is complete.

Hm. :-? There are still a few challenges. Here is what I see on the left side of my screen, under the Data heading. I have set my report to organize items by data provider rather than alphabetically in order to better show which elements come from each data provider. I will almost always do this when working on documents with multiple data providers.

I see both of my data providers. Each has one dimension (month) which has been merged. Each has one detail (month name). And the top data provider (my original universe query) has a measure for sales. The key to this solution is to understand what happens when I merge dimensions. The first result of that process is that my two data providers are now “joined” much like I would join or link two database tables in my universe. Because of that I can now use data from either data provider in my block. The second result is that the merged version of the month contains all values from both data providers. The first data provider has six rows, the second data provider has twelve. The merged set contains a unique listing of every value from either side of the relationship. That’s why all of the months are showing up on my report block at this point.

However, I would not want to deliver the results shown above to the business user. There are still gaps in the month name data, and the sales data only shows in the six rows that existed earlier. I can fix each of these issues. One will take a bit more effort to resolve than the other. I will start with the easy one.

36 Responses to “Making Up Data Part I: Personal Data Providers”

  1. Comment by Nidhal

    Hi Dave

    Very intresting post. I think it will help me in my report. It’s a report showing the hours worked in every two weeks. My problem is that I will need to show weekends also but people do not work on weekends. I will then use you trick to do that. The other problem is that I show my table header like this:

    weekDay: Monday Tuesday Wednesday ...
    DayNumberInMounth: 15 16 17 ...

    But as these days will change every mounth it will be complicated to do this.

    Do you have any solution for me


  2. Comment by Dave Rathbun

    Hi, Nidhal, welcome and thanks for your comment. After thinking about it a bit I think I have a suggestion. It seems that your data always starts on Monday, is that correct? Do you have access to create objects in the universe? What I would probably do is create a “day in reporting period” object that ranges from 1 to 14. This would be done in the universe rather than the personal data provider. Then you can derive the day number of the month by getting the minimum date (which should be the first Monday) and then using the RelativeDate() function to move the date forward from there, then use the DayNumberOfMonth() function to return the day number from the calculated date.

    That’s all off of the top of my head, I didn’t test it, but hopefully the concept might spark some ideas on your end. :)

  3. Comment by Chris

    Dave, I have not yet used rich-client, but in Webi if you move from the query tab to the report tab after making changes without refreshing it requires a purge of the data. That would foil the cached results for the local data provider. Can you move from query to report in rich client without purging?


  4. Comment by Dave Rathbun

    Chris, the query panel for the rich client works more like the query panel for Desktop Intelligence. When you click “Edit query” a new window pops up. If you make any changes you can either run the query or close the query panel. If you close the query panel after making changes, it will tell you that you’re about to lose your changes and ask you to confirm.

    I just reconfirmed that in XI 3.0 before posting this.

  5. Comment by Joe

    Dave, great post — thanks.

    I have a suggestion. Instead of using an external file, users of Oracle can use the “connect by level” function to generate any number of values. Thus, generating a list of months can be done in one SQL statement, as so:

    select level,to_char(to_date(level,’mm’),’Month’) from dual connect by level < 13

    This will produce the same month number/month name list that you used in your example.

  6. Comment by JPetlev

    One method we used in the past for a report similar to yours was to simply use a CrossTab. The trick to what you want can be done rather easily using a union query. Cross tabs will show values in the headers anywhere there is data. We had access to the database so we created a ‘dummy’ record that had a value of .0001 in all 7 days and we simply forced that record to be returned in our query. You could probably achieve the same results via a union query. Once it was in the recordset we hide the row ( I cannot remember if filter worked or not) and excluded it from any total formulas. The result was that we’d see all 7 days even if there was no data for a given day (since our dummy record did have a value the column in the cross-tab was always created properly).

    This may or may not be easier than modifying the universe.. it all depends on what you have access to.

  7. Comment by James Halligan


    do you know whether the personal data providers in Webi Rich Client now have the functionality that would allow the webi docs to be scheduled? There was some speculation that this would be in XI 3.1 SP2…


  8. Comment by Cynthia Giles

    Hi, Dave! The site is great. I’m actually using this method to get back in touch with you, as I have a quick question. Please ping me when you get a chance. Best, Cynthia

  9. Comment by Andreas/Xeradox

    Nice post Dave :)

  10. Comment by Lauren L

    Does this work in BOXI Etnerprise R2? I do not see add a new data provider when I right click on the Query tab.

  11. Comment by Dave Rathbun

    Hi, Lauren, and thanks for your comment.

    XI R2 does allow multiple data providers, but you can’t use Excel. Personal data providers are only available starting in XI 3.0. As far as adding a new query, in the Java query panel you can add a second dataprovider by right-clicking on the query tab. I never use the HTML query panel so I don’t know how it works there. There is also an “Add Query” button on the toolbar.

  12. Comment by Lauren L

    Hi Dave – So, here is my situation. I am a BOXI R2 report writer. My sole purpose is to create complete and accurate reports. I do not have access to Admin functions, and can only work with the Universe(s) I see. Since personal data providers are not available in R2, is there any other way I can “unsynch” the tables so that I get a row for EVERY month, even if there is no data for “the months without an e in them” (using your scenario)?

    this is a great blog, albiet a little over my head just now. I am a dinosaur Crystal Reports developer, and am new to BOXI.

    I am in Rhode Island. Are you aware of any regional training I could consider?

    Thanks – Lauren

  13. Comment by Dave Rathbun

    If you have a calendar table, you can simulate what I did with Excel by creating a data provider that only returns calendar information. That’s coming in Part II of this post. :)

  14. Comment by Lauren L

    What if the rows I am trying to create are not based on a date? In my case, they are based on asset class. I need to show a row for each asset class, even those that have no data.

  15. Comment by Dave Rathbun

    Then create a query that returns all asset classes without any measures, ensuring that every class exists. Create a second query that returns your asset classes with your measures. Follow the same pattern as shown in this block (some steps can be eliminated since the asset class will be the same object in two data providers it will automatically be linked) and see what you get. 8-)

  16. Comment by Lauren L

    Thanks Dave. I will try this.

  17. Comment by Lauren L

    I tried your suggestion (multiple times and ways) and realized I may not have provided enough info.
    Dimension [Reporting Period]

    Dimension [Asset Class] includes: Cash, Mortgage, Public,

    Dimension [Portfolio] includes: A,B
    Measure [Market Value] = dollar amt

    For each Reporting Period entered at prompt, I’d liek to see

    A Cash 10
    A Mortgage 0 (where there is no record for Mortgage)
    A Public 15
    B Cash 0 (where there is no record for Cash)
    B Mortgage 4
    B Public 9

    Any help you can provide would be appreciated.

  18. Comment by Prabhu

    Hi Dave,

    Interesting Post. I have created similar reports for my client. We have a date dimension table with which I do an outer join to get the same result.

  19. Comment by Nidhal

    Hi Dave

    Really sorry for my late to answer(thaught that I will receave a notification email ones you will reply).

    I m really happy to watch you site everyday(veeeeeeeeeeeeery useful!!!)

    I understand now the part starting from relativeDate but I m not sure that I understand the part that I should do in the universe (creating the minimum object etc.)

    Can you explain your point


  20. Comment by Dave Rathbun

    Part II is up, it details how to do some similar work with universe data instead of personal data.

  21. Comment by Roland Bray


    Once again you have not failed to amaze me with your understanding of the tool! I am anxiously awaiting the Crosstab post because I have been working on that requirement for quite some time.


    I forwarded this information (and the universe solution) to my Universe designer to gets his feedback. Hopefully we will also have success.

  22. Comment by Bill Avery

    This is cool! I wish I had known how to do this a few months ago. (Guess I would have if I had read your post!)

    Thank you.

  23. Comment by ravi

    Hi Dave,

    It’s nice seeing new tricks every time I visit your blog. For the above trick I’ve faced similar kind of situation where I’ve used a different way as follows:

    I’ve added a new query(Q2) to the same report where I’ve taken only ‘month’ object in Q2 and then merged with the month dimension object in Q1, then used ‘month’ object from Q2 in the report which shows the months with zero(0) values as well. All worked fine.

    Do you have any suggestions for me if I’m wrong ?



  24. Comment by Dave Rathbun

    Hi, Ravi, please see Part II of this post for a solution more like what you described. :)

  25. Comment by Jamie Whyte

    Dave, this is great, thanks. I have a problem that this goes some way to resolving:

    I have an aggregated chart which shows the number of people who have had a child plan, by how long the plan has been in place for, in months. This is generally between 0 and 24 months, but they can stretch to 48 months, but these are rare. I need the chart to show the month numbers where there is no data, so it is clear to a report reader that the 48-month plans are that much further away from the 24 month plans.

    My problem is that this max of months is not necessarily 48 months. It could be any value. I need the chart to somehow know the last value, and only display up to that, as at the moment, I am having to either manually apply a filter at report time (hinders scheduling) or accept that the chart will have a load of empty data at the end of it (looks clumsy).

    I feel like I’m missing something with variables maybe, but can’t get what I’ve tried to work.

    Any help gratefully received

    Many thanks


  26. Comment by Arti

    Hi Dave,

    First of all I’d like to thank you for sharing your knowledge with us. I first started with BOB forumtopics and then I found your site. Your style of explaining these several topics is amazing. its more of like out of the box thinking.

    Many Thanks

  27. Comment by Ram

    Hi Dave,

    In the above post you have said that “In fact I can delete the file from my computer altogether and the report will still work”.

    without the source file in desktop how the reoport can run?
    Can u please confirm this..

    Ramanathan S

  28. Comment by Ram

    Hi Dave,

    Is a report created using personal data provider can be scheduled?(I dont have access to WRC thats y I need your help)

    Can u please confirm this too…

    Ramanathan S

  29. Comment by Dave Rathbun

    If the data provider is marked as non-refreshable, then it doesn’t have to continue to exist. I can import the data from my spreadsheet, mark the data provider as non-refreshable, and remove the file without impacting the report at all. The data is cached inside the report. That means the report can be scheduled as well.

    One of the rumors for 4.1 is that we can publish a spreadsheet to the repository, and then reference that sheet in my Web Intelligence report, and because it (the spreadsheet) is now an enterprise resource, it can be refreshed. Even during a scheduled refresh.

  30. Comment by Ram

    Hi Dave,

    there is a server called Crystal Report Cache server and there is no server called Web Intelligence Cache server. As far as Webi is concerned there are two servers Webi report server and job server. Where the cached data get stored? which server does it ?

    Ramanathan S

  31. Comment by Dave Rathbun

    Neither. The data is in the report.

  32. Comment by Evelyne PERRIER

    Hi Dave
    Your post is very helpfull. Thanks a lot.

  33. Comment by Alok

    Hi Dave,

    Gr8 post! Thanks for the info provided. I am new to BO and was trying to use a personal data provider in my Webi report. I have some questions on using linking files within a query.

    When updating a linking file within the query, we have the option of clicking on the box by the source file or using the Edit Settings box.

    1. Do you know the difference between these two options and when they should be used?
    2. When using the box by the source file, we sometimes get an error stating: Unable to create or update Excel personal data provider (WIS10884). Do you know what causes this error?
    3. When I use the Edit Settings box to update a file, I lose formatting of summarized reports such as folding. What can be done to keep report formatting in tact?

    I appreciate any assistance you can provide to give me a better understanding of how to handle linking files.

    Thank You.

  34. Comment by Ramanathan S

    Hi Dave,

    Thanks for your useful posts.

    I have few questions on data provider

    My BOXIR3.1 webi report is running for 45 mins to complete execution.

    1)The Webintelligence report has 5 data providers. How the execution time is divided between these data providers? Is there any place we can specify and change the time taken for each data provider?

    2) Out of these 5 data providers is it able to refresh one particular data provider?

    3)If I identify one data provider is taking 40 mins to run then in webi or infoview is there any option to performance tune the report? Reducing number of report variables and data providers are most of them saying other than these are there any options to performance tune the report?

    Can you please answer these questions..

    Ramanathan S

  35. Comment by Dave Rathbun

    Hi, as mentioned elsewhere I don’t generally have the time to offer individual support on my blog, thanks for understanding.

  36. Comment by Sachin

    Thank you for this post.. it has solved my issue which was pending for more than a week in the production environment. i haven’t received any solution from or SCN if you call it..but here i got the right approach

    Thanks you again

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.