Nov 09 2009
Making Up Data Part I: Personal Data Providers
Linked Details Are Your Friend
Earlier I was very careful to reset the qualification of the month name data from my personal data file to a detail. I made sure that the detail was associated with the month dimension. Now I can show why I did that. The month name currently on the block is from the first data provider which only has six rows. My personal data provider has twelve rows and therefore is not missing any months. All I have to do is select the month name from that second data provider and drag it onto my report block. Once I replace the original month name I see this.
Now my month numbers are nicely filled out, and my month names are all provided as well.
Earlier I said that merged dimensions contain all values from either side of the relationship. Details cannot be merged. But if a detail is associated with a merged dimension it can be used in the report block to add the missing data. So far the steps I have taken have ensured that all twelve months are showing on my block, and all twelve month names are also present.
But I still only have six sales values. I could consider fixing this with a custom format; when creating a custom format I can set the cell to display $0.00 if it’s null (empty). Unfortunately that would not generate the proper results for a count or average calculation. An average of my sales over six months is $1,214,408. But my average of the six values shown on the report over twelve months would be $607,204.
The issue as shown above is that the average calculation will ignore the empty cells. If I want a true average I need to make up some more data.
Making Up Numbers
Earlier I created a spreadsheet file with a complete list of month numbers and month names. I am going to open that file and add one additional column that will be used to fill in the missing sales numbers as well. Here’s what that looks like.
After saving the updated spreadsheet file, I will go back into the query panel and “edit” my personal data provider. That means that I get to work my way through the same process as before. This time I also check to see that my sales measure has the proper qualification and projection function.
The next step is to get this value on the report. The problem is that I have two measures, and there is no process to merge measures like I have for dimensions. That’s not what I want anyway. Instead what I am going to do is create a new measure using the variable editor. The formula is quite simple as shown below.
By adding the two measure values together I am combining the values found in each of the two columns. Now I can replace the original measure with the new Total Sales measure I just created. Here are the results after doing that.
Finally everything looks good. 
The last challenge is to keep it looking good.
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/