6.3 Excel Report Example

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.