6.3 Excel Report Example
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
Already have an account? Sign In »
7 hours 36 minutes
I welcome back to module six. Sequel in applications were currently in lesson to excel.
And the previous lesson we set up the my sequel Oh, DBC driver that we needed to use in excel to connect to our my sequel database.
And this lesson we're gonna go ahead and set up a simple Excel report
that shows the numbers and creates the charts
that we, ah used in power bi I as well.
So let's go ahead and get started. Go ahead and open up, Excel.
And I'll have this query available for download with this lessons materials as well.
So I'm gonna open up a blank workbook.
I'm gonna go ahead and
go to the data.
Okay, There it is. I don't know what was going on. It was great out for just a second
from O D B C.
That shouldn't take too long
on. We need to select
the mice equal name that we provided it when we set up the O. T B C driver for use in Windows.
I called mine local my sequel
go down to advanced options and we're gonna take the query that we had and we're gonna just paste it in there. Now. I've made a small
actually. We need to get rid of that.
We're actually going to change this, Cleary just a little bit.
CASS is signed.
So we're taking what would be a bar Char.
Let's make sure this runs.
Sure got that right.
And we've made it into an imager.
Has been period. Now, when you're moving between applications, sometimes you will have trouble.
Uh, doing what I like to call juggling your data types
because the way strings air handled from one application to the next can change, and sometimes that can present a problem.
So in the XL query book, I want you
do a less than an equal to so that I could go in between two parameters
and that was difficult to d'oh
with the fin period when it was of Arch are within excel.
But it's really easy to dio if I make it a number instead.
So you'll see what I'm trying to do once we get into excel and we're using this query.
So Mr to copy what I have,
I'm gonna pace it into the Siegel statement, and I'm just going to click. Okay,
I'm gonna wait for that to load.
It's giving me a preview of the data, and that looks good.
So I'm going to click on the error the narrow down and select the load to Option
Load would just put it into the XL worksheet load to give me a couple options. And we're going to select
the pivot chart that's gonna give me a pivot table and a chart
I'm gonna select. Okay.
And there's our data.
Someone go ahead and select the salesperson CD, the name and the amount.
And there's the data for all our salespeople.
The right click on the chart and select change chart type.
We're changing to the pie chart that we used in power bi I or very similar.
We have a couple different options. You could do three d. I'm just gonna do to d
there's that exact same pie chart
that we saw in Part B. I are pretty close to it anyway,
So now what I'm going to do is a massively going to add some parameters and to do this, I need to go into the data
actually, I think we need the right click
or will double click on Query one
and that will open up the power Query editor, which is where we are trying to get to
going expand the queries pane
and I'm going thio
find the parameter
Manage parameters. That's what we want.
Select new. It's a start,
I'm gonna make that a decimal number.
Go ahead and start at 2017 01
actually, let's go ahead and add the other one too.
make that a decimal number as well. You want to make sure to get the type correct here. If you accidentally select one of the other ones,
it will not generate results. And you will get a generic query air, which is very hard to
or is not as easy to decipher is when you're writing just the sequel code,
So decimal number.
I must say the current value is
to 17. 12.
Now we go back to the query, I'm gonna select the fen period
column. I'm going to select the down arrow. We'll go ahead and on, select all. I'm just gonna select one
that's gonna give me these filtered rose. I'm gonna select the gear for the filter Burrows
and I'm going to put
is greater than or equal to
is less than or equal to
and period and select. Okay,
now we have a parameter sized or we have a query with parameters in it.
Something close and load
looks like our numbers changed. So now we have a query where we can change two parameters and affect our results.
Now, if we want to change the parameters as it set up this way, the way we have to do that, unfortunately,
because it's a little bit jarring for user's who aren't used to this is we actually have to double click on the parameter.
for example, we have 2017. No one here to go ahead and drop this down to 20 1701 as well. I was. I'm gonna click
clothes and load,
and unfortunately, that doesn't refresh the data. You actually have to click on the refresh all button.
We could see the data changed,
So now we have a power query and excel
that hasn't up a pivot table that updates and a chart that updates based on the pivot table data
that accepts a query with parameters to the mice equal database,
allowing an individual
to update their data from an Excel spreadsheet.
Well, that's not too bad.
I hope you're able to get this working, and I will see you in the next lesson. Thank you.