Dynamics Corner

Episode 324: In the Dynamics Corner Chair: The Excellence of Business Central's AI Analysis Views

• Tasha Anspach • Season 3 • Episode 324

Share episode

In this episode, Tasha Anspach joins Kris and Brand to discuss various topics, including personal anecdotes and a detailed discussion on using CoPilot in Business Central for analysis views and Excel reporting. The conversation thoroughly explores using Excel reports and pivot tables within Business Central, including creating, filtering, and analyzing data. It also delves into the functionality of analysis views, data sets, and the integration of Excel with Business Central.

Connect with Tasha on LinkedIn (https://www.linkedin.com/in/tasha-anspach/)

Send us a text

#MSDyn365BC #BusinessCentral #BC #DynamicsCorner

Follow Kris and Brad for more content:
https://matalino.io/bio
https://bprendergast.bio.link/

Speaker 1:

Welcome everyone to another episode of Dynamics Corner, the podcast where we unearth the excellence of Business Central. Whether you're a seasoned expert or just starting your journey into the world of Dynamics 365, this is your place to gain insights, learn new tricks and hear from industry experts. I'm your co-host, chris.

Speaker 2:

And this is Brad. This episode is recorded on June 5th 2024. Chris, chris, chris, we're coming up with half a year done. Half, that's quick. This year is moving, it's moving quickly and I liked what you said there Excellence, see, I like that Excellence. With us today, we did learn some excellent tips and tricks for using Excel analysis views and pivots within Business Central. Today, we had the opportunity to speak with Tassar Ansbach. Good morning, hi. What are you doing? It's nice to see you. Did you switch your office?

Speaker 3:

Fantastic. I just move things. I get bored, so then I move all my furniture around. It's an awful habit.

Speaker 1:

I think you shifted.

Speaker 3:

You shifted, I did.

Speaker 2:

And I still thought, when you joined that with the chair, I thought you were wearing a. What do they call those With the princess? And they wear the, not the veil. What's it called With the thing in the back? You know, when you have something on your head and then it has, like the.

Speaker 3:

Like the train thingy. Well, yeah, I don't know what it is but I thought you had what is the word?

Speaker 2:

I don't know. See, chris, do you know? I don't know what you're talking about. You don't Do you know when, like someone is wearing. They have like something on their head, like whether it be a crown or another device, and then it has like silk or something hanging from the back, a cloth. See what is that called.

Speaker 3:

It's a veil right.

Speaker 2:

Is it the same thing as a veil? I was going to say a veil, but I thought a veil went over the front.

Speaker 1:

It's like you're wearing a tiara with a veil on the back.

Speaker 2:

Okay, so it is a veil, then We'll call it a veil. Okay, but do you want to know what I bought and I have yet to use?

Speaker 1:

yes, look at this. Oh, those are the best things, man, I bought and purchased a bug assault.

Speaker 2:

I have yet to use it other than one shot into the sink, into a bowl of water, to see what it comes up with, and because we have some crazy bugs here. I had one bug that I saw, but I left this in the garage because that's where I mostly see the bug. So I figured if I'm out there it will be available. Yeah, so I can pick it up and just use it. Because what happened is I saw something in here in the dining room and this was in the garage. So by the time I went to the garage and came back, it was gone.

Speaker 1:

So now I carry this with me everywhere you need a holster, so you need a holster for it.

Speaker 2:

I will let you know after my first shot, but I've had this for about a week and I have yet to use it. You know, I even went outside to try to find bugs and I usually see 4.6 million bugs a day. Yes, 4.6 million. I went out for like 20 minutes and I didn't see one they probably saw your amazon box.

Speaker 1:

And then they're like ah, I'm out of here, so I think just purchasing this has now removed all of the bugs Dude that would have been helpful for you. In that one podcast recording where you like looked to your right or made some noise, and I remember you texting me saying there's a massive spider up against my right side, and it was during the recording, which is hilarious at the same time.

Speaker 2:

Yes, yes, there was a huge spider on the wall and I was watching it and I didn't want to get distracted doing the recording, which is hilarious at the same time. Yes, yes, there was a huge spider on the wall and I was watching it and I didn't want to get distracted during the recording. Oh God and then, it disappeared and I have no idea where that spider went. But there's some large spiders here.

Speaker 3:

But good morning the house pet now.

Speaker 2:

Well, I go a couple ways with spiders. I mean, we have some vicious spiders here and some large spiders, but spiders keep the other bugs out, so you have to have a balance. And the other thing that I'm a big fan of in the backyard dragonflies. Dragonflies are very good for your yard, so I'm doing some things to try to promote the dragonfly population in my area, along with bumblebees, hummingbirds and butterflies. With that good morning, I'd like to thank you for taking the time to speak with us. I am excited to talk with you about what we're going to speak about, but before we jump into that, would you mind telling everyone a little bit about yourself?

Speaker 3:

telling everyone a little bit about yourself. Yeah, my name is Tasha Anspaugh and I have recently went independent and my company is Dynamics Maven Consulting LLC. So I do a lot of contract work, focusing more on business analysis, training on Business Central and trying to streamline processes and give end users the power to do things themselves without having to call a developer or have a partner help them with everything that they need. So that's me.

Speaker 2:

That's great. Congratulations on your independent adventures, and I think it's great to have individuals such as yourself available for customers as well as partners to use as an additional guide or implementation specialist to help them with their journey, because I think that's important now with the popularity of the product and a lot of the information that is on there, and with that we're going to have a great conversation today. Great title, which I stole from one of your sessions at a recent conference. I don't know if you picked up on that or not, but do you remember the topic?

Speaker 3:

It's unearthing, the excellence.

Speaker 1:

Yes, yes, excellence, excellence, it's unearthing the excellence. Yes, yes, excellence the excellence.

Speaker 2:

I've been looking forward to having this conversation with you, to talk with you about all things business central analysis and I took the title from one of your sessions at a recent conference From Spreadsheets to Superpowers, unearthing the Excellence of Business Central Analysis Views, pivots, excel Reporting and other stuff. Other stuff the pivot tables I just went through with somebody the use of the analysis views and the pivots. That, to me, saves so much time and affords a user of the application much more flexibility to analyze and review their data. Because I was just speaking with someone and they said well, I'd like to see that as a summary, I don't want to see all those detail lines. And I just went and they saw it summarized.

Speaker 2:

It was a sales information summarized by customer, by item, and I could see the total for the quantity and the sales. And we didn't have to do anything and they exported it to excel and they were done where years ago we would have, you know, had to do something for them to, you know, have a consolidated view, whether it be a paper report or an Excel report. So with that let's jump into Tasha's tips, tricks and wonders for excellence. I like that excellence With analysis views, pivots, spreadsheets and more.

Speaker 3:

Okay, so first question.

Speaker 2:

You're asking me a question. Yes, okay.

Speaker 3:

Asking you a question have you seen the Analyze Features with Copilot? Yes, have you seen how to do it? Because, holy cowballs.

Speaker 2:

I have seen it, but if you have some tips and tricks, I would appreciate you sharing them with us, because just because I have seen it doesn't mean that I'm using it properly, nor does it mean I know all the tips and tricks. There's a lot of information flowing about the application, a lot of features being added at rapid pace and it's tough for everyone to keep up with it. So if you would share some of your analysis views with co-pilot tips and tricks, I would be excited.

Speaker 3:

Yeah, absolutely, how do I share my screen? Oh?

Speaker 2:

if you look at the bottom of the screen, there's a button called share.

Speaker 3:

Oh, that makes sense. That seems like a really good place for it. I promise Technology kind of.

Speaker 2:

No, it's a different platform than a lot of people use, I understand. We do have that question often.

Speaker 3:

Is this where we're going? Yes, oh.

Speaker 2:

So what you're sharing for us now is you have a Business Central online screen. We were logging in weeks to see our main. I call it dashboard when we log into our company.

Speaker 3:

Yes, I call it dashboard when we log into our company. Yes, so I think my favorite thing so far that I have been able to kind of play with and tinker with as far as the analysis feature is just the ability that, like a lot of times, people are like this is what I want, but I don't know how to drag and drop all of the features, so this is one that I threw together last night by using Copilot.

Speaker 2:

I just told it what to do, so you built an analysis or an analyzer. What is it? Analysis view is the old. Put together the tables and the fields and it creates a view Correct. This is what's the proper name for this Analysis view.

Speaker 3:

Yes, this is an analysis view that I've built with Copilot inside of Business Central.

Speaker 2:

So you told Copilot to build this. In essence, I call it a pivot table just for shorthand, because I think a lot of users are comfortable with the word pivot table from using Excel, and that's similar to what this is.

Speaker 3:

Yeah.

Speaker 2:

So you created a new analysis view using Copilot. How did you do that?

Speaker 3:

So they added the option up here, the little Copilot guy inside of any list view page, and all you have to do is click on it and it opens up, basically a prompt box and asks what you want to do. So what is the field layout that you want? You just tell it what you want. So if you just want to see your balance sheet accounts, so which information we analyzing?

Speaker 2:

here you're analyzing the general ledger.

Speaker 3:

General ledger entries Yep.

Speaker 2:

So we're analyzing the general ledger entries and you clicked on the. You started a new analysis view, or did you start it directly from the general ledger entries page? What's?

Speaker 3:

the first step Straight from the general ledger entries page. So I went in to general ledger entries, clicked on the cute little copilot icon and then selected create new view.

Speaker 2:

Speaking of that, do you know? Someone explained to me that the icon is supposed to be two hands, like in, like, holding each other like this, and that's what the copilot icon is. I don't know the validity of it, but someone told me that, and now, every time I see that, that's what I see. It's like, it's like not pure hands, but it, but it's like you know you think of it's two hands cupped together it's like two robots.

Speaker 2:

Oh yeah, it's like two robot hands with solid fingers, I guess but I don't know how true that is, or uh, or not, but that's what was explained to me. Okay, so now we open up the co-pilot. We have a prompt. Do you see it now?

Speaker 1:

It's like Wally hands. If you ever watch Disney and you know how he has that claw hands and just like does this.

Speaker 2:

Okay, so you opened up the co-pilot prompt dialogue from the general ledger entries page, and now what are you typing?

Speaker 3:

I'm going to type what I want it to create for me. So if I just want to see my balance sheet accounts, um, with document type and document number, it'll create that. You just push create and it does exactly what it thinks you're supposed to do once it prepares it. But it's creating that analysis for you.

Speaker 2:

You're just telling it what you want to see and it'll create that value for you.

Speaker 3:

So it creates a base analysis view with the information that you typed, correct. So if I want to add, amounts.

Speaker 2:

Oh, get rid of that. Okay, add amounts.

Speaker 3:

Yep.

Speaker 1:

So you can append to it, so you can start with analysis view and it says okay, I got the three fields I want. I can add more columns.

Speaker 3:

Correct and you can just keep adding to it. So keep adding the detail until you get it to look the way you truly want it to. So, again, keep adding stuff in there and then, once you like it, you just hit keep and it will show you all of your stuff. And I do have filters in there based on the fact that it is a balance sheet account only.

Speaker 2:

So if I want to delete, that filter. It will summarize those to zero for the period of time. I have a couple of questions on this. Yeah, I'm not certain. This is to both of you. Is there a cost to using this for a customer? Does anybody know the licensing for this, chris?

Speaker 3:

This is just part of the product right now.

Speaker 1:

Yeah, it's part of the BC, your BC Essentials license.

Speaker 2:

With your BC Essentials license you have access to use the Copilot Correct. Now analyzing general ledger entries, does it honor dimensions? Because dimensions are variable, right? So if you think you have the standard departments and projects, I think that's in the Cronus data. It's been a while since I looked at the dimensions that are in there. So on our general ledger, could we summarize by department and project?

Speaker 2:

I don't see why not, it's just filtering their dimension values, but the captions are dynamic, so is Copilot truly analyzing your data or does it know the table structure from Business Central See Well now I don't know, we should try it.

Speaker 1:

Yeah, we should try it. Yeah, you should try it, because you could ask dimension one or dimension two rather than department.

Speaker 2:

But a user may not know it's dimension one or dimension two.

Speaker 1:

They may know that.

Speaker 2:

Okay, I would like to see my financial information summarized by business unit, department, project, region or any other dimension that they have.

Speaker 1:

That is true. This will be interesting yeah.

Speaker 3:

So if we want to analyze general ledger entries, do you want dimension values as row groups?

Speaker 1:

Let's give it a shot.

Speaker 3:

Okay, and minor department.

Speaker 1:

I think you could even say group by departments or something like that too.

Speaker 3:

Let's group by department and project code yes, yeah. Okay.

Speaker 1:

Let's see what it gives you.

Speaker 3:

Oh, it can't push enter, it doesn't like that. Okay, let's see what it gives you. Oh, can't push enter, doesn't like that. But yeah, there's oh, go ahead.

Speaker 1:

I was going to say this is very helpful for those that it's easier to just type up what you're looking for than trying to scroll through all the fields and choices. Very helpful.

Speaker 2:

This would have been helpful for the phone meeting that I had prior to this call, because what I ended up doing was showing the analysis view on how I was able to drag and drop the fields and then do the summary. This would have been beautiful for me to demonstrate how you could simplify the creation of this analysis view.

Speaker 1:

So it did group it by your department code and project, because you asked for project as well, it's nested, so it does honor the dimension captions.

Speaker 2:

Yes, that is.

Speaker 3:

It is really cool.

Speaker 2:

That is really cool. It's a definite time saving. Now these analysis views you can save.

Speaker 3:

Yes, and it will automatically save them for you once you hit keep. So I'm going to pivot this one real quick with posting year and posting month as columns.

Speaker 2:

So you just typed pivot with posting year and posting month as columns and this goes through and changes the analysis view. See, we're progressing to where everybody can soon we'll just have to think. I mean because these are popular views that users often need to look at from the financial point of view, also from the inventory point of view, if you're looking at, or project point of view, if you're looking at other information from other ledger entries. Now these are viewed and now from here you can export this to Excel.

Speaker 3:

From here you can copy the link and share it.

Speaker 2:

Oh, so you can actually share the view.

Speaker 3:

The view yes. I don't believe you can Excel export it yet though, which is sad, but hopefully it's coming.

Speaker 1:

So you can give them the link to another user and then this view will be added to their view.

Speaker 3:

Yep, it will open up exactly what you're seeing right now. The other really cool thing is you can share this and then you can embed this analysis view inside of Teams for users who may not have a Business Central license. They just need to be able to see so if they're using that M365 license you can embed this and then they can just view it.

Speaker 1:

Like on Teams.

Speaker 2:

We just have one world, like all these products are becoming one. Soon I see that you'll be able to go into Excel and then open your Business Central data easily. I know you can link now with APIs and pull in some information, and I mean there are ways to do it. But I'm even saying I believe that it will be simplified even a bit more where you can do something like this in Excel.

Speaker 1:

They're living up to the name.

Speaker 2:

What's the name?

Speaker 1:

Business Central Central. It's everything right here.

Speaker 2:

Yeah, it is so it's going to be. Is it going to be Microsoft Office Central or Microsoft Business Central? That has everything.

Speaker 1:

It'd be Business Central Because, remember, they're you know're adding a lot of stuff around here.

Speaker 2:

They are what other tips and tricks do you have for us to live up to this name of our excellence in reporting?

Speaker 3:

Oh, didn't need to click on that. Have you seen the new excel reports that were pushed out with the newest release?

Speaker 1:

uh, I don't think I have, I don't know, in demo.

Speaker 3:

Let's see it okay probably gonna have to reshare a different screen, but we'll try it out. Okay, so it's downloading. It's generating all of my rows. I'm going to be pulling the trial balance and budget because I love budgeting as well, so let me. Oh, that's easy. It even has a button where you can change the share tab. Holy cow. Okay, so this will pull in everything, all of your entries, and then, once you have all of your data in there, it's still loading because there's a lot of rows. So it creates a couple different tabs for you. It has your trial balance and your budget data. It puts the dimension tabs in there as well, dimension tabs in there as well, but then also your caption data, and then so, when you come over, you have all of your information. I'm not sure why it's not loading because it hates me today, but generally it will retrieve all of your data and it will show you everything inside of the screen. So then you can filter, but it builds in all of the filters for you, which is really cool.

Speaker 1:

Wait a second. That is really cool.

Speaker 2:

So built in within the application. We now have an Excel report that you can create. I don't even want to use the word export anymore, and it already has the filters and slicers available within excel for you to further analyze your data it does that is neat, it's excellent

Speaker 2:

yes it is this. No, this is I'd be. You know several. I can't even remember the versions anymore because it's you know, we have the two waves a year with the updates in between, but to me now it's just one constant wave because there's so much coming out that I forget when it was introduced. But the Excel reports from the development point of view, we were able to create Excel reports, use them as custom report layouts as well as develop them and set that layout within the report to be able to create these already.

Speaker 2:

But now they're coming out with those reports as well, and they're just there With this. Is the data embedded within this report or is it linked somehow to your Business Central?

Speaker 3:

So this is all of your data that's linked.

Speaker 2:

Okay, so the data is embedded within the Excel workbook.

Speaker 3:

Correct, and you can refresh it, because it does have the query in there, so you can refresh all of that. And then, oh, look, now we're going to start. We're still loading, but you can go in, you can do the filters, select what you want, but it's all coming back from this specific budget screen here.

Speaker 1:

oh, there we go wow, that is so cool. So it created all your filtered boxes as an interactive menu.

Speaker 3:

All pulling yeah from over here.

Speaker 1:

Yeah it'll just filter and those are all pulling yeah, from over here. Yeah, it'll just filter and go through all of it. See, I didn't even know about this one. This is so much new stuff.

Speaker 2:

So for the new reports, do you know all of the reports that they've shipped with the latest release that work this way?

Speaker 3:

Yes, so they are all within. If you're in the Business Manager Role Center, you can go into the Excel Reports tab and all of the ones that say Preview. So you have your trial balance with budget trial balance, trial balance by period, trial balance in previous year age they are, which is really cool, especially if you have specific individuals who are responsible for calling those accounts receivable accounts. You can filter it by dimension, value or however you need to, so that way each individual person could quickly just have a list, which is really awesome. Um, and then you just have your customers, vendors and aged AP as well.

Speaker 3:

So do you have to do the same thing.

Speaker 1:

Do you have to enable this in feature management or it's just enabled? On the recent update.

Speaker 3:

I did not enable this. It just magically showed up one day and I got really excited, so I think it should be there for everybody, hopefully, Cool, I like magic. Me too. Auto magical is my favorite way to do things.

Speaker 1:

Automagic yeah, I like that.

Speaker 2:

I like that too. Okay, I'm ready for more. Okay, we talked about the analysis views, which now you can use with Copilot, yep, and then also now we talked about the built-in Excel pivot reports. I will call them, I'll use simple terms, for them for users to be able to export, or jeez, I don't even. I just said I didn't want to use the export, but they use Excel pivot tables to view their data, to report on their data. Yeah, so what are some other excellence opportunities that you can have with analysis views, tables excuse me Excel and pivots?

Speaker 3:

So some of the other ways I've seen it used or that I've helped people is trying to pull out like a filtered view that you're already looking at. So not looking at analyze, not looking at you know a pivoted table, but just your straight entries. So if we go into the find entries and we're looking at documents, let me just go in and find.

Speaker 2:

So now we're using the navigate. I used to call it navigate, but now we're navigating to find entries.

Speaker 3:

Yes, so we'll go in find all of our vendor entries, but you can basically anything that you filter in this list. So we're going in and we're creating our find entries and we're looking for the things that we're needing. However, it's going to pull back a lot of entries that you probably don't need. So if I wanted to look at just my vendor ledger entries, I click on this little guy. It'll open my vendor ledger entry screen and I'm going to get out of pivot mode and look at just straight entries and then I can filter this. So if I'm looking for just a specific project code, I'm going to filter and I'm going to look for just number 70 or whatever that project code is there. Demo data is awful, by the way.

Speaker 2:

Demo data is tough. I've been talking about that for a long time, but I think't. I think it should be a public initiative, not a corporate initiative, to have different demonstrator sets for different types of businesses.

Speaker 3:

Yeah, and this is probably me all breaking it, because I put in a plethora of things to try to break the system. Okay, so I filtered my project views. If I wanted to look at just invoices or something else along those lines, I can filter all of that information. But then if you go in, if you use the open in Excel function, it respects all of the filters that you have in on this page. Versus edit and Excel, it just opens up everything with every single field ever.

Speaker 3:

The open in Excel, though, that allows you to just open a spreadsheet with your built views, and a lot of people are able to use this for kind of like ad hoc analysis or ad hoc reporting. And then you can use these tables, because it comes as a table already and you can do all the cool things that you do instead. And I need to switch my sharing tab. Don't kill me, okay, so you can use all of your cool Excel tips and tricks that you want to in here now. So if you wanted to create a pivot table or like a pivot chart, you can do all of that with this data now that you've already just exported, with your filters in place, so you don't have to worry about filtering out your data. It just comes over and it's there.

Speaker 2:

And so when you do open in Excel, it's unidirectional, which means it's read only Correct. If you do edit in Excel, can you edit the data going back. What's the difference between open in Excel and edit in Excel?

Speaker 3:

So open in Excel just allows a stagnant view of everything, with filters, respected filters of what you are looking at on that specific screen. Edit in Excel you have to have special privileges for because it allows you to write back into the system. So instead of going through each individual record and like updating your purchaser on vendors or updating your salesperson on customer records, you can just export your entire list filter, find the values you want to change, make the changes hit, publish and it pushes it back into Excel or back into Business Central. And then this is just reading the data, so you can manipulate, analyze, do whatever you want to with it.

Speaker 2:

Wow, so we're pushing. So that analysis view is a combination of, basically, excel and Business Central data within Business Central, but if you needed to further enhance it, you could open your data in Excel or export the data for you to be able to then use some additional Excel tips and tricks. A lot of people like to use Excel for reporting. I wonder if it's a comfort thing, or flexibility thing.

Speaker 3:

So I feel like the majority of customers that I've worked with. I mean, they're like oh well, I'm using Excel spreadsheets and I'm never going to stop. Especially when I talk to project managers and accountants, Neither one of them are ever going to stop using Excel. So I've just kind of made it my methodology to enhance the way they're using it, because they're never going to stop. So we might as well just use it effectively, versus having a bunch of spreadsheets that don't talk to one another, don't talk to the system. So if we can teach them how to use them, to where it either feeds directly into the system or to where they can just quickly get their information without having to do any manual entry, that's what I try to do.

Speaker 2:

Do you know what would be a cool feature, what and I should have mentioned this before when we have conversations. Now you have the analysis view. I have to write this down because I'm going to throw this in the virtual suggestion box. So you have an analysis view, a summary of your data, to be able to click on it and say, say, create journal entry. Right, because now if you think of how accountants or other finance personnel use the spreadsheets, a lot will export the data, summarize the data and then do some sort of adjustment, accrual or some type of journal entry from the data they see. But if you can do that from the analysis views, chris, don't steal my idea. I know you will, but wouldn't. I think that would be great. Chris, you're going to see me share that momentarily, right as soon as we're done with this. I'm going to write that down.

Speaker 2:

I just had an idea that would be. We should almost have to pause this recording so I could submit that to the virtual suggestion box. No, we're not going to pause. I am excited. I love learning new tips and tricks about Business Central and some of the new features, as well as some of the old existing features. I know there's a lot of talk about what's new, what's coming and what's the latest and greatest, but there's also a lot of tips and tricks from years past that still are applicable today, so I enjoy having these types of conversations with everybody. So do you have more excellent excellence power tips and tricks for us?

Speaker 2:

I don't know if it's ready, but if it's ready you don't know well, then we'll do it live. I'm still looking for a spider or something, so I can use this.

Speaker 3:

I mean I feel like.

Speaker 2:

You'll see me walking around with this everywhere now, by the way, I'm just. Everywhere I walk in the house, I take this with me.

Speaker 1:

That is the best thing.

Speaker 3:

Oh Lord, have mercy. Okay, the only other really cool thing obviously is the report layout. So you have the ability to create your report layout. So when you go in and you export all of your data, you can build your Excel reports, kind of like I was just talking about, over on the vendor ledger entry screen. So if you build pivot tables inside of Excel, you build your charts, you build all your cool stuff, you can do that and then you can save that Excel view and then just push a button to update that data. So instead of having to go in and do it again, it's really just a refresh of an Excel template that you've already created.

Speaker 3:

Well is that over. So your own custom report templates inside of Excel so you can go in and create your report layouts. So let me, I think it's web services. No better way to do this than live.

Speaker 2:

Well, it's live, but recording I know Okay. So you went into web services and then now actions new- New document create data set. Okay.

Speaker 3:

So when I create my data set, this is my reporting data set up.

Speaker 2:

So reporting data set up Yep. Chris, did you know this?

Speaker 1:

I did not know this. Creating a new data set from web services is amazing.

Speaker 3:

So you can create new. You can create a copy of one that's already existing or you can edit an existing one. So I'm going to go ahead and create new, whatever you want the name to be, Okay. And then, what do you want to look at? So you give it a name and then the door to data source type. Words are hard. You can either look at a page or a query. So if I look at a page and I'm just looking for customers, I can come in and then search for what I'm looking for. So I'm going to select page object ID 22, my customer list.

Speaker 2:

I will never get over that like those numbers just need to go away. I remember I follow most of them from my life. Yeah, but it's just you know we're making so many advances and we get we're stuck with that our numbers, of ID numbers they're so much better though.

Speaker 3:

I was looking at Dynamics SL tables the other day and I was like holy cow. How did anybody even remotely know what any of this meant? How do you remember those types of numbers? It was ridiculous.

Speaker 2:

I mean exactly, I like customer, I like number, name, address. It's much easier. I know we can still use them and they're both basically in essence synonymous.

Speaker 1:

But you can't have conflicting numbers. It's helpful for someone new to business central. You just go by the name rather than just the numbers. So we were. We all remember who knows the numbers it's all the old guys that know.

Speaker 2:

old guys and gals, I guess you could say understand what those numbers mean 167 is jobs.

Speaker 3:

I know that.

Speaker 2:

Well, those used to be like the quick tips. What is the post and code unit? What is the check code unit? What does code unit 80 do? What is code unit 90? They usually had, like how you knew that somebody knew the information? What is table 3? 18, 25, 32, 81.

Speaker 3:

I can keep going oh god, it's funny because those were like normal conversations and I'm sure anybody listening was like is this the power ball? What are we doing here, oh lord?

Speaker 2:

that would be something play. What are the power ball numbers? Let's go through a business central posting routine. Whatever table numbers we hit, those are the Powerball numbers. Let's go through a business intro posting routine. Whatever table numbers we hit, those are the Powerball numbers Minus the leading digits if it's more than two numbers. Yeah, okay, so you're going through. I was giving you the opportunity to select your fields here. Sorry, I got sidetracked. Reporting data set. You're able to select the field, so it doesn't export or link all of the fields, it's just the fields that you want.

Speaker 3:

Correct. So go through, put all of the information in that you want. You can also do your dimensions here as well, if you want to. You can add filters, so if there's specific filters you want to look for for just individuals. And then, when you're done, you go ahead and hit publish. It creates your OData URL. You hit Excel and it's opening. It's copying the file over to OneDrive because again, everything's connected and playing with each other, and then from there it opens up, it has my information here and then it will load my spreadsheet. So now this is my living, breathing data set, connected from OData.

Speaker 2:

So that's a spreadsheet that you can share on OneDrive and use and the data will refresh off and within Excel. Do you need to go into Business Central again to access or use or do anything with this?

Speaker 3:

Or is it just a report now that you have? It is just a report now?

Speaker 2:

I don't mean just a report, but it's an Excel report that you can use. I mean, this is actually impressive, so it's not something that's just. Yep, look at that, so it refreshed and there's all my data.

Speaker 3:

So, since there is the connector, if somebody was in there making changes or posting transactions, creating new customers, whatever the case may be, I can come in and I just hit refresh and it refreshes the data for me.

Speaker 1:

Wow, this is awesome.

Speaker 2:

That right there, chris. Look at all those estimates that you do for doing like simple APIs to read data into Excel.

Speaker 1:

Yeah.

Speaker 2:

You don't need those anymore.

Speaker 1:

This is amazing.

Speaker 2:

I'd like to see how this works with the sales order.

Speaker 3:

Sales order.

Speaker 2:

Can you do the header and lines by just the lines?

Speaker 3:

I think we can do just lines. So if we go back, finish and then we will have to go in and create a new data set. So you said sales orders, right.

Speaker 1:

Sales lines yes.

Speaker 2:

Sales orders is a common one of sales invoices, but I didn't know if, like you know, you have the header detail. I didn't know if there was a way to even if it exported or linked the data flat, you know, to have the header and the detail information. I know the lines. Some of those do have the customer number and such, but maybe you could have a, you know, a link, a lookup or something to the name.

Speaker 3:

Sending that, putting the page ID off the top of their heads.

Speaker 2:

There we go the name, sending that, putting another page ID off the top of their heads. There we go. Well, the sales orders, let's see, but those aren't the sales lines. Would be all of the lines for the sales orders. Sales orders is a list of the sales orders.

Speaker 3:

Sales lines is 516. I think this is the one that you're wanting. So it has your document type, document number cell to you can put line numbers in there. What the type is. Description.

Speaker 2:

Yeah, I was looking to see if you could link to the header as well and just have like a flat data, I guess.

Speaker 3:

I haven't been able to figure that out yet, though, but I believe I read somewhere that the ability to join those tables is coming oh, okay, so I was reading correctly. But yes, but you can also do multiple data sets and then create the merge inside of the table, inside of excel, based on that one specific field as well, and that's something that you can do without having to have a developer, because I can do it.

Speaker 1:

I like this. So if you publish this right now, you had the Excel earlier. Did it give you an option to add values to the Excel, or was it just read?

Speaker 3:

So you can. It won't publish back into Business Central, but you can change your spreadsheet and do whatever you want to inside of that field, gotcha, and continuously pull new data if there's new data. Yes, yes.

Speaker 1:

Maybe they'll add, like you know, be able to add values back, write values back. That'd be amazing, yes, yes, I mean another good way to import data.

Speaker 2:

Well, with OData 6, I think you can push data back so you could create sales orders. But again, it's that you know those arrays of the header to the lines sometimes is the challenge based upon what you have. But this is great for reporting. I could see so many practical uses for this that you know. With the analysis views, it's really allowing the users to have opportunity to view and manage, but I don't want to say manage, but view and work with their data in an easier fashion to make proper business decisions.

Speaker 2:

I like it this is excellent.

Speaker 1:

I like it too it is.

Speaker 2:

It's an excellent tip, and we've had a bunch of excellent tips for. Business Central. Where did you go? Are you all done sharing?

Speaker 3:

Well, I feel like that's all of the Excel things right now.

Speaker 2:

Oh, okay.

Speaker 3:

Until I build you some really cool reports and then I have to like send them to you so you can play with them.

Speaker 2:

You could do that. I will look at them and play with them and provide feedback on what I think. Okay, you can do with what you wish that feedback, but you know sometimes Well, Miss Tasha, thank you again for taking the opportunity to speak with us today.

Speaker 2:

I did learn a number of things from that. I'm glad that you were able to share that information with us. As with each release, you know, I'm always looking forward to seeing what's new and it's tough to keep up with. So being able to talk with other individuals to have them explain it to me makes it a lot easier for me and easier to keep up, as well as with others. So we do appreciate you taking the time to speak with us.

Speaker 2:

If anyone has any questions or would like to learn more about your excellent tips and tricks for using pivots in Excel reporting or other business central training implementation needs, how would they get in contact with you?

Speaker 3:

You can find me on LinkedIn Tasha Anspaugh, T-A-S-H-A-N-S-P-A-C-H, and my link tree is all there.

Speaker 2:

Excellent, so we'll have to put a link to your LinkedIn in the show notes. You know, I've never said on spa.

Speaker 1:

No on spa, On spa I always say on spotch Just because.

Speaker 3:

I don't think anybody actually knows how to say my name. I'm pretty sure my husband just made up the way that it said. I don't think anybody actually knows how to say my name.

Speaker 2:

I'm pretty sure my husband just made up the way that it said I don't think that's a real thing. But oh, it's a real thing to make up names. I've spoken with people and I've looked at their names and I've said how do you get that pronunciation from those letters? And I just go with it. And a lot of oftentimes I mean, everybody knows me I give people nicknames because I'm not good with remembering names, so when something sticks, it sticks. But I've seen some interesting pronunciations for letters that don't even make the sound together. You know, but such is life, we do it. But again, we'll put a link to your LinkedIn in the show notes. We appreciate you taking the time to speak with us. We look forward to speaking with you again. Hopefully within the next wave, you'll have some more tips and tricks for us to share with everyone, and until then, ciao, ciao, awesome.

Speaker 3:

Bye, thank you Later.

Speaker 2:

Thank you, chris, for your time for another episode of in the dynamics corner chair, and thank you to our guests for participating.

Speaker 1:

Thank you, brad, for your time. It is a wonderful episode of Dynamics Corner Chair. I would also like to thank our guests for joining us. Thank you for all of our listeners tuning in as, and you can interact with them via Twitter D-V-L-P-R-L-I-F-E. You can also find me at Mattalinoio, m-a-t-a-l-i-n-o, dot I-O, and my Twitter handle is Mattalino16. And you can see those links down below in their show notes. Again, thank you everyone. Thank you and take care.

People on this episode

Head Shot

Kris

Host
Contributor

Brad

Co-host