Hey everyone, Chris here from the Support Team,
I wanted to create a quick video to demonstrate how you can use the Financials Web Services for Reporting.
You don't have to have any developer knowledge to make use of the OData v4 M points,
you can pull data into Excel or Power BI and build reports right now.
So let's take a look at the Web Services page.
The Web Services page is where you can expose pages queries
that you want to be able to pull information out of Financials
and down to Excel or Power BI to build reports.
It's very easy to do, you can use these links over here
and you can pick new pages to expose depending on the data that you need to pull
and the reports that you want to build.
Let's start of by taking a look at the Sales Document lines
and Sales Documents Web Services.
First thing I want to do is make sure
that your user has a Web Service access key generated,
so let's navigate to the user screen,
I'll select my user and what you're looking for here
is that you have a Web Service access key for the user generated,
if you don't, you can use this button at the top to generate one,
but you will want to have a copy of your user name here
and your Web Service access key here,
as these will be used as your user name and password when you go to Connect to the Web Service M Points.
Now that I have my user name and password, I am going to navigate to Excel.
In Excel we can go to Data, Get Data From Other Sources,
from OData Feed and this will pull up a prompt asking for the URL.
Back in Financials I will select my Sales Documents,
OData v4 URL, I'm going to copy that shortcut,
come back to Excel and paste that in.
Now it's going to come up for authentication.
For the authentication we'll go to Basic
and we will use the password that we just got off the user card.
Once I paste that in here and hit Connect,
we should be getting a preview of the data that we are pulling from that OData feed.
There we go, this is our purchase header information, I'll go ahead and load that,
I'm going to navigate to a new sheet and do the same thing, I'll go to Data, Get Data, From Other Sources, From OData Feed
and this time we will copy our Sales Document Lines OData v4 Feed URL,
I'll to plug that in and this time Excel will remember our credentials
and should just load up the preview immediately.
You can edit here if you want to, I'm just going to load it all in
and now I have a refreshable set of data,
header and line data that I could slice and dice and build reports of in Excel,
if you have an Excel power user that you can give this to,
a refreshable report, they can start making reports of this immediately.
Let's take a look at another example and how we can add new web services.
I once had someone ask me if there was a report that showed you the Inventory on Jobs versus the Inventory quantity on hand,
they wanted to be able to see if they were going to have enough inventory on hand to fulfill jobs in real time.
I don't think we have a report like this,
but I said we can build one of the web services.
On the Web Services page, if you want to add a new page,
to expose that for building reports you can do so by using this Create Data Set wizard.
Now, for the report that I'm talking about we will need to have Items page and Job Planning Lines page exposed.
Items page is already exposed, so we just need to expose the Job Planning Lines.
We'll just go to the wizard to create a new data set, this Data Source ID section;
you can click the ellipses to do the lookup,
and I can do a search here on 'job',
I'll do 'job', and I then should be able to find the Job Planning Lines page, here it is right here,
so that's the one I'll add, give it a name
hit Next and then it will allow you to filter the data that you want to pull,
this doesn't really matter, so we'll just publish it.
It finished, and I should get a Job Planning Lines web service published now.
Here it is right here.
So now I have Items and Job Planning lines, I should have the Web Services I need to build the report,
so let's jump into the Power BI desktop,
I am going to be pulling the data into Power BI desktop in almost the exact same way that I did with Excel.
I am going to Get Data, OData feed, and I am going to grab my URLs, we'll go with Items,
plug that in,
and then it will ask for credentials where we will use our username and the web service access key
and this should give us a preview of the data just as Excel did;
there's my Inventory and my Quantity on hand.
Now, I know the data that I need to build this report, so I'm going to add it,
because I really only need to bring in the Number, the Description, the Type and the Inventory Count,
I don't need any of this other data coming in, so we can simply remove those columns.
Now this makes the query a little more clean if I'm not pulling all that data down that I don't need,
the query should considerably run faster, so let's close and apply that.
Now we can get the data for the Job Planning Lines the same way,
I'll just jump ahead here to save time,
so here I am with my Job Planning Lines data,
I'm just going to load this editing to save time;
I now have the queries that I need to build my report;
the first thing I'm going to do is to rename my queries so that they're easier to identify,
so I will rename this one to 'Items'
and I will rename this one to 'Job Planning Lines'.
The next thing I am going to do is setup a relationship between the two sets of data,
I can go to the Manage Relationships button here at the top and set up a new relationship
and I want the relationship to be between the numbers,
so the Item Number and the numbers on the Job Planning Lines.
Now Job Planning lines can contain Resources, Text or Items,
so if I have an Item Type on the Job Planning line it should have an Item Number here,
so I do want those to tie together with the One to Many relationship.
Next thing I am going to do is rename my Quantity field to Job Quantity,
just so I can differentiate that, to know that that's my Job Quantity and not Quantity on hand.
Now let's start adding some columns from the items to our report: Description, Inventory, to start off,
I can see now the full list of my Inventory and how much I have on hand,
now let's add the Job Quantity column.
So now it filters down to just to the matchup and both have values;
I do have a blank one here which I can easily remove by setting a filter,
if I go to Description and Select All and Remove Blank, that will take care of that.
Now I only have inventory items that are listed that are actually on.
Job Planning Lines.
So this is fine, this is showing me how many I have on jobs and how many I have on hand,
but if this got bigger, it would be harder to read, so let's get a third column going,
we want to calculate a column that actually shows us the difference,
so I'm going to right click over here on Items, I'm going to add a new measure;
now, the measure that I'm about to put in here I'm just going to copy paste in,
I'm not an expert on creating measures in Power BI,
but I was able to create this one after about five minutes of looking on Bing,
essentially all it's doing is subtracting the Job Quantity from the inventory QOH
and if we add that, it will help;
if we add that we know get a difference as a third column,
the problem is it added all the items back, not just the ones that we have on jobs,
so let's do another filter
and we will show Job Quantity only where it is greater than zero
and apply that filter and now we're back to see.
So this is great, this is fine.
But the final thing that we would want to do is maybe add some kind of a visual indicator
for when we have a negative difference,
so let's go over to the difference value and do conditional formatting
and we'll do a diverging, we will do the lowest value of zero
and then make it a reddish color I guess,
middle value can be one and the highest value can be nine
and we'll hit OK
and we don't see anything changed and we shouldn't,
because we don't have any negative values,
but, if we go into Financials now
and add some more tables or some chairs to a job,
we should see that change.
So, let's go to one of our jobs and you can see how this updates in real time;
going to this job,
we'll go to the Job Planning lines and instead of eight BERLIN guest chairs,
we'll do a hundred and five BERLIN guest chairs
and I can immediately go back to my report,
hit Refresh and we should see a change.
Now I have a report with a visual indicator allowing me to know if there's a problem
that I am going to have a negative inventory based on the number of chairs I currently have a sign on Jobs.
Now, you can get columns filling in which job is on,
obviously there's a lot of power here, for basic, ugly reporting this is just an example.
You can build up as many report as you want, put them on a Power BI Dashboard,
give those to your users to do their job more effectively.
I hope this video helps you to get more value out of the Financials product
and as always if there is any questions you should head over to the community forms and ask away.
Thanks!
For more infomation >> Skype for Business Live Stream - Duration: 56:02. 
For more infomation >> Manchester man rides 625 miles to raise money for cystic fibrosis - Duration: 0:40. 

For more infomation >> Gnarles Barkley - Crazy (Performance for David Letterman) - Duration: 4:02. 

For more infomation >> Camping Out For Madness Tickets - Duration: 2:16. 



Không có nhận xét nào:
Đăng nhận xét