David Taylors Business Intelligence Blog

Quick Tutorial

Service Oriented Architectures are amazing.  Now more than ever companies are leveraging freely available web services to add value to their internal data.  One of the most common ways on combining data today is via Google Maps. A great example of this is housingmaps.com.  Housingmaps combines the power of Google Maps and CraigsList to allow you to view available housing in your area together with the power of Google Map navigation.

Today, all tools from SAP BusinessObjects can consume web services.  The most popular reporting tool in the BusinessObjects platform is Crystal Reports.  With the report creation wizard, we can be consuming a web service is seconds.  Let’s walk through it step by step.  We will connect to a web service that will provide us driving directions from Point A to Point B.

Step by Step

1. Launch Crystal Reports 2008
2. From the Start Page, choose Report wizard, this will launch the Standard Report Creation
3. Next expand Create New Connection, and scroll down to XML and Web Services and expand the folder.
4. The XML and Web Services data source  dialog box will appear.  Choose Use Web Service Data Source and choose Next>.
5. In our case, we are going to use an HTTP Web Service, so choose Use HTTP(S) WSDL and type in:  http://www.ecubicle.net/driving.asmx?WSDL and choose Next>.
6. This Web Service does not require a UserID/Password, so choose Next>.
7. After a moment or two, the Web Service, Port and Method screen should appear.  You should see: driving, drivingSoap and GetDirections in the Service, Port and Method drop downs respectively.  (If this does not work, there may be a firewall issue at your location.  Check with your local IT.)  Choose Finish.
8. After choosing Finish, you will see a ResultSet box under the http://www.ecubicle.net/ data source.  (You may need to expand it).  Move the box over to the Selected Tables side by choosing the [>] button.   When you do, you will be prompted to enter some parameters.  So far so good.
9. There are four input parameters to this:
   fromAddress – the starting address, e.g. 3734 Elvis Presley Boulevard Memphis, TN 38186
   toAddress – the destination address, e.g. 1600 Pennsylvania Ave NW, Washington, DC 20500
   distanceUnit – miles or km, default: miles
   expresswayEnabled – true or false, default:true
10. After entering the values choose OK.
11. Complete the Wizard by either choosing Finished or by walking through the remaining screens.
12. Once the fields are added to the report, you should the directs Elvis would need to take to drive to the White House.

Here is your finished report:

Leveraging Web Services from within SAP BusinessObjects couldn’t be easier.  If you are interested in seeing the same types of capabilities from within other BusinessObjects tools drop me a line and I’ll add it to the list.  Enjoy.

«Good BI»

Bringing Together HTML Controls and Crystal Reports

Every once in a while I get a chance to present in a forum or user group and show some new amazing capabilities in the product suite. Last year I had the chance to demonstrate WebElements. WebElements is a collection of Crystal functions which generate HTML.

An easy to use library of functions for Crystal Reports designers who are also using the BusinessObjects platform:

• Designed by a report designer for report designers
• Embed different kinds of controls for report consumers
• Easily distributed as an .rpt file to other report designers
• Small in size, less than 60k unzipped
• Automated installation
• Expandable and customizable and code can be shared

Since Microsoft Reporting Services provides easy to use drop-down parameters, more and more customers are asking for the ability to embed web controls into their Crystal Reports. Now with Webelements we can add radio buttons, list boxes, drop-down selectors, action buttons, etc. In fact, Jamie Wiseman, the original creator of WebElements has a lot of great samples you can download from the Business Objects Diamond Developer website.  You can also search and find information about WebElements off the SDN website here.

Watch a 60 minute webinar on webelements now!

Get started now by downloading the WebElements Custom Function Library here.

Next time I’ll discuss mashups and the new viewer JavaScript SDK in BusinessObjects XI 3.0.

So you may be asking…

What is Crystal Reports Embedded? Crystal Reports embedded is an architecture which allows the Crystal Reports Rendering engine to run within the confides of the application environment.  BusinessObjects provides a .NET runtime and a Java Report Component (JRC) which allows developers to embed Crystal Reports in their applications.  Crystal Reports Embedded is included free with every version of Crystal Reports we sell.

So why do I need Crystal Reports Server or BusinessObjects Enterprise?  Well, that leads me into a discussion of the limitations that existing within the “free” embedded report architecture.

Limitations of Embedded Reporting

The current Crystal Reports Embedded Runtime is limited to three concurrent user requests (e.g. three simultaneous processing threads).  These threads are queued such that if the .NET application requests a 4th thread, it must wait until one of the existing three completes before it will be processed by the embedded engine.

Here is a quick view of the Embedded Architecture:

In case you were not aware of this limitation, let me point you do an excerpt I found on the Crystal Reports Developer website:

• Report engine is embedded.

  In Crystal Reports for Visual Studio, the report engine is embedded in the application.

  This embedded report engine is limited to a maximum of three simultaneous user requests. This is not an arbitrary licensing limit; it is a limitation of the embedded report engine architecture.
• Report engine is extracted into a separate report server process

  In the solution that uses the unmanaged Report Application Server (RAS), the report engine is extracted into a separate server process. That server process can run on the same machine or be placed on separate physical hardware, to increase performance by offloading processing from your application server. An unmanaged RAS server is no longer available as an upgrade option. It is recommended that you upgrade to Crystal Reports Server instead. For more information, see Upgrade Options.

  The Crystal Reports Advanced Developer edition provided a performance-governed version of the unmanaged Report Application Server (RAS), which is suitable for smaller deployments with less complex reports and fewer user interaction requirements. This product is no longer available.

  Or, it was possible to license the unmana ged Report Application Server (RAS) on a processor basis, where it takes full advantage of your hardware. This product is no longer available.
• The separate report server process is encapsulated into a complete Enterprise architecture

  In the Crystal Reports Server or BusinessObjects Enterprise solution, an entire Enterprise architecture is added, which encapsulates the separate report server process (RAS). The managed Report Application Server (RAS) becomes only one server, among many other servers in the Enterprise architecture. This architecture provides a rich set of additional features, such as scheduling, load balancing, fail-over, and both vertical and horizontal scalability.

NOTE:  The red was added by me for emphasis.  For more information click here.

So there it is.  There is a limit of three simultaneous processing threads when you use Crystal Reports Embedded within a .NET or Java based application.

What if I need more scalability?

If you look over the product offerings available from SAP BusinessObjects, you will see that all the current offerings include the BusinessObjects platform. This means that you will need to purchase one of our server based product offerings in order to provide additional scalability.

There are a number of advantages to this platform.  The first is unlimited scalability.  One common problem we had in moving customers from an unmanaged environment to a managed environment was that certain parts of their application had to be rewritten.  With the platform, you need to “login”, retrieve your report from a “repository” (instead of a filesystem).  You also wanted to make sure you are using the Page Server instead of the RAS Server (which had more overhead).

Now by requiring customers to move to a repository/platform based platform earlier, they are able to reap the benefits of a dedicated reporting environment

Here is what a basic architecture might look like if you upgrade to CR Server:

And here is one with BusinessObjects Enterprise:

How does this affect Runtime Distribution?

This does NOT affect your ability to create Crystal Reports and embedded them into your Visual Studio or Java based applications and distribute them out.  It is important however that you only distribute out the DLLs and JAR files listed in the RUNTIME.TXT file under the …/Crystal Reports/Help/en directory.

For more information you can also check out my previous post about this topic here.

Enjoy!

Crystal Reports you say? What version? It’s embedded in your application? How is it embedded? What does it do? Do you know what Developer Libraries they used? SDK? What? Who? When?

To be honest, the reason I joined Crystal Decisions all those many years ago was because of Crystal Reports. I’d used Crystal Reports may times in the past and I’d always been amazed at how easy it was to use. I was an old ISAM guy, so the fact that this tool could write SQL was pretty cool. (of course now I know that it’s not as good at writing SQL as other tools, but ignorance is bliss.) When I found an opportunity on HotJobs for a Presales Consultant I jumped at the chance. I’d always worked at small companies with < 150 employees and now – wow – I could get a chance to help sell a product that just about everyone in IT has heard of.

Why has everyone heard of it? Because it’s everywhere. The one good thing about Crystal Reports is that Greg Kerfoot knew the power of market share, so he bundled packaged, included, gave away, OEM’ed, shipped, advertised Crystal Reports in every way, shape and form. It really became a household name. A true reporting standard. Long before anyone ever thought of the term Business Intelligence, Crystal was blazing a trail, which brings me to my question at the start of this blog.

Can I Upgrade My Crystal Reports?

The great thing about Crystal is that it’s everywhere. My first few months at Crystal Decisions I learned that customers had developed all kinds of crazy was to use Crystal Reports and I needed to understand how they might use it so I could help them… especially as the web was now in full swing and people were wanting to web-enable their old Visual Basic applications.

Well, here is a summary of what I’ve learned over the years regarding Crystal Reports. There were a lot of different SDKs (Software Developer Kits) that were introduced over the years. As technologies changed, so did the libraries available to developers for creating applications. Here is a timeline of these changes.

Click to Enlarge

The Bad News

The RDC, the most popular of the SDKs available for Crystal Reports has been depreciated and will not ship with XI 3.0. Although we introduced a new web-client centric SDK in 2002 with the advent of Crystal Reports 9 and Visual Studio .NET many customers have been slow to modify their legacy applications. When moving for RDC to one of the new APIs, you must rewrite your application.

Just like all other code migration from Visual Basic 6 to the .NET Framework, recoding is necessary when migrating from the Report Designer Component in Visual Basic 6 to Crystal Reports .NET. This is because of the new .NET APIs, which vary from the COM APIs.

One of my favorite stories was from back in the Crystal Reports 8 days. I remember visiting a customer who was complaining the Crystal was crashing. I went onsite and in those days if a machine has an IIS server on it, you could access Crystal Reports through the ActiveX viewer by referencing the report in a shared directory. This customer was running 120 concurrent users on a single CPU machine!! They had purchased a $500 piece of software and were serving up reports to thousands of end-users. No wonder the machine was falling over. That poor DLL was getting hammered. (Fortunately in Crystal 8.5 we limited the number of concurrent threads to 5 and no one complained that “Crystal was crashing”. Now it was time for us to collect… because before the 5 concurrent users had been a paper license).

Here is an example posting on Diamond for someone who can’t find all his old RDC features in the new RAS SDK.

The Better News

Crystal Reports continues to provide a RAS SDK as part of the Crystal Reports Developer Addition. This RAS SDK can be used to both embedded as well as managed report environments. An embedded environment is when you manage all the Crystal Reports content yourself through your application. This may be convenient but means there is a lot more work for the developer to do around managing content, scalability, etc.

Note: Consider carefully whether you will use embedded or managed reporting for your development product because the SDKs calls will vary slightly depending on which you use. In any case, make sure and encapsulate your logic around user authentication and opening reports. If you do need to change from embedded reporting to managed reporting, HERE is an excellent whitepaper that walks you through the process.

Originally we followed Microsofts lead my offering a web farm/web garden license, but that just got confusing. Now we make it a lot easier.

If you use Crystal Reports in an unmanaged mode, you will have a limited number of threads you can use. In some cases we offer packages with increased threads (For example we used to offer Crystal Enterprise Embedded which provided more capacity. This is no longer available outside of our OEM channel.) In all other cases, you will need to move to a managed reporting model. That means you will need to move the processing of the Crystal Reports off your application and onto a secondary service which will handle the report processing for you.

The introductory version of this server is called Crystal Reports Server.

Bringing Together HTML Controls and Crystal Reports

Every once in a while I get a chance to present in a forum or user group and show some new amazing capabilities in the product suite. Last year I had the chance to demonstrate WebElements. WebElements is a collection of Crystal functions which generate HTML.

An easy to use library of functions for Crystal Reports designers:

• Designed by a report designer for report designers
• Embed different kinds of controls for report consumers
• Easily distributed as an .rpt file to other report designers
• Small in size, less than 60k unzipped
• Automated installation
• Expandable and customizable and code can be shared

Since Microsoft Reporting Services provides easy to use drop-down parameters, more and more customers are asking for the ability to embed web controls into their Crystal Reports. Now with Webelements we can add radio buttons, list boxes, drop-down selectors, action buttons, etc. In fact, Jamie Wiseman, the original creator of WebElements has a lot of great samples you can download from the Business Objects Diamond Developer website.

WebElements Online Presentation

Click on the picture below to access my WebElements presentation:

I’d love to hear your feedback on WebElements.

Row-level Security on Scheduled Instances Without the Use of Business Views

When I realized that I could get row-level security on scheduled instances without using Business Views I thought, I have to share this with everyone. When you schedule a report, the engine retrieves all the data based on the credentials of the user running the report. If I now want to get row-level security, I must apply security within the report at “format time” or view time. In other words, I must apply the security to the formatting of the report.

The great thing about this is that it not only applies to viewing reports, but also to accessing those view reports through other tools such as Live Office.
Situation: Allow an administrative user to run a report for all users and then apply row-level security at view time but do not leverage Business Views.

1. Make sure you have some type of method for determining row level security. In my case I created a Customer_Security table and added some restrictions so that certain users were allowed to see the data for certain countries. In my case I’m using SQL Server together with some Xtreme Customer data. Here you can see some sample data and the contents of the Customer_Security table:

2. Next, I need to create a Crystal Report which links the customer table to the customer security table and delivers back the expected results. When I created the report I used an outer join since no ever country in the customer table has an associated security setting for it.

3. Next I want to create a suppression formula on the row that contains data that restricted users can’t see. In this case I used the following suppression formula:

NOT (CurrentCEUserName = ‘ADMINISTRATOR’ OR
CurrentCEUserName = Uppercase({Customer_Security.Username}))

This TRUE if the user viewing the report is NOT the administrator or NOT a matching security value. This formula will cause the country records they do not have access to see to be hidden from view.

WARNING: In this example, I am not doing any special calculations, but if you do need to do summaries or groupings, I recommend that you set each value to a formula and within the formula you set values equal to blank (”) or 0 if the row should not be seen by the end user.

4. Now I can publish this report to BusinessObjects Enterprise and schedule the report to run as administrator and then view the report as ron. When I do this, I will expect to see all the records when logged in as administrator and only England, France and Germany records when logged in as ron.

Let’s schedule the report:

Crystal Reports applies formatting a view time, therefore it will evaluate the suppression formula for the individual running the report and apply it to the report instance. In this case the countries that ron is not allowed to see will be suppressed, even though they were retrieve with the original schedule instance.

Here is a view of what the administrator sees:

Here is what the restricted user ron sees:

We did not have to reschedule the report for ron, yet we were still able to achieve row level security from a scheduled instance! Do you know what happens now when I view this report from within Live Office? The suppression formulas are still applied at run-time and therefore the view security remains!

Here is what I will see from within Excel.

You can see here that I am accessing the Latest Instance and yet, even though the latest instance contains ALL RECORDS for the report, Live Office observes that suppression and resticts what the restricted user ron can see.

CONCLUSION: You can obtain row-level security from within Business Objects by using CEUserName and an external security table together with suppression formulas that are evaluated at run-time.