Nov 09 2009
Making Up Data Part I: Personal Data Providers
Locking Data Providers
In order to make this document portable I have to do something about that personal data. After all it’s personal… it belongs to me and exists only on my computer. But I want to be able to share this document with anyone in the company. It turns out this is fairly easy to do.
I will open the query panel for this document, select the proper data provider, and set it so that it is not editable or refreshable. What is the benefit of doing this? The number of months is not likely to change. The zero dollar values I created are just placeholders in case a month has no records, and so they won’t change either. Once the data is cached inside the report client I don’t need to refresh it. Ever. So I mark it as such, and I don’t have to worry about my personal data anymore.
In fact I can delete the file from my computer altogether and the report will still work.
Conclusion
In this post I showed how I can use a personal data provider to “make up” data that might be missing on my report. I showed how to import spreadsheet data into the client, and how to make sure that each column got set to the proper object qualification (type). Finally I showed how to use that new data to solve my report challenge.
Is this the best solution? It’s not bad, and frankly it does a good job of showing off the capabilities of the client. However I have another option to present as well. When I finish that post it will come out as Part II. I am sure you can hardly wait. 
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
Regards,
Nidhal
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.
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?
Chris
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.
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.
Nidhal,
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.
Dave,
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…
James
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
Nice post Dave
Does this work in BOXI Etnerprise R2? I do not see add a new data provider when I right click on the Query tab.
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.
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
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.
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.
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.
Thanks Dave. I will try this.
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.
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.
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
Regards,
Part II is up, it details how to do some similar work with universe data instead of personal data.
http://www.dagira.com/2009/12/05/making-up-data-part-ii-using-universe-data/
Dave
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.
Roland
PS
I forwarded this information (and the universe solution) to my Universe designer to gets his feedback. Hopefully we will also have success.
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.
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 ?
Cheers
Ravi
Hi, Ravi, please see Part II of this post for a solution more like what you described.
http://www.dagira.com/2009/12/05/making-up-data-part-ii-using-universe-data/