Migrating Crystal Reports to PowerBI? 14 things you should know.

Over the last 3 years, I’ve started to do a lot more work using PowerBI. In terms of planning and designing, it’s similar to QlikView/QlikSense in that you first load and transform data, then do the pretty stuff. I’m now seeing more interest in migrating from Crystal Reports to PowerBI.
At first, this made no sense to me as I see them as separate products with little overlap. However, that’s just me looking from a technical perspective rather than looking at how each product meets user needs. Ultimately, you have data and you want to present it in a meaningful way.

Rather than a step by step guide to migration or a product review, this article looks at 14 things to be aware of from the perspective of someone experienced in Crystal Reports, but now looking at a migration to PowerBI.

1. Database Connections 1 – Connection Type
   In Crystal Reports, each report uses a connection (possibly more than one) to a database. When you run the report, the data is refreshed. Using multiple databases is painful.
   In PowerBI, you have a choice (some of the time) of either importing data or having a live query, with the data pulled into a ‘Model’. The Model provides the data for the components on the report. The live query is the equivalent of the way Crystal Reports runs reports – it connects to the database and just retrieves the data needed. The import option retrieves all the data (or what you’ve defined as all) on a schedule, then this data is queried for the report. The advantage of the import is that the report is very responsive and loads immediately. The disadvantage is that the data is from a point in time, so not much use if you need up to the minute data.
2. Database Connections 2 – Transformation
   When you’re creating your data model in PowerBI, you have a series of queries. These go through a series of transformations, but ultimately you’re just defining joins between different views of data. The great bit is that as the joins are separate from the database connection, you’re free to have as many data sources as you need.
3. Database Connections 3 – Other data
   Welcome to the 21st Century. As well as all the regular databases, you can also connect to web data. It’s a bit fiddly and needs a decent wizard to make life easier, but you can retrieve data from the likes of Salesforce, Xero and other web based applications. Then because of 2, you can join them to your other databases.
4. Database Connections 4 – Publishing
   Eventually, you’ll be publishing your report to Microsoft’s PowerBI Service to view in a browser and to allow others to view. That means that the site will probably need to connect to your on-premise database. No panic, there’s a Data Gateway that does all the magic.
5. Themes
   Most Crystal Reports users will have tried using Templates to have consistent formatting across reports. At least, they’ve tried them once as most of the time your report formatting is broken and the fields you’ve spent a day positioning are moved randomly.
   In PowerBI, you have Themes that work in a similar, though slightly inferior, way to those in Word or PowerPoint. Until recently, this involved working with a JSON document in Notepad, but now there’s an easy to use designer. Only downside is that some custom visualisations ignore the themes.
6. Charts
   Crystal Reports has a great chart designer. Sadly, the default formatting is awful so most reports have charts that look dated. In PowerBI, the default formatting is great with muted colours and great interactivity (click a bar and everything else is filtered to that subset of data). You can get more from Crystal Reports, but it’s less effort to get good looking charts in PowerBI.
7. Parameters and Filters
   Crystal Reports is unusual in that you first define a parameter, then decide how that parameter will be used. It’s not necessarily a data filter, it could be used as a simple text comment or used as part of a formula. It’s not unusual for a selection formula to contain something like if {?param1}={field1} or {?param2}={field2}. It’s a very flexible system.
   PowerBI needs a rethink as the approach is very different (not worse). You can have a filter on a ‘visual’ (anything displayed in the report), a Page (think tab in Excel) or all All Pages. You can also have a ‘slicer’ which is an interactive filter that can contain a hierarchy. They all directly filter the data. If you want to work with anything more flexible, you’ll need to add something to your data. In some cases, you may need to rethink your data model.
8. Two Formula Languages
   When you model your data, you use Power Query. When you design the report and need to add new fields or calculations, you use DAX. Just make sure you’re reading the correct help guides! PowerBI is very good at doing the basics, but once you need to do anything more, you’re dumped into a text editor interface. A little learning goes a long way & online resources are plentiful.
9. Two Designers
   PowerBI has two designers! There’s a Windows ‘Desktop’ designer that allows you to create your data model and design a report. Then there’s the browser version that lets you design a report from an existing data model. There’s no difference in the functionality when designing reports. It would have been so much simpler to have a separate product to design the model, but this is Microsoft. Only thing to be aware of is that if you open a report in the Windows designer, then save it back to the server with a different name, you’ll have created a second copy of the model. If you want a second report to use the original data, use the browser designer.
10. Licensing
    There are currently three types of licences: Free, Cheap, Expensive.
    Free gives you the PowerBI Desktop designer, but no publishing to the PowerBI Service. It’s great if the reports are just for you, or if you want to test PowerBI with your data.
    Cheap (PowerBI Pro) lets you save your reports to the PowerBI Service, schedule updates and let other users view your report. The other users will also need a licence, but at $10 per month, it’s not going to break the bank. This is the licence most small to medium businesses are going to be using. There are some other limitations, discussed later.
    Expensive (PowerBI Premium) starts at $5000 per month. That gives you unlimited consumers of reports, dedicated server resource and a few extra features. What seems to happen is that fancy new features first appear in Premium, then gradually drop down into the lower licences.
    Compare this to Crystal Reports where you have a cheap designer, then a slightly more expensive server product in Crystal Server, finally the much more expensive SAP BI.
11. Paginated Reports
    Crystal Reports is based upon the idea that a report is a series of pages. Fill page 1, you get page 2.
    PowerBI has a design canvas. You add tables and charts. If there’s too much data for a table, you scroll.
    Most paginated reports can be redesigned to fit the PowerBI canvas unless you plan to print the reports to paper.
    If you must have paginated reports, they’re now available as part of PowerBI Premium. Personally, if they’re essential, just stick to Crystal Reports for some of your reports.
12. Distribution 1 – Export
    Crystal Reports exports to many different formats. PowerBI isn’t so good. From the Windows designer there’s an excellent pdf export. From the browser designer you can export to pdf or PowerPoint. When you schedule from the Server, you get a fuzzy picture unless you make your report small. Even worse, people receiving the fuzzy picture need a licence. Worse again, you can only send them one page. I assume it’s designed in this way to encourage people to login to the server to view their reports. It’s awful.
13. Distribution 2 – Schedule
    Once upon a time, scheduling Crystal Reports involved getting to work early to run a report. It’s a lot simpler now with Crystal Server and a whole host of third party schedules. Typically, you can decide when a report runs, how frequently, its format and destination (such as email).
    PowerBI is more limited. The data can be scheduled to refresh daily or weekly at a set time. If you’re using PowerBI Pro, you’re limited to 8 of these per day (48 for Premium). You then create subscriptions to the reports to be sent by email.
14. Just to avoid a list of 13 items
    Not superstitious, but why put of potential readers? Help me you by leaving your tips in the comments.