1 00:00:05,540 --> 00:00:12,860 Before we begin using the reports designer in wink, we first need to make sure that we have some dsns 2 00:00:12,860 --> 00:00:16,280 or data sources created for our databases. 3 00:00:16,280 --> 00:00:19,970 So we're going to make sure we go to the ODBC data sources. 4 00:00:20,000 --> 00:00:28,910 Now we did this earlier in the course and we created a DSN and called it my DB class DSN. 5 00:00:28,910 --> 00:00:30,980 And if I hit configure we can look at it. 6 00:00:30,980 --> 00:00:33,950 You can see it's using the local SQL server. 7 00:00:33,950 --> 00:00:39,080 If I hit next and next, you can see it's looking at the battery code database. 8 00:00:39,080 --> 00:00:42,770 So we could use this DSN on our report. 9 00:00:42,770 --> 00:00:46,670 And what I want to do is just go ahead and create a new one for our production. 10 00:00:46,670 --> 00:00:49,100 Let's use our SQL server driver. 11 00:00:49,100 --> 00:00:50,360 I'll hit finish. 12 00:00:50,450 --> 00:00:56,900 This is going to be our production DSN and the server. 13 00:00:56,900 --> 00:00:59,750 I'm just going to put dot backslash CC. 14 00:00:59,750 --> 00:01:01,790 That's going to be the local name. 15 00:01:01,790 --> 00:01:05,330 Of course you could put the computer name backslash CC. 16 00:01:05,630 --> 00:01:12,080 We'll hit next and just leave this at default we're going to use windows NT authentication. 17 00:01:12,080 --> 00:01:19,130 And then our default database for this DSN is going to be our production database. 18 00:01:19,460 --> 00:01:22,790 And then we'll just click finish and hit test. 19 00:01:22,790 --> 00:01:25,580 And it looks like it was able to connect. 20 00:01:25,580 --> 00:01:29,990 So now we have two dsns that we could use for our report. 21 00:01:29,990 --> 00:01:31,820 So I'll just click okay. 22 00:01:31,820 --> 00:01:35,720 Now let's go double click on Reports Designer. 23 00:01:36,170 --> 00:01:40,160 And if you haven't used this designer it's kind of dated. 24 00:01:40,160 --> 00:01:45,930 It is the way that graphics designer used to look, and it's got some similar tabs. 25 00:01:45,930 --> 00:01:51,450 And if you've never used this before, when we come back and design our layout, I'll show you a few 26 00:01:51,450 --> 00:01:52,350 things we can do. 27 00:01:52,350 --> 00:01:58,800 Things like have a cover page, we can have a report contents, we can have a final page, and we have 28 00:01:58,800 --> 00:02:02,370 some limited way to change properties and colors and things like that. 29 00:02:02,370 --> 00:02:08,460 But what I want to do very quickly is introduce you to the database related objects in here. 30 00:02:08,460 --> 00:02:12,930 And once you see how these work, you'll get a better idea of how we're going to do our report content. 31 00:02:12,930 --> 00:02:16,950 So under ODBC databases this is under Dynamic objects. 32 00:02:16,950 --> 00:02:21,720 On the Standard Objects tab you've got a database field and a database table. 33 00:02:21,990 --> 00:02:27,240 So a database table basically allows you to just create a query. 34 00:02:27,240 --> 00:02:31,350 And the results will be printed out in this table itself. 35 00:02:31,350 --> 00:02:32,190 Paginating. 36 00:02:32,190 --> 00:02:36,150 So you don't really have to make it this big if you don't want. 37 00:02:36,450 --> 00:02:43,350 And we're going to double click on this to bring up the properties dialog, and you can make some changes 38 00:02:43,350 --> 00:02:46,320 to your color and font and things like that. 39 00:02:46,320 --> 00:02:51,510 But on the connect tab this is where you define how you're going to link to the database. 40 00:02:51,510 --> 00:02:58,110 So I'm going to double click this link and it's asking for an ODBC data source. 41 00:02:58,110 --> 00:03:01,620 That's why we had to go create our 32 bit DSN. 42 00:03:01,620 --> 00:03:03,540 And we have two here. 43 00:03:03,540 --> 00:03:07,890 We can use my DB class DSN which goes to the battery code database. 44 00:03:07,890 --> 00:03:11,670 We also have our production DSN which goes to our production. 45 00:03:11,670 --> 00:03:14,580 So I'm just going to choose my DB class here. 46 00:03:14,580 --> 00:03:17,610 Now if we had a username and password we could put those here. 47 00:03:17,610 --> 00:03:22,650 Notice that each of these parameters you could also just define a tag. 48 00:03:22,650 --> 00:03:28,470 And when this report is previewed or printed, it will pull this information from a tag instead of what 49 00:03:28,470 --> 00:03:29,040 you print in. 50 00:03:29,040 --> 00:03:31,080 And that makes it very flexible. 51 00:03:31,440 --> 00:03:36,660 Now the SQL statement here we can just put in any valid SQL statement. 52 00:03:36,660 --> 00:03:43,680 So let's say that we wanted to show orders, select star from orders. 53 00:03:43,680 --> 00:03:45,810 And I could just hit test here. 54 00:03:45,810 --> 00:03:48,510 And you can see it does connect to that database. 55 00:03:48,510 --> 00:03:52,410 And it does give us seemingly valid data from this. 56 00:03:52,410 --> 00:03:54,630 So I could put a join statement. 57 00:03:54,630 --> 00:03:57,390 I could put an aggregate put anything in here I wanted. 58 00:03:57,390 --> 00:04:01,440 And as long as it was valid then it's going to fill this table. 59 00:04:01,440 --> 00:04:09,840 Now you can add tags directly into the SQL statement instead of putting the entire SQL statement in 60 00:04:09,840 --> 00:04:10,440 a tag. 61 00:04:10,440 --> 00:04:16,110 So let's just say the order ID is equal to. 62 00:04:16,110 --> 00:04:18,420 And then you could do insert tag. 63 00:04:18,420 --> 00:04:22,980 And if you had a tag that had your order ID, you could just pop it in there. 64 00:04:22,980 --> 00:04:25,920 I realize that's not an order ID, I just don't have one for that. 65 00:04:25,920 --> 00:04:34,570 So in runtime, which we're not in runtime right now, it would pull in the value of this order ID and 66 00:04:34,570 --> 00:04:36,520 put it into this where statement. 67 00:04:36,520 --> 00:04:41,020 That way I don't have to write a script in order to build a SQL statement and then put the whole thing 68 00:04:41,020 --> 00:04:41,620 in a tag. 69 00:04:41,620 --> 00:04:44,170 So I'm just going to get rid of that. 70 00:04:44,170 --> 00:04:50,650 And then if we want to display column headings, we can the next thing that we have is just a database 71 00:04:50,650 --> 00:04:51,100 field. 72 00:04:51,100 --> 00:04:56,830 So let's say that you wanted to have in your report just like a count of the orders. 73 00:04:56,830 --> 00:05:02,320 Then you could put this down here, do the same thing. 74 00:05:03,250 --> 00:05:11,800 We'll do my DB class DSN and we will do select count from orders. 75 00:05:12,280 --> 00:05:16,540 And if we do a test then you can see we got 494. 76 00:05:16,540 --> 00:05:25,360 So then of course we could decorate this by using our static text, making labels and things like this 77 00:05:25,360 --> 00:05:25,960 and do that. 78 00:05:25,960 --> 00:05:26,380 So. 79 00:05:26,380 --> 00:05:29,710 But we're not going to build our report just yet. 80 00:05:29,710 --> 00:05:35,980 What I want to do is now go into SQL server and build us a nice join statement that shows all the information 81 00:05:35,980 --> 00:05:41,080 from our orders, and then we'll come back here and make a nice layout for that data. 82 00:05:41,080 --> 00:05:46,930 And then we're going to build a screen where you can enter data and generate reports from this. 83 00:05:46,930 --> 00:05:54,010 So now let's go back to SQL Server Management Studio and let's figure out what data we want in our reports. 84 00:05:54,010 --> 00:05:56,320 So we can kind of create a nice layout. 85 00:05:56,620 --> 00:06:02,290 I'm just going to go back to something we did in the last module. 86 00:06:02,290 --> 00:06:05,620 And that was our join examples. 87 00:06:05,620 --> 00:06:13,090 And here we have a nice statement that's looking at our orders and filling in our customer and our employee 88 00:06:13,090 --> 00:06:14,260 and things like that. 89 00:06:14,260 --> 00:06:22,450 So I am just going to highlight this hit execute and we get this nice table with our orders our customer 90 00:06:22,450 --> 00:06:25,420 name, order, date, employee description and quantity. 91 00:06:25,420 --> 00:06:30,940 Now for this particular report we would probably want to do it based on time. 92 00:06:30,940 --> 00:06:36,580 Let's say that we printed out every day and we get the orders for the day or orders in the last hour, 93 00:06:36,580 --> 00:06:37,450 things like that. 94 00:06:37,450 --> 00:06:48,100 So on the order by statement, we want to look at our order date first and then that way these are ordered 95 00:06:48,100 --> 00:06:48,880 by the date. 96 00:06:48,880 --> 00:06:52,840 And for our report we would probably want to put in a Where clause. 97 00:06:52,840 --> 00:07:01,090 And if we did it by order date between and I'm just going to plug in some numbers here. 98 00:07:01,090 --> 00:07:03,070 I'm just going to copy this right here. 99 00:07:03,070 --> 00:07:06,370 And remember we have to put this in single quotes. 100 00:07:06,370 --> 00:07:11,980 And I'll just do an and and then I will copy the same thing. 101 00:07:11,980 --> 00:07:19,210 And let's just say it's between 2000 and 1 two four and 2003 four. 102 00:07:19,210 --> 00:07:23,740 And of course I need to move my order statement down before we do this. 103 00:07:23,750 --> 00:07:27,170 and let's just highlight and see what we get, okay? 104 00:07:27,170 --> 00:07:28,550 We get some records here. 105 00:07:28,550 --> 00:07:32,810 So what we're going to do is we're going to use this SQL query. 106 00:07:32,810 --> 00:07:41,450 But then we'll probably automate these values here with tags just to show that we can change this with 107 00:07:41,450 --> 00:07:42,140 tags. 108 00:07:42,140 --> 00:07:46,370 And then we'll build us a screen to fill in this information. 109 00:07:46,370 --> 00:07:48,620 And then we'll just test it in runtime. 110 00:07:48,620 --> 00:07:56,630 So I'm just going to grab this SQL query here and we'll go back to the reports designer and design our 111 00:07:56,630 --> 00:07:57,380 report. 112 00:07:57,530 --> 00:08:03,980 Now before we go into Report designer we need to go create a couple of tags for our begin time and our 113 00:08:03,980 --> 00:08:04,580 end time. 114 00:08:04,580 --> 00:08:05,960 And so I have done that. 115 00:08:05,960 --> 00:08:08,900 I've created a group called reporting. 116 00:08:08,900 --> 00:08:12,200 And I just made two string tags, a begin date and an end date. 117 00:08:12,200 --> 00:08:20,420 And notice that my start value for my begin date, I put a start value of 2001 two four and then for 118 00:08:20,420 --> 00:08:22,430 end date 2001 three four. 119 00:08:22,430 --> 00:08:25,160 So that's February the 4th through March the 4th. 120 00:08:25,160 --> 00:08:28,970 And we're going to use those tags for our where statement. 121 00:08:28,970 --> 00:08:33,650 And we're going to use the tags in place of hard coding these two dates here. 122 00:08:33,650 --> 00:08:38,750 So we're going to go back into our layout editor and create a new layout. 123 00:08:38,750 --> 00:08:41,780 And I'm going to right click and go to properties. 124 00:08:41,780 --> 00:08:47,150 And under our geometry I'm going to set our orientation to landscape to kind of make it wide. 125 00:08:47,480 --> 00:08:53,030 And so what I'll do is on our database table, I'm just going to kind of put it in the middle. 126 00:08:53,030 --> 00:08:59,120 And remember again I don't have to size this big because it is self paginating. 127 00:08:59,120 --> 00:09:02,600 So I'm just going to kind of make it the width here, double click it. 128 00:09:02,600 --> 00:09:08,780 And then under our connect tab this is where we do our database link I'm going to double click this 129 00:09:08,900 --> 00:09:11,720 choose our database source. 130 00:09:11,720 --> 00:09:19,160 And then for our SQL statement I'm just going to paste our SQL statement right in I'll hit test. 131 00:09:19,160 --> 00:09:20,240 That looks good. 132 00:09:20,240 --> 00:09:23,990 And I am just going to replace the dates. 133 00:09:23,990 --> 00:09:26,570 Notice that I'm keeping the single quotes. 134 00:09:26,600 --> 00:09:32,660 Those have to be there, but I'm going to hit insert tag and notice how it's blank here. 135 00:09:32,660 --> 00:09:34,160 This is kind of a bug. 136 00:09:34,190 --> 00:09:37,790 I'll just select a different group and then come back to this group. 137 00:09:37,790 --> 00:09:40,220 Select the tag that I want begin date. 138 00:09:40,220 --> 00:09:46,700 Notice how it puts the dollar sign as place markers to mark the beginning and ending of the variable. 139 00:09:46,700 --> 00:09:50,390 And then I'm going to do the same thing here leaving the single quotes. 140 00:09:50,400 --> 00:09:54,360 And we will do our end date here. 141 00:09:54,990 --> 00:10:00,780 Now I can't do test SQL right now because we are not in runtime and it's just going to give us some 142 00:10:00,780 --> 00:10:01,020 error. 143 00:10:01,020 --> 00:10:02,400 So I'm just going to call that okay. 144 00:10:02,400 --> 00:10:04,770 For now we're going to test it in a few moments. 145 00:10:04,770 --> 00:10:07,650 And then let's go put some static text. 146 00:10:07,650 --> 00:10:10,350 So I'm going to put a header. 147 00:10:10,350 --> 00:10:13,770 Notice how the icon x is out when I go up in this header. 148 00:10:13,770 --> 00:10:15,750 And you can change the properties of this. 149 00:10:15,750 --> 00:10:20,550 This is going to be our action orders report. 150 00:10:20,550 --> 00:10:24,390 And then I am going to make this a little bigger. 151 00:10:24,390 --> 00:10:26,280 We'll do our font size drop down. 152 00:10:26,280 --> 00:10:27,840 So I'll make this pretty big. 153 00:10:27,840 --> 00:10:32,190 And we could do our text color if we want, give it say a blue color. 154 00:10:32,190 --> 00:10:41,310 And then I'm going to go to properties and under line weight I'm going to make that zero that gets rid 155 00:10:41,310 --> 00:10:42,750 of the border there. 156 00:10:42,750 --> 00:10:48,990 And then let's say that we want to have a count of how many orders are in the report. 157 00:10:48,990 --> 00:10:51,750 We could go use the database field. 158 00:10:51,750 --> 00:10:53,820 And I'm just going to pop it there. 159 00:10:53,820 --> 00:10:55,980 And I need to come back here. 160 00:10:55,980 --> 00:11:02,400 And I'm just going to copy this SQL statement because it's got our placeholders in there. 161 00:11:02,400 --> 00:11:08,010 So just control control-c that and then I'm going to double click this here and I'll paste it in here. 162 00:11:08,010 --> 00:11:10,920 And I'm going to get rid of this. 163 00:11:10,920 --> 00:11:16,500 And then on our select it's just going to be select Count Star. 164 00:11:16,500 --> 00:11:16,830 That's. 165 00:11:16,830 --> 00:11:20,700 So we're going to use our aggregate from our orders table. 166 00:11:20,700 --> 00:11:22,740 We don't need all of our joins. 167 00:11:22,740 --> 00:11:25,260 We just need our where statement. 168 00:11:25,260 --> 00:11:27,000 And we don't need our order by. 169 00:11:27,000 --> 00:11:32,970 So this is just going to give us a count of the number of records that's going to be in our detail screen 170 00:11:32,970 --> 00:11:33,720 down here. 171 00:11:33,720 --> 00:11:35,880 And of course we need our data source. 172 00:11:35,880 --> 00:11:39,690 So I'll do my DB class DSN and click okay. 173 00:11:39,690 --> 00:11:42,000 And then we're just going to kind of clean this up. 174 00:11:42,000 --> 00:11:44,250 This is going to be a little smaller. 175 00:11:44,250 --> 00:11:47,730 So I can use my arrow keys to kind of move this around. 176 00:11:47,730 --> 00:11:52,230 And then I'll choose a static text and I'll just do number orders. 177 00:11:52,350 --> 00:11:57,060 We don't have all the nice alignment tools that we have in Graphics Designer in here. 178 00:11:57,060 --> 00:12:02,010 They haven't really updated this editor in a while, but we can do a pretty good job. 179 00:12:02,010 --> 00:12:08,100 Now, let's say that we also wanted to print out the date and time under our system objects. 180 00:12:08,100 --> 00:12:10,200 We do have a date and time and I'm going to click it. 181 00:12:10,200 --> 00:12:12,270 Watch what happens when I click this. 182 00:12:12,270 --> 00:12:13,290 I get this error. 183 00:12:13,290 --> 00:12:17,490 It says no system objects can be inserted in the dynamic part of a report. 184 00:12:17,490 --> 00:12:22,230 That's a little confusing, but what they mean you've got to be in the static part or static mode. 185 00:12:22,230 --> 00:12:26,700 And it's a little non-intuitive, because when you go to your static mode, all your dynamic objects 186 00:12:26,700 --> 00:12:27,540 disappear. 187 00:12:27,540 --> 00:12:31,920 So I'm going to just kind of put the clock right around in here. 188 00:12:32,280 --> 00:12:40,110 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 189 00:12:40,110 --> 00:12:41,100 it nice and long. 190 00:12:41,100 --> 00:12:47,140 use my arrow keys, and then I'm just going to have to switch and say, I can't move it because you 191 00:12:47,140 --> 00:12:49,240 got to manipulate this in the static mode. 192 00:12:49,240 --> 00:12:51,790 So I'll just kind of move this over here. 193 00:12:51,790 --> 00:12:52,300 There we go. 194 00:12:52,300 --> 00:12:53,380 So there's our time. 195 00:12:53,380 --> 00:12:57,160 And then let's say that we want to show our from and two times. 196 00:12:57,160 --> 00:12:59,590 So I'll do another static text. 197 00:12:59,590 --> 00:13:06,400 And this is going to be from and I'll move this down here I'll probably need more room than that. 198 00:13:06,400 --> 00:13:10,750 So let's just move this over and on my properties. 199 00:13:10,750 --> 00:13:15,130 I am just going to get rid of the border on that. 200 00:13:15,640 --> 00:13:20,260 And then if we want our tag information, I'll just choose tag here. 201 00:13:20,260 --> 00:13:24,790 And it's going to be from our begin date. 202 00:13:24,850 --> 00:13:32,320 Give it a little bit more room here and I will paste this and put two here. 203 00:13:32,320 --> 00:13:35,350 And still I think I need to back these up some. 204 00:13:35,500 --> 00:13:37,150 This can be a little smaller. 205 00:13:37,150 --> 00:13:40,690 And then here I'm just going to control C control V. 206 00:13:40,690 --> 00:13:42,280 So they're the same size. 207 00:13:42,280 --> 00:13:44,710 And just kind of try to clean all this up. 208 00:13:44,710 --> 00:13:47,470 If I double click this under miscellaneous. 209 00:13:47,500 --> 00:13:49,870 Your output value is where your tag goes. 210 00:13:49,870 --> 00:13:54,190 So I can just double click this and go to End Date here. 211 00:13:54,190 --> 00:13:59,020 And then I'll just use my arrow keys to kind of just clean this stuff up. 212 00:13:59,410 --> 00:14:00,160 There we go. 213 00:14:00,160 --> 00:14:07,210 And then I am going to go into our static mode, pull this kind of back out and that's decent. 214 00:14:07,330 --> 00:14:14,230 I'm going to save this as my production orders underscore E and you hit save. 215 00:14:14,230 --> 00:14:16,150 And so now we have a layout. 216 00:14:16,150 --> 00:14:17,920 And so I'm just going to minimize this. 217 00:14:17,920 --> 00:14:19,270 And now we need a print job. 218 00:14:19,270 --> 00:14:23,260 So a highlight print job I'll right click do new print job. 219 00:14:23,650 --> 00:14:25,630 And you see it jump around a bit. 220 00:14:25,630 --> 00:14:29,050 And basically it has created a new print job down here. 221 00:14:29,050 --> 00:14:30,100 It doesn't really tell you. 222 00:14:30,100 --> 00:14:36,520 So you just kind of have to know I'll double click this and we're going to call this Production orders, 223 00:14:36,520 --> 00:14:40,750 and we're going to associate it with our layout that we just created. 224 00:14:40,750 --> 00:14:42,070 Now the dialog. 225 00:14:42,070 --> 00:14:47,440 This means it'll show a dialog when you try to print it, and you can put in your tag information or 226 00:14:47,440 --> 00:14:50,590 what other object information that you dynamically assign. 227 00:14:50,590 --> 00:14:53,260 We're not going to do that because we do have our tags. 228 00:14:53,260 --> 00:14:55,480 So I'm just going to click okay. 229 00:14:55,840 --> 00:14:58,810 And we have the ability to preview this report now. 230 00:14:58,810 --> 00:15:01,120 But the problem is we're not in runtime. 231 00:15:01,120 --> 00:15:02,920 So it's just going to give us an error. 232 00:15:02,920 --> 00:15:05,800 So I'm just going to take this project to runtime. 233 00:15:05,800 --> 00:15:10,480 And just to remind you our tags do have start values in them. 234 00:15:10,480 --> 00:15:14,620 So they already have that information and they should run. 235 00:15:14,620 --> 00:15:16,420 So now we're in runtime. 236 00:15:16,420 --> 00:15:22,570 So here's our production orders I'll right click and do preview and it will come up. 237 00:15:22,570 --> 00:15:24,370 And this is the cover page. 238 00:15:24,370 --> 00:15:26,080 We forgot to turn that off. 239 00:15:26,320 --> 00:15:28,660 But here's our date and time. 240 00:15:28,660 --> 00:15:30,880 We have 32 orders. 241 00:15:30,880 --> 00:15:33,020 It's from 2001 two, four. 242 00:15:33,020 --> 00:15:34,700 And then we can go to next. 243 00:15:34,700 --> 00:15:35,930 Here's our next page. 244 00:15:35,930 --> 00:15:39,200 So it automatically gave us a page break. 245 00:15:39,200 --> 00:15:41,990 So I'm going to just close this out. 246 00:15:41,990 --> 00:15:45,440 And we'll go back to our layout editor. 247 00:15:45,440 --> 00:15:48,350 And I will right click and go to properties. 248 00:15:48,350 --> 00:15:50,630 I'm going to turn our cover sheet off. 249 00:15:50,630 --> 00:15:53,570 That way we don't have that blank page at the front. 250 00:15:54,200 --> 00:15:58,910 And just save that And then we can go back to our print jobs. 251 00:15:58,910 --> 00:16:04,340 Right click, do a preview and notice that we don't have the blank page up there. 252 00:16:04,610 --> 00:16:06,950 And we can see all of our pages. 253 00:16:06,950 --> 00:16:09,860 So now we have a nice production report. 254 00:16:09,860 --> 00:16:14,210 Of course we can clean this up quite a bit, but you get the general idea. 255 00:16:14,210 --> 00:16:20,630 In the next section we're going to create a page where we can enter in data into the tags and generate 256 00:16:20,630 --> 00:16:23,300 a report based on the dates that we want. 257 00:16:23,300 --> 00:16:28,700 But in the meantime, you're going to go through an assignment where you're going to create a report 258 00:16:28,700 --> 00:16:32,960 layout and a print job and duplicate what we've done here in this video. 259 00:16:32,960 --> 00:16:35,180 So I'll see you in that assignment.