CD Group Blog

Printing All Rows for an Updatable PDF Briefing Book

I ran into a quirky issue with Briefing Books the other day that I hadn’t noticed before, and the behavior wasn’t readily obvious to me at first. Maybe it has always worked this way – I haven’t had to use Briefing Books much in the past. But I also didn’t find much online, in MOS or the user doc to indicate the behavior that I found.

I’m using OBIEE 11.1.1.6.5 in this case, and unfortunately haven’t tried it on a different version yet to see if its unique to this particular version or not. Please leave a comment below of your own findings if you try the same thing on a different version.

Here’s the simple scenario. I have a BI Analysis on a dashboard page, with that analysis using the default “show the first 25 rows” setting:

I added the dashboard page (that this analysis is on) to a Briefing Book. And at first, I selected “Snapshot” for Content Type:

I also verified that since I was printing the entire dashboard page to the Briefing Book, I updated the Print Rows to “All” in print properties inside: “Edit Dashboard” –> “PDF and Print Properties…” For safe measure, I also applied this same print setting to the table view in the BI Analysis itself that is placed on the dashboard page (so I had Print Rows “All” in two places):

And, as I had expected, when I generated the PDF for the Briefing Book, I saw all rows (more than 25) in my output all the way down to the Grand Total line:

I then realized that I had my Content Type value set wrong in my Briefing Book… I actually wanted the data updatable instead of a one-time static snapshot. So I updated the value accordingly:

SIDE NOTE: In my case, I actually ended up in my tests creating a brand new briefing book from scratch. I think I kept running into another separate issue or bug. It seemed to be related to updating the Content Type setting for an existing briefing book object when I was also updating the Print Rows value. So I couldn’t consistently seem to get an existing book to update to the new viewing definition, and I just started with a fresh briefing book definition each time.

When I started testing my new “Updatable” briefing book, however, the output only showed the first 25 rows, even though my Print Rows were still defined to “All” for both the dashboard page and BI Analysis view definitions. Hmm… I didn’t expect that:

So is that the designed and expected behavior? If so, I couldn’t find it anywhere in the user doc, MOS or a forum/blog online indicating it was supposed to work this way. So this is what I found:

  • “Dashboard page Print Rows: All” for Snapshot briefing book does print all rows
  • “Dashboard page Print Rows: All” for Updatable briefing book does not print all rows

I personally expected it would work the same way as Snapshot worked, but maybe since it’s an updatable view of data, the design thought is to keep the overhead lower by only showing what you’d show to a user in the online viewing of the dashboard page? Not sure. Oh well.

So here was my solution; er, workaround rather…

I bypassed the issue by explicitly indicating the number of rows for the table (or pivot table) view to show. So in the properties for the view, I updated “Rows per Page” to a value greater than 25 and to a value I don’t expect the analysis to exceed:

And now my updatable briefing book renders all the rows (up to 100) and doesn’t show the “Rows 1 – 25” at the bottom:

To me, it’s not a really great work around as I might have wanted to keep the lower number of records for when viewing the dashboard online, and only deliver a PDF book of all rows. But for this particular instance, it’s fine for what I needed.

 

Posted in Business Intelligence (BI) | Leave a comment

Increase User Adoption by Integrating Oracle’s CRM OnDemand with JD Edwards

User adoption is the biggest challenge for companies implementing a CRM system. Users want one place to look for all information about their customers. By integrating JD Edwards (JDE) with Oracle’s CRM OnDemand (CRMOD) , companies can provide a 360- degree view of their customers in one system.

Two types of integrations between the JDE and CRMOD, developed by CD Group, provide companies a 360-degree view of their customers. The first type is weblinks, which use a smart URL to provide integration from CRMOD to JDE AR Aging and Credit Information, Open and Closed Sales Order Status, and Quote and Sales Order entry directly from CRMOD Account or Opportunity records. Through the use of weblinks in CRMOD, a parameterized URL is built that will pass the pertinent information from CRMOD into JDE and bring up the related record. The URL includes the JDE Program, Form and Version as well as the Data Items that will be passed from CRMOD to JDE.

The second type is batch integration using a JDE program and web services. The JDE program extracts the data from the appropriate JDE files into a .csv file and the web services program extracts the data from the .csv file and inserts it into the appropriate object in CRMOD. A scheduler is used with both programs so they can be run at appropriate intervals. Most organizations run these programs nightly.

The two most common batch integrations are the Address Book and Sales Order History, where JDE is the master for both.  Data Selection is used on the Address Book program to determine which records are extracted in JDE and inserted into CRMOD. The Sales Order History program works a little differently, as it may have to pull from both the open and closed files in order to obtain all sales information, depending on whether the organization purges to the sales history files.

Utilizing the analytics in CRMOD, the sales order history data can be sliced and diced to display the information so that a sales rep has this type of information at his/her fingertips. We normally have a dashboard on the home page with overall sales information, as well as sales information specific to the customer, on the Account page. Using both types of integrations allows a sales rep to have a 360-degree view of the customer at all times.

If you have questions or would like to discuss these integrations further, please feel free to comment here!

 

 

Posted in CRM, Uncategorized | Tagged , , , | Leave a comment

OOW Slide Deck: Financial Reporting with BI Publisher+OBIEE Against Relational DBs

Well, after months of a long lingering anxiety about my first ever Oracle OpenWorld presentation, I’m happy to report it’s finally over. I can also proudly say that I didn’t pass out, didn’t catch a pie in the face, nor was hit with (many) tomatoes…

Thanks to all those who attended (way more than I’d expected!), for the kind words that followed, and to all my friends and colleagues who were gracious enough to show up to support me as well.

In addition to Harry Moser and Craig Kelly of CD Group, a huge thank you especially goes out to Kevin McGinley of Accenture, Chet Justice of ORACLENERD and Michael Neal of RockIT City Tech who were totally cool enough to agree to be part of my review and critique team as a personal favor to me.

Per a few requests, I wanted to make available the link to my slides for anyone who might be interested.

Many of them are minimalistic, and therefore the deck may unfortunately prove to be not all that useful if you didn’t get a chance to sit in to hear the commentary. Sorry about that. But I do plan on writing a couple follow up entries in the coming weeks to elaborate on the approach I took, and my reasoning behind why I did it the way that I did. So once that’s out, the deck will (hopefully) make a little bit more sense.

Oh, and another thing – this deck download is complete, I might add, with all my totally corny jokes still in place. So, consider yourself officially warned…

Presentation (PDF, 2MB): Financial Reporting with BI Publisher and OBIEE Against Relational DB

Thanks,

Jeremy

Posted in Business Intelligence (BI) | Tagged , , , , , , , , , , , , , , , , | Leave a comment

Dynamic Value Display Switching in OBIEE: Between Whole Dollars and Thousands – Part 2

In Part 1 of our post on this topic here, we looked at using a Presentation Variable approach for each and every measure in each and every analysis we would like to enable for display switching between whole dollars and thousands (000).

But what if we didn’t want to have to maintain each analysis as a “display divisor enabled” kind of report and have to divide each measure column each time I wanted this behavior? What if instead I wanted to “bake it” directly within the measure’s definition itself? This approach looks essentially similar on the front end to what we did in Part 1, but this time we add some logic to the RPD via a Session Variable.

Create a Session Variable in the RPD. I called mine DISPLAY_SV.

The init block is really basic – just hardcode a default of 1 by doing a query on DUAL (in Oracle DB). Note: This example here actually takes place against a DB2 database, so I faked a DUAL table by building one myself. :)

Now in the RPD, for each measure logical column in the BMM that I want to activate for this display switching mechanism, I’m going to update its calculation:

In my example, I’m dealing with a logical column that was already using a logical calculation for some additional FILTER functionality as you can see below. But you could have just as easily mapped this divisor into the physical calculation mapping in the LTS for a physically mapped measure.

As far as the order of operations is concerned, it shouldn’t matter for a physical calculation measure column with sum aggregation if you do the division first and then the sum aggregation afterwards (as is the definition of a physical calculation mapping). Or if you prefer, you can do the aggregation rule first and then perform the division on the aggregated result afterwards (as is the definition of a logical calculation).

Once this is complete in the RPD, now in my BI Analysis I don’t have to do anything to the measure column. Just create my report:

In your dashboard prompt, instead of Presentation Variable choose Request Variable. This will allow us to override the Session Variable. Just make sure your Request Variable has the exact same name as the Session Variable in the RPD.

After adding the new prompt and analysis to the dashboard, we get the same behavior as before. Only now, we don’t have to write each report to be “display switch enabled” by explicitly listing out the division in each measure. The logic has instead been baked directly into to the yummy, creamy filling inside our tasty measure column. Ok whatever, it’s late and I’m hungry. Hopefully you get the idea.

NOTE:

One thing to be mindful of with this approach is to clearly communicate on the dashboard page or within each analysis via a narrative view or some similar mechanism, the divisor value that is in effect. This is because if you use a divisor enabled measure column on another analysis on another page in the same dashboard, the Request Variable will take effect for that analysis too if the scope for the prompt is set for the entire dashboard.

Then you could have a value that is divided and it might not be obvious to the report user that this was the case. So be mindful of how you implement it – limit the dashboard prompt scope, or place the prompt on each page, or clearly label each page/analysis with a message displaying the value. If only OBIEE would allow me to append a “(000)” to the end of the column heading dynamically in the Presentation Layer… I tried (and failed) with custom display names in the RPD. Let me know down in the comments if you have an idea for this!

Hopefully using this method, the one illustrated in Part 1, or something else similar, you can go easy on the brute force approach and work with something a little more elegant that doesn’t leave such a bad aftertaste later on down the road.

Posted in Business Intelligence (BI), Uncategorized | Tagged , | 2 Comments

Dynamic Value Display Switching in OBIEE: Between Whole Dollars and Thousands – Part 1

When’s the last time you took a brute force approach to solve a problem even though you knew it wasn’t an optimal (never mind elegant) solution? And you did so all in an effort to just get it done and move on, only to live to really regret it later on? Wait, on second thought, don’t answer that. And on third thought, I won’t either…

I worked with a client the other day where I saw that they had literally duplicated all their OBIEE financial reports into a “whole dollars display” version and a “thousands display (000)” version – thereby doubling the number of their reports, dashboard pages (one for each report type), and as a result, doubling their report maintenance when something changed or needed to be updated. Ouch…

Surprisingly, a Google search for OBIEE blogs on how to embed a display switching mechanism like this yielded no related results (that this Googler could find anyway). Maybe a good approach in OBIEE to this problem is obvious to many, but I thought about it a while too before I came up with my preferred option. I’d be really interested to hear feedback in the comments below about how you have tackled a similar challenge before.

Typically, most of our financial reporting requirements are handled via Essbase and Hyperion Financial Reporting where we do this kind of display switch in those tools.

But when I started thinking about it in OBIEE, some different ideas came to mind as to how you could possibly approach it:

  •  There’s always that duplicate each report and dashboard page approach! But I wouldn’t recommend it…
  • Column Selectors? This would only really work if you created two columns in your report (one for whole dollars and one divided by 1000), and even then you couldn’t use a Column Selector to switch for a set of multiple measure columns all at the same time.
  • View Selectors could do that for you. But again that’d mean duplicating the measure columns, and then you’d have to create a view that used the one set of whole dollar measures and another view with the thousands (000) measures.
  • Presentation Variables – probably the main approach most would use. This is the one I’ll start with. In my report I’ll divide each measure column by a presentation var; setting the default to 1. Creating a radio button for “1” or “1000” will allow the divisor to switch and show the display we want. And we won’t need to duplicate columns or views. Hooah.
  • But what if I didn’t want to have to maintain each analysis as a “display divisor enabled” kind of report and have to divide each measure column each time I wanted this behavior? What if instead I wanted to “bake it” directly within the measure’s definition itself? We’ll look at that option after the Presentation Variable approach in a Part 2 follow up post in the following days.

Here’s my simple financial example analysis. Note the four measure columns that we’ll be using. By default, the measures return as the whole dollar amounts, with no decimals displayed.

For all four measure columns in my BI Analysis, I’ll divide each by a Presentation Variable “DISPLAY_PV” with a default value of 1, like so:

Next, I’ll create a Dashboard Prompt using the newer 11g option for “Variable Prompt.” This is because the values I want to display of “1” and “1000” don’t actually come from any Presentation Layer column.

Here I’m creating my Presentation Variable named: DISPLAY_PV. Note I also set the following:

  • A label display
  • User Input = Radio Buttons
  • Radio Buttons Values = Custom Values (1 and 1000)
  • Variable Data Type = Number
  • Default Selection = 1

Notice how my dashboard prompt will display. But I feel that when giving the user a single radio button control it’s a bit over kill to make the user click “Apply.” So I opened the Page properties from the pencil icon:

And disabled the following two options:

Now the prompt value will take effect and submit to the report as soon as the radio button is selected. Put the prompt and analysis on a dashboard page, and test it out. Here’s the default “1” view:

And the 1000 divisor display:

Whew. That’s better than the “let’s duplicate every report and dashboard page approach,” I hope. And trust me, there were LOTS of reports and LOTS of pages…

As mentioned before, in my next post we’ll look at how we could extend this approach to “bake” the display logic directly within the measure itself, instead of having to make each report display divisor enabled each time.

In the comments, I’d really like to hear if you’ve tackled a similar challenge, and if so, how you went about it.

Posted in Business Intelligence (BI), Uncategorized | Tagged , | 1 Comment