Top Ten Tips for Designing A Crystal Report

1.    Planning

Ensure you have fully understood the report requirement and identified the correct data source, data source connection, table joins (if you need to use tables in the report), fields, formulas and formatting the report requires.

2.    Use the most appropriate Data Source

If the report can be designed using a stored procedure as the data source then this will be the most efficient method. If there is no stored procedure, then using a database view or SQL Command will ensure the report runs as efficiently as possible.

3.    Database Optimisation

If the report requires tables from a database, ensure that the fields you will use for linking are indexed. Typically if you link tables on un-indexed fields and then run the report, you may see the ‘Accessing Database’ message at the bottom left of the screen. This means that the SQL query is processing on the database server before returning records to the client computer.

If you have no choice but to link on an un-indexed field, then there are two approaches you can take. Firstly, contact the database administrator to ask if the un-indexed field can be indexed. Secondly, if one of the fields is indexed, then Linking TO the table with the indexed field is more efficient.

4.    Record Select Expert

Try not to use Crystal formulas in the Record Select Expert if possible. Crystal formulas are processed on the client computer. For example, your report may return one million rows of data, if your record selection relies entirely on Crystal formulas, then your record selection formula will evaluate one million times. Try to use database fields and parameter fields in your record selection process.

5.    Use of Crystal Formulas

Formulas are a necessary part of many Crystal Reports. However, if a formula can be reproduced in SQL, the best practice is to use an SQL Expression. This will achieve exactly the same result as the formula, but will be processed on the database server, not the client computer.

6.    Sub-Reports

Occasionally you may need to use a sub-report or multiple sub-reports in your Crystal Report. Wherever possible, try not to place these objects inside the Detail section of your main report. Each time a row is read in the main report, the entire sub-report is processed.

7.    Report Performance

If the ‘Accessing Database’ message appears very briefly in the bottom left hand corner of the screen, but the report still takes a considerable time to run then check the Database|Show SQL Query menu option and pay particular attention to the WHERE clause. This should closely, if not exactly, match the record selection set up in the report.

8.    Return Summary Information Only

If your report has a requirement where only summary information needs to be viewed then you can utilise the ‘Perform Grouping on Server’ feature. This will return one row per group and leave the detail information on the database server. This means the report will run much faster as fewer rows are sent to the client computer.

You will need to ensure that you have at least one group with a sub-total. That you have hidden the Detail section and that you have turned on the ‘Perform Grouping on Server’ feature from the Database menu.

This feature will not work if you are using a Distinct Count or Average summary type, or if you have any formulas visible on the report.

9.    Ensure the figures are correct before releasing the report

This is common sense really, but if you have a known set of trusted data to work with and compare the results of your report against, then this will ensure the information will be trusted.

10.    Learn SQL

If you have been designing reports for a while, but never ventured into SQL, then it is worth learning the basics as this will improve your report design skills immensely.


Written by:  Steve Chapman, Senior BI Consultant, DSCallards

For more information, visit www.crystaltraining.co.uk. 

SAP Dashboard Design 2013 - Loading Canvas Issue

Our consultants have come across a number of customers experiencing issues with SAP Dashboard Design 2013/4.1 whereby they were unable to open or create a new dashboard.

Our team have therefore put together a quick document, with the issue and the resolution to solve it.

Click here to read the full document.

Diffie-Hellman SSL Business Intelligence Launchpad Issue

If you are having trouble connecting to your Business Intelligence Launchpad through Firefox or Google Chrome and are hitting the Diffie-Hellman weak ephemeral key message you will know how confusing and frustrating it is. The obvious work around is to use Internet Explorer as that particular browser does not currently check for the weak cipher keys that make the Logjam attack possible.

Some of you will be stuck using a particular browser due to IT policies so this could be for you.

To allow Firefox and Chrome to connect once again a few changes need to be made to the Tomcat/conf/server.xml file which will allow you to specify which ciphers to use when browsers are trying to connect.

In the connector element for SSL (normally port 443) add in the following ciphers to use:

ciphers="TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_AES_128_CBC_SHA256,TLS_RSA_WITH_AES_128_CBC_SHA"

I added it just before the password section but I believe you can put it in anywhere in the corresponding connector section of the server.xml file.

Assuming everything is in the right place you can go ahead and restart Tomcat. When its back up, you will be able to log in using Firefox and Google Chrome.

There are other methods which involve downloading the Java Unlimited Strength files but these need a little bit of extra configuration and we personally could not get them working. If you can get them working they will allow you to use 256 encryption and not restrict you to RSA. 

Written by Luke Johnson, BI Support Technician, DSCallards

Microsoft Visual Studio (VS) – Good Website Tool or Not?

I’ve been with DSCallards since 2007.  As the Marketing Manager one of my key roles is to ensure that our website presence reflects our culture, technical brilliance as well as the services and products that we offer.

As we are a technical organisation, the Development Team have had a say in the types of tools we use to develop our websites.  In the early days we used DreamWeaver, then Open Source Joomla seemed like a good idea, then we moved onto pure HTML because of the potential security threats, then back to DreamWeaver.

Having gone full circle, and with the task of creating a brand new website, the Development Team suggested we used MS Visual Studio and a series of Bootstraps.  At first Marketing was a little hesitant having been sent around the houses over the course of nearly a decade.

According to Wikipedia, Microsoft Visual Studio:

“Microsoft Visual Studio is an integrated development environment (IDE) from Microsoft. It is used to develop computer programs for Microsoft Windows, as well as web sites, web applications and web services. Visual Studio uses Microsoft software development platforms such as Windows API, Windows Forms, Windows Presentation Foundation, Windows Store and Microsoft Silverlight. It can produce both native code and managed code.

Visual Studio supports different programming languages and allows the code editor and debugger to support (to varying degrees) nearly any programming language, provided a language-specific service exists. Built-in languages include C,^[5] C++ and C++/CLI (via Visual C++), VB.NET (via Visual Basic .NET), C# (via Visual C#), and F# (as of Visual Studio 2010^[6]). Support for other languages such as M, Python, and Ruby among others is available via language services installed separately. It also supports XML/XSLT, HTML/XHTML, JavaScript and CSS. Java (and J#) were supported in the past.”

This was all well and good but what about the design element? 

The Development Team had an answer for this.  By using a Bootstrap design template, we should be able to design and develop a website to be proud of.

Marketing put together a Website Brief and sent it to a number of web design agencies. The responses we received weren’t particularly impressive especially given the many thousands of pounds it was going to cost us. 

We finally decided to run with the in-house MS Visual Studio idea to develop a website that promoted the SAP Business Objects element of our business:  The Business Objects People.

Here’s the outcome:

http://www.thebusinessobjectspeople.com/

We were very happy with what we had developed in-house utilising both our technical Microsoft expertise and knowledge of our business and what we were trying to say.

In fact, we were so pleased, we went on to redevelop a new site to promote our Development services:

http://www.developer-solutions.co.uk

I am now in the process of working with our Development Team to redevelop our corporate website – www.dscallards.com – from a pure HTML website to MS Visual Studio.

We have kept to the same bootstrap and look and feel for an integrated branding approach across our offerings.  Hits to the websites are good and it is easy to incorporate the SEO requirements from Google to keep our sites at the top of the organic listings. Also, via the Bootstrap template technologies available, the sites are mobile friendly – another Google SEO requirement.

Having tried many different website designs, tools and technologies and having looked at outsourcing to third parties, I believe that so far the MS Visual Studio approach has been the most successful for us as a company. 

The reasons are:

• Design tools are intuitive
• Relatively inexpensive
• Easy to update
• Excellent design facility utilising bootstrap technology
• SEO friendly

So, moving forward, I’m looking forward to launching our new DSCallards site over the next month or two.  I’ll then embark upon redeveloping the rest of our portfolio of websites.  So, you could say that after a bit of a reluctant start I’m now a fully-fledged MS Visual Studio convert!

Written by Adriane Gillies, Marketing Manager, DSCallards

DSCallards – Breath of Fresh Air

I am bias.  I have been working for DSCallards since 2007.  But, I’m no spring chicken and have worked for a number of organisations since I started in the world of work back in 1985.  So what makes me stay at DSCallards?

The answer is simple: We are small yet perfectly formed.

We have been around for a long time – since 2001 - and have evolved with the changing external environment.  This is in stark contrast to the larger organisations that I have worked for who struggled meeting changing market requirements due to lack of flexibility and ability to change quickly enough because of all of the corporate bureaucracy, ‘jobs-worths’ and red-tape. 

We are established partners with SAP, who are one of the world’s most successful business software application vendors.  We also develop software applications for some of the most prestigious companies in the UK and we work with other specialist software vendors such as LEADTOOLS, HiT, Yellowfin and Pervasive.

Our customer-base includes organisations such as West Yorkshire Police, Harrods, Princess Yachts, numerous County Councils, Kelloggs, Babcock, Volvo, NHS, the list goes on and on.  And, once a customer engages in our services they invariably come back year after year.

As individuals, we are very much empowered by the Board of Directors to run with our own ideas. I am given a lot of freedom and responsibility to position our products according to our company’s direction and adapt campaigns to bring in the interest required to propel ourselves into our chosen direction.

We are self-sufficient.  As a Marketing Manager, if I need help with building a website in MS Visual Studio or require blog content from our talented group of Business Intelligence consultants for our social media seed marketing and thought-leadership campaigns, all I have to do is ask, and they deliver.

Marketing’s relationship with the Sales team is second-to-none.  More often than not, Marketing are seen to sit in their ivory towers, churning out campaigns that do not reflect the goods and services on offer by the Sales Team.  Here we work hard to ensure that we have a unified message and work together to deliver on this. 

Because we aren’t a huge corporate, everyone’s role is important and everyone plays a part in the company’s success.  Working for larger organisations I found that you are set an objective and given resources to deliver, but when it came to delivery and justifying ROI, so many people got away with weak excuses and poor delivery hiding behind bureaucracy and procedure.  At DSCallards, we all have to deliver true ROI on our work.  Transparency is the key and no-one can hide behind their clip-boards, clever words and empty promises.

DSCallards is not the place for you if you want to step over someone’s head to get to the top or hide behind corporate red-tape when you fail to deliver on a project.  Yes, we have comprehensive quality systems in place, but they are relevant to our culture and customer-base.  We are professional and disciplined without being strangled by unnecessary procedures and red-tape.

Finally, we really know our customers.  We have customers that have stayed with us for years and years.  It’s great to see what started off a small reporting project, evolve into a cross-company Business Intelligence roll-out.  We have been running seminars, in association with SAP, for over eight years.  We have customers that come along regularly to keep up-to-date with trends and technologies.

So, given the choice of working for a colossal defence manufacturer like British Aerospace or a small but perfectly formed Devon-based Business Intelligence and software specialist, based on the fringes of beautiful Dartmoor – where the air is pure and fresh.  I know what I’d choose – any day!

Written by Adriane Gillies, Marketing Manager, DSCallards Ltd

Blogging: How to Extract Knowledge from The Company’s Brightest Minds

Social Media is widely accepted as one of the most powerful tools available today to take messages to the market.  To be considered a thought-leader in our chosen markets, it requires continuous ‘drip-feeding’ of messages that illustrate our brilliance and know-how in our specialist areas.

Knowledge is gold dust.  Also, we all know that we need to speculate to accumulate. But day-to-day our consultants and developers are far too busy with their job in hand – writing code, installing software, writing databases, running training courses – to take time out to write a blog for the Marketing Team.  So it’s easier for them to ignore the relentless nagging and requests for blog and website content and get on with keeping the customer happy - for today.

This is where Marketing need the backup from the heads of the company.  The top brass need to realise the importance of good content as well as the time it takes the busy team members to create this content.  Social Media is not free.  It is expensive as it takes valuable time for the team to create compelling content to strengthen our position in our markets.

DSCallards decided to embrace this challenge.  As a pilot, the Marketing Manager took the Technical Director and BI Practice Manager off-site to a beautiful old venue in the middle of Dartmoor and locked them into a room with their laptops.  During the course of the morning they were asked to create three blogs each which represent the areas of the business that they are responsible for.  Here is the outcome:

Development

Still Not Happy using IT to Run Your Business?

What If Your Software Developer Falls Under a Bus?

DeveloperSolutions Embrace the Demise of Desktop Applications

Business Intelligence

SAP BusinessObjects Resources on Tap

Data Data All Around But Not a Drop to Drink!

The New Universe Tool

The output was excellent and the material has since been used to populate eDMs and websites and helps both with the thought leadership requirement as well as SEO as we know that Google love it when websites are updated with original and topical content.

As a result of this success, the Management Team decided to continue along the same vein and the Marketing Manager was given the thumbs up to take the whole Business Intelligence and Development Teams off site on a Monday morning with the simple objective to write blogs.

So, I’m sat here now.  Surrounded by my colleagues.  Laptops open, coffee cups topped up and minds ticking over and keyboards tapping.  They’re hopefully creating some masterpieces which extol the virtues of why a company like yours should come to DSCallards to help with their Business Intelligence or Development projects.

I’ll be posting the output shortly.  So, watch this space …!

Written by Adriane Gillies, Marketing Manager, DSCallards

LJ Pi SQL Server and SAP Crystal Reports

I’m relatively new to SAP Crystal Reports and I thought a good way to get a grasp of how the database connectivity works would be to create my own database and then connect Crystal to it.

Since we were doing some work with Raspberry Pi’s in the office I seized the opportunity and grabbed one. A bit of research later and I discovered that MySQL server can be run on the pi.

I had flashed the Raspberry Pi’s SD card with Raspbian Wheezy. When booted up open up a terminal window and the first thing to do is update the software list that’s available for Raspbian:

sudo apt-get update

The next step involves updating your current software to make sure you have the most up to date versions on there:

sudo apt-get upgrade

Now it’s time to install MySQL server:

sudo apt-get install mysql-server

You will be prompted to confirm the install and after it’s installed you will again be prompted to set a ‘root’ user password.

Another package that is worth installing is php5-mysql:

sudo apt-get install php5-mysql

This will add the MySQL libraries that will allow PHP access.

At this point you are ready to start up your MySQL server by typing:

mysql -p -u root

the –p item is how you request to put in a password and the –u is specifying which user you are wanting to log in as. At this point you will need to remember the password you put in earlier when installing MySQL server.

At this point you should be logged in and ready to create a new database, you can do this by using the following command:

CREATE DATABASE MY_DATABASE_NAME;

For the purpose of testing I imported a SQL database from a dump .sql file. I was having a few problems importing it so I decided to go the long way round and copy the SQL into the terminal window line by line. Luckily you can do this in bulk and it ticks away loading all the rows in. To do this you will need to specify the database you would like to insert the SQL into:

mysql use my_database_name

You can then open the .sql file in your preferred text editor and copy the whole thing. You can then right click into the terminal window and this will paste it all into it. It will then go about creating the database for you. I don’t know if this will work with very large databases, the one I used comprised of 3 tables and around 4000 rows.

At this point you will need to specify who can access the database, the easiest way to do this is to allow access from all IP address with the following command:




GRANT ALL PRIVILEGES ON MY_DATABASE_NAME.* TO 'MY_USERNAME'@'%' IDENTIFIED BY 'MY_PASSWORD';

flush privileges;

However if you want to only allow access from one IP address then the best way to do this is:

GRANT ALL PRIVILEGES ON MY_DATABASE_NAME.* TO 'MY_USERNAME'@'12.34.56.78' IDENTIFIED BY 'MY_PASSWORD';

flush privileges;

To make sure your crystal reports can connect to the MySQL database drivers can be downloaded from the MySQL website:

https://dev.mysql.com/downloads/connector/odbc/

Once installed go to the ODBC connections and add a new connection, choose the MySQL connector which will then prompt you for the IP address of the Pi and for your MySQL log on details.

Click test connection and then hopefully it will be successful.

The next step is to open Crystal reports and add a new database connection using the ODBC option. If all has gone well the connection you have just set up will be in the list. Once selected click ok and you will be ready to import tables and fields to be able to report from.

The performance of the Raspberry Pi was fairly quick and snappy, I made a few simple reports and found refreshing them fast and efficient. 

Visit: www.thebusinessobjectspeople.com.

Written by:  Luke Johnson, Business Intelligence Support Technician, DSCallards