Business View Vs Universe for Crystal Reports Design in XIR2

building semantic layer
1

Hi Guru’s-

we have BO XIR2 DeskI reports run off of Universe. Sofar no issues. However, Management decided to dump DeskI reports and convert them to Crystal Reports. We run report on transaction database and universe has more than 100 tables with about 3000+ objects created.

So question is what is the best way to handle report development in Crystal in terms of database connectivity. Available options are

  1. Using existing universe to create Crystal reports
  2. Creating Business Views.
  3. Using direct database connects or ODBC connection

All our reports are medium to higher level complexity which has prompts, some reports have cascading prompts…I read that crystal reports only supports @Variable out of all @functions. I have several @Promt(,) in my objects that I used mainly to create Cascading prompts in the Universe. Also, I learned that any changes in the universe do not automatically update the Crystal report. Any thoughts or more information about issues of using Universe connection for Crystal reports?

I am thinking of Business Views option because it is compatible with Crystal reports than Universe. Given the fact that we are designing reports to run against transaction database, I am under impression that Business Views eliminate creating joins each time a new report is being developed, as in the case of direct database connection or ODBC.

I would appreciate expert’s thoughts on implementing business views for migrating from DeskI to crystal reports.

Thanks
KK

BusIntelGuy (BOB member since 2010-11-13)

2

Here’s my two cents.

This would be the least amount of work for you in terms of not having to recreate a data source. We are a large Crystal Reports shop and most of our reports are written over universes. The only exception is reports that are written against a stored procedure. (Now that we have upgraded to XI3.1, we plan to convert these to stored procedure universes.)

This may be an advantage also with your prompts that you use for Cascading prompts as they should work the same. If you decide to use Web Intelligence, you can use the same universes for them as well. I’m not sure where you got your information about @Variable and @Prompt but it could be that Crystal Reports itself doesn’t use this function, but you can design it in the universe and use @Prompt there. Then you just add the condition to your report. The nice thing here is you create you prompt once in the universe and then you can use it in many reports.

You are correct that universe changes do not automatically update in Crystal Reports. Depending on what your change is though, it may not make a difference. From my experience, as long as you don’t change the name or the data type, the report will pick up any SQL changes because the SQL is generated at the time the report is run. The object SQL is not stored with the report.

Your reasoning here for using Business Views applies also to using a universe. The joins are set up in the universe not the report so you get this same benefit from using a universe. We don’t use Business Views here. I’ve worked with them a little and the only advantage that I have found is Business Views handles cascading prompts differently that a universe does. In my opinion, I like the cascading prompts from Business Views better but you can’t use them with a universe based report. Since you are already using the cascading prompts from the universe, you probably would want to stay with those to keep the behavior consistent for your users and report developers.

One other thing I would watch out for, Business Views isn’t mentioned much in the plans for Business Objects. I don’t think there have been any updates to it in a long time. I haven’t heard anything official but they may be phasing out Business Views in favor of universes. If you go the Business View route, you may be headed down a dead end.

I’ve already covered the issue with joins and direct database connections but there are more issues with this option.

When you use a report with a direct connection, you have to store log in credentials with the report in Business Objects. Any time you save the report back to the repository or migrate the report, you have to reset the login credentials. This can be a lot of work. If you use a universe, the login is handled at the universe so it’s a lot less maintenance.

My recommendation for you is to stick with your universes.

There, you have my two cents. :slight_smile:

3

Hi John, Thanks for your detailed reply.

Utilizing existing universe is our first option. Our universe is already very complex which run off of transaction database. Did you have any universes and Crystal off of transaction database? Would like to know any specific issues you ran into?

I have attached the pdf that SAP speaks about known issues with Crystal and Universe combination. Authors speak about issues with nested prompts and @functions in the pages 5, 5, and 7. Speaking of nested prompts, we have limitations of using cascading prompts in XI R2, at least I know of, we don’t have any option to enforce all nested prompts into report filters.
For example, If I have Region>Country>State>City, I would create City, State, Country, Region objects with @Prompt in the WHERE clause of each object (For some reason I don’t like modifying LOV SQL work flow), and finally create a Universe prompt referencing City object and drag this into the report. From InfoView, I would see the prompts as City>State>Country>Region. I answer the questions bottom up. Here comes the issue, I select Region=North America, Country=USA, State=NY and City=Rochester
My report looks like
State City
NY Rochester
MN Rochester
Since my data model breaks the 1:M relation from higher to lower level hierarchy between State and City. So I couldn’t find any work around other than asking the users to enter prompts in two layers as City>State>
and another prompt State>Country>Region to enforce the all selection. My gut feeling is that Crystal can enforce all Cascaded answers into report filters, I haven’t tested it yet. If Crystal XI R2 have this ability, I wouldn’t worry about @functions not working with Crystal no more.

And we know that there is no direct way (Work around again by asking users to do a favor of entering ALL or *) to make the prompt optional in DeskI as well as Webi in XI R2, seems like have the ability in Webi in 3.1, still no with Deski. I don’t understand why SAP implement a capability in one and ignore the other when they upgrade their products.

Can you please elaborate your experience in this. Is this mean if we use universe for SQL generation and can not have Dynamic cascading prompts from BV?

Appreciate others experiences as well with combination of Universe as data source and BVs for Dynamic cascading prompts for Crystal?
Crystal Reports_Universe.pdf (184.0 KB)

BusIntelGuy (BOB member since 2010-11-13)

4

We do have one universe over a transactional database. The main issue with this is dealing with all of the different joins. If your universe is already written over a transactional database, you have probably already dealt with these issues. In my opinion, this is another reason for you to stick with your universe. You’ve already done all of the hard work to deal with the join possibilities. You would just have to redo all of that again to switch to Business Views.

As an aside to the current discussion, it is against forum rules to post copyrighted material. Use a link instead. See rules: https://bobj-board.org/tos#heading–copyright

I am not sure what you mean by @functions. The one mentioned in the attached document that has restrictions in Crystal Reports is @Variable. The @Variable is very specific for what it is used for. Personally, I have never used this. We use the @Prompt function in all of our universes and have not encountered any issues.

I think the way the prompts are written may impact this. In my experience when I was comparing the use of cascading prompts between a universe and Business Views I was creating dynamic cascading prompts.

My observation was this, using your example of Region>Country>State>City.

In the universe, you set up a prompt for City, as part of the where clause or the list of values for this prompt, you set up a prompt for State, you do the same thing for Country and Region. The end result is that when you use the prompt for City, when you click on the list of values to get a list of cities, you first have to navigate up through the other prompts to get to Region, and then back down to get the list of cities that you want. To me this is backwards and takes more effort that necessary. In Business View manager, you could create a dynamic cascading prompt where it started with Region and worked its way down.

Did you try creating a table alias for your city table, link it to the state with a 1 to many relationship and use that as the source for your prompt? This would be a workaround to solve that issue and only have one layer of prompts.

Yes, this is true in XI R2. There is some extended syntax for the @prompt function that will allow you to specify a default value. This syntax is unpublished as far as I know. There are topics about this on BOB. You can always set up your prompts to default to All. With Business Objects XI3.1 and Crystal Reports 2008, option prompts are available.

I could not get them to work together in XI R2. There could be something that I don’t know about it though. We ended up writing the reports that were using them directly against the database and using the dynamic cascading prompts. After all of that work, they decided they didn’t want to use the cascading prompts. Oh well.

5

Hi John-

Sorry about the attachment. Lesson learned.
We had one Crystal XI client license and installed it on my machine for testing Universe-Crystal combination. However, first thing I clicked on to create new report(Ctrl+N) on the top left hand side, launches the standard report creation wizard. Under create new connection foder, I couln’d find Universes as an option, neither Oracle server.
Our old license is for Crystal XI and BOXI server is XIR2. Does it make any difference? Is it because I installed it on my local desk against server?

Any clules?
I have installed 2008 trial version, in this I could see the Universes as an option to connect.

Anyways I will try your suggestion on breaking 1:M relation between cascaded values and let you know.

Thanks

BusIntelGuy (BOB member since 2010-11-13)

6

Hi,

I have few things to share here… I have worked in Crystal reports XI and Universe in BO XI R2 combination.

We have faced lot of issues (like cascading prompts, linking reports etc). Since, we have committed it to customer, we have managed to complete it.

But, using Universe + crystal reports combination is bit tougher than Businss views + crystal reports combination. So, i feel like it will be easier to creat Crystal reports using Business View Manager.

thanks,
Aarthi

aarthi (BOB member since 2008-05-12)

7

I can’t remember. It’s been several years since I worked with XI. It may be because your versions don’t match but I would expect you to at least have the option to use a universe, you must might not be able to log into the XIR2 system.

This shouldn’t be a problem. This should be a pretty basic installation because you almost always have more report developers than you have universe developers.

8

Hi

Crystal Client tools is Just XI (11.0) and BOXI server is XIR2. Both were purchased under two separate licensing agreements. At this pointI am just trying to test to demonstrate the functionality.

Any other ideas why I didn’t see either repository or Universes as an option to connect? This is the basic step to log in to existing CMS server. I tried uninstall and re-install to see if I overlooked something first time.

Any other ideas much appreciated!

Thanks

BusIntelGuy (BOB member since 2010-11-13)