I created this short demo so you can see how I was able to accomplish this. This was done in the Usage Tracking subject area.

Here you can see I hide the Year Column.

I went into the Compound View and added in a pivot table. All is showing as it should with the year column no longer visible.

Here I have trimmed down my Pivot Table to show just the Query Count and the Year.

I now go and change the Year to Hidden, even though it’s already hidden in the criteria view. In the same step, I again go into Year and remove the hidden check mark. This will over-write whatever the Criteria view has set and will allow the column to show up in the report. Simple, but effective.

I know that often times I’ve been asked to create a report using a Table view so as to allow users to use the dynamic sorting functionality and at the same time have wanted to bring in attributes into a secondary pivot, but was unable do so because of the default functionality of the table view…now I can. Enjoy.

The solution that I came up with touches on some of the other techniques that I’ve been using recently in that I relied on creating Presentation Variables through the dashboard prompts.

The first roadblock that I found was that when creating a prompt, you can not utilize the same column twice. At first this seemed like a problem, but then I realized something else. We a filter is created in a report to reference a session variable, if that same field is prompted using a dashboard prompt, the dashboard prompt is going to win out over the presentation variable that’s defined. This is the same way that it works when dealing with values, so I guess I shouldn’t have been too surprised.

How did I get around it? I didn’t use any fields that I was prompting in my report. I selected three arbitrary fields and created a prompt using those. I labeled them “1st Month”, “2nd Month” and “3rd Month” and assigned them presentation variables M1, M2, and M3.

In the “show” field, I defined how I would like my data to show. As you can see, I referenced my month field that I was using for the actual requirements, completely ignoring the actual column that’s being used in the filter.

Next I went to create the filters. This was fairly straight forward.

Once the filters were in place, and there were no “Is Prompted” values referencing any of the false fields in our prompt, we were good to go. Now, when users go into the dashboard, they are given three drop down selectors where they can select distinct months allowing them to see a trending view of the data without the risk of pulling large amounts of data.

“Michael, how do you go about saving a view when one of the prompts on the page contains a variable that populates the initial value (such as date)?”

I explained that I hadn’t really given it much thought. I continued on with my work for the day, not thinking about it much more. Later that day, the developer comes back to me and says:

“I thought you said it couldn’t be done, but you did it on XYZ Report…how did you do that?”

Sure enough, I took a look and was baffled as to how I got it working, as I wasn’t even aware that it WAS working correctly. I had just assumed that whatever value had populated the prompt when the view was saved was the default value.

That day, I took a few hours, researching and looking around for a solution and couldn’t find anything. Even with a working example I couldn’t figure it out. I chalked it up to a strange fluke and kept working on other things.

A few weeks later, a different developer comes to me and asks me how I did that. I explained to him that I had already looked into the issue and couldn’t figure out a solution or a method that allowed me to make it work…as I was sitting there waiting for him to reply, it came to me, so I figured I would share it with the OBIEE Community, as I haven’t seen it posted anywhere.

Problem:

When a user saves a view, all of the selection criteria that is currently populating any prompt will be saved. In a situation where the prompt contains a variable, such as a session variable populating the current date, that evaluated variable is saved. Typically this is not ideal, as items such as dates should change as time passes.

Solution:

Create the dashboard page as you normally would, setting up the sections to allow collapsing. Populate the sessions with all reports, links, etc, except for the prompts which will contain non-static variables. Save the dashboard and go out to the tab to set up your saved view. Collapse sections or set any prompt values that you would like to set up and save your view as you would normally do. Then go back and add in the prompts which contain the variables, save your work and then you will have a saved view that users will be able to use and the evaluated variables will not be saved in the view.

This is odd, as typically, there aren’t many order of operation type functions that have to be followed in OBIEE in order to get reports working the way that we’re looking for.

Happy reporting!

The 6^th column in the prompt editor is the “Set Variable”. In this drop down, we’ll be selecting the Presentation variable. For this example, we’ll use the Customer Field, and create a Presentation variable called Customer.

That’s all there is to creating a Presentation Variable using prompts. Now is when you ask, well, “What can we do with it?”

Well, in this case, we can use it to solve a question that I posted in a previous blog entry.

The question was, when using a collapsed section, how will a user know what they have entered into the prompts and if they have actually clicked the “Go” button?

First, we’re going to create a report that will contain our narrative view.

We’ll reference the presentation variable using the @{VALUE} syntax. Set the Rows to display only a single value (as this method will not work with multiple selections). You can set up whatever text formatting that you’d like in this field.

Ensure that your narrative view is the only object in your compound layout. Then, using the dashboard editor, place the saved report below your prompt.

By default, your prompt and narrative view should look like this:

Notice how the narrative shows nothing, as the Customer Presentation variable has not been set. Once a Customer is selected and the “Go” button is pressed, the Presentation Variable will be set and will appear in the narrative report.

Notice how the Narrative has been populated, telling the user they have selected a Customer and are ready to open their collapsed report.

This is only a single example of how to use the presentation variable, but there are many situations where it can be extremely useful.

Happy Reporting!

I’ve used a few methods in the past to do this, but all of them have issues that make them less than ideal. More often than not, when faced with a situation where a report cannot fire, the following solution is fairly viable and has worked for me in the past. We’ll just call it “The Linking Solution”…

Here’s a typical report. Let’s assume a developer doesn’t want the overhead of running this report without prompt values being populated, but at the same time, doesn’t want to set a default value for the prompts that a user would then have to remove. A straight forward solution is to put a link to the report in place of actually having the report.

While this is a decent approach, it’s limited. The user has no idea if he has actually clicked on the go button, setting his prompt values, and frankly, it’s just not that intuitive.

Also, once a user selects their prompt values, they are stuck with them, as the report will open in a separate window. This can be avoided if you create an additional hidden dashboard page, but in the end, that’s a lot of maintenance for what should be a simple feature.

Another option that can be implemented is collapsing sections.

First make sure you selection is collapsed then save the view.

Saving views is a pretty easy and straight forward method to allow a report to not display. There are a few problems that crop up when using this approach.

1. If there are any session variables (such as Today’s date) in your prompts, then when that view is saved, the value is saved along with the view. With any report that has a session variable, this issue is a show stopper.
2. The user has no idea if they have clicked the go button prior to opening the report, which in theory could cause all of this effort to be a waste.

Fortunatly, there are work arounds to these issues.

Problem 1 is solved here.

Problem 2 is solved here.

Happy reporting!

In BI, we’re often sucked into the client’s wishes for more functioanlity, more complex reporting, more, more, more…but it’s our jobs, as a consultant to direct the client into the fastest way around the track, not just cramming the most bells and whistles into the package and slamming a beastly server under the hood. It’s often easy to forget, amid the 100′s of status calls, discussions, planning sessions, POC’s, etc, that it’s necessary to have restraint and not just give the client everything they are looking for.

Using my car analogy, I’ll go back to a Tom Cruise classic, Days of Thunder.  There’s a particular scene where the hot shot race car driver (Tom Cruise) wants to run around the track as fast as he knows how, but in turn, destroys his tires, thus costing him a quality lap time. Eventually, with coaching from his driving instructor, he learns how to best use his car on the track and performs better overall. This is essentially the same thing that we try to do on a daily basis, coach our users and BAs into creating a product that will be the fastest around the track without slamming a $3M server into to the environment just to mask a design issue.

Some ways that I have been successful in the past at convincing clients to take a more ideal path around the track:

- Stories. Nothing works better to convince a client to go one way or the other than telling them a heart felt horror story of someone else’s mistakes and where they ended up.

- Common sense. Stepping back from a proposed solution and looking at a top down approach and explaining, with clear and simple logic why a different approach may be better suited has often led to changes in report design.

- Examples. The one thing that we, as report developers often take for granted, is that we already know how the tool will look and how it will perform given certain tasks, but this knowledge isn’t something that we can always easily convey to a user. Sometimes the best way to achieve this is to make an example. Sometimes spending an hour to create a mock-up will save you 20 in development and rework.

-Ask the annoying question….”Why?” I’ve often seen that users will make requests for reasons like “Well, such-and-such program did it that way” or “It just seemed like the right thing to do”, when in reality, typically a much simpler design is going to almost always work out better for both the end user experience and the functionality of the analytics being presented.

Happy Reporting

It was brought to my attention by @Nephentur that the only suitable code for passing images through the Excel download link was the fmap used in the following format:

Fmap:images/yourimage.jpg

This of course assumes that yourimage.jpg is located in both of the following directories on the BI Server:

ORAHome\j2ee\home\applications\analytics\analytics\res\s_oracle10\images

And

OracleBI\web\app\res\s_oracle10\images

As it turns out, you can’t use fmap references in the Narrative view, which is where we originally tried to reference the image, but you can use them in the Title object in the dashboard editor.

Using an extra title object, you can place the image wherever you want within the report. It’s good to note that you’re probably going to want to remove the blue line that defaults under the title.

Go ahead and remove the default boarder and you will no longer have the line.

It’s as simple as that.

Thanks to @Nephentur for pointing me in the right direction and to Srikanth Addala for his post on the subject.

That evening, I headed out to the bar, got dinner, some drinks and met with some friends. At the end of the night, I didn’t really think much about it, as the waitress handed me my bill and thanked me for my patronage. I signed the bill and went home that night. Everything was as you would expect. The next day, as I was taking out my debit card to pay for something, I realized that I had an extra Bank of America debit card in my wallet…puzzled, I didn’t really know what to make of it. I figured that I must have picked it up somewhere along the way, and, being an honest individual, I did what any honest person would do, I pushed it through my shredder.

Well, I get up this morning, log into Twitter and have a private message from @LodgeBoca, letting me know that I had the other gentleman’s card and they would like me to come in to square everything up. I was astonished and actually quite impressed. It was pretty remarkable that Twitter was able to make that connection as quickly and easily as it did. It’s things like this that show that the whole social media craze that’s going on today is more than just a passing fad, it’s something that has real and applicable uses other than just allowing teenagers to stalk one another. Nice job Lodge.

This can simply and quickly be done in OBIEE without resorting to complicated RPD changes.

In this report, we will assume we have an index on the “Market Number” column, but the spec calls for the “Market Name and Number” to be prompted. Here we’re going to want to pass the “Market Number” column in the query, but don’t want the user to have to figure out what the Market Name is associated with the Market Number.

In order to do this, we need to create an auxiliary report that will be placed in the Shared Directory. The report will contain a single field (Indexed Field) and a single filter that is the “is prompted” for the prompt. Create this report and save it in the Shared Directory.

Go back into your main report, and create a filter on the Indexed field, clicking on the Advanced button and then “Filter based on results of another request”.

Select the auxillary report that you created in the previous step as the “Saved Request”. Because there is only a single value in the report, it will default to our Indexed column.

Your report should end up looking like this:

Now, when a user goes and selects a Market Name and Number (our M01 Market field) with the prompt, the original report will not see or read the value, but the auxiliary report will. Our main report will pull back any values that are associated with the selected Market Name and Number fields, but use the Market Number.

In real world testing with larger data sets, I’ve found that using more than a single value in the auxiliary report greatly diminished the benefits that were gained using this approach, but that could have been due to our data, so give it a try to help reduce the number of auxiliary reports needed.

It’s also good to note that the auxiliary report doesn’t need to be displayed on the dashboard for it to run and work.