Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back to module six. Sequel in applications were currently in lesson to excel.
00:06
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.
00:13
And this lesson we're gonna go ahead and set up a simple Excel report
00:18
that shows the numbers and creates the charts
00:21
that we, ah used in power bi I as well.
00:25
So let's go ahead and get started. Go ahead and open up, Excel.
00:31
And I'll have this query available for download with this lessons materials as well.
00:36
So I'm gonna open up a blank workbook.
00:39
I'm gonna go ahead and
00:41
go to the data.
00:47
Okay, There it is. I don't know what was going on. It was great out for just a second
00:54
from O D B C.
01:02
That shouldn't take too long
01:03
on. We need to select
01:06
the mice equal name that we provided it when we set up the O. T B C driver for use in Windows.
01:12
I called mine local my sequel
01:15
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
01:23
change,
01:26
actually. We need to get rid of that.
01:30
Mmm.
01:32
We're actually going to change this, Cleary just a little bit.
01:36
CASS is signed.
01:38
So we're taking what would be a bar Char.
01:41
Let's make sure this runs.
01:44
Sure got that right.
01:45
And we've made it into an imager.
01:47
Has been period. Now, when you're moving between applications, sometimes you will have trouble.
01:53
Uh, doing what I like to call juggling your data types
01:57
because the way strings air handled from one application to the next can change, and sometimes that can present a problem.
02:05
So in the XL query book, I want you
02:09
do a less than an equal to so that I could go in between two parameters
02:15
and that was difficult to d'oh
02:17
with the fin period when it was of Arch are within excel.
02:22
But it's really easy to dio if I make it a number instead.
02:27
So you'll see what I'm trying to do once we get into excel and we're using this query.
02:31
So Mr to copy what I have,
02:35
I'm gonna pace it into the Siegel statement, and I'm just going to click. Okay,
02:45
I'm gonna wait for that to load.
02:46
It's giving me a preview of the data, and that looks good.
02:50
So I'm going to click on the error the narrow down and select the load to Option
02:57
Load would just put it into the XL worksheet load to give me a couple options. And we're going to select
03:04
the pivot chart that's gonna give me a pivot table and a chart
03:07
I'm gonna select. Okay.
03:13
And there's our data.
03:15
Someone go ahead and select the salesperson CD, the name and the amount.
03:21
And there's the data for all our salespeople.
03:25
The right click on the chart and select change chart type.
03:30
We're changing to the pie chart that we used in power bi I or very similar.
03:36
We have a couple different options. You could do three d. I'm just gonna do to d
03:38
it. Okay,
03:39
there's that exact same pie chart
03:43
that we saw in Part B. I are pretty close to it anyway,
03:47
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
03:55
and
04:00
actually, I think we need the right click
04:02
or will double click on Query one
04:05
and that will open up the power Query editor, which is where we are trying to get to
04:11
going expand the queries pane
04:14
and I'm going thio
04:18
find the parameter
04:20
option.
04:25
Manage parameters. That's what we want.
04:29
Select new. It's a start,
04:33
period.
04:38
I'm gonna make that a decimal number.
04:42
Go ahead and start at 2017 01
04:46
It Okay,
04:47
actually, let's go ahead and add the other one too.
04:50
And, period,
04:55
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,
05:00
it will not generate results. And you will get a generic query air, which is very hard to
05:08
or is not as easy to decipher is when you're writing just the sequel code,
05:12
So decimal number.
05:15
I must say the current value is
05:17
to 17. 12.
05:21
Now we go back to the query, I'm gonna select the fen period
05:27
column. I'm going to select the down arrow. We'll go ahead and on, select all. I'm just gonna select one
05:32
that's gonna give me these filtered rose. I'm gonna select the gear for the filter Burrows
05:40
and I'm going to put
05:42
is greater than or equal to
05:46
parameter
05:46
start period
05:48
and
05:50
is less than or equal to
05:54
parameter
05:55
and period and select. Okay,
05:59
now we have a parameter sized or we have a query with parameters in it.
06:03
Something close and load
06:06
looks like our numbers changed. So now we have a query where we can change two parameters and affect our results.
06:15
Now, if we want to change the parameters as it set up this way, the way we have to do that, unfortunately,
06:21
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.
06:30
And,
06:32
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
06:41
clothes and load,
06:43
and unfortunately, that doesn't refresh the data. You actually have to click on the refresh all button.
06:49
We could see the data changed,
06:51
So now we have a power query and excel
06:56
that hasn't up a pivot table that updates and a chart that updates based on the pivot table data
07:01
that accepts a query with parameters to the mice equal database,
07:08
allowing an individual
07:10
to update their data from an Excel spreadsheet.
07:13
Well, that's not too bad.
07:14
I hope you're able to get this working, and I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor