Monthly Archives: October 2020

Can’t see “Edit Filters” button in the classic solution designer for a view? Here is one reason why

There is, really, not a lot to write about it. The screenshot below says it all:

image

There is one link missing on the right side, which is “Edit Filter Criteria”. It does show up for some other views, though:

image

But, from what I could see, it disappears once the view(or, possibly, the filters) has been updated in the new designer.

Using OpenXml with Power Automate Flows

Earlier this month, I wrote a couple of post about reporting/document generation. While working on it, I also tried one other option which you may find useful, too.

One of the biggest problems with Word Templates, be it classic word templates in model-driven apps or new populate word template action in Power Automate is that there are always limitations.

However, if there is a wish, there is a way.

Open XML SDK library has all we need to manipulate Word documents programmatically, so it’s entirely possible to even create custom templating functionality if we wanted to. Actually, I know some clients who did just that (to be honest, not sure if they did use Open XML).

In this post, I will walk you through the process of building an Azure Function which will be accepting two word documents, and which will merging those two into one.

Once there is an Azure Function, we can use it in a Flow like this:

image

When the flow runs, it will merge two files into one. Here is what it looks like:

  • You will see each individual file first
  • Then I’ll run the Flow
  • And, as a result, a new file will be created and downloaded. That file will have the content of the other two merged

How does it work?

There is an Azure Function that’s called in that HTTP Request action. This function would extract documents from the incoming HTTP request:

image

And there is a helper class that’s handling the merge:

image

You will find complete source code here:

https://github.com/ashlega/ItAintBoring.Functions

I struggled a little bit figuring out how to pass file content to the Azure Function, so the screenshot below might help – it turned out the trick was to use [‘$content’] parameter of the output:

image

But, realistically, does it solve the problem of generating large word documents out of individual files? Turned out not really – I tried with a hundred individual documents, and this whole thing just timed out. But, as a proof of concept… was an interesting exerciseSmile

Paginated reports in Power BI – not a replacement for SSRS yet?

There are a few reasons I still have not, really, touched Power BI (beyond some very basic things just to see what it’s capable of), but, having looked at my reporting options recently, I figured Power BI paginated reports might be what’s required on the project.

Luckily, it turned out we might be fine on the licensing side, so, having looked at it quickly this morning, I am now starting to realize a few things:

1. Paginated reports are, essentially, SSRS reports

You would recognize the interface, even thought it’s a separate tool now:

image

You would also recognize the file extension:

image

Although, to be fair, what we used to call SSRS reports were, really, reports utilizing rdl file format, and they would be running on the SSRS server.

Now we have Power BI reports, since they would be using Power BI Service… but they are still using rdl file format. And that’s where Power BI Report Builder comes in, since it allows us to store those report in Power BI service:

image

That kind of clears things up, it seems. We are not talking about a new reporting format – we are talking about a different engine to run those reports, and, also, we are talking about a different tool we’d be using to build them.

2. There is no Fetch XML data source

image

Which immediately brings up a question of how do we actually build paginated reports for CDS? With SSRS, we had report authoring tools for Dynamics 365, now we don’t have those, but we do have Common Data Service (Preview) datasource.

3. The problem with CDS data source, at least right now, is exactly that it’s in preview

I tried it, id did not work, so I was looking for what could have gone wrong (especially since I did try it with the SQL Management Studio about a month ago, and all worked well)… till I suddenly spotted this warning:

image

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query

In a way, that’s an extreme example of why preview features should not be used in production – they are not covered by any SLA-s, so, in the situations like this, preview features can be disabled. It’s kind of hard to believe, but that’s why it’s called “preview”.

This brings me back to the list of reporting/document-generating options I could use with model-driven applications… and the only working option right now is, actually, SSRS. I’m running in circles here, it seems.

PS. Sure we might use Azure SQL Database, but that would mean syncing CDS data to the database. Which might not be a huge issue, but that’s just extra work piling up, and, besides, there would be no integrated security (in the sense that it’ll be for the report developer to add query conditions to do data access validations)