Before we begin using the reports designer in wink, we first need to make sure that we have some dsns
or data sources created for our databases.
So we're going to make sure we go to the ODBC data sources.
Now we did this earlier in the course and we created a DSN and called it my DB class DSN.
And if I hit configure we can look at it.
You can see it's using the local SQL server.
If I hit next and next, you can see it's looking at the battery code database.
So we could use this DSN on our report.
And what I want to do is just go ahead and create a new one for our production.
Let's use our SQL server driver.
I'll hit finish.
This is going to be our production DSN and the server.
I'm just going to put dot backslash CC.
That's going to be the local name.
Of course you could put the computer name backslash CC.
We'll hit next and just leave this at default we're going to use windows NT authentication.
And then our default database for this DSN is going to be our production database.
And then we'll just click finish and hit test.
And it looks like it was able to connect.
So now we have two dsns that we could use for our report.
So I'll just click okay.
Now let's go double click on Reports Designer.
And if you haven't used this designer it's kind of dated.
It is the way that graphics designer used to look, and it's got some similar tabs.
And if you've never used this before, when we come back and design our layout, I'll show you a few
things we can do.
Things like have a cover page, we can have a report contents, we can have a final page, and we have
some limited way to change properties and colors and things like that.
But what I want to do very quickly is introduce you to the database related objects in here.
And once you see how these work, you'll get a better idea of how we're going to do our report content.
So under ODBC databases this is under Dynamic objects.
On the Standard Objects tab you've got a database field and a database table.
So a database table basically allows you to just create a query.
And the results will be printed out in this table itself.
Paginating.
So you don't really have to make it this big if you don't want.
And we're going to double click on this to bring up the properties dialog, and you can make some changes
to your color and font and things like that.
But on the connect tab this is where you define how you're going to link to the database.
So I'm going to double click this link and it's asking for an ODBC data source.
That's why we had to go create our 32 bit DSN.
And we have two here.
We can use my DB class DSN which goes to the battery code database.
We also have our production DSN which goes to our production.
So I'm just going to choose my DB class here.
Now if we had a username and password we could put those here.
Notice that each of these parameters you could also just define a tag.
And when this report is previewed or printed, it will pull this information from a tag instead of what
you print in.
And that makes it very flexible.
Now the SQL statement here we can just put in any valid SQL statement.
So let's say that we wanted to show orders, select star from orders.
And I could just hit test here.
And you can see it does connect to that database.
And it does give us seemingly valid data from this.
So I could put a join statement.
I could put an aggregate put anything in here I wanted.
And as long as it was valid then it's going to fill this table.
Now you can add tags directly into the SQL statement instead of putting the entire SQL statement in
a tag.
So let's just say the order ID is equal to.
And then you could do insert tag.
And if you had a tag that had your order ID, you could just pop it in there.
I realize that's not an order ID, I just don't have one for that.
So in runtime, which we're not in runtime right now, it would pull in the value of this order ID and
put it into this where statement.
That way I don't have to write a script in order to build a SQL statement and then put the whole thing
in a tag.
So I'm just going to get rid of that.
And then if we want to display column headings, we can the next thing that we have is just a database
field.
So let's say that you wanted to have in your report just like a count of the orders.
Then you could put this down here, do the same thing.
We'll do my DB class DSN and we will do select count from orders.
And if we do a test then you can see we got 494.
So then of course we could decorate this by using our static text, making labels and things like this
and do that.
So.
But we're not going to build our report just yet.
What I want to do is now go into SQL server and build us a nice join statement that shows all the information
from our orders, and then we'll come back here and make a nice layout for that data.
And then we're going to build a screen where you can enter data and generate reports from this.
So now let's go back to SQL Server Management Studio and let's figure out what data we want in our reports.
So we can kind of create a nice layout.
I'm just going to go back to something we did in the last module.
And that was our join examples.
And here we have a nice statement that's looking at our orders and filling in our customer and our employee
and things like that.
So I am just going to highlight this hit execute and we get this nice table with our orders our customer
name, order, date, employee description and quantity.
Now for this particular report we would probably want to do it based on time.
Let's say that we printed out every day and we get the orders for the day or orders in the last hour,
things like that.
So on the order by statement, we want to look at our order date first and then that way these are ordered
by the date.
And for our report we would probably want to put in a Where clause.
And if we did it by order date between and I'm just going to plug in some numbers here.
I'm just going to copy this right here.
And remember we have to put this in single quotes.
And I'll just do an and and then I will copy the same thing.
And let's just say it's between 2000 and 1 two four and 2003 four.
And of course I need to move my order statement down before we do this.
and let's just highlight and see what we get, okay?
We get some records here.
So what we're going to do is we're going to use this SQL query.
But then we'll probably automate these values here with tags just to show that we can change this with
tags.
And then we'll build us a screen to fill in this information.
And then we'll just test it in runtime.
So I'm just going to grab this SQL query here and we'll go back to the reports designer and design our
report.
Now before we go into Report designer we need to go create a couple of tags for our begin time and our
end time.
And so I have done that.
I've created a group called reporting.
And I just made two string tags, a begin date and an end date.
And notice that my start value for my begin date, I put a start value of 2001 two four and then for
end date 2001 three four.
So that's February the 4th through March the 4th.
And we're going to use those tags for our where statement.
And we're going to use the tags in place of hard coding these two dates here.
So we're going to go back into our layout editor and create a new layout.
And I'm going to right click and go to properties.
And under our geometry I'm going to set our orientation to landscape to kind of make it wide.
And so what I'll do is on our database table, I'm just going to kind of put it in the middle.
And remember again I don't have to size this big because it is self paginating.
So I'm just going to kind of make it the width here, double click it.
And then under our connect tab this is where we do our database link I'm going to double click this
choose our database source.
And then for our SQL statement I'm just going to paste our SQL statement right in I'll hit test.
That looks good.
And I am just going to replace the dates.
Notice that I'm keeping the single quotes.
Those have to be there, but I'm going to hit insert tag and notice how it's blank here.
This is kind of a bug.
I'll just select a different group and then come back to this group.
Select the tag that I want begin date.
Notice how it puts the dollar sign as place markers to mark the beginning and ending of the variable.
And then I'm going to do the same thing here leaving the single quotes.
And we will do our end date here.
Now I can't do test SQL right now because we are not in runtime and it's just going to give us some
error.
So I'm just going to call that okay.
For now we're going to test it in a few moments.
And then let's go put some static text.
So I'm going to put a header.
Notice how the icon x is out when I go up in this header.
And you can change the properties of this.
This is going to be our action orders report.
And then I am going to make this a little bigger.
We'll do our font size drop down.
So I'll make this pretty big.
And we could do our text color if we want, give it say a blue color.
And then I'm going to go to properties and under line weight I'm going to make that zero that gets rid
of the border there.
And then let's say that we want to have a count of how many orders are in the report.
We could go use the database field.
And I'm just going to pop it there.
And I need to come back here.
And I'm just going to copy this SQL statement because it's got our placeholders in there.
So just control control-c that and then I'm going to double click this here and I'll paste it in here.
And I'm going to get rid of this.
And then on our select it's just going to be select Count Star.
That's.
So we're going to use our aggregate from our orders table.
We don't need all of our joins.
We just need our where statement.
And we don't need our order by.
So this is just going to give us a count of the number of records that's going to be in our detail screen
down here.
And of course we need our data source.
So I'll do my DB class DSN and click okay.
And then we're just going to kind of clean this up.
This is going to be a little smaller.
So I can use my arrow keys to kind of move this around.
And then I'll choose a static text and I'll just do number orders.
We don't have all the nice alignment tools that we have in Graphics Designer in here.
They haven't really updated this editor in a while, but we can do a pretty good job.
Now, let's say that we also wanted to print out the date and time under our system objects.
We do have a date and time and I'm going to click it.
Watch what happens when I click this.
I get this error.
It says no system objects can be inserted in the dynamic part of a report.
That's a little confusing, but what they mean you've got to be in the static part or static mode.
And it's a little non-intuitive, because when you go to your static mode, all your dynamic objects
disappear.
So I'm going to just kind of put the clock right around in here.
I'll go to my static mode, I'll grab a date and time and just kind of put it out here and kind of make
it nice and long.
use my arrow keys, and then I'm just going to have to switch and say, I can't move it because you
got to manipulate this in the static mode.
So I'll just kind of move this over here.
There we go.
So there's our time.
And then let's say that we want to show our from and two times.
So I'll do another static text.
And this is going to be from and I'll move this down here I'll probably need more room than that.
So let's just move this over and on my properties.
I am just going to get rid of the border on that.
And then if we want our tag information, I'll just choose tag here.
And it's going to be from our begin date.
Give it a little bit more room here and I will paste this and put two here.
And still I think I need to back these up some.
This can be a little smaller.
And then here I'm just going to control C control V.
So they're the same size.
And just kind of try to clean all this up.
If I double click this under miscellaneous.
Your output value is where your tag goes.
So I can just double click this and go to End Date here.
And then I'll just use my arrow keys to kind of just clean this stuff up.
There we go.
And then I am going to go into our static mode, pull this kind of back out and that's decent.
I'm going to save this as my production orders underscore E and you hit save.
And so now we have a layout.
And so I'm just going to minimize this.
And now we need a print job.
So a highlight print job I'll right click do new print job.
And you see it jump around a bit.
And basically it has created a new print job down here.
It doesn't really tell you.
So you just kind of have to know I'll double click this and we're going to call this Production orders,
and we're going to associate it with our layout that we just created.
Now the dialog.
This means it'll show a dialog when you try to print it, and you can put in your tag information or
what other object information that you dynamically assign.
We're not going to do that because we do have our tags.
So I'm just going to click okay.
And we have the ability to preview this report now.
But the problem is we're not in runtime.
So it's just going to give us an error.
So I'm just going to take this project to runtime.
And just to remind you our tags do have start values in them.
So they already have that information and they should run.
So now we're in runtime.
So here's our production orders I'll right click and do preview and it will come up.
And this is the cover page.
We forgot to turn that off.
But here's our date and time.
We have 32 orders.
It's from 2001 two, four.
And then we can go to next.
Here's our next page.
So it automatically gave us a page break.
So I'm going to just close this out.
And we'll go back to our layout editor.
And I will right click and go to properties.
I'm going to turn our cover sheet off.
That way we don't have that blank page at the front.
And just save that And then we can go back to our print jobs.
Right click, do a preview and notice that we don't have the blank page up there.
And we can see all of our pages.
So now we have a nice production report.
Of course we can clean this up quite a bit, but you get the general idea.
In the next section we're going to create a page where we can enter in data into the tags and generate
a report based on the dates that we want.
But in the meantime, you're going to go through an assignment where you're going to create a report
layout and a print job and duplicate what we've done here in this video.
So I'll see you in that assignment.