Archive for February, 2010

Text Formatting Your Way in Crystal Reports

By
InfoSol
Published: February 24, 2010Posted in: Crystal Reports, Tech TipsTags:
Text Formatting Your Way in Crystal Reports

Question: Have you ever created a formula, to build a string of text, and wanted to format parts of it separately?

Answer: Use HTML codes to format your string.

Explanation

This Tech Tip uses the Customer Table from the Sample Database that comes with Crystal Reports.

The easiest way to format a text object is to right-click on it and select “Format Text…” from the context menu. Unfortunately, this only allows you to format all of the text the same way. You can make everything bold, change the font style and size, and underline it, etcetera. But, what if you want to only bold certain parts?

Well, you could make several text boxes and format them separately, but that is a lot of work and it doesn’t look nice. More advanced users may have noticed that you can double-click into the text object and format parts of text separately. You can even drag in other objects like summaries, database fields and special fields. These can all be formatted separately also. The problem here is that you can only change certain formatting once you drag the object into the text object. It will then be considered text and only simple text formatting options will be available like bold and size. So what happens if you summary rounded to two decimal places and later the report owner wants it changed to a whole number? You would have to create a new summary, drag it onto the report, format it and then replace the old one in the text object with the new one.

Now, let’s say you decided to build your text using a formula. Maybe you want to use functions to do your formatting such as Round() or ToText()? Maybe you just needed to build a complex formula to get the results of your text string. In any case, once you put your formula field on the report and try to format it, you are back to formatting changes that affect all of the text. Or, are you?

By incorporating a few HTML tags into your text string, you can give some parts your special touch. Here is an example of using a formula to print the Data Date in your page footer. Follow the steps and try for yourself:

Step 1:  Create a new formula to display your text. In this example, we will name our formula “dtData Refreshed”. Like the example above, we will bold just the date and time. The formula for doing this is:

Step 2:  Place the new formula field in the Page Footer.

Step 3:   Right-click on the formula field in the report and select “Format Field…”

Step 4:  Using the Paragraph tab, change the “Text interpretation” to HTML Text. As you may have noticed from our formula, you must surround your HTML tags with quotes so that they are a part of the text string. The end result of the formula will be all text and interpreted as HTML.

You’re done!

 

CaveatsNot all HTML tags are created equal. Most of the tags associated with text formatting (ex. bold, italics, font color/size/style) are supported. However, not all HTML tags are supported. As newer versions of Crystal Reports are released, more tags have been included. Please check the Crystal Reports support site for a listing of supported HTML tags for your version of Crystal. SAP Note 1217084 is a good place to start.

If you have ever used ChrW(10) or ChrW(13) to force a new line in your formula, you will need to use “<br>” instead. Yes, line breaks are supported. The sad part is that bulleted and numbered list are not supported in most versions.

So, how exactly did I get the red “Feb” in the date above? Well, that took a little more coding. Here is the code that I used. Of course, I could have simplified things using a variable, but instead, I just used cut-and-paste.

 

Group Training Can Be a Slippery Slope

By
Paul
Published: February 15, 2010Posted in: Paul Blogs on BITags:
Group Training Can Be a Slippery Slope

With the Winter Olympics kicking off this week, I was reminded of my own somewhat painful experiences on the slopes. The first time I ever attempted to ski was at a small ski resort in Northern Arizona.  At the advice of my wife I attended the Beginner Camp with about 15 other people. We had two professional instructors, one of whom told us that he had been teaching people to ski for more than 10 years and during that time had never given up on anyone and so no matter how bad we think we are when we start, he will have us skiing by the end of the day. This instilled some confidence in me because although I had never skied before, I had tried ice skating and the instructor then had pleaded with me to quit before I seriously damaged myself. When I told him it was just bruises and I would not give up, he begged me to stop for the sake of the damage I was inflicting upon the ice!

The first part of the lesson focused on how to start and stop on the skis with the emphasis on stop. We were just practicing on small bunny slopes and after a while, I was really starting to get the hang of staying up and not falling over. However, I was struggling with the stopping part. While the instructor assured me that if I turned my skis inwards, I would slow down and stop, I just seemed to go faster. At one point, I turned them inwards quite dramatically which caused them to cross and send me flying through the air with a spectacular somersault! The rest of the class appeared to be doing better so the instructors took us up to a slightly steeper and longer slope to further advance our skills.  At the top of the slope we were told to ski down and slow to a smooth stop at the bottom. The instructors demonstrated this and then waited for us at the bottom and they had told us that if we were not slowing down enough one of them would hold out their hand for us to grab to bring us to a stop. I was the last to go. As I headed down the slope, I picked up speed and it felt really good. As I approached the rest of the group, I started to turn the skis in but I did not feel like I was slowing down. Panic was written across my face and the instructor could see it. He held out his hand at the same time as yelling commands at me that I could not hear. I reached out and grabbed his hand but I was going so fast that his whole ski glove came off and I continued racing on towards the forest holding his glove! I remember the instructor telling me that if all else failed then just sit down to stop. I did this but it did not prevent me from hitting a tree, losing both skis and face-planting in the snow before finally coming to a stationary position.

The instructor who had lost his glove now also lost his cool and began screaming at me saying that I had not listened to anything he had told me. After a minute of this verbal barrage, it suddenly occurred to him to ask me if I was OK. When I responded yes, he continued his barrage for another minute! When he had finished, I suggested to him that I try again. His bright red face drained to white at the terror of this thought. He quietly said that he did not think this was a good idea and that I should seriously reconsider learning to ski. When I reminded him of his 10 year track record of never giving up on anyone, he hung his head in defeat and said, “Like Napoleon Bonaparte, I have met my Waterloo!”

As I hobbled back to the ski rental, I bumped in to my 11 year-old son coming down a slope and told him what happened. After he picked himself up after falling over in hysterical laughter, he took me to one side and patiently showed me how to turn the blades of my skis to slow down and stop. After a dozen attempts and about 20 minutes, I could do it.

The lesson I learned that day is that training in a group is not always effective and that some people, like me, need individual or personalized instruction. This is as true for Business Intelligence as it is for skiing. While generic courses on universe, report and dashboard development may be adequate for some people, others will learn a lot better by sitting down one-on-one with an experienced consultant and getting personalized knowledge transfer. Similarly, people will often learn better if the course material is adapted to the way they will be using the tools and software and the workshop exercises are using data with which they are familiar.

I have seen an increase in demand for this type of customized knowledge transfer over the last year as more companies discover the benefits of this approach. In addition, some companies are requesting remote support hours after the training that they can use for further help from the instructor or consultant once they get back to their office and start to use the tools in different situations. This is a brilliant idea since it combines both the economy of group training and the effectiveness of one-on-one assistance for those who need more specific help and attention.

Personally, I do not think learning Business Intelligence is as difficult as learning to ski but then it is “different strokes for different folks” or maybe that should be “different slopes for different dopes”!

Comparing Xcelsius Connectivity Choices

By
Roxanne
Published: February 5, 2010Posted in: Business Intelligence, InfoBurst, InfoBurst-XDS, Xcelsius, XDMTags: , , , , , , , , , ,
Comparing Xcelsius Connectivity Choices

As more people deploy Xcelsius dashboard solutions, Xcelsius connectivity options has become a hot topic and I am often asked to compare the most popular methods used by BusinessObjects customers which are Query as a Web Service (QaaWS), Live Office and InfoBurst-XDS (or XDM for non-BOE deployments).

This is a brief summary of the differences between these 3 options which may be helpful.

QaaWS is a BusinessObjects web service that access BusinessObjects universes (and only universes) to pull back data.  Each time a dashboard is opened and selections are made, a real-time query is launched against production data, which, depending on the amount of data requested, can take many long seconds or minutes.  I have not found it to be very effective, and the few companies that do use it, have highly summarized tables in their data warehouse to use for this purpose (i.e. lots of preparation work).  The only upside I see to this method is that security is already in place based on BO user ids, and, real-time data retrieval (if that is the requirement). Large amounts of data (i.e., more than 500 rows of data) returned by the web service will significantly slow down the performance of the dashboard.  

With Live Office you can access Web Intelligence or Crystal reports as your data source and use the functionality of these reporting tools to summarize, crosstab and format your data.  The advantage is that you can schedule these reports to refresh in BO (InfoView) and then use the latest instance of that report for your Xcelsius data source, without waiting for it to refresh.  The disadvantage is that it requires users to log into BusinessObjects every time they access the dashboard.  (Supposedly you can use single-sign-on with this, but I have not seen it working anywhere).  Obviously, if you want to deploy this solution outside of BusinessObjects, like hosting the SWF file on SharePoint or an Intranet, most companies do not like having to sign on again.  Also, it would not work if you want to deploy outside the network (outside a firewall).  There have been many posts on the forums about the “flakiness” of LiveOffice, often losing connections and unable to refresh reports.  Since Live Office has to hold the data in the Xcelsius spreadsheet, the amount of data returned will have a negative impact on dashboard performance.  This last item has been the biggest barrier to our customers, who often wish to show granular data in their dashboards, or a significant amount of historical data.

InfoBurst XDS and XDM were designed to overcome these obstacles.  XDS can use Web Intelligence or Desktop Intelligence reports as data sources for pulling data, which it can refresh overnight (based on a schedule) and convert this data to xml.  XML is the fastest way to load data into Xcelsius.  XDS does not require a BusinessObjects login.  XDS also has the option of accessing a database directly (any database that can be accessed via an ODBC connection) through SQL queries, which can also be scheduled and converted to XML.  XDS also provides options for distributing the dashboard (putting on a network share, an intranet site, or as an email attachment or embedded in an in-line html email).  The biggest advantage, though, is the Intelligent Cache feature which allows dashboard developers to “cache” a very large set of data (I am currently working on a data set of over 18,000 rows of data), and then pass parameters through the xml code, based on selections the user makes on the dashboard, which then returns the small data set needed for that particular chart.  (On the above example I am passing parameters, based on drop-down menus, for Division, Plant, Department, Day and Shift, which returns the 6 rows of data I need to display – shift hours per day per Department.)  Since the dashboard is only loading these 6 rows of data, obviously the performance is very fast.    We recently converted a QaaWS dashboard where the data loading time when changing customers went from 2 minutes or so to less than 10 seconds, using Intelligent Cache filtering.

Lights Off for the Old Technology and Lights On for the New

By
Paul
Published: February 1, 2010Posted in: Business Intelligence, Paul Blogs on BITags:
Lights Off for the Old Technology and Lights On for the New

I like to take late evening or red-eye flights in order not to lose the day travelling, so when I arrived at Detroit Metro airport recently at 1:30 am this was not unusual for me. I picked up a rental car and arrived at my hotel at 2:30 am with a plan to be in bed by 3 am and up at 8 am for my 9 am meeting.
As I crawled into bed at exactly 3 am, everything was on track and the only thing left to do was to turn off the light in the corner of the room and fall asleep. I looked around the bed area for a switch but there was none to be found. The only switches in the room controlled overhead lights but this was a desk lamp hidden behind a huge cabinet with an equally huge flat screen TV on top. I stood on the bed and could see that the lamp was plugged directly into a wall socket. There appeared to be no way for me to access either the lamp or the socket so I picked up the phone and called the reception. The receptionist said that she was not familiar with the room configuration and had no idea how to turn off the lamp. She said that when she could find someone to cover the front desk she would check out another room and get back to me. I told her not to bother.
I got out of bed and approached the cabinet but as soon as I got within about 8 feet of the TV, it turned itself on! I quickly grabbed the remote and turned it off. I approached the TV a second time and it turned on again. On the third attempt, I decided to just let the TV stay on and simultaneously discovered there was no easy way to get to the lamp and that once the TV stayed on, it started to automatically increase the volume on its own! I turned off a very loud TV determining that it was either possessed or someone had set this up as a practical joke.
I went back to bed and tried to sleep but it was impossible. I got up again and devised different approaches towards my illuminated tormentor in the corner. I shuffled “cop style” with my back pressed against the wall, I tried “commando style” crawling on my belly and I even tried “Monty Python style” with a combination of silly walks but as soon as I got near the TV, it turned on and ratcheted up the volume. In desperation, I decided to pounce from the edge of the bed directly in front of the TV. This was probably not the smartest thing to do since I landed in front of the TV, lost my balance and banged my head against the screen hard. I rolled back on the floor holding my head while the TV turned itself on again. As I sat there half dazed fumbling for the remote , I noticed that the collision with the TV had turned the TV about 30 degrees one way on its pedestal. Now that I realized that the TV could swivel (duh!), I was able to approach it by crawling on the ground and carefully slide my hand up to it and turn it 90 degrees without turning it on. I then gently lifted myself up on top of the cabinet and stretched by hand down to grab the power cord and pulled it out of the socket extinguishing the offending light!
I must finally have fallen asleep about 4:15 am but I felt I had only slept for 5 minutes when the early morning call woke me at 8 am. I did not really start to wake up until I walked outside the hotel and the cold Michigan air hit me.
My first meeting was at a local city government. I was not my usual animated self but once I demonstrated a few Xcelsius dashboards, the audience was very animated to the point of wanting to change their whole strategy around budget forecasting right away!
The next stop was a local cable TV station where I was to be interviewed about Business Intelligence. I thought this would not go well in my sleep deprived state but once I whipped out my iPod Touch and showed some Business Intelligence reports rendered through RoamBi, the interviewer was hopping with excitement! The interview turned out great.

Business Intelligence has come a long way in the last few years and marrying it with the latest hand held device technology on the iPhone and iPod Touch, just takes it to a whole new level of possibilities. Harnessing the amazing graphics and touch actions of this device with the ingeniously designed business intelligence dashboard displays of RoamBi creates some of the most effective interactive displays I have ever seen. Again, check it out yourselves – The RoamBi application for the iPhone is free and can be downloaded for the Applications area of the iTunes Store.
Of course, my favorite feature of the iPhone/iPod Touch is that it turns the display off automatically after a period of non-usage – unlike some lights in hotel rooms that I know!