Tag Archives: Reports
Top 5 reasons why our 1 Click productivity app Click2Export is Preferred App to Export Dynamics 365 CRM reports!
is a productivity add-on for Dynamics 365 CRM and Power Apps that allows users to export Dynamics CRM Reports/Word/Excel templates and CRM Views data in just one click. Now a , this app helps the user in exporting Dynamics 365 CRM data in 5 different file formats viz. Excel, Word, PDF, CSV as well as TIFF. Apart from exporting reports, users will also be able to download the exported reports, , attach it to a note, or attach it to an email. Moreover, users can send embedded CRM Views data via email and also schedule the exports at regular intervals.
Let us go through all the features of Click2Export and find out how and why this 1 Click productivity app is an ideal Dynamics 365 CRM solution for your organization.
1. Export Report/CRM Views/Word/Excel templates in a single click:
With the help of this feature, users get the flexibility to export Dynamics 365 CRM data in different templates in a format that best suits their business requirements. And all this in just one click making their tasks easier and saving more time thereby increasing efficiency.
2. Export files in different formats:
Click2Export allows users to export files in five different formats – PDF, MS Word, MS Excel, TIFF or CSV. Word templates are exported in PDF or MS Word format whereas CRM Views or Excel templates are exported in MS Excel format.
3. Set a schedule to export reports at regular intervals:
Exporting of Dynamics 365 CRM data manually is a tiresome task that reduces the productivity of users. But using this feature, setting a schedule to export required Dynamics 365 CRM data at regular intervals is made possible. Users can set a schedule to export the data for a defined period of time – daily, weekly or monthly. These reports can also be emailed to the necessary recipients automatically using Click2Export.
4. Workflow automation:
With the help of this feature, users can export the Dynamics 365 CRM data on the occurrence of an event easily through automation with the help of workflows. For example, on activation of ‘Quote’, an email can be sent out to the client with the Quotation attached to the email.
5. Email exported files as Attachments, Attach as notes, Upload to SharePoint or Download:
Click2Export not only just exports Dynamics 365 CRM data but also helps users to perform 4 different actions with the exported files in Dynamics 365 CRM. They can download the Reports/Word/Excel templates for offline consumption, attach them to an email to send to the required recipient, store them as note attachments in the respective Dynamics 365 records or upload them to SharePoint.
With all these features of Click2Export, the mundane and burdensome task of exporting Dynamics 365 CRM data will turn into effortless work.
So, what are you waiting for? Reap the benefits of this 1 Click productivity app Click2Export and increase your Dynamics 365 CRM efficiency by downloading it from our or
That’s not it, check out our other 1 Click productivity apps and . For more information on any of these, please send us a mail at crm@inogic.com.
Till then, stay safe and stay healthy.
Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.
Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
How to set it up
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.
Can a Microsoft Dynamics 365 User Save Money by Building Their Own Reports?
Companies evaluating Microsoft Dynamics 365 Sales will often ask us, “Can Microsoft Dynamics 365 users build their own reports?”
The answer comes down to a couple of factors:
- What kind of reports do you need?
- What kind of relevant experience does your team have?
By building their own reports, a business could potentially save thousands of dollars. However, there is a lot to consider.
First things first: What kind of reports do I need?
This is often the main source of confusion. What one person considers a report may just be a list for another. When asking your CRM partner for a report, there may some misunderstanding as to what constitutes a report.
Here are three common substitutions to consider instead of a full-blown report:
- CRM Views: Views in Dynamics 365 Sales are essentially a filtered list based on certain criteria put in place by the user. For many clients, this is enough on its own. Moreover, once a user learns how to create their own CRM views, it helps them become autonomous. This helps speed up the analysis process for this user or department.
- Dashboards: Dashboards display critical data that you need to review on a regular basis from your CRM (customer relationship management) tool. Potentially, much of the data you would need in a report can be viewed from a dashboard, which you can create yourself within Microsoft Dynamics 365 Sales. Key statistics can be displayed as graphics or lists depending on user preferences and requirements. Like Views, this is something that users can create themselves with a quick training, some understanding of the fields within the forms, and a vision of what they would like to review.
- Excel templates: As a final option we would suggest looking into Excel templates in the CRM. This allows you to build your spreadsheets as you would like them to be presented and refresh the data as you need it. The most common application for this tool is to share information with your clients. What differentiates this from a report is the limitations in filtering and intelligence (e.g.: display this information if “x” exists). For some applications it makes more sense to create a report rather than an Excel template.
By using these tools to their full potential, you can probably limit the number of reports you need and save the extra costs associated with building them. The first step is then to ask yourself if you really need the information to be consumed as a report or if one of these tools can meet your needs.
In some cases, reports are necessary as there are limitations to what you can do in a view. If this is the case for you, you likely want to know if you can build these reports yourself and what the cost will be.
Next step: Can my team create their own reports in-house?
The short answer is it depends. Creating your own reports depends on the relative competency levels available internally. Ask yourself if anyone on your team has the SQL/Fetch XML experience required to build reports.
For those who want to create their own reports but don’t have the experience, it might be worth considering instead. This business intelligence solution is more dynamic and simpler to use once the data set is created and in place.
You could improve your skills by taking classes and consulting a partner. But realistically, the cost of these could come out to about the same as having your reports developed by a Microsoft Dynamics partner.
Bottom line: Can I build my own reports or not?
The answer is not the same for everyone. Building your own reports may sound appealing, but unless you have the competencies required internally, you may have to look at alternatives, like Power BI, CRM views or dashboards, and Excel templates. It basically boils down to what kind of report you need and how much experience your team has.
Our suggestion would be to have an honest and open discussion with your Microsoft Dynamics partner to ask them what building a specific report entails. Don’t be afraid to ask questions to see if there is a better way of doing things. This way the partner will get a clearer idea of what you need, and the effort required to produce it.
Here at JOVACO Solutions, we have decades of experience working with organizations to help them get the information they need. Our consultants are available to figure out the best way for you to take advantage of the wealth of data stored in your system. today to tell us more about your challenges and objectives.
By JOVACO Solutions,
About JOVACO Solutions
JOVACO Solutions is a leading ERP and CRM solution provider operating in Quebec for over 35 years. As a specialist of Microsoft Dynamics business management solutions, we offer a wide range of products and services to meet all the needs of professional services firms and project-based organizations. We also offer specialized project management tools and timesheet add-ons fully integrated to Microsoft Dynamics solutions. Visit or for more information.
4 Ways to Reduce the Cost of Custom Reports in Microsoft Dynamics 365
When estimating the cost of Microsoft Dynamics 365 projects, reporting can sometimes represent a significant portion of the quote. Many customers that want the Dynamics 365 system to fit within their budget ask if there are ways to reduce the project cost. If a large amount of effort is dedicated to building reports, it is an area worth revisiting. Often, the customer can take on the task of building some reports on their own, and reducing the overall project cost.
When you look at the cost of Dynamics 365 reporting you might ask:
- To what degree can a Dynamics 365 user develop, create, and run their own reports?
- Is SQL or SSRS (SQL Services Reporting Server) experience required?
- What is the difference between “reports” that users can create themselves and a report that has to be developed by and experienced Dynamics 365 Partner?
- What kind of data could be viewed in dashboards instead of custom reports?
The answer is that there are a number of options for creating reports in Microsoft Dynamics 365.
- Simple on-demand reports, i.e. push a button, get a report, can be easily created using the . If you understand the data and where it lives you don’t need SSRS skills to create insightful and actionable reports.
- More complex Microsoft Dynamics 365 reports would require SSRS skills, however, we have many customers who have come up to speed on SSRS and turn out some fairly complex reports. Having internal resources that are well-versed in creating reports is gold. It’s an area data driven companies will want to invest in.
- Another simple way to accomplish reporting is through dynamic filtered views. Views allow you to pull out groups of Contacts or Opportunities that have things in common, like location or sales stage. You can then tie views to charts and create some pretty compelling dashboards. Dashboards may also allow you to combine multiple reports into one view. You can also export views to Excel for great on-the-fly reporting.
- If the reports are well-defined in terms of layout, criteria and parameters….and you need SSRS reports, you could look to outsource the report development. If you work with a reputable, reliable resource well versed in SSRS, you could get your report development done at a fraction of the cost.
When you work with a , they will help you evaluate the realistic options to lower the cost of custom reporting, and still get the end results you need. The team can help.
If you are interested in evaluating Microsoft Dynamics 365 contact us .
By Ryan Plourde, Crowe, a Microsoft Dynamics 365 Gold Partner
Follow us on Twitter:
Power BI and Paginated Reports: Better Together
Wise Professionals See the Big Picture
Bringing it Home
Today we’ll be concluding our 3-part series on Analysis versus Reporting, Power BI versus SSRS and Paginated Reports, and looking closely at our habit of using the word “versus” so liberally:
-
Part One summarized the history of the industry, its long-stagnant roots in Reporting, and the recent “triumph” of Analysis.
-
Part Two examined the relative strengths of Power BI reports and Paginated (SSRS) Reports, and why you need both.
-
Today, we’ll explore how you can use both Power BI and Paginated Reports together, as part of a wholistic system.
What Does “Triumph” Mean?
I’d like to start by clarifying what I mean when I say Analysis has “won.” Even today, traditional reporting is STILL the majority of the “biomass” in the BI world. For example, right now around the world, there are probably three people using a Traditional report for every one person using an Analytical one.
So Analysis isn’t dominating usage today, but it IS dominating mindshare. No one is BUYING platforms which are 100% traditional reporting – not anymore. And in fact, lots of people ARE buying pure Analysis platforms like Tableau. Of course, what they SHOULD be doing is buying platforms capable of BOTH, which is kinda the focus of today’s article.
Analysis Will Keep Stealing Usage Share for Awhile
So we have a ways to go before Analysis equals or eclipses Reporting in usage, but the wind has most definitely shifted. Keep in mind that a LARGE percentage of “usage” of Reporting is not “looking at it with our eyeballs” usage, but rather EXPORT usage, which powers ANALYTICAL work in Excel. Below the radar of IT and invisible, and crude compared to the Analysis you can perform in Power BI, but Analysis nonetheless.
Until the world stops using Reporting as a crude form of database access (to power its Excel analysis), the fight is not over. And we have a LONG ways to go to get there. Even today, a client told me a story about a team member who last week printed out a 30-page report and was planning to go line by line through it, re-keying a subset of the data into Excel (manually!) to get a total of how many units of Product X they’d sold in the prior month. Said individual was thrilled to be shown the Power BI report where a single click provided the answer. So we still have some work to do, awareness-wise.
Where Will We Land?
At most, I expect the inverse of today: three Analysis reports for every Traditional report. If we slowly convert the “Traditional reports which pretend to be informative but are really just export sources” into Analytical (Power BI) reports, that should get us there – and really, that’s ALL that we SHOULD do.
And if you read Part Two, you know there are certain kinds of reports that Power BI is “bad” at. We still need SOMETHING for those, and I expect they will still constitute, say, 25% of all reporting usage once we reach equilibrium.
Switching from “Or” to “And”
Part Two covered when to use X versus Y, but even a firm grasp of that leaves something important on the table: what are the ways you can use BOTH in an integrated manner, thus getting a result which is more than the sum of its parts? Ah yes, the “Better Together” cliché! But there is substance here.
Microsoft should be leaning HARD into this aspect of their platform – that it has STRONG products in both worlds, because so few of their competitors have anything to offer in the Traditional space, even as said competitors are already behind in the Analysis space. The combination of the two – when well-integrated, brings at least three very interesting classes of benefits:
-
Cross-report drillthrough in either direction.
-
Both kinds of reports in a single portal.
-
Shared analytical model powering both flavors of reports.
Let’s take these one at a time.
Bi-Directional Drillthrough
This is the most fun, so I’ll cover it first. Consider the following wireframe of a Power BI report:
We’re Going Wireframe Today – No Time for Distracting Myself with Hyper-realistic Demo Building 
I hope it’s easy to imagine the interactive features of the above report. Click on a bar in one of the charts, for instance, and watch the rest of the report filter down to that Category or Customer, etc.
Imagine seeing Cust2 and thinking “hey, why is Customer 2 so low this month? Aren’t they normally buying a lot more from us? Let’s see what they actually bought – show me the invoices!” Well, here ya go…
Select Customer 2’s Bar, Click the Icon/Link
And then you get navigated to a Paginated Report to view the actual Invoices which were sent to Customer 2, because the filter selection you made in the Power BI report is “transmitted” as part of the URL:
Paginated Report Displays the Authentic Invoices
(The system “remembers” which customer you selected in Power BI – by passing it in the URL)
“Wait, Couldn’t I Build That Invoice Viewer in Power BI?”
Yeah, smart aleck, you COULD build precisely THAT one pictured above. But a couple things. One, a more realistic invoice might span multiple pages, with page breaks and intermediate subtotals, and Power BI isn’t good at that paginated stuff. Two, if you want to be absolutely sure you are looking at PRECISELY THE INVOICE which was sent, you would want to go to the source which generates and prints said invoices yes? And since said invoices often need to be pixel perfect, the original source again cannot be Power BI.
“Wait, How Do You Pass ‘Cust2’ in the URL?”
This isn’t a technical how-to article, and I’m trying to stay above the weeds, but here’s a quick series of screenshots which should help:
Format Shape –> Action –> Web URL –> fx –> Choose a Measure
Example of a Measure Which Constructs a URL to a Paginated Report
(Comes from a slightly more complex example than our wireframe demo)
And Now the Reverse Direction!
OK, so you’re looking at the invoice, and you spot the change. “They only bought ONE Flux Capacitor this month! Don’t they normally buy more??”
Aha! Well NOW you’re back to an Analysis kind of task! You COULD thumb backwards through invoices using the “Prev Invoice” link, but if the invoices are multi-page, that’s gonna be tedious, AND it’s hard to spot trends that way. Power BI is REALLY good at this kind of thing.
If only there were a way to seamlessly navigate to a Power BI report, pre-filtered to answering my question…
Ooh! It’s a Link!
Navigated back from Paginated to Power BI, With Product Category=”Flux Capacitor” and Customer=”Cust2”
(A different Power BI report than the one we started on, but of course depending on actual needs, it COULD have been the original)
“OK, Spill It – How’d You Do the URL THIS Time?”
Shockingly similar on the Paginated side (in Report Builder) to the Power BI side, actually…
In Report Builder, Select Text Box Properties
“Go to URL”
Build Yourself an Expression
(Again though I’m taking a pass on the weedy details today – trying to keep it as concise as we can around here)
Pretty Neat Huh?
Let’s see that whole flow one more time, in a more concise manner:
Glorious: You Bounce Back and Forth Across “the Line of Versus” with Agility
***BONUS THOUGHT: As cool as that is, I wonder if even TIGHTER integration makes sense in some cases. The custom visual framework in Power BI, in theory, could support an embedded Paginated Report control right? Would there be use cases for that? Or would the clarity of being in a completely separate report always be preferable? I’d bet that it would at least be interesting in SOME cases, but I need to think about it more. Leave a comment if you have an opinion, please!
Benefit Two: Single Portal
Not as sexy as cross-technology drillthrough, but a real benefit for everyone’s sanity as well as usage: both kinds of reports can be published to a single Workspace:
OMG, BOTH TYPES of Reports in the Same Workspace? THAT’S PETER VENKMAN’S MUSIC!
Back off, man. I’m a data scientist.
Don’t sleep on the little details like this. They make a big difference.
Benefit Three: Powered by the Same Analytical Model
SSAS Tabular (Power BI’s analytical engine) brings MANY advancements relative to its predecessor (SSAS MD), and one of them is the ability to return hyper-detailed rowsets without the performance penalty we suffered in the old days. If you want the data to populate an Invoice, you can fetch what you need with a DAX query and not sit there waiting forever for it to return. In fact, things like the Table visual already do this within Power BI itself.
So if you’ve invested in a robust Power BI analytical model to power your analysis, you might find yourself reaping unexpected Paginated Reporting benefits. If you don’t NEED to go build new, traditional SQL-powered plumbing to power a more traditional report, great! By all means, connect it to your Power BI model (aka Dataset), and off you go!
Your Data Model Can Power BOTH Kinds of Reports
And folks, that wraps up our series! What did you think? Did I leave anything out? Wait, what’s that you say? There IS something I missed? Lemme see…
“Hey Rob, How About a Good Microsoft Rant?”
How about more than one? I mean, I’m so glad you asked, cuz I’ve got two…
Integration Shouldn’t Cost So Much
First, why is it so expensive to get an integrated environment? Best I can tell, you can’t get a Power BI + Paginated integrated system for anything close to the price of getting them separately. 100 users of Power BI Pro would cost $ 1k a month, and an SSRS On-Prem server is a one-time payment of, say, $ 6500. So to run those two – separately – for two years, it would cost you about $ 30k all-in.
But to get them integrated, today you’d have to plunk down $ 5k a month for Premium, and that pushes your 2-year price to $ 120k – 4x having the same capabilities separately. Now, I’m pretty sure I can get benefits 1 and 3 (drillthrough and powering both from a shared data model) at the $ 30k two-year price, so what the extra $ 90k gets me is… benefit two? Side-by-side publishing of both report types in a single Workspace? Steep price jump for just that.
On the on-prem front, you can go Power BI Report Server, but that’s also a premium price point. Enterprise Edition plus Software Assurance is required, apparently, and that’s 100% an Enterprise SKU – well out of the reach of SMB’s, and just as importantly… out of Departmental reach within the Enterprise itself.
In fairness to Microsoft, the pricing for Enterprise customers DOES need to drive their thinking in large part. And that comes with benefits! Most of Microsoft’s data stack is actually quite underpriced for SMB’s and Departments, and we never complain about THAT, do we? At P3 for instance we can run all of our MS data stuff for a FRACTION of what we pay for Salesforce (which we deeply regret getting hooked into, but that’s a story for another day isn’t it)? There’s a lot more value in the MS platform than Salesforce, and yet the prices are reversed.
So this may be a sensible pricing strategy for the Enterprise, but this is one of those cases where it prohibits SMB’s (and again, the Departmental level of the Enterprise, which is crucial for “land and expand”) from playing the game properly.
It’s a massive strength for the MS platform versus the competition, and it seems a shame that Departmental/SMB is accidentally priced out of the most elegant version today.
OMG Naming!
Authentic Action Photo of Microsoft Naming Their Software
Oh, “Paginated Reports,” how I LOATHE thee as a name! This name caused me SO MUCH TROUBLE for a very long time. “How does this relate to SSRS?” is the question which kept plaguing me. Is it a new thing? If someone knows SSRS really well, will this be a foreign experience – a brand new technology – or super familiar? Is it not capable of pixel perfect document generation, and instead ONLY leans into the truly Paginated benefits of SSRS? Ugh.
Here’s what it REALLY is: it’s SSRS technology living within the Power BI environment. You still get Report Builder, and the whole thing still uses RDL. If you knew SSRS, Paginated Reports are for you. Which is good news – SSRS was, and is, a market leader in its space. Super robust and capable. Refined by TWO DECADES of development and experience.
Now, again, I kinda DO get why they’re doing this. Power BI is “the brand” now, and everything needs to line up under that banner. Years from now, this naming scheme will be a good one. “Power BI just has two kinds of reports, great!” But I think they underestimate the utter carnage this wreaks in the meantime, and how many times we’re going to have to explain to people that “it’s just SSRS in Power BI.”
Remember, I’m the guy who tried to rename PivotTables when I worked on Excel at Microsoft. So I’m definitely cut from the same cloth originally – I’m just trying to heal, and to atone for my past sins. But “yeah,” said Rob circa 2004, “PivotTable is such a wacky and scary name which has nothing to do with its primary function, which is to summarize/aggregate data.” I wanted to rename it SummaryTable. Just like I explained above, if you fast forward ten years, that’s a better name. Zero question. But is it worth the transition cost? In the end, wiser heads talked me out of it.
While we’re at it… can we rename Power Pivot and Power Query to be… Power BI in Excel? Because just like we’re explaining Paginated Reports = SSRS in Power BI, we have to explain the Excel relationship over and over and over…
Nah, too sensible.
Power BI and Paginated Reports: Have We Come Full Circle?
This is where we’re headed in Part Two. Today: taking stock of how we got here.
(Click image for larger version)
Summary of this 2-part piece:
- There’s always been a blurry line between Analysis and Reporting
- 20 years ago, BI was dominated by Reporting, with Analysis as an upstart
- Today, Analysis seems to have “won,” and this DOES reflect major progress!
- But I also think we’ve overcorrected quite a bit, and it’s time to take a fresh look
- Old habits die hard, and old use cases die even harder
- In an era of Action, we have the chance to hybridize in ways that were impossible before
The Thick Grey Line
There are few stories as tortured and twisted as my relationship with Analysis and Reporting. In 2002, as part of the Excel team at Microsoft, I was introduced to the concept of Business Intelligence. This is back when the term (BI) was fresh. It sounded cool, like Jack Ryan in a darkened room at the CIA. (The stodgy 2000s-era, IT-bottlenecked reality would not dawn on me until much later).
Excel was about to make a large investment in BI-related capabilities, and the powers that be had selected me to lead our part in it. I was excited, but now I needed a crash course in “what the hell is BI?” I was given multiple tutors, and they all were quick to introduce the concept of Analysis versus Reporting. The “versus” seemed to be pretty important. It wasn’t an “and” – no, the “versus” was chosen deliberately in these sermons. You see, these were Two Very Different Things.
I struggled mightily to grasp this difference. I was told that interactive things like PivotTables were Analysis tools – NOT Reporting tools! Reports were something completely different. “But,” I pointed out, “they’re called ‘Insert PivotTable Report’ on the Excel menu today!” (This was Excel 2003). “Yeah,” said the mentors, “…we might want to fix that.”
I tested the fences. “If interactivity is the key differentiator between Analysis and Reporting,” I asked, “if I just LOOK at the same PivotTable every day, and I NEVER manipulate it in ANY way, then THAT’S a report, yeah?” “No,” said the mentors with the tone of an exasperated Yoda addressing an impetuous Luke, “it’s still a PivotTable, and that’s an Analysis tool.” “OK then, how about an Excel report worksheet that’s assembled with formulas alone, and therefore non-interactive. Is THAT a report?” “Rob,” they said, “you’re just gonna need to get it through your head that Excel is an Analysis tool. We have work to do and we can’t get hung up on this.”
Based on my mentors’ evasive answers, I concluded that this was merely a Distinction Without a Difference. “Reports are things which inform people with data” became my motto – whether they were interactive or not. “And EVERYTHING should be interactive,” was the second half of my motto. It seemed to me that the distinction between Analysis and Reporting was an artifact of historically inadequate software. As a species to that point, we’d built some interactive tools, and some non-interactive ones, and each of them lacked some of the features of the other. It was time to Evolve. We were going to be Modern, and blur the line. “Good looking AND interactive – we shouldn’t have to choose.” And off I went.
(Aside: one of my top 50 memories of working at Microsoft follows. Early in this process, the leader of the SQL Server Reporting Services team told me in a meeting I was not allowed to refer to Excel documents as “reports,” and that only SSRS reports were reports. He instructed me that I could use the words “document,” “spreadsheet,” or “workbook,” but “report” was off limits. I found it amusing that someone thought they could trademark a basic noun, and dedicated myself to a campaign of good-natured rebellion. Since he had no political control over me whatsoever, for YEARS I then made a point to ALWAYS call Excel docs “reports” in his presence, turning each time to slyly grin at him after delivering the irritant).
How Did Excel 2007 Do? (At blurring the line)
It was a noble cause, and we made some progress. PivotTables became MUCH better-looking… but they were still PivotTables. Excel charts got prettier, but hardly set the world on fire. We added Cube Formulas to enable cell-by-cell layout of reports, but cell-by-cell is a far cry from the pixel-perfect capabilities of SSRS.
Ironically, I now give us worse marks on interactivity than aesthetics! Being told over and over that “Excel is interactive analysis” must have taken its toll, because we took interactivity for granted. Yes we added some cool filtering features to PivotTables, but that was basically it. Hell, we didn’t even think to add Slicers! (Those came in 2010).
Analysis in the Shadow of Reporting
Let’s get back to 2002: since Excel was on Team Analysis, I got partnered up with the SQL Server Analysis Services (SSAS) team. The best way to describe SSAS at the time is/was “SSRS’s smarter but younger brother.” SSAS was a super smart product built to tackle enormous and important challenges. Even the circa-2002 multi-dimensional version was one of the wonders of the software world (and today Tabular is even better). A brainy product built for brainy people tackling real-world problems of nearly every flavor.
SSRS is/was a much simpler beast, but it was FAR more successful in the market at the time. Even though it tackled a more modest set of problems, and quietly left MANY business problems unaddressed (more on this later), it outsold SSAS by a multiple. There’s really no mystery as to why: the world is FULL of people who know SQL, and if you give people a way to turn SQL queries into pixel-perfect reports (and distribute them), you’re gonna get a lot of takers.
Reporting’s dominance over Analysis lasted a long time. Even in 2010, when I spoke at a user group dedicated to professionals working in Microsoft BI, almost no one in the audience had any significant experience with SSAS. I was stunned, but it was a powerful lesson: most “BI” professionals were actually “Data Warehousing and Reporting” professionals.
Why this Was a Problem
SSRS was more popular than SSAS because reports are just easier to build than analytical models. SSRS reports basically answer a NARROW set of questions from a SINGLE data source, whereas SSAS models attempt to answer EVERY question, powered by MULTIPLE data sources. The latter is an exponentially more ambitious undertaking, AND it requires you to learn skills beyond SQL, so yeah… I get it. “Path of least resistance” might be the world’s most powerful force.
One little problem though: the world is an “every question from multiple data sources” kind of world. Everyone has/had that problem, and Reporting did nothing for it. Worse, Reporting gave us a false signal that we WERE doing a good job of BI. “What do you mean, you don’t have the data you need to answer your biz questions? What about those jillion reports we built you?”
I’ve written about this problem extensively – here and here for example, but the gist is this: ignoring those unanswered questions doesn’t make them go away. You either manage from your gut instincts, which is basically just guessing, or you answer them in Excel.
So Business Intelligence was still sorely lacking in… Intelligence. A Reporting-dominated world is a sad one – for the Business.
The Part Where I Say Something Nice About Tableau
Gather round children, because this doesn’t happen often: Tableau (and Qlik, and Spotfire, etc.) did us all a huge favor by breaking Reporting’s stranglehold on BI, and for bringing Interactivity to the forefront. We may take it for granted today, but the idea of a report canvas where damn-near EVERY element is interactable… just wasn’t anything near a mainstream idea before the rise of Tableau.
Tableau completely reset the reporting world’s expectations for interactivity – from “minimal” to “ubiquitous.” In the Reporting world, the developer had to go out of their way to enable every single point of interactivity – so we never got much (if any). But in Tableau, interactivity was the DEFAULT. If the Age of Reporting was the Dark Ages, Tableau was the Renaissance.
Interactivity is crucial – because without it, you cannot answer followup questions. A traditional SSRS report might tell you there’s a problem, for instance, but NOT help you decide on a course of action. Sometimes, all you needed to do was look at a different report, but very often, that report didn’t exist, because this particular problem had not been anticipated. So you request a new report. And wait. And wait. And then maybe use it once – or never, because the window for making a decision passes before the report arrived. (I think this is a big reason why every company has hundreds, if not thousands, of “active” reports which haven’t been used in years – they were one-offs to answer a specific one-time question. Interactivity cuts down on this problem dramatically).
Non-interactive Reporting was never going to cut it again – not once the Biz had seen Tableau. Something needed to shake us out of the Reporting-Dominated world, and we got it. For that, we should all be very thankful.
Fast Forward to Today
The illustration above gives you the gist: it’s all about field of vision. The “pinhole” view of traditional reporting was never sufficient to power the needs of the Business. Tableau’s more interactive approach was/is captivating by comparison, precisely because it enhanced end users’ (biz users’) field of vision – WITHOUT requiring additional IT intervention. But alas, Tableau also still relies on flat-table backends – it’s still “just” fundamentally another tool on top of SQL, and therefore only goes so far before you’re back waiting on IT.
If Tableau marked the Renaissance, Power BI is the Industrial Revolution. The multi-touch interactive approach established by Tableau married with the power of SSAS Tabular models under the hood (plus agile ETL in the form of Power Query/M) is the culmination of our multi-decade story. (I’m sure the world of data will continue to advance, but I expect the next breakthrough to be a Black Swan moment, rather than following the lineage of the story here).
Here’s another “slice” at the same diagram, this time a bit more specific to the capabilities of each genre:
I believe this is what progress looks like
Wait, didn’t Tableau just add a data model?
Why yes! Yes they did. And that actually reinforces my point rather than diluting it: you NEED an analytical data model engine under the hood, else you’re still bottlenecked. I’m glad to see Tableau acknowledging this, because it’s fundamentally good for the world. Heck, it even makes OUR messaging easier, since we can now say “yep, Tableau agrees, and they’re playing catchup.”
BTW, I’ve long said that our company (P3) is committed to using the best tools out there, and that if something better comes along, we’ll use it. So we’ll be keeping a close eye on Tableau but… I don’t expect them to be a credible competitor with their data model any time soon. Microsoft has a tremendous lead here – arguably twenty years or more – and we all know how that played out for Google vs. Bing. Plus, I’m skeptical that you can retrofit a data model into such a long-established “single table” product without serious compromises.
Analysis has Usurped Reporting, But…
Analysis (aka Interactivity – plus a true Analytical Model under the hood) has now risen to dominant position, relegating Reporting to the backburner. You can’t be a credible BI competitor these days with just a Reporting tool. You have to bring that sweet Interactive Dashboard love if you want to be considered.
But even though “my team” won, I think the industry is oversteering, and now neglecting Reporting to its own detriment. The SSRS bloodline is valuable and deserves to be part of your toolbox – in parallel with, and integrated with, Power BI. It’s time to talk some detail, and about the Way Forward, and we’ll do that in Part Two coming Thursday (June 11).
You Wouldn’t Use a Skateboard as a Surfboard (or Vice Versa).
We’ll talk about that in part two on Thursday.
Self-Documented Reports (aka the FAQ Page)
Sometimes in the grind of data wrangling, slinging some DAX code, and laying down some sweet charts, graphs, and tables, we can forget that the softer “non-data stuff” can really drive usability and adoption.
It’s very easy for us (the report authors) to underestimate how much better we understand our reports than our users do. We just implicitly “get” the meaning of each metric, and are aware of each interactive capability. It’s important for us authors to try to step outside our own knowledge and history in order to see things from the naïve user perspective.
A good data visualization is like a good joke – it shouldn’t need to be explained. I frequently use that phrase when teaching our students about report design. However, sometimes a little context about what a visualization is communicating is necessary.
Let’s look at an example.
Here’s a Sales Snapshot for Stores with the Contoso company. Pretty snazzy huh? You don’t have to be a data pro or MBA to get the general idea. Green is good, red is bad, and yellow is eh. Most of the report is pretty intuitive. However, it’s also completely fair for someone to want some more understanding and context to feel comfortable making decisions based on what they learn from this report.
Enter the FAQ page. Seems obvious right? But for some reason, we don’t use them in our empirically-driven reports enough. But we should. It’s such a simple way to enhance report usability.
For example:
- How are the arrows and their colors calculated?
- What are the thresholds to determine the arrow direction and color?
- What do some of the metrics mean?
- How are they calculated?
These are probably only a handful of common questions that most stakeholders would ask.
I suppose you could add a bunch of text boxes with questions and answers. But, what if you have several questions and there isn’t enough space? I’m reminded of something a fantastic boss once told me, “Never pass up an opportunity to wow someone and grab their attention with your work.” So, let’s add some wow effect to this by leveraging the bookmarks and buttons functionality.
Bookmarks and buttons allow us to create a user experience that is intuitive to the user and that allows them to navigate around the page easily by just clicking. In this case, click on a FAQ and the answer appears. Click on the FAQ again or a different FAQ… I think you get the point.
Here’s how we are going to do this.
First off, be good to your future self. This is something we love to say in our training classes, and it holds true here. A little organization will go a long way. We are going to have a bunch of items on the page and we’ll want to keep them all straight. The selection pane is going to be your friend here, so let’s turn that on (View > Selection Pane)
We start by adding our buttons (Insert Tab). Select a blank button and format it as needed. In this report, I have two buttons for each FAQ. One for the collapsed FAQ, which is dark, and the other for the expanded FAQ, which is white. I like to create all of one set (like the collapsed) first and then all of the expanded afterward. This allows me to copy and paste so I don’t have to reformat buttons.
For each FAQ the dark (collapsed) and white (expanded) sit right on top of one another. Using the General section in the Formatting pane make sure you have both buttons with the same X and Y location and width and height. It’s important.
Now we can add an image that we can use as an icon. I used a chevron pointing to the left or right to indicate expand or collapse. As you can see the number of images will start to add up. Now, I am what some people call “particular” (I’m sure some other adjectives have been used as well, but we’ll stick with that). I use consistent naming conventions for all of my buttons, images, and objects so I know what is what. I can’t say this enough… Be good to your future self.
Once you add all of your buttons and lay them on the page, you can add your text boxes with the FAQ’s answer.
Now that I have everything I need on the page the number of objects in the selection pane can be a bit overwhelming, but no need to fret – Groups to the rescue.
In the selection pane, hold control and click on the objects that would be grouped. Then click the ellipses by one of them and select Group. In this case, I created two groups for each FAQ – one for collapsed and the other for expanded. I know… particular. And yes, I rename my groups.
Now we can start to add our bookmarks. Add the bookmark pane (View > Bookmarks). Using the selection pane, we want to hide all the objects that shouldn’t be visible. We do this by clicking on the eye icon. Once you have only the objects you want visible, select Add on the Bookmark pane. Double-click to rename. Should you rename? Of course you should!
Now repeat the process of hiding and displaying different objects for each of your FAQs. Once that is completed we can go to the collapsed button for each FAQ and by using the formatting pane, slide the Action slider to On, Set Type to Bookmark, Select the appropriate bookmark (see why you renamed them?), and if you want, add a tooltip to provide some additional instruction when someone hovers over the button. Extra points if you do this.
Ok, final steps. Let’s add some Page Navigation buttons. First, we’ll add a Back button on the FAQ page to take users back to the Snapshot. Once you add the button, go to the formatting pane and under Action select Page Navigation and select the name of the page. On the Snapshot page, I want a button that when clicked will take someone directly to the FAQ page. I use the information button. Follow the same process we used to create the Back button.
And there you have it. This is one of those small details that goes a long way to making your report more impactful.