Introduction

 Top Next

To ease us gently into Reporting Services, let us look at a few different reports as they are

displayed on the screen.

Firstly, let us see a report being displayed from 'Report Manager' running across a local network (LAN).

[ *** Note *** - the report is shown in a browser .... ]

===============================================================================================================================================================

We will discuss Report Manager in more detail later, for now realise that it is a pretty simple way

of presenting a user with a list of the reports that they are allowed - or permitted - to run and see.

Please notice the interactive input controls just above the navigation bar. These sorts of controls

are very common for many, or even most reports. They provide a great degree of flexibility.

Below is a live example of what the Report manager may present to a user for a report 'choice' :-

=====================================================================================================================================================

The user simply clicks the live link to launch the chosen report.

The friendly information, as well as report name, is supplied by the DBA or IT Manager when they upload

the RDL supplied by the designer / developer.

At the other level of report 'availability', we can have a report running in a local dotNet application and

the image below shows a Forms 2.0 control (called 'ReportViewer') running in a Host control on a WPF

window - the code is Vulcan.NET :-

[ Yes, I know, the report is about as 'basic' as it can be - the point was to make it work ! ]

This sample above has only a very simple, or basic report, but a report it is !

Notice that the same navigation controls are available in the application as on the Report Manager screen.

There are menu icons to display the report in a variety of ways, changing size and setting page layout :-

=====================================================================================

Changing page settings from landscape to portrait :-

And we can also search the whole document for a character string, as well as have the size of the

text appear smaller or larger :-

And we can go back to a layout display and also much smaller, like shown below :-

=========================================================================================================================

Notice however that the search is still in operation.

Finally one very useful menu tool is that to export the data, see below :-

=====================================================================================

One way to use RS is to set up a report specifically for the export of the selected data into Excel.

I have just completed a report like this for my client and we intend to run a mail merge to print mailing

labels for sending out birthday cards to chosen customers. Obviously, the selection of customers

depends on their birthday and the date on which the report is run, as well as length of time with the

company.

Finally, we can see a report being run from the Visual Studio (VS) designer itself. The 'Preview' tab

has been selected and the report run with user inputs.

Notice that from here we seem to have a few more export options, I will have to see just which are

available when a report is run in the Report Manager environment.

==============================================================================================================================

Enough for now !

You should have seen plenty to wet your appetite, as well as convince you that such a powerful,

and in many ways simple tool, is worth further investigation.

Oh! and that is without even mentioning the basic printing options as shown below :-

RS is probably the easiest way to print information form data to hard copy - and I have done a lot of

my own printing over the years, and so appreciate this 'simple printing tool' as such.

I my mind designing a report is not the best way to achieve all printing 'tasks' but it does many nicely.

It is now time to move on to the next section to see just what Reporting Services is.

See you there.

PHH - June 13th. 2010.


What is Reporting Services

 Top Previous Next

It would appear that most people use "Reporting Services" as a general term to cover both

the display of the report itself, along with the activity of creating the report 'definition', and also

the system which allows the report to be selected and displayed on screen.

So in simple terms we have a creation activity, a delivery system, and a report definition itself - the 'object'.

The report code, or definition, is termed the RDL file, where 'RDL' stands for Report Definition Language.

The 'designer' is an IDE which allows the user an easy way to create the RDL file, by mouse use,

drag and drop, and clicking in the way we all have become accustomed in Windows software. I doubt if

many (any?) developers create RDL's outside of an IDE. I never see the RDL script itself.

In many companies the 'Report Manager' software runs across the internal network and presents the

users with a list of reports that they are allowed to see and run.

Any new RDL files (created reports) have to be uploaded in the report manager software by the IT Manager,

or the Database Administrator (DBA).

In a lesser used scenario, the RDL may be used within an application to be run and the resulting data

displayed in a Form or WPF window control. This is a nice way to show users the output of a report. It

is also a lot easier than hand coding the dotNet code to print directly.

Also keep in mind that the Report Viewer has many capabilities like print and export to other formats.

It is now about time that we see some nice images to illustrate what we have said so far in only words.

The interactive 'Designer' for the report :

Below is the 'designer' belonging to Visual Studio, and this is also seen in the tool called BIDS - 'Business

Intelligence Development Studio'.

====================================================================================================================================

A sample of the 'RDL' code produced by the 'designer' :

As you can see by the schematic thumbnail of the contents of the editor (right hand side) this is just

the first part of a much longer file content named 'ManufUsageByNurse.rdl' - see below :-

==========================================================================================================================

You don't need to worry yourself about RDL, as it is the XML 'stuff' that the designer creates for us from the IDE.

[ However, you do need to be aware that it uses classes in a namespace, and therefore the IDE which generates the RDL

script will choose the namespace it requires. This can cause us grief in different versions of Visual Studio, as we will

discuss later. ]

An example of the report output (live) :

==============================================================================================================================

==============================================================================================================================

The 'Report Viewer' control in use in an application :

PHH - June 13th. 2010.


Do we Need RS ?

The simple answer to this is YES !

If you are using SQL Server in any way the the answer is 'Certainly' !

Remember that Reporting Services (RS) is free, and comes along bundled with the full version of

SQL Server your clients will have purchased, and also the free download of the Express version you

may well be using for development.

Depending on whether you are using 2005 or 2008 versions of SQL Server you may have to request

a version of the download to include 'Advanced Services'. This was certainly true for the 2005 variety

of download - it maybe that the present 2008 download has it all rolled into a 'one choice' download.

Also it used to be that the extra parts of the Advanced Services option, could be downloaded separately,

as an 'after thought' for those already with SQL Server 2008 installed, or in use. This may no longer be

true for SQL Server 2008 R2, it may come as a complete download.

Why do we need it ? :

It provides those facilities which for an application developer are difficult and time consuming to provide

within a dotNet application. That is, display or large amounts of data in an easy to read format, as well as

allowing interactive input for various dataset selections by the user.

The benefits get better, as all physical printing is taken care of too, as well as exporting of the chosen

dataset to other formats.

As a further benefit, text searching is also available as a standard option. And for those who need to see

the "bigger picture", the screen output can be enlarged for better and clearer display for those people at

a distance.

Oh! and once again, it is FREE. Don't get me wrong, it is not the 'free' bit that sells it to me, but the fact

that it is good quality, fully supported by Microsoft, and then FREE !!!

Does your client need it ? :

Whether they need it depends on you, them, and their individual needs.

However, if you ask "will they like it?" the answer is yes. It is a pleasantly visual experience which has much

flexibility at hand, without getting difficult to use.

Just be warned, once you start providing your clients with nicely designed reports, they will keep coming

back for more - many more ! And that keeps you in a job ;-)

Will I use it ? :

I have been handling my own printing in total - right from Visual Objects days - and can still do things that RS

does not seem to handle easily, however, I am determined to add it into my palette of development tools.

What stopped me using it previously was its seemingly inaccessibility - mainly due to my lack of knowledge,

and partly due to those converted guys around not spreading the story as loudly as they should have.

I have to confess that there does seem to be a definite lack of learning material which addresses all of the

problems faced by a single guy in his office (like me), coming to terms with RS for the first time.

So for me I will definitely try to keep learning more, and use RS reports from my applications wherever I can.

Remember, the print preview and physical printing options are all there as a standard default, that is great in

itself without the rest of the benefits.

Now its time to move on. See you in the next section.

PHH - June 13th. 2010.


Personal Experiences with RS

Background :

You may be interested to know how I came to Reporting Services (RS) as it may well be repeated

a few times in other places around the globe - many I would guess.

My client in Cardiff of many years standing (about 17 years) moved their large network business

application from Visual Objects / DBFs, to dotNet and SQL Server 2005.

So suddenly I was asked by them to provide SQL support services, mainly by designing and

creating reports with RS. This meant I needed some reasonable SQL knowledge.

Luckily for me I had gotten into SQL Server and ADO.NET with Vulcan some twenty months before

this situation arose so was just about up to speed enough to say 'OK, I will do the job for you' !

General Job Specification :

My clients wished me to have a copy of their databases locally, on my development PC, and then

design new reports at a distance (Newport, South Wales).

After testing in Newport, any new or changed reports would be attached to emails and sent to them in

Cardiff, where their DBA (database administrator) would upload them onto the local company network.

He would point the report at their data source and give it a friendly name so that users would see this

name on offer in their drop list (or whatever) in the Report Manager environment on their LAN.

They sent me a 'backup' copy of their databases (they have two) and then I restored them on my development PC.

[ Out of interest, I have both SQL Server 2005 and 2008 - Express editions - installed on this PC platform,

as well as of late the Developer 2008 R2 and Developer 2005 editions. Yep! quite a mixture.

If you have choice in this, stick to the latest - use the 2008 Developer R2 version if at all possible. ]

As well as doing new reports for them, I was also to edit existing ones from time to time, which were

originally created by the third party firm who did the whole IT 'upgrade' to their whole network. They are

now fully 'converted' to SQL Server 2005 for all of their systems, and most are linked together in some way

or another.

For this reason I needed to have a version of SQL Server 2005 with RS, so that my designer IDE would use

the same namespace for the RDL classes.

My First Tasks :

Simplicity of the report - make it as simple as possible - KISS !

Moving My Skills Forward :

Simplicity of the report - make it as simple as possible - KISS !

The Near Future :

Setting up a Maintainable Structure / System :

Outside of the reports themselves, I decided I needed to develop an approach to making the report,

files and database objects, easy to identify and maintain. This started with the naming of any files

and objects, through to the way the T-SQL code was written. I tried to identify as many good practices

as possible, and apply these whenever I could.

The Long Term Future :

Put simply, I will retire as an old (er) man and someone else with RS skills will have to take over.

One reason for steering my client in the direction of RS is so that it is a commonly known technology,

and that they ought to be able to get professional help easy enough - YES - they may well have to pay

more for it than they have paid me, but that is the world out there !

Knowing that computer systems and IT are long term issues these days, I have built in to my plans and

design of their reporting system generally, good practices which should help any third party taking over

the role of report maintenance.

This is just the professional thing to do !

PHH - June 13th. 2010.


What Software do we Require ?

For those without a full version of Visual Studio (VS) the application which comes with the SQL Server Express

download is called BIDS [ Business Intelligence Development Studio ] and the application shell is simply

that of VS itself, with the required parts installed within it.

Anyone with VS however, will also have the appropriate parts installed into their main copy of VS, and can

use it from there.

When Reporting Services (RS) is downloaded with Express SQL Server (Advanced Services option) they get

RS and the software tools, BIDS and 'SQL Server Management Studio' (SSMS), as well as a Configuration

Manager also.

Here is what MSDN has to say :-

================================================================================================================================

If we now look at the contents of my BIDS shell - 2005 'flavour' we see the following available for

new projects to be created :-

This is indeed VERY simple after what most of us are used to from using VS for other tasks, like writing

dotNet applications.

Remember however, I have not installed VS 2005 on this PC myself previously - as a specific install.

Software versions - SQL Server 2005 & 2008 :

So far we have simply seen what my DIBS-2005 new project dialog box looks like, and we now need to

compare this with what the equivalent 2008 versions has to show is on offer. Look at this below :-

It is not difficult to see that my 2008 version of BIDS has far more possible project types with associated templates.

Now if I show my full VS one which has been installed since before the Advanced Services download of SQL Server

2008 Express it looks as follows :-

The list of project types is the same, and although I have chosen to highlight Vulcan to avoid a complete

duplicate large image having to be included in these notes, it should be reasonably obvious that the stuff

on offer in BIDS and VS is the same.

So to summarise, 2005 and 2008 version are to be kept separate, and dealt with quite separately too. If

you have VS installed previously and choose to install BIDS then they end up the same, two 'shells' with

similar project types on offer.

*** WARNING *** Do NOT mix reports for SQL Server 2005 and any 2008 software for report generation.

The only 2008 flavour of software which can be used for SQLServer 2005 and 2008 without any issues or

problems is SSMS - Management Studio.

I use SSMS 2008 for all my work with SQL Server because as the image below would tend to indicate, it

handles both at the same time, and also for me it has a few more options available too. So it simplifies my

complex life by doing two jobs from one interactive 'shell'. I like that ;-)

Below is how my SSMS 2008 looks with two instances of SQL Server open, one version 9.0 and the other

version 10.0 - you need to know that these version numbers are what Microsoft calls SQL Server 2005 and

2008 under the hood.

=============================================================================================================

What do I use while developing and testing new Report ? :

I have to tell you that I find it very useful indeed to have SSMA open on one screen while having BIDS 2005

open on another. I do a lot of trial and testing of select statements in SSMS while designing my reports

and data sets. Quite often I may wish to make and test new 'view' and 'UDF' entries to the database I am

using - to support the data sets in the report. This I do in SSMS.

Why do I go on about me using BIDS 2005 ? Quite simply this is the version of SQL Server used by my

client on their network in Cardiff. I can't use RS projects from VS2008, as they are NOT compatible. I know

I tried it out at the very beginning and it did not work. the RDL files use quite different namespaces for their

objects.

Willie Moore was very helpful and supportive to me, even though he was many thousands of miles away. But

even after all his clever tricks, we came to the conclusion to keep stuff quite separate, and then it just works !

So my advice - once again - is keep 2005 stuff and that of 2008, quite separate - except for SSMS which can

handle both 'flavours'.

PHH - June 13th. 2010.


Diferences - 2005 & 2008

General :

At this early stage for you getting into Reporting Services (RS) you most likely don't wish to go into

any detail as to what the differences are between 2005 and 2008 flavour of the reporting system.

It should be sufficient to say 'take care' with this issue, and don't try and mix the two.

I have found by experience (with advice from VOPS colleagues) that it is best to use RS 2005 with

SQL Server 2005, and RS 2008 with SQL Server 2008.

The only support tools which can 'bridge' the gap (and do both) are SSMS (Management Studio) and

from my tests, Report Builder 2.0 from Microsoft - care with this later tool, depending on how and where

you wish to display the report.

When it comes to using RS from the VS shells on offer (BIDS [Business Intelligence Development Studio]

and VS [Visual Studio] itself) keep exactly to the correct identifying numbers - versions.

With these later tools, one reason is that they use different namespaces when creating the RDL (report

Definition Language) file. Not just in the namespace reference, but also in the classes that are used

to create the report layout.

As it Affects the Design Layout :

I have been through the whole of Mark Long's DVD training which addresses RS 2008, and he mentions

in his video sessions that there are some differences between RS 2005 and 2008, however he does not

make a big fuss over it and I would guess that I won't have too much trouble going to use RS 2008 now,

after my initial reporting experiences have been with RS 2005.

I don't thing the IDE is a lot different, and certainly I can say that having watched everything he did in

the videos, and then coming to RS 2005 to make my reports, it has not been much of an issue, if any.

Under the Hood :

What I have learned from my research is that apparently under the hood the two systems are very

different, and something is related to IIS on this front. Whether this is more to do with report deployment

than the creation of the reports themselves, I will no doubt see with time.

We are more likely to see any of these differences when we come to look at the issue of setting up

the Report Manager to run locally on our test and development PCs. I have yet to do this as I am client

(or customer) driven. His needs have been for me to design and make new report against his existing

SQL Server database. At the moment I only use the 'preview' option in the VS shell (BIDS 2005) to see

that my report does as requested by my client. This seems perfectly sufficient, and looks just as the

IT Manager gets in Cardiff, when he runs the report through his browser based Report Manager software.

Obvious Difference :

From watching the Mark Long videos it is pretty obvious that the 'Gauges' facility is new to RS 2008. This

looks good, as well as interesting, and is something we will need to go into.

Converting 2005 Reports into 2008 'Flavour' :

This is one issue that I have learned little about at the moment. Could it be that the obvious silence means

that this is not easy or straight forward ? I need to research this topic and then place the results and the

recommendations in Appendix 'E'.

One thing which is reassuring is that all the hard work of writing the script to create the correct dataset is

not going to change, so the lion share of the task is not affected.

Advice for Us All :

If I possibly can, I will always use the very latest version of RS with a solution for myself or a customer. At

the moment this means NOT using RS 2005 unless the client's situation requires it. Obviously, if they have

SQL Server 2005, which means they run Report Manager from that version of SQL Server, then we need to

use BIDS 2005 to create their reports.

We will worry about what needs to happen to the existing reports when they move from SQL Server 2005 to

2008 at a later stage. But console yourself with the fact that the dataset work will be unaffected ;-)

Which is great news indeed.

Now time to move on, see you in the next section.

PHH - June 13th. 2010.


Overview - Correct Focus !?

Here is a tip after learning some basics, and getting into production of new reports on a real life customer

database.

By far the biggest, and most important, issue is that of designing and creating a suitable SELECT statement

to produce the dataset on which the report will be produced.

Often MUCH more effort and time goes into this stage of a report project, than the nice fancy graphical and

display part of the project.

Remember - a report is ONLY as good as the correctness and completeness of the data being used to generate it.

Also, most real company databases are much more complex than the tables used in training materials, both book

and DVD.

The complexity and degree of normalisation of most company databases means that the SELECT statements used

in the data sets for reports are quite large and necessarily complex.

For these reasons you will need to get very familiar with the JOIN clause, and how it is used, both INNER and OUTER.

However, once it is simply explained, the JOIN is quite easy to use and get correct - I was surprised !

Example to Prove the Point :

Just yesterday I finished off a report for my client in Cardiff. The objective, or point, of the report was simple enough.

We needed to list in a report format all those people with birthdays coming up in the next week (7 days), and

starting from the day after the day on which the report is generated. The people in question have to have been

registered with the company for a single calendar year, or less.

Well this sounds easy enough - BUT - after exploring their data tables for some considerable time we needed to use

following code in the dataset for the apparently simple report.

Take a look at this to see what I mean :-

===============================================================================================

The code, or script, of the above data set looks verbose in the extreme for such a simple sounding report !

However, at this moment in time there is good reason to have all of the parts and lines that are included. Once

we are 12 months past the change over date (November 23rd. 2009) when they moved their data from an old

Visual Objects network system to the current SAGE1000 and SQL Server 2005 based system, half of the above

script of the data set can be removed.

Even so, the script seems complex for just generating a birthday card list with address details.

Before we go into looking at details any further, let us look at how, in comparison, the actual design of my

desired report appears to be very simple :-

========================================================================================================================================================================

Now, returning to the code / script of the data set, let us see what else makes sense from the long listing.

For the present, lets just focus on the FROM clause, which specifies the tables to be used to create the

'de-normalised' data records we require. The code is shown below :-

=============================================================

You can probably see that the full record we need to generate comes from data in four different tables. Three

in the database itself, and one generated by a 'view' called 'ActPatsAgeWithDoB'.

The view simply finds all patients with active status, who have a specified date of birth, and supplies the following

type of data output :-

==========================================================================================================================================================

So what are we doing with the whole FROM clause ?

The answer is simply combining person name information, and address information, from foreign keys held in the

account table, for all those active patients supplied in the table output from the 'view' shown above.

By carefully specifying which of the many resulting columns (or fields) in the de-normalised records so created,

we get a basic report as follows :-

==============================================================================================================================================================

[ Sorry! - the data can't be shown for confidential reasons - just the general information etc. !! ]

For now don't worry about how we got the report layout created in the IDE designer, we will do more on that later.

Also, don't worry about the 'columnation' and width of this report - the truth is it is actually designed for exporting

the data found into Excel, so look at the subsequent section, a couple on from this, to see about exporting data.

Notice in the report image above, we have two interactive controls displayed in a section just above the report itself.

The default values are set to tomorrow (June 14th.) and for a period of seven days on from tomorrow.

When preview is clicked in the IDE the report is generated as shown, as all parameter (interactive) inputs are known

from the defaults, however these can be changed and the report re-run

Finally :

To complete this short section on correct focus on the report data set, let us look at the dataset code / script to

select out only those fields (columns) we require for the subsequent mail merge to print address labels.

Below is the script to specify the columns :-

=============================================

Notice above that I have made very clear to myself what I am selecting, by line layout as well as the

use of aliases for the table names being used.

So basically, we are choosing data fields related to age and date of birth, address and also name and phone

details. A whole bunch of other columns have been ignored.

Explanation :

Before we leave this section it is probably just as well to point out that the UNION clause is used to join or merge

two sets of similar data.

Until the new system has bee going a year (next November 23rd - 2010) we need to combine data for recent people

existing in the data tables on the day the system 'moved over' to the new one, as well as most of any people added

to the system after that date - once they had 'gone live'.

Obviously, next November only one half of this script is required, and I may well remove the then unnecessary script

so that future maintenance is simplified and more understandable to those you need to do maintenance tasks in years

to come ;-)

Have a look at the Excel sample exported data in 'Reports - an easy Way to Export data'.

Please note that the data shown is fictitious and based on me and my friends and family !

See you there ;O))

PHH - June 13th. 2010.


A Basic Truth !

What I will tell you now is worth remembering at all times when dealing with reports.

The readers and consumers of reports get most out of simply presented, and easy to understand

types of reports.

Many of the 'interesting features' of the whole reporting system will 'over complicate' most reports,

whether we like it or not !

Unfortunately for us designers and developers, most often a simple table is quite sufficient to get

the report message across to the user, and reader of the report. Matrix controls for 'cross tab' reports

are OK as well.

However, I am an image man, and believe that a picture can speak a thousand words, so I would

support my table data with a straight forward graph type. Unfortunately, many DBAs and IT managers

are not so visual - I did not say they lack imagination, that is for you to judge ;-)

Obviously, at the end of the day, "keep the customer satisfied" in the words of Paul Simon. If they

want over complex reports, then go for it - after giving your advice first.

I would tend to be pragmatic on this one, and look at the whole thing from the supporting dataset

point of view - if the style and layout of a proposed report makes the dataset creation more difficult,

and hence will affect maintenance of the report script in the future, go for the simpler option if possible.

Finally, on this short topic, I also feel that many reports should have interactive inputs to allow the reader

of the report to select the region of the possible report data, rather than present everything in a longer report.

To my mind the more interactive inputs (parameters) the better, and also provide suitable default values

whenever possible - so the report will run first time, but the consumer of the report can then adjust inputs

to suit their needs for subsequent runs.

Now let us move on to the subject of how to get going.

See you in the next topic section.

PHH - June 17th. 2010.


How to get Going !

This can be difficult, and it is rather tricky.

It is hard if you are isolated, and working on your own.

One of the reasons I am writing these notes to share my hard earned experience with you is that

although it may be tricky to get some results at first, it is NOT difficult to actually design and make

reports once you have mastered all the little bits here and there etc. etc.

So, don't give up, whatever you do ! The results are certainly worth it.

Overview :

You don't need to spend any money, so cash investment is not required.

However, you do need to invest a lot of time and effort with learning SQL Server and its related T-SQL

scripting code, as well as the VS shell operation, Management Studio parts specific to writing queries

and making views and UDFs, and obviously the report designer itself.

With the help of these notes you can save yourself a lot of time that you would otherwise waste going in

the wrong direction, and following blind allies etc. However, if you still are in a hurry, or have limited time,

then you better find another occupation I suggest ;O)

The time you invest will be well spent, and afterwards you will be a better professional too, as well as

feeling good about yourself.

First Things First :

Get your desktop set up correctly with the required software.

If you can possibly miss out using SQL Server 2005 then do so, and start with version 10.0 (i.e. SQL Server 2008).

Download the free version of SQL Server Express with Advanced Services - this may come as a complete

package at the present time, since RS is seen as part of SQL Server by Microsoft these days.

Once you have 'SSMS' and 'BIDS 2008' as icons on your desktop (rename the icon names !) then you are

ready to start.

If you need more help installing SQL Server then look at the 'ClickStartSQL' eNotes volume I have written.

Knowledge of SQL Server and Related 'things' :

There is little point in confronting reporting Services (RS) if you do not have at least a working knowledge of

SQL Server, and the T-SQL script to construct SELECT statements and create 'views' and User Defined

Functions (UDFs).

I have to agree that I have learned more T-SQL while doing RS but I did have some useful knowledge previously,

and completely re-visited all my training DVDs on SQL stuff while starting my journey into reporting.

The good thing about training DVDs is that they can be used many times over, and are greater refresher

material after a layoff of some months - which often is the case.

PHH - June 13th. 2010.


What makes the Task more Difficult ?

In many ways this is an easy question to answer.

The task of generating a report with RS just takes some basic familiarity with the design tools and the

design surface itself.

With some time and effort, anyone capable of application development can create and use reports.

However, because of legacy 'issues' as well as resistance to change and upgrade, on behalf of both customer

and developer, the job (or task) is often made much more difficult than it needs to be.

At this very moment :

Although VS 2010 (MS call it version 10 under the hood) has been out a few weeks now, I have done little with

it except have a quick glance at the "look feel" and the general layout etc.

For any new work, whenever I can, I use 2008 'flavours' for both SQL Server and BIDS / VS shell apps. But

because of legacy 'issues' I need to earn my living using BIDS 2005, and the main client for whom I do this work

is not likely to change in a good few years I suspect.

So I will soon have three versions of RS shell on the go, and with which I need to cope, at the same time as waiting

for the next SQL Server version in a year or so.

And if you think that is a bit of a minefield to manage, there is also the MS Report Builder applications to take

into consideration - these are aimed at client / customers, but still need to be considered seriously by us as

developers when it comes to an on-site reporting solution.

A Possible Approach :

PHH - July 5th. 2010


Reports - an Easy Way to Export Data

Let us first take a look at the data as it comes into Excel.

Below is the exported report data after saving the Excel formatted file and opening it in Excel itself :-

[ Note the data shown above is made up and is based on me and my family, although mixed around a bit too ;-) No confidential stuff here ! ]

=====================================================================================================

========================================

PHH - June 13th. 2010.


Learning and Support Material

provide the link to the executive page of ClickStartVulcan.net .....

====================================================================================================================================

=========================================================

====================================================================================================================================

===============================================================================================================================

======================================================================================================

======================================================================================================

================================================================================================================================

PHH - June 13th. 2010.


Books on Line

This next small section is just Good News - and all the way too !

There is a wealth of information available to you free, and almost instantly.

Below is the open application icon screen area in W7 which shows the old 'Books on Line' icon and a screen

thumbnail image.

I recommend you use this VERY helpful information a lot - from both SSMS and BIDS 2005 (VS shell 2005).

[ *** SSMS *** is SQL Server Management Studio ... BIDS is Business Intelligence Development Studio ... ]

=======================================================

You launch the 'Books on Line', or SDK documentation form the Help drop down menu shown below :-

[ this one is from SSMS ... ]

=============================================================================

Once you have Books on Line displayed you may set a filter so that you see only the help notes

appropriate to which task you are doing at the time.

In the image below I am just trying to gain access to the standard SQL Server and T-SQL information.

========================================================

You may notice in the image below, that the drop list menu for my BIDS 2005 shell is different to

that which we saw for the SSMS environment.

============================================================

The options on my filter drop list for BIDS 2005 seems more extensive than my SSMS 2008.

Below you will see that I have mine set for Reporting Services at the time of the screen capture.

However, I often change this during the course of a session of development and report creation.

========================================================

It is VERY useful to select the appropriate filter when looking into functionality like the DateTime functions.

These date and time related functions can vary quite considerably depending on which environment you are

in at the time of scripting - so be warned ;-)

In much of my other eNote work in ClickStart I refer to such helpful information as 'SDK documentation'.

It was while watching videos by Mark Long and Paul Nielsen that I was made aware that 'Books on Line' was

the way things were back in 2005, and they have just moved sideways a little since then.

Same old quality information however ;O))

So use it wisely !

Obviously, like a lot of this stuff today, a general search from this environment can take you out into other

web areas - MSDN and the likes - so even more information.

For your information, I used this sort of documentation extensively while researching the material in Appendix F

on DateTime related issues. Extremely valuable, and helpful.

PHH - June 25th 2010.


Acknowledgements

For this part of my exploits into learning Vulcan.NET and SQL Server related topics, I have only

one main VOPS colleague to thank specifically.

Willie Moore Jn. from Alabama - known to all our community as 'Willie' - has been helpful and supportive

since day one of my mission. He has been great support to me during my fumblings to get going.

"Thanks a bunch Willie", I will still be in touch, as I have lots still to learn - particularly on the web

services front.

Another thank you, or acknowledgement, should go to Pelican Healthcare Ltd. of Cardiff. These

guys had the confidence in me to take me on board as their report writer, even before I had acquired

the full set of skills required. See, they had faith in a tried and tested VO hacker !

Pelican Healthcare Ltd. also sponsored DevShare, and were particularly interested in Willie coming over

to give his RS session - a repeat of what I missed in Germany at VODC.

Behind the scenes generally, I have many VOPS and VOUG mates and colleagues to thank for their

overall support, without them I could not really do any of this 'ClickStart' stuff.

On the SQL Server side of things, Mike Pitcher, Colin Newell, and Ginny have more than done their bit,

and in early days GS prodded me in the right direction, it has to be said ;-)

Thanks to all - " it's a community 'thing' ", and a good community the VO and VN world enjoy.

Long may it continue !!!

PHH - June 13th. 2010.


Chapter 1 - the Necessary Report Parts

Some Background :

If a new report is designed 'against' the same database structure as that of the 'target' database,

then usually the resulting report file is sufficient to pass on to the DBA (database administrator) at

the target site.

However, if as part of the 'report solution' the developer creates any new entities which are 'views',

or items stored within the 'Programmability' section of the database, then these items too need to

be transported to the target site and placed within the target database - essentially since the DB

being used for the design is different once such items are added to it at the design site.

Any views, functions, or procedures added to support a report are transferred to the target site through

the use of ' .sql ' script files. These are created in the SSMS shell.

My Professional Scenario :

You may be interested to know the details of how I am working for my main client at this moment

in time. It may help you understand a few issues surrounding report generation and deployment.

At regular periods (of about two to three months) my client's DBA sends me a Backup of their

working database - it fact it is two databases, one called 'pelican' the other 'SAGE1000'. Below

is an image of my Object Explorer (OE) in my Management Studio (SSMS) when I have my

database engine set to my instance of SQL Server 2005 on this PC.

Notice the 'pelican' and 'SAGE1000' entries :-

==========================================================================

If we now open out the 'pelican' folder structure we will see the places where extra entities may be

stored within the database, after I have created them using SSMS.

[ *** Remember *** this tree view looks like folders and files as in the filing system of Windows (WE),

but it is not. It is mainly a tree view of the database objects, where tables are part of the DB object.

Views, functions and procedures are also part of the DB object, as indeed are columns and indexes too.

>> DO NOT << think of a table as a file !!! ]

Under 'pelican' look for Functions and Views in the image below :-

==========================================================================

Above you can see the table valued functions I have created (called UDFs for 'User Defined Functions').

Below is the OE with my 'Views' on show :-

=======================================================================

I stick with a simple naming convention provided by my client's DBA, and that is to add 'phh' to identify

that I made the 'entity', and then to use ' v ' for a view, ' f ' for a function, and a name which describes the

purpose of the entity.

It is therefore easy to see in the image above that the 'surgery' view was not made by me but the third

party consultants called 'FDS'.

Obviously, each person should only edit their own entities, and once a change is made then this entity

needs to be transferred to the target site for uploading to the target DB to replace the existing version.

Finally, to finish explaining my situation for designing reports for my client, test the final report on my

stand alone PC, with their old data, and then send a few files to them by email.

The files I send are script files ( ' .sql' ) from SSMS to Alter / Create entities, as well as the RDL file itself.

One aspect which needs careful attention and consideration is that of managing versions of views and

functions used in the RDL script itself. It would be all too easy to be using a newer version of a view

script at the design end of things, and when the client runs the report they get different results (or worse,

no results at all !) because they are using an earlier version of the view.

You will need to work out your own system for handling and managing these extra DB scripts for views

and programmability and the likes.

Summary of My Scenario :

I am sent backup copies of my client's databases, which I restore.

In designing new reports I may create new 'views' and UDFs, and name the accordingly.

After testing the new report against my out of date data, I send the RDL file along with any script files, to

my client in Cardiff - by email, as these are very small.

At the Cardiff end the DBA uploads the RDL file into their 'Report Manager' system on their internal network.

He also takes any entity script file and runs this within his SSMS to create or update the view or function.

I usually provide him with a small test script to allow him to easily checkout the 'entity' in question.

When the DBA runs the RDL for the first time he first needs to point it at his data source, which really involves

changing the connection string for the SQL Server to be used - this is easier than it sounds as he just uses

a few mouse clicks to point it at an existing data source.

The last task he carries out is one to provide a 'user friendly' name for the report. One with which his users

can identify.

This system seems to work quite nicely for us and quite often we don't even require a phone call.

Obviously, if I am sent a report (RDL) from him as a result of past third party consultant work, I need to

change the data source it is looking for at my end - to one that is know to my system. This also is much

easier than it first appears, or sounds. I will have any entities as I have the DB already, and no new work has

recently been undertaken by any outside consultants.

If this was a recently made report from outsiders, I would probably need their supporting script files too.

Simplest Report Scenario :

If I was to create a new report base solely on existing tables in the DB, then I would only need to transfer the

RDL file which would contain all the necessary code for data source, data sets, and the report layout and

formatting etc.

However, when I am physically creating a new report I find it easier to think of the three 'things', or aspects,

shown and discussed below. I think of a data source, then the data sets, and the using the designer to

create the RDL file itself.

let us go through these three aspects an see how the visual shell of the SSMS makes it easy for us to focus

on the project solution in this way.

Data Source :

In the Solution Explorer pane we usually see an entry similar to that shown below :-

=============================================

This illustrates that we have already made an 'rds', said as 'report data source'.

So how do we make this when there is not one in the SE pane for the new report ?

This is very straightforward, just click to highlight the project item in the SE pane, and right click

to bring up the context menu, as shown below :-

=============================================

Select 'Add' then 'New Item' after seeing this below :-

======================================================

My simplified IDE in my BIDS2005 shell brings up the following dialog screen :-

Select the 'data Source' template and click the 'Add' push button to bring up the dialog box shown

below :-

Provide a suitable name for your data source, also select the correct type (I suspect most will be SQL Server), and

then click edit to bring up the next dialog screen as shown below :-

All that needs to be done is for the server and database to be selected from the provided drop lists, as I

suspect most of you, like me, are using a reasonably standard, and simple, installation of SQL Server.

I have entered my details, selecting my SQL Express 2005 instance of a server, as well as the 'pelican'

name for the database itself, this is what I often use, as you have seen above. See the image below :-

As can be see above, the 'Test Connection' operation was successful.

This simply means that when the 'Connection Properties' dialog closes we see an entry in

the 'Connection String' text control on the 'Shared data Source' dialog screen as follows :-

And as a result of this, all we now need do is click the 'OK' button.

In the SE pane we now have this :-

=============================================

Yes, we have the new 'ClickStartTest' data source showing in the 'Shared Data Sources' section.

So what has happened after all this clicking and selecting ?

Quite simple really, the 'shell' has made a new small file called 'ClickStartTest.rds' as shown below :-

[ stands for 'Report data Source' ]

=========================================================================================================================================================================================

And if we open this new file in Notepad we see that it is very short and looks like this :-

The contents of this is not surprising to most of us, and the XML tags tell us all, only the GUID may be

a bit of a mystery to some of us. If you want to know more about GUIDs seek out a copy (buy hopefully)

of Alwyn's book.

So to summarise, making a Data Source is simple enough when you know how, and even simpler when

you know what is in it. Basically it is a set of pointers to the correct DB using the correct server, with a

couple of other correct items thrown in ;-) nothing magic or complicated !

Now, lets move on to see what 'Datasets' are in the context of report generation.

Data Sets :

RDL File / Script : [ generated by the Designer tool on the Layout tab ]

=============================================

==============================================================

==============================================================================================================

PHH - June 13th. 2010.


Chapter 2 - First Simple Example

Report Items - the Table Control :

A table is a data region that presents data row by row. Table columns are static. Table rows expand

downwards to accommodate the data. You can add groups to tables, which organize data by selected

fields or expressions.

Much of the training material provides ready made 'stuff' so that the report design process

can be started as soon and as quickly as possible. In many ways this is understandable,

as they see their task of teaching the use of the designer as a HIGH priority.

However, in my view, it is best to show the complete process of report 'creation' as it will

be experienced by most in a 'day-to-day' environment. For this reason I start with some

existing tables in an SQL Server database, and go from nothing else, right through to the

final display, printing, and even data exporting.

I will write this first example as a result of my experiences of a real world developer having

to create a report from scratch with limited experience. I have one thing to say first, and

that is 'I managed to get there!' and so I wish to share this with you.

Remember : The design and creation of a report is a "whole process", and should be seen

as one when learn how to 'do it'.

Also remember I am to use the BIDS 2005 version for this report so some small things may

change for later versions - nothing we can't easily cope with, so don't panic !

Now to get going.

In order to see the complete process from start to finish, we will limit ourselves to a few sensible

things - like a publicly available SQL database, a simple data set based on a single data table,

and a very straightforward report layout of a single table.

Let's roll up our sleeves and get going ;-)

Publicly Available Database :

Download from the internet either the light (LT) version of the AdventureWorks database, or the full version,

and attach it to your SQL Server 2005 (version 9.0) in SQL Server Management Studio (SSMS).

If you don't know how to do this consult 'ClickStartSQL' from my Executive page of my website.

When you have been successful your SSMS should look like that below :-

================================================================================

In the image shown above I have both the light and full versions of the 'AW' database attached and available for use.

For this first simple report I will use the light version, and the customer table from that database.

See below for the details :-

==============================================================================

As you can see above we have fifteen columns available for our simplified report. This will be more than

enough for us to test out our skills !

Later, when we get to more complex reporting we can join the data from other tables to make our report

both more realistic, and more interesting too.

Before we move on to the report creation, let us see the typical data rows in the table :-

===================================================================================================================================================================================

Yep ! we can probably do without the 'rowguid' column for our present purposes, so expect to not see that

in our report to come ;-) That is probably also true for the Password fields, so we can simplify things later

by not displaying them either.

Looking at the 'Messages' tab in the query pane of SSMS we see this :-

======================================================================

So there are not 1000 records to print, just 440 of them.

Starting the Report :

Open your BIDS software (Business Intelligence Development Studio) shell, or a full copy of VS with a Project

Type of Business Intelligence Projects, and the Report Server Project 'template'. I will use a BIDS 2005 shell

which looks like that shown below [make sure you close any previous solution] :-

==========================================================================

Notice that although I am using the BIDS 2005 as downloaded, when I open the shell it clearly shows the MS

Visual Studio name at the top left of the main window.

Before we add any suitable connection in the Server Explorer pane, let us chose to start a new project using the

File, New, Project options as shown below :-

============================================================================

After doing this you should get the New Project dialog screen as shown below. Make sure you select the required

project type as well as template, and then supply the name of the project as shown in the blue highlighting :-

If this goes according to plans you will get the following in the Solution Explorer (SE) pane :-

=========================================================

Notice we now have folders for Data Sources and Reports, both of which are empty at the moment.

We will now attempt to add a new 'Shared Data Source' by right clicking in the SDS item line as shown :-

=============================================================

Selecting to 'Add New data Source' should bring up a suitable dialog screen for an SDS and you will need

to supply a name and then click the edit push button to bring up the Connection Properties dialog.

Chose and supply data as shown in the following image, as well as doing a test to see that all is well ;-)

The result of all this connection dialog 'stuff' is as listed in the text input control below :-

If you appear to get success then click the OK button.

You will now see this entry in your SE pane :-

==========================================================

At this point we need to tread carefully, as when we come to try and add a new report we may have a wizard

take us sideways, or at least in a direction we don't wish to go !

Even the Mark Long video suggests NOT using the wizard, and taking control ourselves.

The problem comes because we select to 'Add new report' from the context menu as in the image below.

Below is what we don't want to do :-

==========================================================

If you get it wrong and see this dialog :-

then you need to click / select Cancel, and NOT the 'Next' option.

Instead you should select to 'Add' and then a 'New Item' as shown below :-

===================================================================

When the dialog screen is displayed select the Report template and provide a suitable name for the report as

shown in the image below :-

If you manage to get all this stuff done correctly you should see the following :-

==========================================================

In fact if we now look across the complete width of the VS shell we see that a few other things have changed,

and things added too.

See below for the full effect :-

==============================================================================================================================================================================================================

Unexpectedly perhaps, the SE pane has been overlaid with a Datasets pane, and the design middle section

added ready for use.

The design section has three parts to it and we will look at these in detail as we go.

What may surprise you is that we seem to be able to ignore the Server Explorer, and so far we have not

placed anything there for this new report. We will keep this in mind as we go.

Selecting Some Suitable Data :

Before we try to start our first report layout, let us first try to make ourselves a suitable dataset.

Look at the image below to see both that the drop down list for datasets is empty, and that we can apparently

add a new one using this option shown :-

====================================================================================================================================

After choosing to add a new dataset from the drop list, we get the following dialog :-

Study the above image and copy the input data into your own example.

I have only provided the Name, left the next two as defaults, and then copied the select statement from SSMS

after it was used and tested - way back before we started the Report itself. You may remember we were checking

out the 'AW' database entry in the list of attached databases.

Obviously a free text entry could have been made here, but I always prefer to supply one which has been tested.

In fact this script will get changed as we edit and develop the Dataset, but it is good to start with success, and

get back some data.

Click OK and you shall see this in your VS shell :-

==================================================================================================================================

Notice the presence of the 'CustomerLT' item in the Datasets pane. Also that it has a field for every column

within the table chosen from the AW 'light' database.

Now the fun bit, we are going to click an icon and see some live data, as easy as one click !

Look at the small image below to see the 'exclamation mark' icon (said 'pling' in English) :-

====================================

Although it says 'Run', it actually executes the SQL query script, and does not 'run' the report.

Let us see what we get when we click this 'run' icon :-

===========================================================================================================================================================

Yes, I know, we still have the 'rowguid', but that we not be shown on our report ;-) Also remember we may also drop

the Password fields also.

So as you can see we are getting meaningful data and we did not need to make an entry in the Server Explorer pane.

Oh! I should have said, I did move the 'split' pane divider 'thingy' up a certain amount so the data appears

closer to the simple text of our query - normally the space is divided 50 / 50.

Gaining Some Control :

Taking the 'rowguid' as an excuse to try something new, instead of just ignoring the field when we create our

new report layout, we can edit the dataset to not provide the data in the first place. In real life this may not be

a good thing to do, but this is a learning exercise, so here goes.

We can now do click our mouse in the data tab editor section and change our SELECT statement as follows :-

=============================================================

If you check back you will see that I have removed from the editable area a few fields from the previous list.

You do the same by changing the text in the Dataset script section with the aid of mouse and keyboard.

You will notice that the fields (columns) seem to stay in the Datasets pane, even after editing. However, if

you do a File, Save All, from the main menu bar, the Datasets pane is refreshed and should look like that

shown below :-

============================================

Now redisplay the live data for the dataset by clicking the run icon (pling ! ) and see this :-

==============================================================================================================================================

Note I have adjusted some of the columns widths for a better, and cleaner, display.

We have done enough with data, and can now move on to creating the layout of the report.

Laying Out the New Report :

At this point in the process, if we select the 'Preview' tab from the three at the top of the design area, we go no

results - but not an error - as shown below :-

=============================================================================================================================

At first you may get a symbol showing something is going on and there is a slight delay. The icon

used is a green circle which is 'dashed' in style. When this happens in the VS shell the SQL Server is being

queried and data retrieved. You will see this a lot while doing reports.

Now click on the middle of the three tabs, the 'Layout' tab.

You will see something like this shown below :-

===================================================================================================================

So to stop us getting 'nothing' when we run our report preview, we need to put a control on the report from the toolbox,

and then provide the control with some simple data.

Before we change and improve our non-existent layout, let us make sure we have the Toolbox available for use.

If you can't see your Toolbox on screen - mine is below the Datasets pane as follows :-

==========================================

then go to the main menu bar and select 'View' as below :-

==============================================================

I am expecting that you will have some basic Visual Studio skills, and can move panes around the screen.

If you don't have sufficient VS skills to follow this, then you probably don't know enough to tackle reporting.

You can learn more about the VS shell by working your way through 'ClickStartVS' from my web site, using

the recently added 'Executive' facility.

For now, place the Toolbox somewhere handy for the way you work in VS.

Notice that there are only about ten tools in our 'box' and so this layout process can't be too complex as far

as placing controls on the report 'layout area' is concerned.

The Most Common Report Style / Format :

As fancy as we would like to be as designers and developers, the simple fact of the matter seems to be that

most reports are required in a table format. Yep! a plain old table is enough to help the reader of the report

get all the information they require.

Whether you agree on this or not, we will start with a Table report item, and make a tabular report. Everyone

needs to be able to create tabular reports - so here goes.

From the Toolbox drag and drop a table item onto the layout grid in the centre of the screen.

You should get something like this :-

=================================================================================================================

As with the convention of most other parts of the VS shell, the asterisk ( * ) after the 'SimpleOne.rdl' in the main tab,

suggests that the content has not yet been saved. Do 'Ctrl S' and you should see it disappear.

In the image above you see we have a basic table showing, with only three columns at the moment.

Let us now attempt to get some data onto our report layout, and as quickly as possible generate a report, as simple

as it maybe ;-)

To start our 'design and make' process, go to the Datasets pane and click on the 'CustomerID' item, then drag it

into the first column, and also the middle of the three rows indicated in the table control.

You should see this :-

=======================================================================================

The blue highlighting of the 'CustomerID' item, in the Datasets pane, goes when the drag cursor enters the

layout area. Release the mouse button - i.e. complete the drop operation - and you will see this below :-

=================================================================================================================

Now do two more similar operations, drag the FirstName item to the second column and middle row (called the body

of the table), and then do likewise with the LastName to the third row, also the the body 'cell' of the table control.

Here is a snap shot of me just about to drop the last of the three column items :-

===============================================================================================================

Once you release the mouse button you have completed the three column form of our simple report and

you should now see this :-

================================================================================================================

You may have noticed that if you click anywhere outside of the table control it loses focus and looks like this :-

================================================================================================================

To place the control in focus once again, click anywhere within the table control area. This is what I get, and it

is not a difficult question to answer if I ask 'where do you think my mouse pointer was when I clicked ?"

===============================================================================================================

Once we have run our report (in a few seconds time) we will return to the table control to see what the various parts,

or sections, of it are all about - what they do, and what they are used for.

For now lets us generate our first data centric report ;-)

Move the mouse pointer to the top of the design area and click the 'Preview' tab.

In a few seconds or less you briefly see the green circle icon and then this :-

=====================================================================================================================

*** Note *** I have truncated the image at Kathleen for ease of display in these eNotes.

Now try clicking the little green icon in the menu bar belonging to the layout area the screen changes to

look like this :-

============================================================================================================================

Clicking once more changes it back to what it was.

You may also notice that we seem to have 12 pages in Print Layout format, and only 10 in the basic screen layout. Since

most reports are designed to be printed then it is probably best to always work with the Print Layout number.

Changing and improving some basic layout features :

Although we seem to have live data in our first report, and 12 pages of it too, we seem to only have some basic column

headers, and nothing more, as the report embellishments.

Form our next task lets us try to improve the table headers, and also provide a basic report header, as well as some basic

report footer information.

Here goes.

Click the layout tab to return to the layout area of the designer, Then click in the table header section of the first column.

You should get something like this :-

================================================================================================================

At this point we have an option - or few !

We can just click in the area to get into direct edit mode from where we can change the text, see below :-

===================================================

Or we can bring up a much fuller option dialog from where we can change many property settings for the table column header.

To get the full dialog screen, right click in the relevant column header cell and you should see this :-

=================================================================================

Simply select the properties option to bring up the dialog as shown below :-

Notice I have changed the 'value' to reference, this means the string content to you and me !

Now before we close this dialog, lets use the tab selectors at the top to show the 'Font' choices - do that now.

==========================================================================================================

Follow what I have done, and change both the size and the weight of the font. Leave it as Arial however.

Now click OK at the bottom of the dialog to see this :-

=====================================================================

Notice that when I did this the new value has been right justified within the cell. We can see this in the menu

bar, and this is shown below :-

===================================================================

To change this, make sure the column one table header is in focus, the select the left justify icon by a mouse click.

This is what I get on my system, with my current settings :-

=======================================================================

Before we think of adding any colour to our report, go and make the same changes to column headers 2 and 3.

You should then have something like this :-

[ note - I have added a colon to make it obvious to you that changes have taken place ]

================================================================================================================

OK then, how does our report look now ? Click the 'Preview' tab to see what we get :-

======================================================================================================================================

A bit better, but still plenty to do as yet !

Actually, this is what report design is really like - get the data, and the gradually improve and 'massage' the

output until it is satisfactory ;-) All part of the design and make cycle.

Before we attempt to change the justification of the column contents, let us try to add some colour to the

column headers.

Put the layout tab in focus and then click in the first column header while keeping the mouse button down - then

drag it across the second and third columns in such a way as the whole table header area is put into focus.

See below for how mine looks :-

===================================================================================

What we will do next, is use the Properties Pane (PP) to change both the background and the foreground colours.

So to be ready for this change the size of your PP to be a suitable size (general VS skills required).

With all three table column headers in focus, select the Background property and then the web colour as shown :-

===================================================================

Once the new colour appears in the input field on the right, the change is automatically reflected in the layout of

the report itself - see below :-

================================================================================================================

Now do similar for the foreground colour - BUT - it is called 'Color' in the PP, as shown below :-

==================================================================

Now preview the report and you should see we have some colour for the first time - like this :-

===========================================================================================================

OK then, lets move forward and use what we have learned. Drag and highlight all of the body of the table in the

same way as previously - click and drag - then select the left justify icon as shown below :-

===============================================================================================================

This starts to look better - see below :-

===================================================================================================

With the three body cells highlighted in the table control, use the PP to change the Background colour to 'SeaShell'.

This is what I have :-

===============================================================================================================

Which produces an output like that below :-

==========================================================================================================

I some ways (many ?) this starts to look like a report ;O))

Before we move on to add report titles and page footers, let us make a couple of useful changes to what we already have.

It would be useful to have the column headers appear at the top of each printed page (Print Layout) as well as having the

headers stay at the top while we scroll down the page in the alternative layout mode - see what I mean below :-

==============================================================================================================

Notice above, page twelve now has table column headers.

And in the image below we have changed the screen and pane sizes to see we now have a scroll under the

headers - we are viewing page one :-

Notice how row 57 is only partly visible.

And so how do we do this ?

The answer is once again quite simple - when you know how ;-)

Look at the image below and then we will talk through how to do this :-

===============================================================================================================

Go to the 'Layout' area and if your detail does not show the table control as above - with extra grey top and left hand

side sections - simply click the mouse with the pointer anywhere in the table control area. Obviously my pointer

was in the bottom LHS (left hand side) cell when I clicked.

Now to bring up the 'Table Properties' dialog we have to right click the mouse while the pointer is in the little grey

square at the top left of the table region.

===============

If you get it right the context menu will look as follows :-

=================================================================================================================

In which case you can now select the Properties option to bring up the necessary dialog screen :-

=====================================================================================================================

Notice that the two check boxes which are selected - these were done by my clicks to bring about the behaviour

described in the notes above.

You make the same selections and then click OK. Test out the preview to see all is well, and change the Print Layout

option also.

What happens if we select in the wrong place ? :

Well all this starts to make sense if we explore what happens if we click in cells adjacent to the little grey square for the

table control properties.

Lets us see what context menu we get when we right click in the grey column header :-

=====================================================================================

The options available in the context menu are obviously related to columns, and not the table itself.

Also when we select Properties from here we do not get an extra dialog screen, it just puts into focus the PP itself.

Alternatively, if we right click in the square with an icon which looks like a downward facing square, we get the following :-

=================================================

In fact this is the same context menu that we get with all of the three grey squares to the left of the table control. Those

for table body, header and footer. Also the properties option once again simply set focus to the PP and does not bring up

another dialog screen for changing properties.

If you are to experiment with any of these options, take care, and remember 'Ctrl Z' can always get you out of trouble if

you appear to lose any items / objects.

Notice that the context menu from the left hand area of the table control provides us with a way to manage rows. We will

see just what this does later.

For the moment, we need to see how we can add and manage columns in our report. Lets move on to that now.

Adding more table columns :

In version 2008 of BIDS and the VS shell we can do drag and drop from the Datasets pane, however for the 2005 version

we are using here we need to use the context menu option.

Let us try to add an extra column at the end of the three we have now - that is on the right hand side of 'Lastname'.

Click in the table area to bring up the grey regions to left and top.

Now place the mouse cursor in the top right grey table cell and right click as shown :-

==========================================================================================================================================

It now makes sense to choose to insert a column to the right, as we wish to have it on the end, and not between

Firstname and Lastname.

Below is the result for me :-

============================================================================

Now go to the Datasets pane and drag the email field (item) into the body cell of out table control, as show below :-

=======================================================================

The result is as below :-

=======================================================================

Now you can go to the column header (right click) and in the properties dialog, make changes.

However, since we just need to add a colon to keep to the stye of the other columns, we can do it directly, with

a left click and edit.

Did you notice that the added (extra) column has been given the same formatting as the existing ones. I suspect

that what was copied was that formatting of the cell clicked - in this case column three.

Okay then, what have we got in our preview ?

Try it and see :-

========================================================================================================================================

It now looks as if we could do with changing the width of the columns in the layout area, by dragging the column

boundaries. Lets give it a go :-

=======================================================================================================================

Do notice that it does not matter about the displayed content of the body cells being partly hidden, the data will still look

as it should when we do a preview.

Lets do that now :-

=====================================================================================================================

One thing which is now not correct, is if we display the report after clicking the Print Layout icon, we seem to have

lost our last column, see below :-

========================================================================================================

However, if we explore further we will find that the fourth (and last) column has been placed on page two as below :-

==============================================================================

One quick and easy way around this is to use the Page Setup icon to make the report Landscape instead of Portrait.

Here is the required icon :-

=====================================================

Selecting this icon brings up the required dialog and we can change the report orientation as follows :-

===========================================================================================

Now click OK and re-check in both layout modes to see we have all four columns on one page.

Here is what I have :-

Note that in the general screen layout of the report pages (not the Print Layout mode) there is no apparent difference.

However, here is Print Layout mode, which as changed as required :-

==================================================================================================================

Note this is page two of the report - total pages 16 in this screen mode - there is still 10 pages in the other 'screen' mode.

Why has then happened when we seemed to have plenty of space on our Portrait paper ? To get the possible cause

we need to examine what we have in the layout section.

Here is what I have :-

==================================================================================================================================================

It may surprise you that it is the extra grid area on the right which causes the problem. Notice that the ruler shows a width of over 21 cms.

Most of us are used to Form and Window design tools where grid stuff seems to be ignored once we have our object. Well, this seems

NOT to be the case here.

If I now reduce the width of the grid by mouse drag, and leave it just wide enough for our four columns as they are now, we can then set

the orientation back to Portrait and see if we have fixed the problem - here goes :-

===============================================================

Finally a 50% view of the last page in this readjusted format :-

Making some final report additions and changes :

Okay guys, this seems to have been a long haul but hey! that is reporting for you ;O))

It would seem sufficient for now if we try to add a report header on page one, and then a page footer which will be

printed on all pages and have the page number, date printed, and the likes.

So to the final bits of our first report .......

To make things as clear as I can before we start to make our last additions etc., let us look at a layout from a fully

working report which is in use.

Study the sections to this report, those indicated by the long horizontal grey 'bars'. These are labelled as 'Page Header',

'Body', and 'Page Footer' :-

===================================================================================================================================

Before we return to our developing report, let us discuss what we can see above in the working example.

Notice the report is once again based on a table control - as many, many, do. However, the table itself is

within the 'Body' section of the report. Above this is a report Page Header section and then beneath it a

report Page Footer section.

Above the table in the report body section is a text field extended across the whole page, this will appear on

each and every page.

This is enough for us to know before we try and add some further items our own simple report.

Let us remind ourselves just what we have gotten so far, here is an image :-

=======================================================================================================================

We now need to find a couple of more 'magic' areas of the layout pane, where right clicking brings up different context menus

from what we have so far seen.

Referring to the image above, click in the top left little grey square to get this context menu :-

========================================

Note that these are report options, and not table options. let us see what we get when we choose to display the properties :-

===============================================================================================================

You may like to have a quick look at the other tabs, but we can leave all these settings as they are for now.

We are really interested in the middle two of the four context menu options - those for Page Header and Page Footer.

Bring up the report context menu once again - click the very top left little grey square - and select first Page Header,

and then Page Footer.

You should get this in your layout section :-

=========================================================================================================================

Notice we have also got two new report sections, similar to the working report we saw earlier.

At this point it is probably a good idea for you to return to the menu and make these selections once more - yes, a second

time !

You will see that they new sections seem to disappear, however, I feel that it is more of a toggle than a removal. More of

this later when we have some entered content, to so if it disappears or just turns off and on - toggling.

Adding a Page Header :

Before we can place any text in the Page Header section we need a text box control from the Toolbox.

Go to the Toolbox and drag a Textbox item into the relevant section as shown below :-

=====================================================

This text box now needs to be correctly placed and stretched across the page similar to what is shown below :-

=======================================================================================================================

I have dragged the right hand end of the text box to snap on the grid at the same place as the end of the last column.

However, now there does not seem to be enough room in the Page Header section itself, so we need to move the grey

bar dividing the two sections - Page Header and Body. See below :-

==================================================================

Dragging this further down than shown, we have made some space to make the header text box deeper.

==============================================================================

The reason for doing this is so that we have room for a larger font text heading.

Now enter some text into this new text box and also from the Textbox Properties dialog make a few changes

like those shown below :-

=======================================================================================================================

This shows in Preview to be like this :-

=======================================================================

I have had to make a couple of changes to the Page Setup as well as ease the right hand side of the report controls.

Here is what I now have :-

==================================================================================================

Note the pagination and orientation often seems a bit tricky to get right, or as you require. However,

usually I leave this to the end when everything else is sorted out.

For now get yourself a header similar to that shown above - colour it if you like, I will do this now :-

========================================================

And the result is as follows :-

===========================================================================================================

Whoops! I have just noticed a typing error - "Simpley" should read "Simply" so I will leave this to you to edit the

appropriate text box ;O))

Good Luck !

Now lets move on to adding detail to the page footer section, useful details at that !

Adding a Page Footer :

before we move into the process of adding our own page footer detail, lets look at the output from an existing

report. Don't under estimate the value of looking at existing reports, and ones done by more experienced guys.

You can learn a lot from studying existing reports, as well as finding out what NOT to do sometimes ;O))

Notice that in the footer shown above we have some useful and important information. In fact for one of my

clients they request that this is placed on all reports, in the same format and style.

What is the information shown ? :

You will clearly see three parts to the footer. On the left we have the user name, as it is know to the system.

On the right we have the page number and the total number of pages in the report. And finally, in the middle

we have the date and time that the report is printed.

If we now look at the same section of the report, but in the layout tab, we can see more of how this is formed.

Study the image below :-

====================================================================================================================================

From the above image we can see that we simply have three separate text box controls, which have appropriate

contents. To see more of the detail of the contents lets look at the Textbox Properties dialog screen for the left

hand part of the footer.

Below is shown the user information screen :-

Looking at the detail in the above image it is clear we need to find out more about the 'Value' input box.

If we click the 'fx' (function) button we get this next screen, shown below :-

Looking at the image above, it is no starting to get clearer, there is a set of system functions which provide

the developer with immediate access to 'User Id' as well as 'Page Number' and 'Total Pages' too.

The top section of the 'Edit Expression' dialog is just an edit box where text can be entered freehand but

also where some text is automatically entered when we click and choose specific functions. You will need

to practise with this dialog, and when we come to do our own footer in a short while this will be a good

opportunity for you to start experimenting.

Although the contents of the edit box look complex, it simply has two strings shown by red " " quotes, and

then the concatenation symbol ' & ' to add these together along with the output from the chosen function.

Note that ' User!UserID ' is placed in the edit box (at the cursor position) when the relevant function name is

selected from the lower list by a mouse click.

Let us move on to see another 'global' function in the right hand part of the footer, for page information :-

And to see more of the 'Value' input detail select the ' fx' (function) button :-

Once again, in the image above, we see that the group of 'Globals' functions provide the detail.

The text ' Globals!PageNumber ' and ' Globals!TotalPages ' is simply concatenated with some static

string characters entered by me, the developer.

Okay then, are you now ready for the middle part of the footer, that part where we use DateTime functions ?

Study this Edit Expression screen to see what you can make of it :-

As with many other places, DateTime related 'stuff' can be both fiddly, and frustrating, and is commonly

the place where mistakes are made - so tread warily !

One big problem is that they can vary from which parts of the overall system you are using, dotNet framework,

T-SQL itself, and there seems to be a variant in RS too - maybe this is more VB like, I am not an expert but

guess it is something like this. Anyway, when I find problems with 'DateTime' features, and then solve them, I

make entries in the Appendices for reference by you and me ;O))

Once again, the edit string is just a concatenation of function output, with user entered character strings.

Now to add our own Page Footer :

Return to our layout tab, and the Page Footer section. Make sure that it is deep enough for some text boxes

to be added, as well as a small space above the footer details.

Here is what I have made before any layout screen entries are made :-

================================================================================================================

Now we need to place three text box controls in a line and stretch them to be about one third of a page in

width, making sure they join up as well.

Try this now for yourself, and then I will show you what I have done :-

=====================================================================================================================

Now, if we think straight on this one we can save ourselves some work - unless we wish to get some practise

in with the dialog screens !

Below are the text strings required for the three parts of the Page Footer, copy and paste them into the text

boxes appropriate to their contents / function - I suggest you use the 'Value' input box of the 'Textbox Properties'

screen. this seems to be the safest and most reliable way to paste.

="[User >> " &User!UserID &"]"

="Printed on : "&Day(Now)&"/" &Month(Now)&"/" &Year(Now) &" at " &TimeOfDay

="[ Page " &Globals!PageNumber &" of " &Globals!TotalPages &" ]"

Below is one example - that of the User Identity :-

===================================================================================================================

Complete all three and the try a 'Preview' by clicking the appropriate tab.

Here is my layout before I add colour to background and text :-

=====================================================================================================================

And a sample of the output when the report is run :-

Now let us add colour - highlight (or set in focus) all three text boxes using the mouse and the 'Ctrl' key to get :-

======================================================================================================================

and using the standard PP (Properties Pane) set the colours as see below :-

===========================================

This now looks like this, after de-selection, or moving focus :-

===================================================================================================================

Finally, a last preview after selecting the appropriate tab :-

Well, that looks OK according to my tests, so make sure you get yours going as well ;-)

Good Luck.

Finally, we will discuss why we missed out using the wizard ......

Why 'NOT' to use the Wizard :

See you in the next section.

PHH - June 13th. 2010.


Some Further Improvements - Data

In the last half (or more) of the main section of this chapter we focused on the layout of the

report, and the formatting of columns and headings, and so for.

Now let us look at ways we can improve this simple report, as far as the data is concerned.

Reducing the Returned Set :

Move to the Data tab and click it to bring up the Dataset editor.

Add a WHERE and ORDER BY clause as shown below, and click the 'pling' icon to run the query.

Here is the returned data I get on my system :-

================================================================================================================================================

Note I have collapsed a couple of columns to make the display clearer, as well as also resizing a few.

Also note the my first 'test' WHERE clause has been commented out.

When we run the report the newly change dataset will be used to produce this output :-

==============================================================================================================

So we have a vastly reduced 'returned set' and the order of presentation is 'Lasname' A to Z.

Interactive user input - parameters :

We can go a good step further now, and allow the user to make an input of data items before the report is run.

Let us return to the Layout tab, and in the top left grey square right click to bring up the context menu for the

report, as we did previously, in the main section.

This is what you need :-

================================================================

Having got the correct context menu, choose Report Parameters and bring up the following dialog screen :-

=============================================================================================================================================================

We now need to add two new parameters so that the user can specify the first letter of the range

which will restrict the data set, as well as the second letter also.

So with the dialog on screen click Add and then make entries similar to those shown below :-

======================================================================================================================================================

The values entered into the dialog will cause an input parameter to appear at the head of the report section in the

manager, and the value of this parameter can't be blank or NULL, the default is 'A' and the possible drop list values

go from A to D.

Let us close the dialog and see what we have in the Preview tab :-

==========================================================

In the image above we have a drop list as hoped, and the value displayed prior to running the report is 'A'.

All we need to do before this will affect the report content, is to tie it into the Dataset script in some way.

Going into the script of the Dataset tab, we need to change the text as follows :-

==================================================

The '@' sign is added to the 'name' of the parameter in our script, and this picks up the value from the

interactive drop list.

When we try a run of the Dataset, using the 'pling' icon, we get an input box as shown below :-

===========================================================================================

Initially parameter values are blank, and test values need to be supplied. It would appear that the values

are stored for subsequent test runs so it is much easier to test than it first appears ;O))

Click OK we get a bunch of rows returned in the query set, similar to what we have already seen.

To complete this section, lets add a second input parameter for the end of the range :-

Revisit the layout pane and bring up the context menu for the report, by right clicking in the little grey

square at top left of the layout design area.

=============================================================================================================================================================

Don't forget to fill in the value of the 'Y' entry by supplying another 'Y'. Then click OK.

Now in preview we see this :-

===========================================================================================

The second drop list looks like this :-

=====================================================================================

Don't forget that we still need to tie in our new parameter, by changing our Datasets script as shown below :-

============================================================================================

Notice the WHERE clause now has two '@' parameter entries, AND, the 'Define' dialog shows we can

actually enter test and trial values which are not in the drop lists we made. 'D' is but 'H' is not.

Click the OK button to see all works as expected.

So with a little bit of image 'magic' and double exposure, we can see this limited range being tested :-

===========================================================================================================================================================

If you wish to push this example a bit further, you can revisit the parameter dialog and increase the drop list letters.

However, take care, this is a bit of a silly example, and there are other ways to do this better. But a little trial

and error never hurt anyone ;-)

Now that we have pushed things to restrict the Company name, we do not have a column for this !

So your next trial is to add a further column, turn the report into landscape, and re-run it.

Before we go and do our last trial test after these new changes, make sure the order is changed as shown :-

===========================================================

The old one is commented out, and now we ought to get the report order by Company name.

Lets try it for input values of 'D' to 'I' :-

========================================================================================================================================================

To be quite honest, I think we have come quite far enough for one simple report ;-)

Let us now move on to the next chapter and learn some more advanced tricks and skills using a more

suitable example.

We need to move onto using more than one SQL data table, and be able to JOIN these in a meaningful

way.

We also need to see if we can aggregate parts of the report and print out summary values and the likes.

See you in the next Chapter.

PHH - June 18th. 2010.


Chapter 3 - More Complex Example

Report Items - the Table Control :

A table is a data region that presents data row by row. Table columns are static. Table rows expand

downwards to accommodate the data. You can add groups to tables, which organize data by selected

fields or expressions.

First of all, don't let the wording of this chapter heading put you off ;-)

By 'More Complex' I mean we will look at two SQL tables being JOINed in the SELECT statement, so that

one table control in our report can display field (or column) data from the two tables, in a single row.

Basically we are going to de-normalise the AdventureWorksLT data to suit our reporting purposes.

You are probably well aware that 'normalisation' is a good thing for us and our data - stuff in one place

and no confusion over maintenance etc. etc. - well the down side of normalisation is seen when we need

to report on our data. The various JOINs allow us to take the data held and de-normalise it ready for

display and output purposes.

So to summarise, in our results set that we generate ready for reporting, the JOIN parts of the FROM

clause allow us to duplicate lots of existing data, as we the user would expect to see it. Good reporting

means we need to have a firm grasp, and understanding, of the use of the various JOINs.

Okay then, lets get to work on a straight forward example.

Below we will see the tables available to us in the AWLT database :-

============================================================

Yes, you will notice that I have done a little bit of 'time lapse' screen capture 'magic' to allow us to see

two tables from the AWLT database highlighted at the same time ;-)

For this more adventurous report we will use both of these SQL tables to provide us with suitable data

from which to populate our report table control.

Let us examine the fields of the two tables to see how they are linked.

First the 'SalesOrderHeader' table - see below for details :-

======================================================================

Notice that we have one primary key called 'SalesOrderID', and three Foreign keys, of which the one

we need to be particularly interested in, called 'CustomerID'.

Now lets see the structure of the second of our report tables, that of 'Customers'.

See below for the column, or field details of the Customer table :-

==========================================================

Notice we only have a primary key showing - no foreign keys - and the primary key is the 'CustomerID' field.

It does not take much SQL experience to realise that we can join these two SQL tables using the two

'CustomerID' fields. However, do be warned that most real and legacy databases do not have the names of

the fields the same. It is a bit of an 'art' form to use clients existing and 'real life' data since naming conventions

have generally been changed a few times at least, over the years ;O))

Some preparatory work in SSMS :

Before we get into using BIDS 2005 to design and create a report, let us do some exploratory work using SQL

Server Management Studio (SSMS).

Open your SSMS and connect to the appropriate 2005 SQL Server, where your AWLT is attached.

Select the 'AdventureWorksLT' database in the Object Explorer (OE) pane.

In order to check that you have access to the data, and all is working OK, right click the 'Customer' table and

get back a bunch of records - see below for the way I do it on my development PC :-

==============================================================

[ The above image was made of SSMS 2008 .... read on .... ]

At this point I had better remind you that I always use SSMS 2008, simply because it has been improved by MS

a lot, and gives me equal access to SQL Server 2005 and 2008 for my projects - great for developers in my opinion.

So if you don't have SSMS 2008, go onto the MS site and download the free application. It was possible to

download this as a separate download to SQL Server 2008 Express.

Unfortunately, the handy context option shown in the image above is not available in the SSMS 2005 app. and

what you see is as follows :-

===============================================================

[ .. image above is from SSMS version 2005 .. ]

If you choose 'Open Table' you can at least see that some data is returned. But my advice would be to

go and get the latest SSMS offering from Microsoft.

From now on any images you see me produce form SSMS will be from the 2008 version, unless otherwise

stated.

Joining the two tables :

We need to get a feel for what we are about to use in our report design. A feel for how we JOIN the two SQL

tables, and what this may look like in image form also.

With SSMS open and connected to our SQL Server 2005, open a 'New Query' using the main menu option :-

=======================================

In the new tab which appears on the right hand side of the Object Explorer (OB), right click anywhere

within the clear tab area to bring up the context men - see below for an example to follow :-

========================================================================

[ Note that the keyboard shortcut is 'Ctrl+Shift+Q', as long as you have clicked in the tab area to give it focus. ]

The Query Designer should launch and you will see something like that shown below :-

==================================================================================================================

For now just 'Close' the 'Add Table' dialog and focus on the lower section of the 'Query Designer' window.

I will now copy into this query script editor area, a query I have already built and tested in SSMS previously.

Type this text into the query editor yourself manually, or try copy and paste from here :-

========================================================

SELECT *

FROM [AdventureWorksLT].[SalesLT].[SalesOrderHeader] SH

INNER JOIN [AdventureWorksLT].[SalesLT].[Customer] C

ON SH.CustomerID = C.CustomerID

ORDER BY SH.CustomerID

Now, with this text in the Query Editor, click the mouse in the top section of the Query Designer window.

Quite a few changes take place and you should see something like this below :-

The changes are VERY interesting, and the first thing we notice is that our simple query script has

been changed for a fuller - and in some ways more correct - version by the Query Designer (QD) itself.

The second obvious change is the inclusion of a table 'schematic image' in the top portion of the QD.

You will need to experiment with resizing both the QD window (and its three sections) as well as the

schematic tables themselves. Below is how my QD windows looks after such a few changes and

resizings :-

===================================================================================================================

Notice in the image above that if you hover the mouse pointer over the JOIN schematic, the tooltip is most

useful as general feedback information.

Take CARE :

Although this link was correctly made 'automatically' by the QD from our SELECT statement, you MUST be

well aware that this not always the case.

When we use the QD by selecting a group of tables it tries to do an auto join based on field names alone,

and for most legacy databases the tables do NOT have consistent naming applied.

In fact we often see naming conventions like 'acc_customerID' and 'ord_customerID' which specifies within the

field name that the table is Accounts and Orders, and the detail is CustomerID.

Although I have not tried this personally, I would guess that incorrect, or no links are made under such naming

conventions - and auto linking.

Quite often in training DVDs the presenter actually encourages the user to physically remove all

auto links, and them remake any which are required. I prefer this latter approach.

For the case in hand, the link is correctly made since the QD is working backwards from our supplied query

statement. Since we tested this statement in a previous query tab, and it checked out OK, it is not surprising

that it is showing correctly.

The summary is - take care with all schematic linking, and thoroughly check them out to see they are what

you really require.

More with the QD :

Before we move on, try a useful exercise with the QD as it now shows.

Check the top check boxes in both tables, and physically remove all the rest of the checks.

Hopefully, you will see something like that shown below :-

====================================================================================================================

Notice the syntax of the SELECT script, the " SELECT SH.*, C.* ", this is interesting, and may come in useful

at some other time in our SQL work and reporting.

We will return to the QD when we need to join further tables at a later stage, to make more complex reports

than this one in hand.

===========================================================================================

<< *** Note *** On the operation of the QD >>

The QD seems to work 'hand in hand' with the query tabs. If an existing query script is highlighted with mouse or keyboard,

and then the QD opened (using 'Ctrl+Shift+Q'), then the schematic and other parts are fully displayed in the sections of the QD

for the query which is highlighted.

If however, nothing is highlighted in the query tab, and then the QD is opened, we get a blank or empty set of QD sections.

When the QD closes, and a new query script has been created, the query text is placed in the query tab at the position of the

cursor - before the QD opened.

If you are not experienced or practised at this, you may well get a new query text inserted into the script of an existing one.

Remember, there can be multiple queries together in a query tab. The one highlighted is the one executed.

Multiple queries need a 'GO' to separate them - this 'GO' belongs to the SSMS shell and not T-SQL !

===========================================================================================

Now close the QD by clicking 'OK' and you will be returned to the query tab we open afresh a while back.

The query we had in the designer is now in the tab pane, and when we 'Execute' with the red 'pling' icon in the

menu bar we get this output shown below :-

==============================================================================================================================================================================================

Okay then, I think we have a suitably 'tried and tested' query to use in our new report, so let us

make a start to it now.

Let us call our report 'CustomerOrders' as this probably best suits the data and what we are doing with it.

What we are 'Aiming' for in our new report :

We will hope to add a couple of new features from what we did previously. One will be to sum the values for

the whole bunch of orders, based on order value and tax and shipping costs too. So we will be summing across

the complete table.

And, just for the fun of it, we will see if we can further extend out interactive report controls to allow the user to

select any number of existing customers to be included in the report. This will entail making a multi selection

drop list of all the customers in the database (DB).

Well, those sound useful aims indeed, so lets get to work ;-)

Starting a new report :

Open up the BIDS 2005 shell, or your preferred VS shell, and make sure you select the most recent project if

in fact this was that done in Chapter 1 - see below for my IDE :-

========================================================================================================

With this project open you should see the Solution Explorer (SE) pane like this below :-

=================================================

Now right click the 'Reports' item in the SE and bring up the context menu as shown below :-

=======================================================

Remember, we wish to Add a New Item and not get the 'Wizard' by selecting 'Add New Report'.

The wizard forces us to make too many decisions in quick succession, some of which we may not be certain

about at the start of our design and make process. Let's take it step by step.

Select from the context menu and sub menus, to bring up the 'Add new Item' dialog as shown below :-

Select the 'report' template, provide the name - as 'CustomerOrders' and then click the 'Add' push button.

You should get this below, if you have done everything correctly, and been successful :-

================================================

And your full IDE should be clean and fresh as that image shown below :-

===============================================================================================================================================================================

Okay then, the first thing we need to do is create a suitable Dataset, so go to the 'Data' tab in the central

editor section of the VS shell, use the drop list to select a <New Dataset> to bring up the 'Dataset' dialog

window - see below for my completed one :-

===================================================================================================================

If you do this correctly then there will be a couple of major changes when you click the OK button.

Firstly, the query gets placed in the editor section of the 'Data tab', as shown below :-

==============================================================

Secondly, the Datasets pane get filled with new entries - mostly field item names as shown below :-

==========================================

And by pressing the 'Run' icon (or 'pling') we get data populating the lower section of the 'Data tab' area - like so :-

=========================================================================================================================================

Please note that we have provided many more fields in the 'CustomerOrders' dataset than we will need, but it

will not be a problem to us, and we can find how to remove surplus ones later.

My advice would be to move cautiously, leave all fields as they are at the beginning of report project, you never

know when you may have to include an extra one - clients are funny folk, as no doubt you all well know by now ;O))

If we right click an existing item in the fields list of the 'CustomerOrders' dataset we see this context menu :-

====================================================

We will see later how we may use some of these options, particularly the one to add a calculated field.

For the moment it is enough to see what we get if we chose to open the 'Edit Field' dialog box for our

selected item - study this below :-

Notice that the name in the Dataset is copied from the field returned from the SQL query, and we would

recognise this as the 'Database field' name. if we so wished we can change the name used by the RS

shell. But I would advise caution, too many variations of names for fields and columns can become

very confusing, and facilitate mistakes and human errors too !

For now lets just leave all the existing fields, as well as the names they have been given. Lets move on.

Starting the Report Layout :

Because we still have a lot to learn - AND - because our chosen example best fits a table control, we will

display the report data as rows in a table.

Remember, most reports make sense in table format, so don't force the issue to 'show off' your reporting

skills !

Changing Grid Size :

Before we get started on our report layout, let us gain some more experience by changing the grid size

from the default setting of 0.25 cm.

Right click the top and left most little grey square to bring up the context menu for the report itself,

as shown in the image below :-

[ often the centre tiny 'black' square needs to be showing for the correct context menu to show - just left click for this ]

================================================================================================================

Notice the grid spacing, we will change this.

Now select the Properties option to bring up the following screen :-

Leave the Draw and Snap boxes checked, but change the spacing from the default of 0.25cm to 0.20cm and

the click the OK button.

You should now see this :-

=================================================================================================================

Fine, lets keep it like this for the duration of the whole report design period, it maybe that we will prefer this

setting to the previous one.

Lets start the creating process.

Increase the width of the layout grid to a full 29.0cm - this will give us plenty of room for our columns.

Drag a 'Table' control onto the blank layout grid and stretch it across the whole layout grid.

Even though we will not use them just yet, and a Page Header and Page Footer section as well.

Remember to make the tiny black square visible before right clicking for the report context menu.

After all this the layout grid ought to look something like the image below :-

======================================================================================================================================================================================================

As you can see in the image above, we still only have three columns showing. So in preparation for

much more data than in our last simple report, let us add a handful more, and by a different method to

last time we added columns.

Click in the header of the first column and then right click to bring up the context menu as shown below :-

================================================================================

We now need to be careful what we do, or probably to put it a more meaningful way, we need to be careful

how we interpret what we see happening - or what we think is happening !

Before we go into any inserting operations it would be best to add a small text 'marker' to the detail row as

shown below :-

======================================================================================================

Now we have a way of tagging and tracing what goes on as we add more columns.

So now we have this before we do any additions :-

================================================================================================

This time go ahead and insert a new column, and to the left of the first one.

The immediate result should be this shown below :-

==============================================================================================

To keep track of our actions add another marker to this as " << col. four >> "

Notice that the 'water mark' type text of 'Header' and 'Footer' seems to be placed in the second column,

but second column after any additions, and I suspect also after deletions. This is why we need a tag

or two to keep track of insertions - otherwise they appear to be going wrong.

So now copy what is shown below and insert another column, but to the right this time.

================================================================================================================================

And viewing the image below we see the immediate results of this insertion :-

==================================================================================================================================================================

Oh! there goes my theory about the second column and the water marks !

Still, lets keep going, it could be that it tries to put the water marks in the central column, we shall see ;-)

Now mark this new column as " << col. five >> ", and this time we will attempt to see what happens when

we insert a new column left of the far right column, that tagged as " << col. three >> ".

=====================================================================================================================================

Wow ! The layout has gone wide, and needs bringing back a touch ;-)

This is what I get - shown below :-

============================================================================================================================================================================================================

So, in order to see an overall picture of what we now have, we need to resize all of the columns to a reasonable

width, so we can see them altogether.

Lets try this now :-

=====================================================================================================================================================

That is better, and it could be that the central column idea still holds up - we need more tests.

Finally, lets tag this new column, and the at the far right hand end add two more, to see what happens.

Here goes :-

======================================================================================================================================================================================

Yep! we are not stupid, the central column theory seems to hold up.

Anyway, if you have followed any or most of this you will now know a lot more about adding columns.

You can do your own tests and experiments for removing columns - I have devoted enough time, text and

images to this activity ;o)) Time for some World Cup Soccer !!

**************************************************************************************

[ Soccer was very good, makes a change, now for some more progress !!! ]

Let us clear the cells of those marker text entries.

Highlight the whole group of body cells in the layout table control like shown below :-

[ *** Note *** if you inadvertently click in the first cell first it will not allow you to drag the highlight. ]

====================================================================================================================================================

Once you have the whole group of marked cells in focus (highlighted) just press the delete key to get this :-

=================================================================================================================================================

With the 'Detail' cells cleared we are now ready to drag some data fields into the empty 'Detail' row, column

by column.

Make sure you have the full list of field items showing in the Datasets pane.

Click anywhere in the layout grid area to remove the group highlighting.

Now go to the Datasets pane and click and drag the 'CustomerID' item into the 'Detail' (or body) cell of the first column.

This is what I did here :-

==============================

And now the results of my click and drag operation :-

==========================================================

You may be interested to know just what the field item 'drag' operation achieves.

It does two things at once, filling the table header cell with a text string similar to the field name itself (including

case), as well as filling the body or detail cell with an expression - in this particular case it is as shown below :-

=Fields!CustomerID.Value

We need to know this in case we have anything go wrong with our drop action, as we will need to make two

deletion operations if we wish to clear the cell contents - well, we could just do a 'Ctrl+Z' to undo the last completed

operation.

Remember though 'Ctrl+Z' as well as 'Ctrl+Y' (to redo the last editing action) just work on what has been done

immediately preceding their action. So knowing about the two actions needed to clear a dragged field item will

still be useful to us in the future.

Completing the other columns :

The actions required for filling the other columns may well involve adding extra columns, as well as deleting cell

contents to get things 'just right'. I did a combination of all these smaller actions myself, I have to admit.

Below is what I now have on my screen :-

=====================================================================================================================================================================================================

In the above image we can see a total of ten columns, some coming from the orders SQL table, and others coming

from the customers SQL table.

Making a quick few changes, to set the page orientation to landscape and margins to less than default, gives me

the following output - which can be improved later :-

==================================================================================================================================================================

At this point I am going to behave as though I am the client, and I have changed my mind on the columns required.

I now wish to drop the eMail, first name, and account number. In addition I would like inclusion of sub total, and

shipping and tax amounts. These three will be totalled at the bottom of the table output.

So the first thing we need to realise from this is that when we as designers and developers have started to get

something nice on screen, the client jumps in with changes - since they paid our wages we need to respond in

a positive manner. So lets get to it ;-)

Go into the layout area by clicking the layout tab.

Note that if we drag select vertically we can delete the two cell entries in one press of the delete key.

See below for what I have before pressing the key :-

================================

Whoops ! I have just noticed that I have previously included two columns both with 'Sales Order', we had better

correct that too as we make our changes.

The mouse drag multiple highlight, combined with the press of the delete key, actually works quite nicely on a

few columns to be tidied up. remember this, it will come in useful later.

'Copy and Paste' tricks :

In readjusting the column order and display for our demanding client, we can learn another useful little trick or two.

Although in this version of BIDS 2005 (VS shell 2005) we can't reorder the columns in a modern way, we can make

life easier for ourselves by using Cut and Paste to help us.

As shown below, drag and highlight the pair of cells to move, then right click in the top one :-

===================================================================

Now choose 'Cut' from the context menu provided - the cell contents will 'go'.

Now move to the column where the contents of the move need to be pasted. Either select the top or header cell,

or group highlight the top and middle cell of the target column, the right click to bring up the context menu and

select 'Paste'. Obviously you can also use 'Ctrl+X' and 'Ctrl+V' - either way this is all very neat ;o))

Here is my screen as I am preparing to do the paste :-

=====================================================================================

*** Note *** Don't forget that 'Ctrl+Z' is your friend, and there to help if things go wrong !

Anyway, at my end, after a similar multiple move operation on the phone column here is what I now have :-

=====================================================================================================================================================================================================

We have in this output above, enough information to identify the order and the customer, as well as make a contact,

but now we have enough room to add money columns related to each order.

As a tip, or reminder, don't forget that you can try a 'Preview' at any stage of development as long as you have not

broken the Datasets script. So while working with columns in the layout mode we can usually try 'Preview' and get

success every time.

Lets try it now - you may have to make a couple changes to page setup and margins.

Here is what I get, shown in the image below :-

====================================================================================================================================================================================================

For now don't worry about formatting and style etc. - lets get the data correct, and in a suitable order.

Now we need to focus on the money data fields, and the columns into which we need to add them.

See you after another fresh cup of tea ;0)

************************************************

Refreshed - now lets add the money column details.

While I am working on this I have reduced the width of the first filled columns so that we can see my layout

area more easily.

From the Datasets pane drag the fields into the empty columns for 'Sub Total', 'Freight' and 'TaxAmt'. You

ought to then have something like this below :-

===========================================================================================================================================================================================

And just a quick preview to check on things - we see this below :-

==============================================================================================================================================================================================

Making a few 'Summations' :

Okay then, now let us move on to making a summation across a couple of cells, or fields.

Yes, I know that in the data we have a field which is the sum of the value of the order, plus the tax raised on

this amount, plus the shipping costs too. However, quite often such a summation, or totalling, would be required

outside of the data itself. So we will do the next exercise as a learning one, just so we know what to do ;o)

Into the empty right hand column we are going to add a header text of "Calc.Sum :", so do this now.

My layout looks like this :-

========================================

Now we need to right click in the 'Details' cell to gain access to the 'Expression' context menu option

as show below :-

==============================================

Choosing this option brings up the 'Edit Expression' dialog screen like that shown below :-

In the image shown above I have added a few things to the top editing area, by first of all selecting

items from the fields list shown on the extreme right, and simply manually adding a ' + ' sign in the

edit input control at the top.

Put simply, we are saying add up the there fields called 'SubTotal', 'TaxAmt' and 'Freight'.

Now click OK and test what we have done by going to the Preview tab :-

==================================================================================================

Well, looking at the preview output, we seem to have produced a new column with detail of the previous

three added together.

Really, we have made a calculated field (on the fly) rather than doing a summation. We will see summation

used later, where we are referring to the 'Sum' function in the Aggregation group. More of this later.

Before we move on, let us take the opportunity to see how we may actually add a calculated field into the

Dataset pane itself. from there we can then use it as any other data field.

This exercise will get us to add together the cost of Tax and Shipping (Freight) as one filed called "TnS".

Right click in the 'CustomerOrders' item in the Datasets pane as shown below :-

===========================================

Select the 'Add' option to bring up the 'Add new Field' dialog screen as shown below :-

===========================================================================================================

Looking at the image above you will see that I have entered the name in the relevant input box, as well

as selecting 'Calculated Field' and then filling out the input box using the extra dialog displayed when we click

the ' fx ' (function) button.

We are now familiar with this dialog, so the image below will be no surprise to you :-

The expression string was made up from selecting the field item from the right hand list, as well as manually

entering a ' + ' sign in the edit input control.

The result of this exercise should be a new field in the datasets pane as shown below :-

============================================

Finally, I will leave it to your new found skills to have you insert a new column at the right hand end of our existing

ones, and then drag the new 'TnS' field into the detail cell of this new column.

You should get something like this, as well as the preview results too :-

============================================================================================

=========================================================================================

Change the column header to 'Tax + Freight' as a further exercise :-

===================================================

Okay guys, time to move on ;O))

Summing the Table cash values :

To return to our original report plan, or brief, remove the last column as shown below :-

=================================================================

Check with preview that all is OK, as shown below :-

=====================================================================

Now we wish to sum the 'Sub Total' column by entering something suitable in the 'Footer' cell for that

column. This is the first time we have used the 'Footer' part of the Table control - previously it was the

Page Footer which we worked with.

Right click in the Footer cell to bring up the context menu, as shown below :-

================================================

The 'Edit Expression' dialog look like this after selection from the context menu :-

Notice that I have selected the Common functions 'group' or 'folder' called 'Aggregate' and the the

'Sum' function itself.

After the 'Sum was placed in the edit input control by the system I the manually added a left hand

parentheses which caused the intellisense to 'kick in'. See my screen display below :-

Now the open function text in the edit box needs the name of a field, this is the field which will be

summed.

Here is my dialog screen, just after mouse selecting the SubTotal field :-

Now all we need to do is add the closing parentheses and click OK.

BUT !!! what will happen if we forget to add the closing parentheses ? I will try this now so we can learn.

=====================================================================================================================

We can close dialog screens and return to layout, it is only when we click the Preview tab that things go wrong.

Above is what I got and the error message does inform us of the problem ;-)

So I will now return to the Edit Expression dialog and correct things.

In fact you can make the change directly in the cell itself, remember it is basically a text box control, so clicking

inside the cell and moving the cursor to the right hand end allows me to type the closing ' ) ' without need to open

up the dialog screen - see below as an example :-

================================

This may seem quick and easy - BUT - it also means we need to be careful what we doing in the layout tab if cell

contents can be so easily changed.

Have we been successful ? :

Well to answer this question move to the preview tab and navigate to the bottom of the table to see this :-

As you can see in the above image, the bottom of our table does have a large sum present. This is on

a new line, extra to what we have had previously.

yes, I realise this needs a fair bit of formatting work to make it look nice, but before we move into the next

subsection of the notes to make further improvements let us also sum two more columns, those for the Tax

and the Freight. For now leave the Total as we will do this in a different way.

I will leave you and your skills to make the changes to create the summation on the two columns mentioned.

Here is what I now have in my report :-

==================================================================================

If you have done your own changes similar to mine you may have noticed that it is quicker and easier

in the Edit Expression dialog screen to just manually enter ' Sum( ' before selecting from the field list.

This way we have less mouse selections and clicks to do. In many ways the list of functions is as much

a memory aid that a necessary selection stage, or process. In the end the entry in the edit input control

is just a text string, so this can be assembled in a variety of ways - pick the way that suits you and the

job in hand.

Looking at the above image, notice that one simple way to see the text entry is to view the Tooltip by

hovering over the cell with the mouse pointer. Save a few clicks etc. when all we need is a look to see

what is there.

Adding our final total :

Okay then, let us now add the text for the expression to total our final column, providing a grand total at

the bottom of the report table.

We are not going to sum this in the way we did the other three, but we are going to make a calculated

cell content by adding together those values from the previous three. This will be good practice in the use

of the Edit Expression dialog as well as getting to see how the cell text box works too.

Unfortunately, it does not seem possible to do this the smart way, like Excel would do it by saying add that

cell to that one, and then the other one - we have to copy and paste (and edit a bit too) the text contents of

the three summed cells.

To get an idea of what I mean look at this image below :-

===============================================================================================================================

I made up the full column footer by copying and pasting the contents from the other three.

You will have to be careful and make sure you remove any extra ' = ' signs which come across with the copying

process.

In fact once we see the structure and syntax of the expression text string we could actually do a manual

typing, since it is only the field name itself which changes. But that is experience, and we are trying to gain

experience by doing these sometimes silly things for practise purposes ;O))

Before we finish, and go to see some World Cup Soccer, let us see if we get any results, and if they are correct.

============================================================================================================

Well, I will leave you to do the maths to prove it, but for me the least significant digit (that on the right) sum is :-

1 + 1 + 7 which is 9 so I expect the rest will be OK too !

Lets go see some soccer and then move into the next subsection to do some tidying and formatting.

Just simply making our report nicer and more colourful ;-)

See you there !

PHH - June 19th. 2010.


Improvements - Formatting

If you were following along with what we have done so far, then like me, you probably had to fight the

system as far as it try to force the report to print in portrait and with certain margins etc. etc.

Well, it is time to sort things out, and for us to gain full control over printing and presentation. This

part of the report generation, of 'design and make' process, is probably left to the end in any case.

Some Report Properties :

Let us access the properties dialog and look at the tabs, to see what can be set and changed.

Remember how to get the dialog ? Get the tiny black square and then right click in the slightly larger

grey one. Just as shown below :-

========================================================

Once the Properties option has been selected from the context menu you should see this below :-

Well, you will not see the values as shown in the above image, as I have just been and changed them

to what you see here.

Note that the columns refers to the report and NOT the table control we have been focusing on.

By setting the page width to the length of an A4 page, and the height to the width of an A4 paper size,

we have effectively defined landscape orientation, so this should help.

So too should setting the margins to a minimal width, since we will probably never print the report onto

paper.

I suggest you go and look at the other tabs, to see which settings are available, but for our present

purposes of training and learning, no others are really that important.

However, one setting that s important to our output on screen is the width of the layout grid itself. If

you remember this seems to be set wider automatically as we make changes to the columns in the

layout tab, so check it out now.

Study the image below and then set your layout grid to match the width setting shown :-

=============================================================================

Clicking the preview tab we get the following output, AND, I don't seem to have had the fight the system ;-)

Here is what I get below :-

===============================================================================================================================================================

Obviously, we could easily improve things by changing (customising) the column headings, as well as by

formatting the contents of some of the data items in the column cells. We will come onto this now.

To start with, let us make changes to the text entries in the column headers.

In the layout tab use your new skills and knowledge to change the headers to the following :-

===================================================================================================================================================================================

It is possible to make multiple cell alignment changes by drag highlighting the group of cells and the using

the standard PP (Properties Pane) to set the required alignment - see below for an example :-

========================================================================

I set my first column settings in this very manner - give it a go !

After changing the header text values, let us now add colour and some font size and styling.

Highlight (or set in focus) the whole header group by a drag select action, you should see this :-

=======================================================================================================================================================================================

Now use the standard PP inputs to select colours as shown below :-

=======================================

We now have an improved header region which looks like this :-

================================================================================================================================================

Improve this by group selection once again, and setting the font weight to 'Bold', and the size to 11pt.

Just as in the image below :-

========================================

This is what we now get when we click the Preview tab :-

=================================================================================================================================================

Now let us turn our attention to the first two columns.

We can reduce the width of the first as the data takes up a smaller amount of room than when we had a long header text.

This is better - see below :-

===================================

And now we need to chop down the 'DateTime' value to a simple date string - this will help the report look and feel !

The next bit is not as difficult as it may first appear, simply because we have a tried and tested method from the

last chapter, which we can employ. Yes, it took me a while myself to fathom it out but copy and adapt is my approach.

Let us first look at the success in the preview tab, and then how to make it happen :-

====================================================================

That looks better, with just June 1st. and the year in four digits.

Now what expression has been employed behind the text control - or cell ?

Well one way to see is to use the tooltip, as previously mentioned, see below :-

========================================================================================================================

Or the full Value input box of the 'Textbox Properties' dialog screen, as shown below :-

Notice I have expanded the width of the dialog box by mouse dragging the right hand edge.

To get this formula I took the one from the last chapter - see below :-

="Printed on : "&Day(Now)&"/" &Month(Now)&"/" &Year(Now) &" at " &TimeOfDay

Then substituted our present DateTime value for 'Now'.

Our present DateTime value is >> Fields!OrderDate.Value << which is the syntax for the data item in the field

of the returned data set.

There may be a smarter way to do this, but this one works, and till we do find a better way we can use it. By

the way, let me know if you find a smarter and shorter way ;-)

Let us look at the output as it now stands :-

=============================================================================================================================================================================

We can now use the space saved in the Date column to add to that available in the Company column, and avoid

the need to make a second row to complete the data output.

Let us try this improvement now :-

=================================================================================================================================================================================

Yes, better, but now we see that the Phone column needs easing. This may have to be done at the same time

as easing the cash sums - BUT - remember these need to be wide enough to take the grand total values at the

end of the table control, in the table footer cells. So tread carefully !

Here are my results of the 'easing' process, and we have a reasonably sensible compromise I feel.

Study the following output image :-

==================================================================================================================================================================================

Of course, if we were more desperate to gain back space, we could chop off the 'Company' column data to a

fixed number characters using the expression text.

It may be worth you try to do this as an exercise anyway. Good Luck !

Turning our attention to the table end :

Okay guys, we seem to be on the up - winning that is. Lets keep it going.

Now that we have tidied the row formatting let us look at the bottom of the table control, where we find the

summation figures, or totals and grand totals.

What do we have at the moment - see the image below :-

=========================================================================================================================================

This is enough of the screen output to see that we need to make the totals more obvious. At the

moment they do not stand out from the result of the bulk of the report details.

Go to the layout grid and group highlight the four total cells as shown below :-

==============================================================================================

Go to the Properties Pane (PP) and change the colours as shown below :-

=====================================

This is in keeping with earlier selections for the headers.

Actually, these changes may be sufficient, as making text 'Bold' will use up valuable horizontal space, and

could restrict output of the values.

Highlighting the final total column :

As a final touch for this part of the exercise, and notes, we could improve the look of the report by using colour

shades to highlight both the cash sums in a row, as well as the total of these.

Let us make the first three a light shade of green, and the last one a light shade of red - here goes.

Group highlight the SubTotal, ShipAmt and TaxAmt 'Details' cells, and then use the standard PP to set the

background to 'Honeydue'.

Now highlight the Total 'Details' cell and set its background to 'SeaShell'.

You should now see this shown below :-

====================================================================================================================================================

Much better I feel, and what of the bottom of the table ?

Here is what I have :-

==================================================================================================================================================

And again, a big improvement I feel.

Okay then, enough for now, lets move onto the next subsection where we attend to matters

concerning the report header and footer. We have to get used to polishing up a report ready to

impress our clients, and them their users or customers.

One thing we need to improve is the output format of our cash values !!!

See you in the next subsection.

PHH - June 20th. 2010.


Some Further Improvements

In the last section of the notes we left the report project looking like this below :-

=============================================================================================

In the image above the money (or cash) values are not suitably formatted in my opinion.

We now need to see how we can use the VS shell layout tools to improve this.

Making a start :

Let us start by selecting (by highlighting) the 'Tax Amt' column in the layout grid as shown below :-

===============================================================================

Now right click to bring up the context menu as in the next image below :-

==============================================

Then select the 'Properties' option to bring up the 'Textbox Properties' dialog screen as we see below :-

Make sure you choose the 'Format' tab with a mouse click.

We are now going to select a value for the 'Format code' input box at top left of the dialog screen.

Since we are probably still unfamiliar with what the codes can be, we need to select the ellipses button

to the right hand side of the ' fx ' button. This brings up the 'Choose Format' dialog as shown below :-

========================================================================================================

Notice in the image above that I have already selected 'Number' and a sample output from the list on the right.

It may interest you to know that 'N' just happens to represent this style, and that there are other codes as

seen in the small images below :-

=================================================

And one other for good measure ;-)

===================================================

At this point I suggest that you have a look at all the options, and see the code which is offered by

the dialog selection tool.

In fact, by doing this I have found the smarter way to format the DateTime values that bothered us in

an earlier section ;O) See below to see that the code we nee is ' d ' without the quotes.

At this point I will tell you that when I try the 'Currency' option it does not seem to pick up from my

Windows 7 operating system that my local setting as UK based.

See the image below to see that I have dollars being indicated :-

[ I am presently asking Willie Moore about this, and hope to resolve the O/S issue soon ... ]

Anyway, the currency symbol at the front of each value seem 'too busy' to my personal tastes

for display when we have a lot of similar values on screen. So I prefer the cleaner look of the

' N ' code for the formatting.

*** Note *** I am aware that we can change the 'International' setting in the PP from default to

English (UK) to get our ' £ ' symbol but that is not the point, we don't wish to have to make these

sort of changes on a regular basis.

=================================================================

Okay then, time to take a look at our report to see how things are progressing, and admire the

new look to the end of our report, see below :-

=============================================================================================

Well, as you can see in the image above, we have improved the output of the 'Tax Amt' column,

and I have also changed the 'Sub Total' and 'Ship Amt' columns too.

So now that we know we need the ' N ' code to format our cells, we can go and change the others

in the 'Total' column, as well as the table footer cells for the summations.

Why can't we set multiple formatting codes ? :

The first thing I tried was to multiple select (highlight) the required money cells, and to then format

the whole group as one. However, as you can see from the image below, we are not given this

option in the BIDS 2005 shell, see below :-

==========================================================================

So the out shot of this is that we have to format each cell separately - not a big deal, so off you

go and do it now.

I have just made the changes, and being able to enter ' N ' into the input for a code value in the

Format tab makes life much easier, and quicker.

Here is what we now have as report output :-

===========================================================================================================================

It may make sense to add the currency symbol to the summary values only. We can try to do this

two different ways, just as practise in using the VS shell and its tools.

Let us start by multiple selecting the total cells and then looking at the PP for what is highlighted.

Here is what I get :-

==============================================================================================================================================

As you can see we may have found something very useful by doing this, the PP clearly shows that

the 'Format' property has a value of ' N ', and that rings a bell. So there is an easy way to multiple

select and format the cells after all ;o))

Okay then, lets try a test to see if what we want to do is possible. Let us change this 'Format' code

to ' C ' and view the results.

Notice in the image below that I have entered manually the ' C' and that there is not a list of code options

from which to choose :-

=================================================================

Now let us check to see what we have showing on the report, see below :-

==========================================================================================================================

Well, I am surprised to see the ' £ ' symbol appear, but happy and relived at the same time.

It would appear that all these cells still have the default 'Language' setting we discuss briefly above.

Here is what I have :-

==========================================================================

If you are having any problems getting your local currency symbol then make sure that you first

highlight all the cells required by a multiple selection, and the use the PP to choose your country.

For those guys in Europe with the Euro as currency, lets see if we can force this now - using the

setting I mentioned. Let me try Greece for our friend Chris.

===============================================================

And this is what we get from the new settings :-

==========================================================================================================================

Yep! it would appear we have lift-off ;-)

Just remember that totals, particularly grand totals, are longer than the rest of the column entries and so can

cause space related problems for a large data set. So be cautious with you symbol inclusion, unless you can

make the column quite wide.

Becoming 'self sufficient' with the RS designer, and its tools :

One thing that all this recent formatting 'stuff' may have taught you is that as a developer, and

designer of a report, needs to be able to survive while using the VS shell for making reports.

There is often many more than one way to achieve what you want, and make the look of the report

appear as you require. So you need to be able to go and search the tool options for what is available

within the various tab screens and drop lists etc. etc. Gradually, if you do experiment and research in

this way, you will become both more familiar with what is on offer, and also 'master' of your tools ;O))

Yes, at the moment both you and I have a long way to go as yet, BUT, we are getting suitable, and nice,

results it would seem ! So lets keep going.

Before we move on, do remember to set back the International setting from Greek (or whatever your test

was) to your own country, in my case 'English (UK) '.

Adding Headings, and Footer details :

We are nearly done with this particular report, we just now need to add some suitable heading text, and

also our regular footer 'stuff' to provide useful information on " 'Who', 'When' and 'How Much' " was printed etc.

Let us begin with a straight forward heading.

Into the Page Header top section of the layout grid, drag a Textbox control from the list of Report Items in

the toolbox.

Now resize and adjust the control so that the text box takes up a position similar to that shown below :-

=======================================================================================================================================================

After making my changes, and trying a few options I did not like, I came up with this below :-

======================================================================================================================================================================================

The settings I used can be seen in the PP once I have made the new text box the focus of the designer

by clicking with the mouse within its boundary.

Checkout these properties and copy them to your report if you so wish :-

This is looking OK I feel, so lets move onto the footings for each page.

We now need to add three equally wide textbox controls into our Page Footer section of the layout grid.

However, we ought to be able to fill out the middle section regarding data of report printing in a simpler

fashion now we know more about the format codes.

Add the three empty text boxes now, and size and place correctly.

=========================================================================================================================================

Make sure you only select the middle text box as this is where we will place the date information.

Below I have set the middle control in focus, and have got my PP ready to try entering a Format code,

you do the same :-

===================================================================================================================

We can now enter the Format Code as ' d ', even though we have not yet specified a value for the textbox.

Go ahead and do this, and I will then tell you how to get the correct value in place - here is what I have after

taking both actions :-

====================================================================

With the middle textbox still in focus, move through the PP until you find the 'Value' property,

then use the drop list as shown below :-

====================================================================

Choosing the <Expression> option will allow us to enter a value which is other than the Dataset fields we

have in the bottom part of the drop list.

When you bring up the 'Edit Expression' dialog screen make the choices as shown below :-

After clicking the OK button you will get the following result :-

Remember the PP is interactively 'live' and so the change in the layout grid happen immediately the

property is completed.

The result of all this in our report preview is as shown below :-

So we do have what we require, and now we only need to add our fixed text string before it.

In the image below I manually typed the expression, making sure the ' = ' sign was kept at the beginning

of the string, and that an ampersand sign ( '&' ) is used to concatenate our new string and the function

itself.

Here is what I typed :-

==========================================================

And below is what is produced by the property settings as they now are :-

............................................................................................................................

We appear to be doing well so far, but in our last chapter we had a further string which provided the time

information.

There are two ways of changing this and both methods will teach us more about textbox formatting, and

our options when doing so.

The first approach is to actually split the meddle textbox part into two textboxes, one as we have created

so far, and another quite similar where we use ' t ' as a Format Code. Let us look at this approach now,

and test it out :-

=================================================================================================

Then PP settings for this new textbox are as shown below - in two images :-

Notice the ' T ' value of the Format Code, and this results in the following report output :-

...............................................................................................

Well, this works OK and gives the 24 hour clock values for time, it would seem.

What about another way of doing this, in one textbox, using the Common Functions available to us

from the 'Edit Expression' lists.

Let us look at this now :-

==========================================================================================================================

The results this are shown below, and do realise that there is no 'Format' entry required for this

in the PP input box - it is left blank.

=====================================================================================

There are a few obvious differences, and you may like to add a couple of extra spaces to ease things,

however the dashes between the date figures for day, month and year are fixed, and part of the function.

WARNING !!! See, I did warn you that dates and times were a bit of a nightmare and that there were loads

of different ways to do things - some may say many ways to make errors ;O)) well, that is true also !

Either way, we need to experiment a and look for different ways to do things like this, just so we can then

choose the best way for the way we work, or the problem at hand.

Now go to the previous report ( 'SimpleOne' ) and copy and paste the contents of the textboxes into those

still empty in this report - the left and right most textboxes of the Page Footer.

Also make sure that the standard property pane (PP) shows correct settings for the text alignment property,

particularly the RHS textbox where the page information is contained :-

=======================================================================

If you do that you should, and get it right also, then this is what you ought to get as output from the report :-

==================================================================================================================================================

*** Note *** I found it quite easy to select the 'SimpleOne' report, highlight the chosen textbox in the

Page Footer section, and then right click for the context menu. I then selected 'Properties' from the menu

which was presented, and then copied the 'Value' content after highlighting it - as shown below :-

===============================================================================================================================================

Okay you guys, enough for now I feel. We have come a long way since we started, and now have

more than one way of doing quite a few necessary tasks.

Let us now move on to the next chapter where we will look at grouping data records within a 'Table'

control. We will sum the groups in some simple way as well.

See you in the next section.

PHH - June 19th. 2010.


Chapter 4 -

Chapter 4 - Table 'Simple Grouping'

Report Items - the Table Control :

A table is a data region that presents data row by row. Table columns are static. Table rows expand

downwards to accommodate the data. You can add groups to tables, which organize data by selected

fields or expressions.

Very often in table based reports we require a grouping records within the table data.

It is probably best to see an example than to write too much text about this situation.

Below is an example from a real live report I designed for a local client.

Study this image below :-

============================================================================================================================

You may have enough data visible to see that data records are grouped by nurse, and ordered by

product in an A - Z manner.

Also, the 'value' columns are summed, and their totals displayed.

If we now take a careful look at the layout of this report, we see some extra rows in the table

control - extra to what we have so far seen in our simpler report examples developed so far.

Notice in particular the symbols in the left hand little grey squares. We have new enumerated

icon symbols, these contain a ' 1 ' as well as a '[ '. The bold 'mark' at top and bottom indicate

that the row is the start or end of a group specification.

Enough for now, we will see more of this detail when we design and make our own report soon.

===================================================================================================================================

Before we move on to start a new report, just take a look at the Table related dialogs screens

shown below :-

==========================================================================================================================================================

You can see we actually have two groups here, a nurse group and a product item one.

The dialog shows the nurse group to be grouped by the NurseID data field.

Lets move on.

Starting our own group report :

We will keep using the 'AdventureWorksLT' (AWLT) database as it is suitable enough for our purposes.

However, when demonstrating reports and charts etc. it is vitally important to pick appropriate data to best show

what it is we are trying to do.

It is not possible to group any tabular data, in any sensible way at least !

I am going to suggest that we use the data records from the [Sales].[SalesOrderDetail] SQL table, as this

has ProductID fields and examining the existing data in SSMS shows that it is suitable. We have many

items of each product being used in the period covered by the data.

Study this test output from an exploratory SELECT statement in SSMS :-

=========================================================================================================================================================================

You can see in the image above that as well as having a variety of quantity values, each product

item is also present a number of times too.

In the image we see records for product 707, 708 and 711.

Using this data set (from a query similar to this) we ought to be able to sum the quantities and sales value

of each product in the sales table. However, be aware that some products will not have had any sales in the

period covered by the data - so some genuine products will be missing from our table.

In order to get a couple more fields of useful data I have JOINed the 'SalesOrderDetail' table with that of the

'SalesOrderHeader' table. But essentially we are selecting the same data records, just de-normalising a bit

more ;-)

When we have opened a new query tab in SSMS, and copied the SELECT statement into it, the query

designer (QD) dialog screen looks like this :-

=======================================================================================================================

Once again I point out to you that this database (DB) has been well designed - and probably all at once -

and that column names are similar from table to table, for fields which are the same. This is NOT usually

the case in the real world - unfortunately.

So in the DB we are using, 'SalesOrderID' is called just that (exactly), in each table we see it in. So I did

not need to clear the 'auto links' and remake them myself ;-) I did check them out however !

Making the Dataset :

Now that we have a suitable SELECT statement we can create a new report and make a Dataset from it.

Open the BIDS 2005 app, or your VS shell app you have been working with so far.

Start a new report called 'ProductItemsSold' as shown in the following image :-

=====================================================

Select the 'Data' tab and from the drop list select <New Dataset> and then name it 'ProductItemsSold'.

Copy and paste the select script from the trial query tab of your SSMS and run the query in BIDS.

You should get something like this :-

========================================================================================

And the 'Datasets' pane itself is like this :-

============================================

Starting the report layout :

In the VS shell change to the layout tab and onto the empty grid drag a Table control.

If you wish, click in the little square at the top most left position of the layout tab and bring up the report

properties dialog :-

=================================

Remember, to get the tiny black square you mat first have to left click into this same area.

In the 'General' tab change the grid spacing to 0.20cm as in the last chapter, and this time let us added some

report information about who is creating th report and what it is about.

This is what I have entered :-

Return to your empty Table control, and increase the number of columns from three to six, using the

methods shown in previous chapters.

Here is an example to remind you - click the Table control to bring up the grey boundary to left and top,

then right click in a chosen column to add an extra column to either the left or the right of the column

in focus.

===================================================

However, unlike the image above, your Table control ought to be completely empty.

Moving and positioning the Table control :

While we have the opportunity, we are going to look at how the Table control may be repositioned on the

layout grid. When you are new to the VS reporting shell it seems a bit sticky or difficult. We need to

know more !

Lets suppose your layout looks like mine shown below :-

============================================================================================================================

In the image above you can see that we could move the control further towards the top of the screen, and

to the left a shade too.

In order to do this we need to get the repositioning tool displayed, below is what we are looking for :-

=====================================================================

In the above image, notice the four headed arrow icon. When we have this showing we can then drag the

Table control over the grid and drop it where we require it for our report.

How do we get the repositioning cursor to show in the first place ?

Well, starting with the control when it is out of focus, as shown below :-

===========================================================================

Click the mouse once while the cursor is within the area of the Table control.

You should get the Table looking like this :-

=======================================================================

The highlighted cell is simply as it is because that was the area (or region) where the mouse cursor was

when I made a left click.

What I do next is to move the cursor to the little grey square at top left of the Table control, and then make

a single left click to get this :-

===========================================================================

Now all we need do is hover the mouse cursor over any section of the thick grey boundary line in order to get

the shell to respond.

Below we have images which represent what happens next, and when you drag the control around the grid,

please this study carefully :-

====================================================

and when we move to effect the drag we see this :-

=====================================================================================================================================

Move the wire frame around the screen and see how it is dragged over the grid.

Finally position it towards the top and left, then release the mouse button. You should now have

something like this :-

========================================================================================================================

And now finally, before we start work on the table details, return to the Report Properties dialog,

and at the Layout tab change the inputs to look like those shown below :-

Close the dialog and return to our empty Table control.

*** Note *** This approach to moving controls around the grid will equally apply to the Matrix control and

a few others too, so it is well worth knowing, and us being familiar with how to apply the technique.

Placing some data items in the table :

Now that we have six columns we need to drag field items from our Datasets pane into the 'Detail' cell

of the appropriate column.

Here is my table after simply dragging the six items shown, nothing else has been done as yet :-

===========================================================================================================================

These data fields will not be our final choice, but will serve a purpose for now, until we make a grouping.

If we now try a Preview by using the appropriate tab selection, we see the following :-

======================================================================================================================

So the next simple step is to enter ' d ' into the Format input in the standard Properties pane while the 'Order date' column

'Detail' cell is in focus in the layout grid, and then enter a ' N ' in the Format input for the 'Line Total' column 'Detail' cell.

Take care with the 'case' of these entries.

The Preview now looks like this :-

======================================================================================================================

This is starting to look better already ;-)

Before we move on to the grouping exercise we are hoping to do soon, first of all let us sum the Line Total across

the whole report by entering some suitable content into the Footer cell of the Line Total column.

================================

Right click in the cell shown above to bring up the context menu as shown below :-

======================================

And then open the 'Edit Expression' dialog screen as shown below :-

*** Remember the intellisense only kicks in when the opening parentheses is typed.

And the aggregate function of SUM is to be used on the data field of 'LineTotal', so here is

the full expression string as composed by the dialog tool and our entries :-

Close the dialog with the OK button and run a preview to see the end of the report.

Mine looks like this :-

=======================================================================================================

However, before we go to the PP Format entry input box for this Footer cell, and enter ' N ', let us take a moment

to discuss the "Expression" and what it is.

Now that we are getting more familiar with report creation, and more experienced in the use of the VS shell tools,

we can take a moment to discuss the 'Edit Expression' dialog itself, and related issues.

It should be reasonably obvious to you by now, if you have followed through all the previous sections of these notes,

that the cells within the table are based on Textbox controls, and that they each have a text 'content'.

If the we extend the sixth column in our report at the moment we get this :-

============================================

Studying the Footer cell we have just filled out, we see that the text string present begins with an ' = ' sign. This symbol

has a BIG significance on the behaviour of the cell content.

Let us do an experiment and remove the ' = ' to see what happens :-

======================================

And now a preview and a look at the end of the report :-

===================================================================

Not surprisingly, the text string is simply printed out as it is seen in the textbox itself, just as if we

had contents of "Grand Total : " or some other text string acting as a label.

An even more telling experiment is to place the '=' at the end of the string, as shown below :-

=========================================

Producing the following output :-

================================================================

I have sinceremoved this trailing '=' and also inserted the leading ' = ' sign, and all is well once more,

we get a grand total figure, or summation, for the 'LineTotal' for the whole table.

So what do we make of the experiment ? Well, it would suggest that when an '=' is the first character in the

textbox content string, the VS shell takes this as an instruction to interpret the content of the string, in order to

create a string output to use.

Knowing this also means we can more easily create our own 'instruction' strings and have them work for us.

For example, let us try an entry of >> =Count(Fields!OrderQty.Value) <<

I have copied this string directly form these notes (H&M5) and pasted it into the first of our Footer cells as

shown below :-

==================================================

I have had to widen the first column so that we can see the whole string.

So what do we get if we run a preview ? Lets do this and see :-

===================================================================

It would appear that the report shell is interpreting our string after the '=' sign and is making a character content

after doing the aggregation function. We appear to have 542 records in our data set.

*** Remember *** we have done this without going near the 'Edit Expression' (EE) tool itself.

So the EE must simply be a tool to prompt us with what is available in the long list of functions, fields, and other

related stuff. From our selections and entries it makes up a string for us, but we could equally as well type in

our own, if we know what we want ;O))

Summary on 'Edit Expression' dialog :

My summary on this would be to realise that the EE is there as a memory aid, that it does nothing 'magical' and

that we can live without using EE if we are dealing with common stuff we do regularly.

It is all too easy to think these GUI tools are very clever and not to try and fathom them out, but this is NOT what

I think, and recommend gradually removing the mystique from them by doing experiments such as those above, as

we get more used to the environment, and the VS reporting shell itself.

Now, time for a fresh cup of tea, and the 'groups'. See you after the tea.

Making our first 'Group' :

If you haven't already, remove the test cell entries to get back to this layout :-

========================================================================================================================

And now we need to do one last thing before we 'group'. Bring the 'Line Total' Footer cell into focus and then

set the Format PP input to ' N' so that we get the following :-

=====================================================================

If you so wish, you can try ' C ' the format code for currency. I will try this now to see what I get :-

====================================================

And yep! just as I thought, back to US dollars once more :(

So not to be outdone, I have also set the International setting in the PP for this same cell to that for

'English (United Kingdom)' and all seems OK as this is what I now see :-

===================================================

In order to make our first Group we need to bring up the 'Table Properties' dialog. The group will be a

property of the Table control and not the report itself.

If you need to refresh your mind about accessing the Table Properties dialog screen then look back at

early chapters and sections.

Here is what I have in my 'Product Item Sold' report, for the 'groups' tab related to the main Table control :-

Choose the 'Add' button and you will see this screen :-

You simply need to open the drop list for the 'Expression' by clicking on the down arrow at the right hand

end of the input box - shown with blue highlighting of the content I selected.

Pressing the OK button shows the layout to now have a few changes, and look like this :-

============================================================================================================================

Low and behold, we now have two extra rows, and these have the group ' 1 ' parentheses icons showing.

We should know enough of the VS report shell now to be able to call these two new rows the Group Header,

and 'Group Footer' rows. I will indicate which we are referring to by displaying images below.

first the Group Header :-

======================================================================================================================

And now the Group Footer :-

=======================================================================================================================

Results of our first Grouping :

Running the report from the Preview tab produces this following output :-

=====================================================================================================================

As you can see from the image above, even our very simple entry into the 'Grouping and Sorting Properties'

dialog produces some promising results it would appear.

Yes, we need to do a whole bunch of things to improve the presentation, but we appear to have a product

grouping based on 'ProductID'.

Let us now 'push the boat out' (or in modern parlance, 'go for it' ), by attempting to sum two of the group fields. Let

us try to make a group summation of quantities and line values totals.

Go into the layout tab, select the 'OrderQty' cell which is in the Group Footer row as shown below :-

=================================================================================================

How you enter the following string is up to you and your experience.

=Sum(Fields!OrderQty.Value)

I typed it directly into the 'Value' input box of the 'Textbox Properties' dialog (General tab) as seen below :-

========================================================================================================

Now that I understand more of what the cell content means, I feel more able to try a direct text entry.

However, the expression editor is always there when we need it ;-)

Lets run this now and see what our preview gives us :-

========================================================

We appear to have success, and the individual line quantities do seem to be summed correctly.

Now let us do the same sort of thing for the 'Line Total' column in the grouping, and make sure also that

we have the correct ' N' or ' C' formatting of the cell too.

Here is my layout :-

==============================================================

I have chosen to use ' C ' for currency, as my Format entry in the PP input box, and I have also

set the International setting to English(UK).

And now the results that this produces :-

============================================================================================================

What can we do to improve this ? :

It is easy to see that for each grouping of our 'table set' we have constant entries for 'Unit Price' and the

actual 'Product ID' itself. We may choose to clean up out table information by removing the columns for

these and putting the same information into a group heading.

*** Care *** For some reason it appears as if the 'Order Date' field has limited date range, and although it

looks consistently the same, in real life the date of the orders would vary considerably. So I will leave this

date field in place.

Removing two columns :

To remove the 'ProductID' column, highlight it by selecting with the mouse in the column grey boundary cell.

=================================

Now just press the 'Delete' key and it will immediately be removed. However, be careful, as the next column

along is now in focus and an inadvertent second key press will remove that too !

If something does go wrong, don't forget 'Ctrl+Z' and 'Ctrl+Y' are your friends, and can help you navigate your

way through changes to the layout grid. 'Ctrl+Z' undoes what you have most recently done while editing, and

'Ctrl+Y' re-does the action. You can step through a number of recently done edits.

Okay then, now let us remove the 'Unit Price' column shown below :-

============================

Running a preview shows this :-

===============================================================================

Now that our image has the 'ProductID' and 'UnitPrice' removed we need to add this information back

into the header for the grouping.

This is a convenient place to look at how we can 'merge' cells to provide a larger area for some output.

Go to the layout grid and multiple highlight the four cells in the Group Header section, as shown below :-

=====================================================================================

Right click within the bounded area to bring up the context menu as shown below :-

=================================================================================

From the context menu select the 'Merge Cells' option.

The whole area now appears 'as one' as the cell 'dividers' have been removed - see below :-

=================================================================================

If you hover over this large merged cell we get the same tooltip wherever we are within it.

==================================================================================

I have just done a check, and 'textbox5' is the first of the four cells, that on the left hand side. So

basically the first cell has been extended across the whole four cell area.

Adding content to the 'Merged' cell :

For this more complex cell expression entry I will use the expression editing tool to produce the

following at the layout screen :-

======================================================================================================

And below is the EE dialog itself :-

===============================================================================================================================

Do be aware that the general behaviour of the input box of this dialog does not work 'totally' as

I think it ought to (BIDS 2005 and VS shell 2005). The colouration of the entered text for string

and function sections is not as shown above, when the process is interactive. The screen has

since been closed and reopened, and it worked perfectly the first time, even with stranger colours

shown etc.

Anyway, enough of moaning, what of the successful output ?

===============================================================================

Well, it is getting there, and does look something like what we were after.

We need to format the currency, I wonder if we can apply the ' C ' Format to this cell, and the International

setting too. Lets try it and see - no it does not, so plan 'B' is required, as seen below :-

=============================================================================

OK, this is better, two decimals in the currency figure, AND, a BP symbol also !

So what does the expression look like ?

=====================================================================================================================================================

Simply, our entered string means - "build a string from two pieces of text, one data item, and the result

of a function operating on a second data item".

Adding some 'glitz' :

Let us now focus on adding some colour and highlighting to our group area of the layout grid.

We can make our group header and summation parts stand out from the rest of the background data

by doing this.

In the layout grind highlight the long 'merged' cell and set background and text colours as shown below :-

=====================================================================================

You will notice that I have also set some other colours after some trial and error, testing for how it looks

and feels etc. etc.

Here are the results of my labours :-

============================================================================

And to prove that the Unit Price is correctly being picked up as a product related value, here is

a section from further into the report :-

============================================================================

However, looking at the very end of the table, we still need to do something with the grand total.

So after I have shown you what me chosen settings are for the above group display, I will get to

work on final 'Grand Total'.

=================================================================================

============================================================================================================

=============================================================================================================

The colour setting in the images above should help you make the changes required to get

your preview results to look like mine.

Now for the 'Grand Total' :

I have just spent a minute or two experimenting, and this is what I have come up with :-

===============================================================================

The settings for the 'Honeydue' Grand Total section are as follows :-

And now to help some of you, the setting for the borders on the 'LightYellow' group summary :-

Okay you guys, I think we have done enough, and certainly achieved enough for one section.

I will see you in the next subsection where I will try and add some further enhancements to this

report.

Maybe we can add a way to select a group of products and limit our report to those ?

We shall see - see you in the next section !

PHH - June 21st. 2010.


Some Further Improvements

So far we have managed to construct a report which handles each and every product

item in the data set. However, it may be nice to provide the user, or consumer of the

report with the option to select specific and particular product items from a list.

If we are to provide a drop list interactive control at the top of the report, we will need a

list of the products in the sales SQL table to populate it.

So we need to go back to SSMS and design a SELECT statement which will provide a

list of unique product IDs, but only those in the 'SalesOrderDetail' table.

Look at the following image from a test I have done in SSMS :-

==============================================================================

This seems to produce a total of 142 unique 'ProductID' values - just what we require.

================================================================================

So now we return to BIDS 2005 (or the VS shell) and copy / paste the recently tested query into a new

Dataset which we make in the Datasets pane as shown :-

========================================================================================================

So saving these changes we get the following script in out Data tab area of the design section

of the shell, and I will also click the query 'run' (pling) icon to check it out.

This is what we have :-

=========================================================================================================

Now we can return to the Layout tab to add a report parameter, which will accept our new list.

Bring up the Report Parameters dialog using the relevant option in the context menu for the report.

This is what I have here :-

=========================================================================================

Now the dialog screen itself, after I have made some entries :-

After closing this with a click of the OK button, we can see the result in the Preview tab :-

==========================================================

The default is all product items selected, but we can return to this shortly.

For now we need to find a way to incorporate the list parameter, called @ProductIDs, into our

script code for the main dataset.

In the Data tab, returning to our 'ProductItemsSold' dataset using the drop list selection box, we

see this adjusted text entry :-

=============================================================

The extra, and important, line of script is shown in blue highlighting.

Also notice that the parentheses are necessary for the code to work correctly. leaving them out

produces this error when the Preview tab is selected :-

=================================================================================================================================

Replacing the parentheses and running the report again provides a full report as default, but

after selecting three product items from the list we get this result when the preview is run :-

==============================================================================================

I will leave it up to you to check the IDs in the interactive drop list with those values shown

as part of the display.

Now time to move into the last subsection of this chapter, to see a graph for the first time.

See you in the next subsection.

PHH - June 22nd. 2010.


Embelishments - pictures !?

Okay guys, we have worked hard enough to have earned ourselves and excuse to draw

a picture or two. Or should I say a graph or two ?

In the layout tab extend the grid from the bottom by dragging the bottom edge boundary.

Then move the Table control quite a way, to make room for a graph or two. See the image

below to get an idea of what to do :-

=========================================================================================================================================================

Then from the toolbox drag a Chart control onto the top part of the grid as shown below :-

=======================================================================================

Then use the handles oh the chart control boundaries to resize it, and stretch it across the width

of the report grid, as shown below :-

==========================================================================================================================================================================

=============================================================================================================================================

====================================================================

===========================================================================================================================

===========================================================================================================================

============================================================================================================================

===========================================================================================================================================================

=============================================================================================================================================

===================================================================================

June 22nd. 2010.


Chapter 5 - Groups within Groups - Tables

Report Items - the Table Control :

A table is a data region that presents data row by row. Table columns are static. Table rows expand

downwards to accommodate the data. You can add groups to tables, which organize data by selected

fields or expressions.

Well guys we have come a long way since we started out on our quest to understand and master

Reporting Services, or as far as the report designer is concerned, at least.

Now it is time for us to make that extra 'push' to take ourselves into a region which is particularly useful

along with the Table control. Very often we will need to structure a report so that we have groups of

things with other groups. I call this 'groups within Groups' !

In the last chapter we saw how we could group our sales order items by Product ID, and have both

group headings and also group summations in the group footings section.

Now we will carefully choose our data example, and the dataset to use, so that we can have a meaningful

example of such a group within a group scenario.

Enough of the words, lets seem some action, script, and even images ;-)

Choosing our Dataset :

If we first do some research using SSMS on the 'AdventureWorksLT' database we have so far been using,

we see a table called 'ProductCategory'.

Here are some of the necessary details regarding this SQL table :-

=================================================================

Now that this image above has informed us of the detail of the 'ProductCategory' SQL table we can now look

at the Product SQL table with different eyes !

If I list out the Product table rows order by this new ProductCategoryID we see below that a number of products

usually come into a single category :-

==================================================================================================================================

Looking at the small section of the returned rows we can see that in category ' 6 ' the product IDs are

789, 790, 791, .......... and many more. In fact the image below shows that this category group goes

right up to product ID '999' :-

===================================================================================================================================

So to summarize, we have a lot of products within a product category.

So we could revisit the problem of reporting on Sales Orders, and group these sales first by category of

product, before the products themselves.

Obviously, we can create subtotals on the category as well as the products. And have a 'Grand Total'

as well !

Creating a suitable Dataset :

Now that we know we need to involve data from three SQL tables, 'SalesOrderdetail', 'Product', and now

'ProductCategory', we need to use SSMS to do a few tests to see which SELECT statement will provide

us with a suitable and correct dataset.

I have completed some tests at this end and have got one which seems to suit the bill nicely ;-)

So returning to BIDS 2005 (or the VS shell 2005) I have started a new report as shown below :-

=====================================================

And also I have started a new Dataset for this report as shown in the next image :-

============================================================================================================================

After clicking the OK button, and running the query from the ' ! ' (pling) icon, we now get the following

showing up in our Data tab region :-

==========================================================================================================================================

We appear to have the same number of records as in the last chapter, however we now have the

product category information as well, ready for our 'grouping'.

Once again notice that we have more data fields available to us than we will probably use in our report.

Starting the Report layout :

Click the layout tab in our new report. Change the grid setting to 0.20cm in the report settings, as we

have done in earlier chapters.

Now drag a Table control from the toolbox onto the grid, and then resize it accordingly.

You should now have this showing in your IDE :-

===================================================================================================================

Size the grid width to about just over 20, and then reduce the width of the three existing columns.

The point of reducing the column widths is when we add another four columns, they will be created

with the same width as the column in focus at the time, but if we don't also have the other two much

narrower also, then the total width of the growing table automatically increases the grid width.

So which ever way you try to add the extra four columns we may need for our new table, make final

width adjustments so you have something like this shown below :-

==============================================================================================================================================

It would appear to me that we all need to develop good skills at adding and removing table columns

when using Reporting Services, and that also includes column resizing activities too.

We already mentioned in the last chapter that we need to acquire good skills at repositioning controls

on the grid, so now we have a few more skills to keep in mind !

Adding data fields to the Table control :

We now need to drag a few data fields from the Datasets pane into the 'Detail' part of our columns. As

I am not yet sure which fields we will need in which order, I will go and do some testing and trials etc.

Remember, when doing groups within a control, it is a good idea to initially include the group fields, just

so that it is easy to visually check that the grouping is working. Once we have the table and groupings

looking OK, we can remove the columns where the data is 'static' or 'fixed' for that group. Much as we

did in the last chapter.

Homework done - I have come up with a starting layout as shown below :-

==========================================================================================================================================

I suggest you follow the choice I have made, and drag the necessary fields into your columns in the same

order as shown in the image above.

Remember that if you get any wrong, just click in the 'offending' cell and press the Delete key. This works

either one cell at a time or after a multiple highlight - selection.

So if you get any field drag wrong, delete the contents and try again.

How are we doing ? :

Another good discipline to make a 'habit' is to use the Preview tab regularly as you improve your report.

Let us see how we are doing so far with our new report :-

===============================================================================================================================

Well, we have results, and they are much as we may have expected.

However, do not thing we have any group as yet, the 'ProductCatergoryID' is only as it is because the

SELECT statement in the dataset does a final 'Order By' on this very field.

Making a few improvements :

Let us now improve the slightly confused look of this output by simply setting the Format setting of the

Properties pane (PP) to ' N ' for our money / cash fields. here is what I get :-

========================================================================

*** WARNING *** Be careful when adding the Format entry to the PP. If you put 'N' instead of N this

is what you get :-

=========================================================================

So do NOT do the following !!! :-

===================================================

Making our first Group :

Okay then, enough fiddling about and learning etc. Time to make progress !

Let us now make our first Group based on the category field.

With the Table in focus, right click in the little grey square with the three 'row' icon as shown below :-

==================================================

When the 'Grouping and Sorting Properties' dialog shows on screen, make the entries as shown :-

It may actually be useful to not check the 'Page break at end' control while doing

your initial testing - I have just unchecked mine at this end.

Doing a quick preview we see this on page twelve :-

=======================================================================================================================================

Although this image above shows the category grouping, it is a poor choice in as far as the

product ID does not vary within these small groups.

Here is a better example of different product IDs within the main grouping :-

[ this example is from page 9 ... ]

=====================================================================================================================================

In the image above you will notice that the product ID is not in any order (other than apparently random),

so we can change the SELECT statement in our Dataset to correct this.

Here is what seems to work okay :-

==============================================================

And to prove it here is the result of a fresh preview with the new dataset :-

======================================================================================

Now we have 885,886,889, ... through to 900, and in suitable order too ;-)

Just what we required !

Another way to sort ? :

We need to know that there appears to be another method to sort a group, and this is shown on the

'Sorting' tab. However, I have not yet got enough experience to make it do the multiple field sort we have

used in the SELECT statement.

I have managed to get a single field to work okay, as can be seen listed in the image below. However,

I am as yet not successful on the multiple field front. I did get the expression editor to syntactically take

a combination of fields, and this ran with a preview, but did not give the results I required.

I will do some more work and research and then feedback to you.

Maybe we need to sort on the next grouping level to achieve the results I am after. We shall soon see.

For now here are some images to refer to later :-

========================================================================================================================

Summing the 'Category' group :

Before we move on to make a sub-group let us do what we know how to do first - that is sum the fields

for quantity and 'Line Total' - similar to what we did in the last chapter.

Here is what we have at the moment in our layout grid :-

============================================================

Now add the summation expressions to the text box cells in the group footer part of the layout.

Below are my results of this summation cell expression entry :-

===================================================================================================================================

I have temporarily widened my money columns so that you can see the expression 'contents' :-

==========================================================================

*** Remember *** you can actually type in directly the expression string !

So as a final few changes to the layout - before we go to sub-grouping - merge the group header

cells and then add suitable content for now as shown below :-

=======================================================================================================================================

Make the background colour 'Gainsboro' as we have used previously.

Which produces the following results :-

====================================================================================================================================

Until we have time to format this in any better way, it will do as a group indicator so that we

can work on our sub-grouping without getting too muddled up.

Making the 'Sub-Group' :

When we start to deal with more than one grouping, we need to remind ourselves that all groups belong

to the Table control itself, not the report. And that one group does not belong to another group.

However, once we specify a second group, it is then possible to specify a 'Parent' group as the other

existing group. So we are saying - by specifying a parent group - that one group is within another.

Instead of trying to explain all this in words, it is best to do it and show some screen images !

Before we go into the few details which need entering carefully, let us see the pleasing results :-

===================================================================================================================================

Checking through the whole set of pages produced, it would appear that we have our desired table structure.

Our first "Group within a Group" ;O))

However, remember that the order of the categories and products has been determined by our Order By clause

of the SELECT statement - it would be nice to use the sorting tab in our group dialogs. I will do some testing

with what we have here.

Before I go and test, here are the screen details of the second group entry. Study these carefully and then copy

the details into your own report :-

========================================================================================================================================

Sorting within the Group specification :

Well guys, I have done some testing, and the sorting issue is much easier than I had thought.

It would appear that we specify in the sorting tab for each group, the order for just those items on which

we are grouping.

So I have sorted the Category group by a single field, that of 'ProductCategoryID', and the second of our

groups, the Product group, by 'ProductID' field.

By making this second group a child of the first, the multiple field sorting seems to be taken care of quite

nicely.

I my tests I have sorted in descending order on both chosen sort fields as you can see in these next couple

of images - shown below :-

=====================================================================================================================

And now the start of page one :-

==================================================================================================================================

If we look in the layout we see the following when the table is in focus :-

================================================================================================================================================

Now notice, in this image, the extra pair of Group header and footer cells. They are marked with suitable icons.

If we wish to edit the details of any one of our groups, we simply right click with the mouse cursor inside the area

of the little grey squares which show the group icons. See below as an example :-

================================================

*** Note *** that we can equally click in the footer icon for a group, as the header icon area.

Here are the settings I used for the 'Sorting' tabs in each group :-

=====================================================================================================================

Above is shown the Category group tab, and now below the Product group :-

=========================================================================================================================

If you copy what I have done then you will see the same report row order.

Also, if you feel up to it you may like to do a further test where you change the dataset 'Order By' clause

to something VERY different - or nothing at all - just to see that the group sorting is now actually overwriting

any previous data row order.

Good Luck !

Adding a Header to the sub-group :

What will make our report more obvious is if we give our second group both a heading and a summation

footer.

Also, if we offset the header it will stand out better. Lets try this now.

See the results before we look at the expression strings and other settings :-

==================================================================================================================================

So this is looking quite good, even as it is now, and it does need some extra formatting, as well

as a couple of columns removed etc. - and headings improved.

Here is the layout for what we have at the moment :-

==============================================================================================================================================

*** Note *** the expression string content of the second grouping (here marked in Bisque) is exactly the

same as the the Category group - our first group. The VS shell sorts out the scoping for us.

Another thing to note is that I have only merged cells 2 through 4, so that an automatic indent appears

in our second header row.

The problem now is that we really need to remove a couple of columns and place the 'static' values in the

headers, and this means changing the merge cells as well as some major redesign.

However, we needed to see what was going on while we learned how to 'Group within Groups' ;-) So

just bear the pain for the moment !

Before I go and redesign the layout as mentioned, it is worthy of note that in a Table control I can't see that

going much deeper than two levels of grouping will ever either be necessary, or indeed produce a report

which is easy for the user to digest.

Now off you go and do the changes I discussed - it will be good practise for you ;O))

In the next subsection we will look at how we can improve the initial look of the report by collapsing the

second grouping, and allowing the user to toggle a collapse and expansion of each chosen sub group.

See you in the next subsection.

PHH - June 23rd. 2010.


Some Further Improvements

Before we go into the details of what to do to make our further improvements, let us take a

few moments to study some successful and different output :-

=================================================================================================================================

And also this :-

=================================================================================================================================

As you can see in the images above, we seem to be able to expand and collapse each separate

Category group - with all of the Product groups contained with it.

There is now a 'region' control at the beginning of the screen area based upon the reports layout textbox5

control. See below for an indicator image :-

===========================================================================================================================================

If we open the dialog screen for the second group - the Product group - we we the tab for 'visibility' look

like this :-

=====================================================================================================================

The default setting for the VS shell is 'visible'.

I am a bit new to this feature for 'expand / collapse' and have not quite fathomed it out, as to why these

settings work as they do. We need some more experience and experimentation I feel.

However, at least we can see and example working nicely ;-)

Also, from the dialog tab above, we can get an idea that we could set a condition such that the visibility

is data driven, and not set at design time as we have done here.

For example, we may wish to have any section expanded if the sales were beneath a certain amount.

This is still early days and we need to feel our way I suggest.

First try at data drive visibility :

To get us on our way I have tried to supply an expression so that we have the Product group collapsed unless

the quantity of the sales is low. have a look at the image below before we get to discussing the fine details :-

=================================================================================================================================

Notice we can only see Product groups expanded (visible) for those will quantity totals of less than

thirty eight (38) in total.

This is because of the setting I made as shown below :-

====================================================================================================================

For the present test I have turned off the toggle and so no 'region' control is displayed on the report.

You are going to ask why 38 ?

Well the simple and straight forward answer is that he Category 35 is comprised of three Product groups,

and the other two are 38 and 51.

So lets see what we get for this same Category if we change the limit from 38 to 45 :-

=================================================================================================================================

Notice the 711 Products are now visible, along with those of 707 which were previously.

I have also replaced the region toggle, and it now seems to have a very useful function, one which

quite surprised me ;-)

Let us have a look at the case when this category is toggled the other way :-

==================================================================================================================================

The high groups are shown instead of the low ones. And this toggles as you might expect.

This facility could be VERY useful in many reports I feel, focusing the attention of the consumer

of the report on critical regions of the report figures.

I think we need to sleep on this new feature, and come up with some great ideas for its application

in the future.

Going one step further :

Now that we have gotten this to work above, it was worth a trial to see if I could set up the group one,

'Category group' to do something similar.

Here are the settings which I provided to the dialog screen input boxes :-

===================================================================================================================

You will see that an extra 'region' control is now associated with textbox control number 1.

Let us see what we get before any further discussion :-

=================================================================================================

Above is part of the report output. It takes a little study to see what it does.

If a Category group has a quantity value greater than 199 then it is hidden from the report, whether

the hidden section contains a Product group of less than 45.

If the region control at the left of the main heading row is expanded then all category groups with

quantities of 200 or more are shown, and the others hidden.

The region controls for the Product group work as expected when the are shown, again toggling between

the two sets of above and below the specified condition.

This is WELL worth you try out seriously, as it could prove VERY useful in the future.

However, it does take a little effort to see just what is going on, and how the specified limit conditions

work with the interactive on-screen report.

Please NOTE that the printed copy has no region controls, and prints what is visible on screen - ONLY !

Good Luck, work carefully, and make sure you find out about this ;O))

See you in the next chapter.

PHH - June 23rd. 2010.


Chapter 6 - Report Items - non Data Regions

Below is an image of the Reporting Services toolbox, for use with the Designer within the RS shell.

All the Report Items available are shown :-

============================================

In the image above, some of the items are 'Data Regions' and some are not.

The BoL (Books on Line) has this to say about 'Working with Report Items' :-

A report in Reporting Services uses report items to display data and graphical elements. In addition to data regions,

report items also include text boxes, images, lines, rectangles, and subreports.

Here we will concentrate on the non Data Region (DR) items, and leave the discussion on DR items till the next

chapter, where we will look deeply into what they are, and what they have to offer us.

Item Properties :

All items in a report, including groups, table and matrix columns and rows, and the report itself, have associated

properties. These properties govern the appearance and behavior of the item to which they are associated.

The Non Data Region items :

[ Textbox, Line, Rectangle, Image, & Subreport ]

Textbox :

Text boxes display all text data in a report. A table or a matrix cell includes by default, a text box to display data. Text

boxes can be placed anywhere on a report and can contain labels, fields, or calculated data. You use expressions to

define data in text boxes.

Line :

Lines are graphical elements that you can place anywhere on a page. A line is defined using a start and end point, and

can have a number of styles (for example, weight and color) assigned to it. There is no data associated with a line.

Rectangle :

Rectangles can be used in two ways: as a graphical element and as a container for other report items. When you place

report items within a rectangle, you can move them with the rectangle. This is useful for keeping numerous text boxes

and other items together in a report.

Image :

Images display binary image data in a report. An image report item can use a URL to display an image stored on a Web

server, display embedded image data, or display an image from binary data in a database. Reporting Services supports

.bmp, .jpeg, .gif, and .png files.

Subreport :

A subreport is an item in a report that points to another report on the report server. The report that the subreport refers

to can be a full report that can also run on its own, or it can be a report that looks best when embedded within the main

report. When you define a subreport, you can also define parameters to filter data in the subreport.

When deciding whether to use a subreport, you may want to consider using a data region instead. Because the report

server processes each instance of a subreport as a separate report, performance can be impacted. Data regions provide

much of the same functionality and flexibility as subreports, but with better performance.

After reading the detail of each of these items as described in the 'BoL' documentation above, it would seem likely that we

will see examples of their use in the more 'freeform' data region called a 'List'.

Since a 'List' can have its data elements more freely laid out, with relation to one another, we need to use rectangles as

embellishments as well as containers, and lines and images will certainly improve the look and readability also.

Do remember however, that the image displayed in a 'List' data area is quite likely to come from a database, and as such

be part of the data. So an employee data area in a list may have an image of their face, as displayed on a company

photo array / board.

The Layout Surface - the "Designer" :

When I refer to the 'Designer' in these notes I am talking of the layout grid and its supporting tools and features, in much

the way we talk of the 'designer' for Forms GUI creation, and also the WPF 'designer' also.

remember, as with the GUI designers mentioned above, in the VS shell the Document Outline (DO) is extremely useful at

times in helping us select the item we require - particularly when the elements are close together, or overlaid.

Below is a simple reminder :-

==========================================================================================================================

To bring up the 'DO' use the keyboard shortcut of 'Ctrl+Alt+T', or the drop menu option as shown below :-

==================================================================================================

In order to see the Line, Rectangle and Image in use I suggest you read the chapter on the 'List' data region

in a couple of chapters time. Here we will use these to improve and embellish our data layout within the

repeating 'row' of the 'List' item itself.

PHH - June 28th. 2010.


Chapter 7 - Data Regions

We need to know what Data Regions are when dealing with reports.

'Books on Line' has this to say on the topic :-

Data regions are report items that display repeated rows of data from underlying datasets. These rows

can be displayed in a table, matrix, list, or chart.

In later chapters we will go more deeply into the Matrix, List and Chart controls, as well as nesting data

regions too. We have already done a substantial amount with Tables.

For now let us see what 'Books on Line' (BoL) has to say :-

Table :

A table is a data region that presents data row by row. Table columns are static. Table rows expand

downwards to accommodate the data. You can add groups to tables, which organize data by selected

fields or expressions.

Matrix :

A matrix is also known as a 'crosstab'. A matrix data region contains both columns and rows that expand

to accommodate the data. A matrix can have dynamic columns and rows, or static columns and rows.

Both columns and rows can contain other columns or rows, and can be used to group data.

List :

A list is a data region that presents data arranged in a freeform fashion. You can arrange report items to

create a form with text boxes, images, and other data regions placed anywhere within the list.

Chart :

A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more

styles are supported.

Nested Data Regions :

You can nest data regions within other data regions. For example, if you want to create a sales record for

each sales person in a database, you can create a list with text boxes and an image to display information

about the employee, and then add table and chart data regions to show the employee's sales record.

Rule One of Reporting :

All this 'stuff' above sounds exciting and interesting, particularly to the developer, however, 'Rule One' for

designing reports generally is :- 'Keep it as simple as possible.'

If you make things too complex then the meaning to the user may get partly lost in the viewing !!!

Charts are wonderful, but can be overdone. Often users like charts more than the Database Administrator

(DBA) who is requesting the report - a difficult issue ;-)

So the tables we have designed and made so far are single data regions.

When we added the bar graph to our report in chapter four, we then had two data regions, a chart and a table.

Remember a chart is the general name for a bar graph, pie chart, and others. I have a feeling however that in

RS 2008 the new feature on Gauges is seem as an extra category, and not part of charts.

This is indicated by the look of the Toolbox in VS2008 - see below :-

==============================================================

Yep! it looks as though we have an extra Data Region, the Gauge. This is obviously quite separate to the Chart.

See you in the next subsection where we try to answer the question - "What is a data region ?".

PHH - June 25th. 2010.


What is a Data Region ?

This seems a simple enough question to be asking, and quite understandable as to why it is being asked also.

Let the 'Textbox' report item Explain ! :

As you may have guessed by now, the 'Textbox' report item is a rather fundamental part of the the whole report

design process. It seems to appear everywhere, in just about all elements of the report itself. We are well aware

that it appears in cells within the Table element, both header/footer and body sections.

You are also probably quite aware that when we wish to place contents within the Textbox the 'Expression' is a

fundamental part of specifying a number of properties.

Let us study the detail in the image below :-

=======================================================================================================================

If we right click the 'detail' cell of a Matrix report item (and Tabular 'detail' cells are similar) we see the

following when we bring up the 'Edit Expression' dialog, as seen below :-

==========================================================================================================================

In the image above, the thing we are focusing on is the fact that we have a list of field names available

to us when the 'Fields' item is selected in the left most list box.

So in effect we can choose any of the fields on offer from within the list.

However, let us make a contrast now with another Textbox item in the same simple report 'structure'.

Examine the image below to see that the Textbox in focus is just within the body of the report itself, and

no other report element :-

=======================================================================================

If we now display the 'edit Expression' dialog for this Textbox we see an important difference.

Look in particular at first and third list box :-

===========================================================================================================================

The image above clearly indicates that there are no data field choices available - none can be made.

So why is this ?

The answer is a simple one, the Textbox is not within a 'Data Region', or should we say a report item which

has a Dataset associated with it. Only, when a Textbox is within a data region can it have access to the data fields

of the Dataset in scope - as it were.

So what about another Textbox from this simple example ? Take a look at the image below,

there are both differences and similarities :-

======================================================================================

The highlighted Textbox is within the List report item, but outside the Matrix item.

Let us see what we get when we look at our Expression in its edit dialog :-

==========================================================================================================================

Yes, we have fields available for choice. And it is because we have a simple example to hand that the

fields are the same for the List element as for the Matrix element.

If we had more than one dataset we could have a situation where the Textbox in the List was within one

data region and the Textboxes within the Matrix were in another. Then the fields on offer would be different.

Now look at the List properties below :-

=================================================================================================================================================================

And to prove a point, the Matrix properties are shown below :-

=======================================================================================================================

Yep! the same Dataset, as there is only one on offer in this example.

It is easy to specify a scenario in which this would be different however.

Let us say we have a List which will display record details of each salesperson, and this is associated with

a dataset called 'SalesPersonnel'. However, for each sales person we are to have displayed a matrix (or table)

which shows the detail of the sales made by that person over a period of time. The Matrix will therefore more likely

be associated with a different Dataset.

So Texboxes within the Matrix will have different data fields available to them than those Textboxes within the List.

You need to look out for this sort of situation in future as we see and create more report solutions.

Do be aware however, that many more levels of data regions can be nested than just two ;O)

Simple Summary :

All report items which are not themselves associated with data regions, use the data region of the report item

that they are within. So placement of Textboxes and Images needs to be done so that they access the data

they need to do their job.

Most often it is the Texbox which will be affected in the way we discussed above.

Using our Knowledge to Predict :

Now that we understand the 'scoping' of data regions and Textboxes, we can make a useful prediction.

If we are to design a single document type report which is basically an 'invoice', and have Textbox items display

information which is coming from a dataset (one data record), then these Textbox item MUST be within an

item which has an association with the dataset in mind.

The simples of the report items which have associated Datasets is the List item.

So my prediction is that a List with lots of different Textbox items would at least allow the Invoice report to work.

To see what we can do with this idea, go to Chapter 18 to hopeful see a solution ;0)

PHH - July 5th. 2010


Data Regions in our Design

Once we get our heads around what a Data Region (DR) really is, we can see that we must plan both the

Datasets we require, and where we can use them in areas on our report surface.

Since a DR is simply the scope of an existing Dataset, we need to take this into account when we do a

Top Down design for any new report.

The Datasets (and our data) come first, and then the report items we need to use the Datasets. We place

the report items appropriately on the report, to aid our clients understanding the data.

We must NOT get into the mindset that the report item comes first, and this then influences the data displayed.

Rule One : Data comes first !

Sketch out the outline of your developing report. Roughly show the data that is required in the various areas

of the report, including images, headings and footings.

Then think through which Datasets are required from your database to be able to present the data on the report

as your design demands.

Then, and only then, think of the report items which associate with DRs, and which are best suited for the task

in hand.

At this point you can decide if DR items need to be nested within one and other.

It is often really the 'DR items' which are nested, and not the DRs themselves. The wording of support material is

confusing on this issue.

However, there are cases where the data from the outer DR can be used to determine the data which is supplied

as a returned Dataset in a nested and lower DR.

These finer points on DRs will emerge as we see suitable examples - ones we can discuss in detail at the time.

One thing we will have to research is how we may have to design our Datasets so that they can be nested and

then function correctly. How do we pass a data item into the next level of Dataset to then determine the returned

dataset from the SQL Server data source.

PHH - July 5th. 2010


Chapter 8 - the List Control

Report Items - List Control :

A list is a data region that presents data arranged in a 'freeform' fashion. You can arrange report items to

create a form with text boxes, images, and other data regions placed anywhere within the list.

*** we need to explore this 'freeform' placement later ***

The above is what the BoL (Books on Line) had to say about a List control on a report, however, I feel

that it is when we come to the chapter on 'Nested data Regions' that we will see the List come into it's

own !

It gives us a looser, or freer way to display data regions within one outer region. A table layout is much more

rigid, or restricted.

For the present chapter we will see the straightforward use, or application, of the List control in a simple

report we will create for the purpose of exploring the simple details and setting of the control as it comes

out of the toolbox.

If we have a list item (data region) on our designer layout, and then right click the mouse we bring up the

properties dialog as shown below :-

==========================================================================================================

As you can see in the image above, we have a number of tabs to the dialog screen, as well as a button

to launch the Grouping and Sorting Properties dialog as shown below :-

=============================================================================================================

Yes, it looks as though we can create our own groups, as well as setting filters and sorts.

First Trials :

For the present, let us leave the deeper settings of the List dialog screens, and just make a simple example

which is based on a very straightforward SELECT statement, which as it stands below only gives us a handful

of records to display.

Below is shown the dataset with which we will start our List item 'exploration' :-

========================================================================================================================

In the image shown above, please note that the WHERE clause has been added to simply reduce the

number of records returned from the SQL Server. We will try to add our filter conditions to the properties

dialog of the List item itself in a short while.

So with the present data in the 'AWLT' database we get eleven rows to display.

Now let us focus our attention on the layout tab in our VS shell central work area.

Below I have set out a simple list as our first trial. Let us study the layout :-

============================================================================================================================================

In the image above there are a couple of things to note in particular, the first one being that we now have a

new Report called 'ListOne' and therefore we expect to see another entry with this name in the Solution

Explorer (SE) pane.

I suggest you start a new Report for yourself now, and get your SE pane to look like this :-

=========================================================

Once you have your new report, make a dataset looking just like the one shown previously in this section.

Make sure you run the query using the ' ! ' (pling) icon, and check you get 11 rows ready for display.

Drag a List item from the toolbox onto the layout grid, and set the grid spacing to our usual 0.20cm as well as

making the width just under 20 cm as shown below :-

======================================================================================================

*** Note *** I have got my PP floating and have moved it onto the layout area so that my image can show

two things at once. If you don't know how to handle the pane docking, floating, and slide-out, within

the VS shell then go to 'ClickStartVS' and follow the notes through so that you get the necessary skills.

As you can see the List item is not very deep, and this is by design, we will see what happens when we

change this in a short while.

In order to help you create the List and its single Textbox item, I have shown below the PP for the textbox

itself, including the tooltip with the full expression string :-

=====================================================================================================================

Now let us preview what we have so far - click the Preview tab to get this :-

=============================================================================================

In the image above notice we have 11 rows displayed, and each is made from the contents of the

'CompanyName', 'LastName' and 'FirstName' data fields, with concatenation and a few other characters

to make it a little more readable.

Notice that the List item expands in the way the Table item does, meaning it eventually takes up the

amount of space dictated by the results set. And, that the border is around the whole List item, not the

individual rows or records.

Before we move on to explore making our repeated data area more interesting, let us call up the DO pane

and see what we have at the moment. [ keyboard shortcut is 'Ctrl + Alt +T' ]

===================================================================================================

By clicking in the DO pane, on the Report itself, actually brings up the tiny black square in the little grey

square at top left of the image, so the Report is in focus.

Before we move on to adding further report items to our List, let us simply see what happens if we increase

the depth of the Bisque coloured List area :-

====================================================================================================

===========================================================================================

Looking at the above images we see that the area we select in the layout grid is exactly drawn for each

row when the report is rendered (or made).

And now if we split up the content of this long textbox and place it in three shorter ones as shown below :-

================================================================================================

We then get the following output when the preview is run :-

==========================================================================================

The above sample output is from the body of the report, and shows word wrap in the first textbox, we could

do with it being wider.

Note also that the colour of the text in the extra two text boxes is still set as black.

Improving what we have :

Let use deepen the List area on the grid and then move the three text boxes all together.

The report items 'marquee select' as in many other IDE shell applications - click outside of the

three text boxes and drag the dotted line which appears to surround the three items, as

illustrated below :-

============================================================================

When the mouse button is released we have handles on each text box item as show below :-

==============================================================================

Being careful not to lose the multiple selection, use the arrow keys to reposition the group of

text boxes 'as a whole'. I have moved mine down slightly, and to the right :-

====================================================================================

Now click in the bisque part of the List to deselect all items, or alternatively use the DO pane and

click the 'Body' as shown below :-

==============================================================================

Adding a rectangle :

We are now going to add a new report item, the Rectangle item.

Drag one from the toolbox into the List data region as shown below :-

===================================================================================================

Use the handles to drag the edges one at a time to get the following shape :-

==================================================================================================

Okay, I know, we have lost sight of the text boxes ;-) So we need to right click within the white and 'handled'

area and send this to the back in the common way with designer tools - see below :-

======================================================

The results should look like this :-

==================================================================================================

Before we try a preview, let us first colour the background of the rectangle and set a border.

Add the properties as shown below :-

============================================================================

Now when we run a preview we see this :-

=========================================================================================

Looking at what we now have in the image above, we can see that things are improving but we

could now do a lot more to make things different to a table, if nothing else ;O))

Now 'marquee select' the four report items as shown below :-

================================================================================================

So now you need to do a few new things, starting with moving the group to the left and adding a

second rectangle, and a couple of text boxes, as shown below :-

======================================================================================================================

For now add an expression to each of the new text boxes, one for the phone data field, and the other for the

email address data field.

And also you need to see the PP settings I have used for the second rectangle :-

We will now learn something new when we try to preview this layout. See below :-

==================================================================================================================

Yes, the second rectangle surprises us as it does not come down as far as we may well have expected !

The limit of the rectangle as we have set things up, is determined by the report items within it. Or so it

would seem.

To check this out lets move down the lower text box as shown below :-

=========================================================================

And this is what is produced :-

==================================================================================================================

Well, we have achieved something along the lines of what we were hoping for, but whether we can say we are

in control of things is another matter ;-)

A brief summary of where we are :

Okay then, what have we done, and what have we learned ?

The List report item is a data region, in that each of the report items within it are repeated for each record of

the data set for that region.

Each report item placed anywhere within the List layout area, is placed as required in that place, BUT, we need

to realise that other report items may have their limits set by data also, so sub-items may be data driven as

far as their boundaries are concerned.

Here is our example after a short amount of time has been spent on improving the layout and details.

See you can now study the image below, and try to make your own report similar :-

[ *** Note *** a line has been added to the second rectangle, and its style set to dashed. ]

================================================================================================================

I realise that is is in many ways a bit odd as a report style etc. - BUT - it was done to demonstrate that there

are definite differences, and possible benefits, in using a List data region in place of a Table.

As yet we have not seen the more complex use of the List control, where images and more can be included, as well

as data driven.

However, we had to start somewhere, and so our results are more than acceptable I feel ;-)

Now we have done enough for one section, lets move on to the next subsection where we will explore List

items further, as well as some detail of the DO and how to group report items within a Rectangle item.

See you there.

PHH - June 25th. 2010.


Exploring Lists Further - Images

Comparing the two Rectangles :

Let us start this 'further exploring' section by seeing some more of the detail of how the two rectangles are 'made up'.

In the image below the second rectangle is in focus, and this was done by mouse selecting it from the DO pane - see below :-

=============================================================================================================================

In the above image, if you view the DO pane you can see that 'rectangle2' has four child objects.

One line and three text boxes are 'grouped' under the Rectangle item. Notice that when the Rectangle item is selected

and moved, the whole group moves together as one. This is similar to 'grouping' in Desktop Publishing (DTP) applications,

and can be very helpful when designing.

However, at the moment, the first Rectangle item, which was dragged onto the layout surface after the other three

text box items were in place, is at the same level as the 'Lastname', 'CompanyName', and 'Firstname' text box

objects.

In the images below I am part way through making the three text box items become part of the 'group' formed around

the 'rectangle1' object. To do this I am cutting and pasting the text box items directly from the DO pane.

===================================================================================================================

Try to repeat for yourself the exercise shown in the above image, and remember your friend 'Ctrl+Z' if things don't

go exactly according to plan. 'Ctrl+Z' will undo the last editing operation, and a whole bunch of operations is held

by the system in its editing 'history' list. Re-do, 'Ctrl+Y' also seems to work fine.

You may need to reposition the items within the grid part of the Rectangle item, my first attempt seemed to place

the three of my text box objects - one on top of the other - in the centre part of the Rectangle item.

When you are done try moving the whole new 'group' by repositioning the Rectangle itself.

In the image below the task of placing the three text box items within the first Rectangle is completed

and the structure of the DO pane shows this :-

=============================================================================================================================

After group repositioning both Rectangle one and also two, we get a layout grid looking like that shown

below, with the extra 9surrounding) List area reduced :-

===========================================================================================================================

Now when we do a preview we get the following - I am showing the last two records :-

===================================================================================================================

Notice the border at the bottom, that belongs to the Rectangle object itself.

Now we must move on to something new !

The Image item :

Before we see what we may do with the Image report item within a List data region, let us look at the

Image item itself.

Below is part of a report from the examples in Stacia Misner's 2005 book - see Introduction for more information -

this example has five images on the front page of the report, and this below is just one of them :-

=========================================================================

Notice that the image which is placed on the layout grid is a JPEG and that it has also been embedded. This

means that it has been completely placed within the report code / resources.

Before we look at making our own, lets look at a second image from the excellent book by Misner.

Here is a logo type image which has been designed and made in software, then saved to a JPEG format.

Notice that both the photo image above, and the graphics image below, have each been given a 'sizing' setting of

'AutoSize' - in the image below I have opened the drop list so you can see all the options :-

========================================================================

You may find that the 'AutoSize' setting is most suitable for many situations, but be aware of the others.

Making and Placing our own Image :

Before we go through all the details of placing a static image, let us look at some success to motivate us

and get us on our way :-

=====================================================================================================================================================

The image shown above has an image placed into our extended (right hand side) List item.

And the preview look of this is as shown below :-

=====================================================================================================================================

Before we clear / remove the image and go through the process of selecting and placing one, let

us look at my sub-folder where I have placed a suitable handful of JPEGs :-

==========================================================================================================================================

I suggest you make an 'Image' sub-folder within your report folder structure, and find a few suitable JPEG images

from Google images. That is where I got my 'mask' images shown below :-

====================================================================================================================================================

Placing a Static Image :

Select your List report - 'ListOne' - and extend your List report item as shown below :-

======================================================================================================================================================

Notice that any previous Image item has been removed - there is nothing else in the DO pane.

You will also need to extend your grey grid area so that it is about 22cm wide.

Now look in your 'Toolbox' pane a find the Image item. It may appear differently in your pane that that

shown below :- ( simply as the Toolbox items an be dragged into different list positions )

===========================================

Drag an Image report item from the Toolbox and into the bisques coloured part of the List item on the layout

grid, to the right of the second Rectangle item.

Wow ! What is this ? the Image Wizard appears as shown below :-

===============================================================================================================

When we click 'Next' we get a list of possibilities as shown :-

==============================================================================================================

It would appear that from my limited experimentation at the moment, the 'Embedded' option is the safest from

a single report point of view, and always having the image available for rendering on a report 'Front Page' for

example.

However, we need to look further at the other options as they open up some interesting solutions to reporting

problems. Select next and use the Embedded option for now. This is what you see :-

=============================================================================================================

Obviously, we need to select the 'New Image' option by clicking the button at top right of the dialog screen.

This brings up the 'Import Image' dialog at which point you need to navigate to the sub-folder you created,

with the small JPEG images from Google, or similar :-

=====================================================================================================================================

For this example I will choose my cathedral image as show below :-

============================================================================================================

The 'Open' click with the mouse causes a new entry to be placed into the 'Embedded Image' list as shown below :-

=============================================================================================================

With only one image in the list, it is automatically in focus (selected) and so we now only need to press 'Next' to

select this image for display within the Image report item itself. Remember the image is just one property of the

report Image item object (or class) - as viewing the PP will demonstrate in a short while.

This is what you ought to get :-

==============================================================================================================

Finishing the Wizard redisplays the layout with the new report item in place and displaying the necessary 'picture'.

Here is what should now be on your grid and screen :-

=====================================================================================================================================================

Notice that there is a new entry in the DO pane, and that the 'image1' item is at the same level as the two previous

Rectangle items.

Requesting a preview shows this :-

=======================================================================================

Your Homework ! :

Okay then, now that you know how to place a static image, look at this next sample output and then change

your first Rectangle item to include a second image, as show below :-

============================================================================================================================================

In order to get your preview output to look like that above you will need to make some adjustments to the handles

of previous items, and also resize the second (now smaller) image.

If you like, make this second image item display a different picture ;-)

Good Luck !!

Image 'picture' display by 'Expression' :

PHH - June 29th. 2010.


Lists within Lists - different data !

PHH - July 5th. 2010


Chapter 9 - the Matrix Control

Report Items - Matrix Control :

Before we get into the business of trying a suitable example in the IDE, we need to do some research, and

read a few lines from 'Books on Line' (BoL) to get a few ideas, but not to worry about what we think we don't

understand.

As you read further, remember that a Matrix control should only really be used for a problem which NEEDS

such a control for a report solution. Don't just use it in place of a Table !

Once we get to typing and displaying data, a good and suitable example will make things so much clearer ;0)

*** To have a 'suitable' example, we must first have data which 'lends' to a Matrix solution. ***

Now the reading :

A matrix is also known as a 'crosstab'. A matrix data region contains both columns and rows that expand

to accommodate the data. A matrix can have dynamic columns and rows, or static columns and rows.

Both columns and rows can contain other columns or rows, and can be used to group data.

"Books on Line" has this to say :-

Working with Matrix Data Regions :

In a matrix data region, data is arranged into columns and rows. Matrices provide functionality similar to 'crosstabs'

and pivot tables. Unlike a table, which has a static set of columns, matrix columns can be dynamic. Using Report

Designer, you can define matrices that contain static and dynamic rows and columns.

You can add a matrix to a new or existing report. You can create a matrix report using the Report Wizard.

=====================================================================================================================================================

Dynamic Columns and Rows (Groups) :

You can add additional dynamic columns and rows to the default matrix. Dynamic columns and rows are used to

group data by field. When you add a new dynamic column by creating a new column or row header, the new header

is nested within the original header. When the report runs, the new header is repeated within the original header. For

example, a nested dynamic column or row can have a header containing a field for region, and within that header,

another header that contains a field for city.

You can add dynamic columns and rows by dragging fields from the fields list onto the matrix. When you drag a field

onto a matrix that has an existing column or row header, you can choose to place the field on the inside or outside of

the header. Report Designer displays a bar on the top or bottom of an existing column header, or to the left or right of

an existing row header, depending on where you drag the field. For example, to create a new dynamic column that

contains an existing dynamic column, you would drag the field to the existing column header, position it so that a bar

is displayed on the top border of the header cell, and then drop the field.

[ End of 'BoL' details for the moment ! ]

=====================================================================================

Enough of the words, "huff and puff", and the likes, let us try to see how far we can get with a working example of

our own. And remember, users get more from simple and straightforward reports where the meaning is clear.

Viewing Success - to Motivate Us :

There is nothing like a colourful picture showing what we can easily achieve to motivate the 'learner' !

So lets look at what we can produce within a few minutes, with only a few clicks of a mouse, in the right

places it has to be said - and there are lots of wrong places ;0)

Below is one of a couple of worksheets in Excel which show our Matrix in action :-

====================================================================================================================================================================

As you will see in the image above, one of the problems of working with matrix data regions is that often with

meaningful data the visual effect is hard to squeeze into a small space, either screen or paper. But certainly

paper !

Also please note that our report is easy to export to Excel, where at least we don't have to fight paper size

limitations to be able to see it nicely laid out.

Below is another image from this same report output, it helps see what is going on :-

===========================================================================================================================================

Notice in the state of California we have grouped a number of cities.

However, in the image below other states have just one out-let, and so a single city heading :-

=========================================================================================

Okay, you spotted it, the report heading is missing from this image, as it was an earlier screen capture ;o)

However it does show the single cities for the same bicycle product range.

Remember the whole point of Matrix layout as we can have dynamic column grouping, as well as dynamic

row grouping. We have obviously focused on the (new to us) dynamic column grouping in this example.

Making this Report :

Let's get into making this report using the same BIDS 2005 shell we have so far used to create reports based

on the Table data region.

The first thing we need to know, before we get going, is that we will use a query from Mark Long's training CD

and do the same simple example on the Matrix control that he attempts. This select statement is fully documented

in the subsection of where we are now. [ It is also used in Chapter 16. ]

before we go any further open SSMS and make yourself a View object in the AWLT database using this query

script - test it out both as a query and as a View.

below is what I have here in my SSMS :-

=========================================================================================================================================

And here is another image which may help you out :-

=============================================================================================================================================================================================================

Starting a New Report :

This time when we make our report, we will use the Wizard, just to see what it can do for us in creating a

Matrix data region.

So right click the Reports item in the SE pane and from the context menu select 'Add New Report' :-

===========================================================

This selection launches the Wizard directly, and the next thing you see is this :-

====================================================================================================================

After clicking 'next' we get the 'Select the Data Source' screen and we do not need to do anything as

it looks like this below :-

===================================================================================================================

Moving on to the next screen we see the dialog for 'Design the Query', at which we can copy

and paste the query script from the Mark Long text in the following subsection - this is exactly

what I have just done, and it looks like this below :-

===================================================================================================================

And before we move on to the final phase of the Wizard 'process' we can click the 'Query Builder' button

to open that dialog, and test our select statement, as shown below :-

[ Notice the 'pling' icon with the ' ! ' mark which runs the query ... ]

Press the appropriate buttons and move to the 'Select the Report Type' dialog and select 'Matrix' :-

===================================================================================================================

Now we feel we are getting someplace ;-)

In the 'Design the Matrix' dialog screen which appears, make the choices I have made, which are shown

clearly below :-

====================================================================================================================

Notice I have enabled drill-down - even though we may not as yet have suitable data to show this feature ;O)

Also, although I have chosen a second column as City, I am not sure if the Wizard will give me the nested grouping

we are after, so we may need to make a couple of small changes after we have a report and a design surface to

work with. Let's see what we get when we complete the Wizard.

For now let's move on, and choose a 'Matrix Style' I like the 'Bold' colouration :-

====================================================================================================================

So 'finally' we have a summary screen to check-out - change the report name to something meaningful also :-

At this point click 'Finish', after crossing your fingers and toes !

I see this image below :-

=============================================

Notice, although I was taking a bit of a gamble asking for the 'drill-down' feature, it appears to have cause a suitable

control to have been placed in the header section.

I immediately clicked the small ' + ' icon to see what would happen, and this is what I saw :-

==============================================================================================================================================================

The twelve cities in England have been expanded from the earlier 'collapsed' version of the display.

Also notice that the totals for the first product item 'AWC logo Cap' also get expanded - there are 17 for all of

England, and 6, 1 and 10 as sub totals - making 17 in all.

Even with our simple understanding of the facts and figures, it would appear as if the 'Woolston' outlet is

quite a major one, simply by totals comparison.

Moving on to the fourth page, where the Unites States begins, we see this :-

===================================================================================================

Okay, the formatting leaves a lot to be desired - BUT - we have all this from the Wizard, and have had no need to

do anything else so far to gain all this functionality. We will come on to improving the formatting in a short while.

For now let us see what happens when we drill-down on the California column :-

===========================================================================================================================================================================================

Notice (as best you can) that Colorado and Nevada are still collapsed, and only California has been expanded.

Once again, our collapsed total (was 28) has been expanded into 3,4,11 and 10, which I think you will agree is correct.

Before we move on to looking in some detail at the layout of the report, let us remember that when we designed the

Matrix using the Wizard dialog, we chose the following fields, in the following fashion :-

======================================================================================================

In the image above, it would appear that by selecting 'City' after 'SateProvince' we nested the groupings for

columns such that the 'City' grouping was nested inside that of 'StateProvince'.

Please also be aware that the order of these two fields can be changed after highlighting, by the up / down arrow keys.

It is the order of the fields (in a top to bottom fashion) which determines the nesting, so make sure you choose

carefully or you may get some strange looking results !

Examining the Report Layout :

Return to the Layout tab and the layout grid by the appropriate mouse clicking.

Bring up the DO pane ( 'Ctrl+Alt+T' ) and then click the 'List1' item you see displayed in the DO list of

items. See the image below for what I have here :-

==============================================================================================

Yes, in the image above we see that the Wizard has used a List data region, in which to place the

Matrix data region along with a 'Textbox' control for some header text.

I suggest you spend a few moments clicking around in the items shown, so that you can identify just

what is what, and what may need changing and improving.

Seeing the Properties of the Matrix control :

Use the DO pane to easily allow you to bring into focus the Matrix data region control itself. See

below for what I have done :-

============================================================================================

Make the necessary mouse moves to bring up the left and top grey regions of the Matrix control, and

then right click in the top left little grey square to bring up the context menu for the Matrix :-

===========================================================================================

=================================================

Once you bring up the Properties dialog screen, with multi tabs, select the 'Groups' tab as shown below :-

========================================================================================================

If you wish to see how critical it is to get your group nesting correct, then use the appropriate actions

to swap the orders of the column fields as shown in the above image - if you have City placed first you get this

when you try to Preview :-

=============================================================================================================

This in itself does not say we can't have this revered grouping, but we obviously have some setting (probably

'drill down' ) which is conflicting. Also, saying it would work does NOT mean we will get any sensible, meaningful,

or useful results however.

So I suggest you don't mess too much with what the Wizard has done for us.

General Summary - Matrix :

So what have we learned so far ?

Well, the Wizard is our friend when it comes to making a report based on the Matrix data region. So use it and

experiment to see what it can offer us.

However, it may be that by the time we come to looking at the Matrix report item (control) that we are so much

more experienced with RS generally, particularly the Designer.

Whatever the reasons, we seem to have our foot firmly in the door as far as making a matrix report is concerned.

Although I have learned a lot from Mark Long (thanks) and his training CDs on T-SQL and RS, the one particular

section on the Matrix, was poor in my eyes, and did not a lot for my knowledge or understanding of the Matrix

and what types of report may be best handled by it.

I think together, here in these notes, we have come a lot further down the path to understanding and comfort

of use too. I hope you learned as mush as I have in exploring the possibilities. My guess is he had to do a section

on the Matrix and had not had enough time to experiment, that's what it felt like to me watching and listening.

I thoroughly recommend hie CD on T-SQL however, you would do well to sit through the whole thing ;0))

Let us now move on to the next subsection where we will do some format tidying, as well as set some homework

tasks for you guys to have fun with ;-)

See you in the next subsection.

PHH - July 4th. 2010.


Mark Long's Query

SELECT SalesLT.Customer.FirstName, SalesLT.Customer.LastName, SalesLT.Address.City, SalesLT.Address.StateProvince, SalesLT.Address.CountryRegion,

SalesLT.Product.Name, SalesLT.SalesOrderDetail.OrderQty, SalesLT.SalesOrderHeader.OrderDate

FROM SalesLT.Customer INNER JOIN

SalesLT.Address INNER JOIN

SalesLT.SalesOrderDetail INNER JOIN

SalesLT.Product ON SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID INNER JOIN

SalesLT.SalesOrderHeader ON SalesLT.SalesOrderDetail.SalesOrderID = SalesLT.SalesOrderHeader.SalesOrderID ON

SalesLT.Address.AddressID = SalesLT.SalesOrderHeader.ShipToAddressID AND

SalesLT.Address.AddressID = SalesLT.SalesOrderHeader.BillToAddressID ON SalesLT.Customer.CustomerID = SalesLT.SalesOrderHeader.CustomerID

=================================================================================================

Copied from his Training DVD - by VNC [ Introduction to Microsoft reporting Services ]

PHH - July 3rd. 2010.


Improving the Formatting

Look at the image below and see that the Matrix control (report item) has been change somewhat.

Study all parts of this to see what is new :-

===========================================================================================

First let us concentrate on the basic parts of the report, like headings and font styles, sizes and weights.

==============================================================

Most of this slight embellishment you have seen before, and ought to be able to do for yourself by now.

============================================================

The inner group column header has been reduced to the leftmost four characters so that we can have a

reduced column width.

However, if you wish to see how to write the Expressions to have the data drive the formatting, then I suggest

you look at Appendix A - "using and writing Expressions". it is all there to follow.

There is still a few things which may be improved, so I leave these to your artistic tendencies.

Good Luck !

See you in the next chapter.

PHH - July 4th. 2010.


Chapter 10 - Charts, More General 'stuff'

Report Items - Chart Controls :

A chart presents data graphically. Examples of charts include bar, pie, and line charts, but many more

styles are supported.

PHH - June 25th. 2010.


Chapter 11 - Nested Data Regions

Nested Data Regions :

You can nest data regions within other data regions. For example, if you want to create a sales record for

each sales person in a database, you can create a list with text boxes and an image to display information

about the employee, and then add table and chart data regions to show the employee's sales record.

PHH - June 25th. 2010.


Chapter 12 - RS 2008, Gauges

PHH - June 25th. 2010.


Chapter 13 - User Friendly Designers (RB2)

The chapter is going to be all about exploration.

Together we will explore what can, and can't be done with Report Builder 2.0 (RB2).

We will see if we can make reports for SQL 2005, and well as SQL 2008 - I know now it is compatible with the later.

The free download from MS comes with a CHM file with Help information - see image below :-

In fact there are some tutorials to get us on our way - all included in the Help folders.

Here is the Tutorial section expanded :-

I have just checked with Windows Explorer (WE) and it does appear as though the CHM comes with the

download - see below :-

=====================================================================================================================================================

PHH - July 6th. 2010.


How to Download and Install

Use the live link below to go to the download page on the Microsoft site where the application may be found.

I have just used this live link myself and the files come down in a few seconds.

Report Builder 2.0 - MS Download .....

http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

========================================================================================================================================================================

=========================================================================================

==========================================================================================

===========================================================================

PHH - July 6th 2010.


Report Builder 2.0 - First Impressions

After a quick download and install - no trouble at all - the application is up and running.

I have to say it looks very 'modern' and is in style with the newer MS Office products, and others.

It looks VERY promising to my eyes, and what strikes me immediately is that all the 'shell' menus and

options are specific to Reporting Services (RS) and other unnecessary 'clutter' is missing.

This seems to be good for any keen client, but also for the developer and report creator too ;-)

I think we should give this tool a real try, and not dismiss it too easily.

Have a look at some of the features available, in the image shown below :-

====================================================================================================================================

And even more, that make me think that all the functionality is available once we know our way

around the IDE.

This image below shows part of a new report I made in very short time, it certainly has the feel to me, of the

earlier MS 'big daddy' tools. Only it feels nicer ;O)

See how clean it looks :-

========================================================================================================================================

Although the environment is quite new to me I have managed to add a fourth column quite quickly.

Notice in the image below the 'CompanyName' field has been dragged into the table body :-

==============================================================================================================================

And to run the report we see a new style icon below :-

[ still based on F5 however .... ]

===============================================

I suppose you are wishing to see a sample output, well here it is - after a play with the zoom slider :-

If we choose to uncheck (deselect?) the 'Print layout' icon in the main menu bar, and then do some

resizing, we see this :-

Notice the report print time which is automatically placed at bottom right of the page. This is a standard

default, to make our lives just that bit easier.

Finally, the Bonus ! :

This First Impressions 'test' report was done with a SQL Server 2005 instance, and a 2005 'flavour' database.

So we appear to be able to use the same tool for designing reports for the 2005 and 2008 platform, definitely

a step in the right direction.

Look at the image below to see that the Data Source used is an MSSQL Server 2005 variety :-

==================================================================================================================

I now suggest that we leave this introductory section about first impressions, and move on to looking at how

to design and create reports for SQL Server 2008, and in the necessary detail so that everyone can follow along

and revisit later ( as I will do myself ) to refresh our memory and ideas.

See you in the next subsection where we make a less than simple report, maybe having a few groups too.

PHH - July 6th. 2010.


Report Example 1 - SQL 2008

Okay then, before we get going, make sure that you start a new report, and that the previous one is

saved with a suitable name.

Here is how the new style IDE looks for this set of tasks :-

======================================================================

Note that as we go through this full report design process we will name most objects we meet with suitable

names for easier identification later. So far we have just accepted default values.

Acquiring the right data :

The first thing to do before we get to creating the report is to make a 'View' object within our database

so that we get all the fields from our joined tables, to allow us to easily make our report layout.

In other parts of these eNotes I have made a 'View' called ' vPHH_MarkLongMatrix ' and I will use a modified

form of this here, in this example.

[ For your information I have added a coupe of extra fields, that of 'ProductID' and 'ProductCategoryID'. ]

Below are some screen shots to help you understand what I have done :-

================================================================================================================================================================================================

And another with all fields showing :-

[ * note * I do not know why the red under-scoring is in place as all the tests shown appear to work fine ! ]

=======================================================================================================================================================

This data structure could be very helpful to us for a Matrix report where the rows are grouped in products, by

product category, and the columns as in other parts of these notes, by Country, Sate and City.

Starting the Report :

After starting a 'New' report from the main application menu (as seen above, at the start of this section) you

should see your RB2 environment looking like this below :-

=====================================================================================================================================

Notice we have an 'Untitled' report and the layout area (doesn't look like the old 'grid' ) has only

a couple of defaults for us to use.

I many ways this is a combination of a default layout and the old Wizard. Let us see how this new

environment works - and in my eyes, works nicely !

In the 'Report Data' pane towards the top left of screen, we have only got 'global' or as seen in the RD pane

Built-in-Fields'. See the image below :-

=======================================

So we now need to specify a Data Source, by bringing down the drop menu as shown below :-

============================================

At the 'Data Source Properties' dialog screen enter values and text similar to those shown below,

and remember to use the drop lists and screen prompts wherever possible, keep free hand

entry to a minimum :-

==============================================================================================================================================================================

In the image above, it shows that I have used the 'Build' button to bring up the 'Connection properties' dialog.

The following is a result of suitable inputs and testing :-

===================================================================================================================

And the final result of all that is this, in the 'Report Data' pane :-

=======================================

So we can go right ahead and add our required Dataset by right clicking the new item in our pane,

and selecting 'Add Dataset ... ' - see what this looks like :-

=============================================================

When the 'Dataset Properties' dialog appears, make the entries and selections shown below, and

get the Query script from your open SSMS where you tested the new 'View' object in the DB :-

[ ... it is a good practice to keep SSMS open and minimised while you work on report creation ... ]

You may like to see the 'Query Designer' dialog as it allows free entry of a query to produce your returned

Dataset. You could have put the whole 'View' script here if you so wished. However, it is easier to do it this way

I suppose :-

==============================================================================================================

If you doubt my words about the whole HUGE query script version, here is an active sample :-

The consequence of which is that we get a rather full scrollable area in our Dataset Properties

dialog as shown below :-

=============================================================================================================

Which ever way you do it the result is the same, and the following new data fields are available to

us through the Report Data pane - see below :-

======================================

In the image above notice we now have available to us the 'ProductID' and 'ProductCategoryID' fields.

Using the Data in our Design :

In the report layout area mouse click the 'Table or Matrix' icon as shown below :-

This brings up the 'new Table or Matrix' dialog as shown below :-

Note that my screen above had the 'MLmatrixMain' dataset entry as a default, and that is how I will leave

it as we move on - click the 'Next' button.

Now we are confronted with a whole set of options. However, things are not as bad as they may first seem.

Here is the dialog we need to fill out carefully :-

And if all else fails, please realise that the 'Help' button brings up the CHM help notes already open in the

appropriate place - as shown below :-

Dragging the Field Names :

Below is the look of the input boxes after I have dragged the 'OrderQty' field name from the 'Available'

list into that headed as ' Sigma Values' :-

============================================================================

Notice that the default 'drop' entry is 'Sum' and for my report I think I wish my cells in the body

of the report to be simply the order quantity itself. So right click this 'orange' coloured item to

bring up a context menu as shown below :-

Un-checking the 'Sum' achieves what I am after, as the next images shows :-

=========================================

Okay then, we are getting a feel for this new GUI style of report designer, it is nice to use. let's

just hope that we can get the report we are looking for ;-)

Study the image below, and see which field names I have dragged into the row and column input

boxes. Also note that the order of these appears to matter, and relates to the group 'nesting'.

===================================================================================================

So the order "Country, State, City" is important - lets give it a go and find out !

The following screen is what I get when I tried to click the 'Next' button :-

So it looks as though I was wrong, and I should have left the aggregate function as 'Sum' !

So I have replaced the 'Sigma Values' entry and continued. This is what comes up next :-

============================================================================================================================

I think I left this default 'as is' after my last attempt to over-ride the design tools ;O)

[ Maybe later I will see if I can alter these options and try out the others on offer, we shall see. ]

Choosing 'Next' we move on to the colour scheme choice which is familiar to us by now :-

============================================================================================================================

Rightly or wrongly I have chosen the Forest, well I 'wood' wouldn't I ;0))

After selecting 'Finish' I get my screen report layout to look like this :-

Now when we run this report in 'Normal' mode, as opposed to 'Print Layout' mode, we see the following :-

=====================================================================================================

And if we see a page in 'Print Layout' mode we get this :-

===================================================================================

Notice that in the above two images the categories 8 and 20 have been expanded from the default

setting of collapsed.

Turning our attention to the column groupings, if I now expand the USA and then California we see this below :-

====================================================================================================================================================================================================================

Yes, I realise, this is difficult stuff to try and display / show in notes, and at the same time give a reasonable idea

about the look and feel of a expandable / collapsible matrix report.

In the image shown above, notice that product categories 9 and 10 are expanded, as well as United Sates and California

column groups.

Saving our report :

At the moment in my report 'shell' I have an 'Untitled' report - this shows in the very top caption.

It is time to go and save what we have done - see the image below :-

==========================================================================================================================================

Follow the options to 'Save As' and when you have been successful you should see something like

this shown below :-

======================================================================================

Final 'bits and bobs' :

Don't forget that the export to Excel feature is there, and produces some excellent results.

Below are a couple of screen shots from me doing an export and opening the new file in Excel itself.

===========================================================================

============================================================================================================

And now the report in Excel itself - VERY nice !

=====================================================================================================================================================================================

Please note that it would appear that the exported data preserves the expanded state of the report at the time

it is exported from the view at the time of action.

You need to appreciate that the expand capabilities are only active in the 'Normal' mode within the RB2 design

area, and that the 'Print Layout' and the 'Export' facilities reflect the state of expandability at the time of doing them.

Time to finish and move on.

I will see you in the subsection follow this (not the query script), where we will improve some of the report detail

as well as the formatting.

See you in that section.

PHH - July 6th. 2010.


Script for 'View'

USE [AdventureWorksLT2008]

GO

/****** Object:  View [dbo].[vPHH_MarkLongMatrix]    Script Date: 07/07/2010 07:21:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--ALTER VIEW [dbo].[vPHH_MarkLongMatrix]

create VIEW [dbo].[vPHH_MarkLongMatrix]

AS

SELECT        SalesLT.Product.ProductID, SalesLT.Product.[ProductCategoryID],

SalesLT.Customer.FirstName, SalesLT.Customer.LastName, SalesLT.Address.City, SalesLT.Address.StateProvince,

SalesLT.Address.CountryRegion, SalesLT.Product.Name,

       SalesLT.SalesOrderDetail.OrderQty, SalesLT.SalesOrderHeader.OrderDate

FROM    SalesLT.Customer INNER JOIN

         SalesLT.Address INNER JOIN

         SalesLT.SalesOrderDetail INNER JOIN

         SalesLT.Product ON SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID INNER JOIN

         SalesLT.SalesOrderHeader ON SalesLT.SalesOrderDetail.SalesOrderID = SalesLT.SalesOrderHeader.SalesOrderID ON

         SalesLT.Address.AddressID = SalesLT.SalesOrderHeader.ShipToAddressID AND SalesLT.Address.AddressID = SalesLT.SalesOrderHeader.BillToAddressID ON

         SalesLT.Customer.CustomerID = SalesLT.SalesOrderHeader.CustomerID

GO

PHH - July 7th. 2010.


Improving the Formatting

=====================================================================================================

First obvious improvement is to click in the heading text box and add something suitable.

Here is my attempt :-

=================================================================================================================

And if we wish to change the colour of the background we see that Properties Pane (PP) has this to offer :-

=============================================================

The drop down option system is slightly different to VS 2008 but is sufficiently similar to be

of immediate use - see below for two images of this :-

============================================================

And now the 'more colors ...' dialog :-

==================================================================================================

Set the background to Gainsboro, and the color to Firebrick.

Having also set the border for the heading it looks like this in 'Normal' mode :-

===========================================================================================

The PP for the border looks like this :-

===========================================================

PHH - July 7th. 2010.


Compatibility with 'VS shell'

=============================================================================================================================================================================================

PHH - July 7th. 2010.


Report Example 2 - SQL 2005

PHH - July 6th. 2010.


Report Builder 3.0

Would you believe it ? - you not sooner get to grips with Report Builder 2.0 and then Microsoft

bring out a newer version.

It would appear that version 3.0 is to go with SQL Server 2008 R2 and is not just an isolated upgrade

in itself.

Below I have overlaid the newer version (3.0) on the older menu bar (Insert tab) so you can see a few

of the differences.

Looking at the 'Data Visualizations' section it would suggest that i ought to add a new full chapter as

'Chapter 24' and do the new tool justice, along with Gauges and much more stuff.

Notice that MS have also created a whole new CHM to accompany the tool. I suggest having a good

read of that too, if you are intending to use SQL Server 2008 R2 for your reports.

===================================================================================================================================================================================

And in case you are wondering just what the Map icon is all about I suggest you study the image

shown below :-

Enough of all this new stuff, I think we ought to leave it to later, and do things properly in Chapter 24.

See you there, or in the next section, when we look at the Report Manager tool.

PHH - Sept. 20th. 2010.


Chapter 14 - Report Manager

Report Manager (RM) is a very important part of Reporting Services (RS).

In many ways it is the RM which puts the 'service' into the phrase or term 'Reporting Services'.

Without the 'manager' tool the reports could not be deployed, and folders of reports setup for users.

Earlier in these notes we said that RS had a 'delivery system' and RM allows the DBA to administer the

delivery service. Once things have been correctly setup by the IT Manager using the RM tool, then users

can view their reports using the web service provided as the other part of the RS 'delivery system'.

All the access rights for users, as far as seeing reports is concerned, are created, edited, and generally

handled with the RM tool. It is browser based as you will notice in the image shown below :-

The image above if from a desktop PC on which I have installed both the SQL 2008 R2 server as well as the

Report Server to go along with it. These all came as part of the same 'Developers Edition' download.

As you will see in the next couple of images there are some management menus and options available.

Study the details :-

And after clicking the 'MatrixMLtest' folder we go down a level as shown next :-

As well as security and deployment options, the manager can also use the RM tool to both view and

edit the report using the Report Builder menu icon to bring up the Design tool.

When I tried to launch the RB 3.0 tool the first time I got this - see following image :-

After a few seconds to a minute I then got this after selecting the 'matrix' report, and then using

the slider to do the WPF 'thing' and shrink the layout in the Designer :-

As you may have guessed, there are lots of things which can be achieved from the manager's tool.

Below is the dialog screen which allows a new Data Source to be created.

I have not done this yet myself, as i have had very limited exposure to the RM tool, and environment.

However, I know the DBA at Cardiff does this on his system to make my RDLs work over his internal network.

Below is the screen after I clicked for a Test Connection without first providing all the necessary details :((

Study the image as this is the sort of thing you are likely to do yourself :-

How do we get to the RM ? :

Well there is an easy way to do this, before you add it to your favourites in the Browser list.

The image below shows what we get when we launch the 'RS Configuration Manager' which comes

to us along with the other support apps when we download and install SQL Server.

If all goes well with the install and setup (not an easy process!!) then clicking the blue link will

bring up the browser, and after some thinking time the sorts of stuff you have already seen above.

Here is what the Configuration manager looks like :-

Finally for this section, we ought to realise that a report can be actually run from here too !

Often you will get the following screen image as things start to whir away in the Browser and at the remote

server assembling its return dataset - wherever it may be :-

Below is an image of my VODC1 test report running in the RM and me just about to select to

export the data returned to an Excel spreadsheet :-

Once you have a working setup for RM on your system, and it connects OK with your Report Server,

you will find it quite easy to do the 'housekeeping' and make security level changes for your users,

as well as grouping the reports to give the users correct access to only those reports they need to view.

Typically, reports may be grouped by department, so all relevant report for 'Order Processing' would be

placed in one folder, maybe called 'OP_department', and then another folder for 'OP managers' where

monthly, quarterly, and yearly summary reports may be held. Quite honestly, this is the easy part of

RS because by the time you are doing this, all the servers and software have been setup correctly.

Here is the context menu for a folder in my very simple test system :-

The reason my system is simple is because all my databases, and development reports are on my other office

PC, on which I failed to be able to setup a Report Server. Since that is the system which earns me my wages

I have not been able to mess around too much - AND - need to have version 2005/2008 & 2008R2 on there too.

The computer I do have running RM successfully is a newer and 'cleaner' build. If possible I suggest you start

your SQL and Reporting Services on a 'clean' PC where nobody has attempted to install SQL Server previously.

See you in the next subsection where we will look at the web service that the users need to access in order

to see their reports - those to which RM has given them access.

See you in the next subsection.

PHH - June 27th. 2010.


Reporting web services URL

Once a report has been deployed - that is the term for making it available for users, or placing

it in the Report Server - it may be viewed by a user as long as they have been granted permission.

The user views the report via a standard browser, or a Web browser control on a GUI screen.

One benefit of displaying reports via a 'web service' is that once the correct URL is provided to the

browser it does not matter which version of SQL Server is being used, or what the namespace

version is for the RDL being processed in the remote Report Server.

As long as the web service being pointed to by the URL works as it is supposed to then a report

will be shown in the browser.

In the image shown below I am using 'Configuration Manager' (CM) - from the RS software tools installed

at the time when my SQL Server was installed - to alter a few settings to make my reports show

correctly.

Note that there are two sections in the left hand pane of particular interest to us at the moment, that

of the 'Web Service URL' and also the 'Report Manager URL'.

Study the following image closely :-

========================================================================================================================================

Although I have had a very limited exposure to experimenting with the Configuration Manager

software tool, it would appear that we could have more than one URL setup, and displayed in

relevant panel to the right hand side of the dialog screen.

If the blue / purple URL link is clicked then it will launch the browser and possibly ask for username

and password to allow you access to the web service selected.

In fact the RM is really a web service, but just for the DBA or IT Manager to use. So I suppose to

aid clarity we could call them the Manager's web service and the User's web service - each with

their own URL.

If you hover your mouse over the link then the full text line is displayed in the tool tip - see below :-

=======================================================================================================================================

As you will see in the first image above, that of the 'Web Services URL', the required text line

often will contain 'ReportServer', however this can be configured differently in the CM tool and

the default name changed to whatever you wish.

For now, we will focus on this text string of 'ReportServer' as useful marker to follow - see below

the result of providing the web services URL to my browser :-

The image above shows the results when I chose to point my PC browser at the web services URL

which is displayed in the main body of the image.

After seeing this I then clicked the 'VODC1' link to produce the following :-

Notice in the image that this report has parameters and needs and initial date to be supplied

before a dataset is requested, based on this date. Sometimes a default value is provided for

many reports - a practice I would encourage.

My feeling is that a developer should design each report to run for the user with the minimum of

interaction by them - 'click and display' is what I would call it. Only, then does the user need to

provide other input values for the parameters provided, and then click the 'View report' button.

Here (below) is the output for this report but with a chosen 'test' date (yes, there is a typo in my label)

of 13th. August :-

PHH - September 20th. 2010.


Reconfiguring - a Data Source

Please don't get alarmed by the sight of the image below - you may see this quite often ;-)

Read the detail of the message shown in the body of the tab in the browser - then you will at

least know what is wrong, even if you don't yet know how to fix it :-

The DBA / IT Manager at my client's site often sees this when I give him a new RDL to deploy

on his network.

There is nothing 'wrong' as such, it is just that the connection string required to point at the correct

database is different for his network than it is for my development PC platform - even though the actual

DB name is the same.

*** remember *** I am using a backup copy of their databases to design against, so the table structures

are identical.

I will now go to my PC on which RM runs correctly for me, and try and remedy this situation by supplying

the correct DB for this report, as well as getting the connection string correct.

I think we have a matrix report which needs the 'AdventureWorks' light database ( 'LT' not the FULL version) so I need

to attach this to my SQL Server instance and get to be able to see the data using SSMS.

Once I can do that, then I can try and change the settings and create a new data source using the dialog

screen in the RM tool.

Wish me well, these things can be tricky !

Results of my Fumblings :

Okay you guys, I am back. Just had a useful - and instructive - session at using Report Manager on my other PC.

Well its a bit like learning to drive a car (a life skill), so I will do my best to explain what I did. However, let's

start with the success, here is what I made happen :-

In the image above I am using the web service pointed to by the URL for the "RS web services" after changing

the data source for the RDL.

As you can see the matrix of the pivot table / cross tab report, shows nicely.

Overview of what was done :

The "AdventureWorksLT" database was missing from my PC platform, so I made a backup on my development

PC and copied the ' .BAK ' file to the new platform. There I use SSMS once again, but this time to restore the DB.

It will be helpful if you have done backup and Restore in SQL before this rather crucial time ;-)

Then basically, from the Report Manager showing in my browser, I pointed at the report which failed on me previously,

and opened it in the Report Builder, to makes some editing changes to its data source. Quite simply the report was

looking for the wrong server.

So what we need to do under circumstances like this is create a new and correct (tested) connection to an existing

server with database, and then edit the RDL to point to this new and correct source.

There is also a 'little' catch in the tail of this, as unless you then re-deploy the RDL (or copy into the Report Server

using the option in RM) the report will run from the old RDL, even though the correct changes have been made.

Finding the Connection String :

Like a few things related to SQL the creation of a new data source can leave you with an empty dialog and no clue

as to how to provide the right information.

I stopped worrying a long time back, and now just keep going, trying this and that - here i s what I just did.

First lets look at the dialog we need to complete :-

The above image shows how blank and intimidating the dialog can look.

So I used a little trick, and I looked at what the connecting string of an existing and working report

looked like. This then allowed me to modify (hack if you prefer) the detail of the string to suit my current

computer system.

Here is what I ended up with :-

Remember, the image shown above is a result of making a new data source for the Report Server.

We still have to change the settings in the RDL itself.

So lets look at how to get at the editor from the icon showing in the browser tab :-

============================================================================================================

Remember that this is a browser and not a Forms app written in dotNet !

Instead of our usual practise of a right click for the context menu, hover over the icon area and select the

arrow at the right hand side of the yellow box, as shown above.

Do NOT use the context menu it looks like this below :-

=====================================================================

Now once the Report Builder 3.0 app is up and running (and it may take a little while) find the Home tab,

and then in the 'Report Data' pane right click on the 'Data Sources' item to allow you to select to add a

new one.

In the one image below I will show a few things at once to help you. The Data Sources Properties dialog opens

but at first, for a new addition, it is mainly blank.

Let us study the compound image I have which may prove useful :-

Now we have more help at hand than when we used the browser a little earlier.

Although the DS Properties dialog is blank essentially, we have help at hand when we click the 'Build' push

button next to the connection string.

The Connection properties dialog is familiar to those with some SQl experience and is easy enough to use.

Once you correctly select in the Connections Properties dialog, and do a Test Connection, then the OK button

makes sure the chosen string is entered into the text box as shown in the image above.

Once again, you have the option to do a test Connection, so do it to see all is OK - see below for example :-

Now we need to reload (upload a second time) the RDL into the Report Server. remember it needs to be

refreshed so that this new version is run when the report is requested by a user.

In the RM browser Home tab choose the 'Upload File' option in the menu bar, as shown below :-

========================================================================================================================================

Selecting this option brings up the following screen :-

You then need to navigate to the folder where you have the recently changed ' .rdl ' file, and select it.

If like me in my recent situation you need to over-write an existing one which is incorrect, then check

the box provided for overwrite. Click the OK and things will happen.

Summary :

If you do all this and correctly for your system, then you will be able to go and run the report with its new

data source.

Try running directly from the RM environment first - if it runs from there it should run for users from the

Report Viewer URL.

Here are a couple of images of it running from the RM web service :-

=========================================================================================================================================================

And with some extra rows and columns expanded, as well as the size reduced (see the drop list),

we now get the following :-

==============================================================================================

And with these nice 'green' images I think we ought to move on to the next subsection.

See you there.

PHH - September 21st. 2010.

Addendum :

Now having gone to all that trouble to make the report work correctly, here is an image which is

quite unusable - simply 'cos it is too small - but it does give a better idea of the expandability of it :-

It is surprising how small scale this can become while the + / - controls to expand and

collapse the groups still function !

PHH.


Chapter 15 - More with Images

PHH - June 30th 2010.


Chapter 16 - Forms 2.0 - Report Viewer

Let us look at what we may be try to achieve in this chapter and subsections.

Here is a first colourful taste of success :-

Above we have a live data set being retrieved via the SQL Server, from our target database.

For you information the report was actually the one we designed in the RS design shell at the beginning of these

notes. However, I did have to adjust settings for the data source in order to show this.

What I intend to do now is to learn how to make new data sources, datasets, and reports them selves in the VS

shell while developing a Forms 2.0 application - as well as a little later on finding out how to use other RDL files so

that we may be able to 'cross use' RDLs designed for the Report Manager and those designed for the Report

Viewer control shown in action above.

Getting a start in VS 2008 :

For the moment we will look at designing a report with the Report Viewer GUI control placed on a Form in a C#

project of a new solution. Later we will look at doing a Vulcan version of the same thing. Since most of the work

required is 'VS shell' based this is no great issue.

Go into VS 2008 and close all solutions.

Start a new project / Solution which is Forms based and using C# - name it 'ReportViewerExamples'.

You should end up with something like this below :-

Display the Properties pane (PP) using the main menu or the 'F4' key.

You need to change the text and name property as follows :-

==============================================================

*** note *** the folder is still called 'Form1.cs' in the SE pane. leave it as such for now.

Bring up the Toolbox in VS and from the Reporting folder drag a control onto the design grid as shown

in the images below :-

===========================================================================================

After dropping the control we see the following on screen :-

====================================================================================================================

In case you have lost the 'fly-out' input box, here is an image of what the control normally looks like

after previous mouse clicks and re-focusing etc. :-

=======================================================================================

It is the little (tiny?) right facing arrow icon which provides us with the facility to redisplay the input box.

So to experiment, click the mouse a few times to both close and open this option box shown below :-

===============================================================

For the moment there is no need to adjust the size of the control on the Form, we have far more important

things to do.

Clicking with the mouse to drop the list we see this :-

========================================================

Selecting the 'Server Report' option is not what we need right now but we need to see what it does when we

select it. So here is an image of what we get after menu choice :-

=======================================================

However, we are going to try and design our own report, and a new report too.

So click the appropriate blue label to bring this up :-

===================================================

This then brings up the 'Report Wizard' which we may now have enough experience and knowledge to handle.

Here is the Wizard :-

Let us see what we get next :-

===================================================================================================================

Since we are doing everything from a clean fresh start, we need to choose the 'Database' option.

This will allow us to select the database on which our required dataset will be based, or built.

I suppose we can think of the database, and its objects that we select, as the data source.

Lets click next to see what comes up :-

===================================================================================================================

Depending on what you have done previously, the contents of the long drop list will vary, and most likely

you will need to create a new connection using the button on the right.

Let us see what we get when we choose to create a new connection :-

==============================================================================

From the drop list choose a server name that you know will connect you to the AdventureWorksLT

database on your system. If you have not got this available then check out the earlier parts of these

notes to see how to get it.

My system needs me to choose the middle of the three options.

And now to our choice of database - using the lower half of the dialog screen :-

===============================================================================

After appropriate selection test the connection as shown :-

===============================================================================

This should then return you to the Data Source Configuration Wizard and expanding the 'Connection String'

you will see something like this :-

===================================================================================================================

Clicking to continue our progress, and then to save the chosen string, we see this :-

===================================================================================================================

This brings up the following dialog, which itself will take some work and sensible choices - see below :-

In the image shown above, no objects have yet been selected from the database for inclusion in the

dataset to be created / designed.

With our 'AWLT' database in focus let us expand our options to see what we have available to us :-

================================================================

Obviously we can select any (or all) of these, but let us also explore deeper, by looking at an expanded

single table object :-

==============================================================

For now let us do something simple, or at least straight forward, and select only a few fields from the

'Customer' table. We have used these in earlier chapters of our notes :-

=====================================================

So according to me we should get seven columns carried through into another dialog, or choice pane.

Let us see what happens when we chose next :-

===============================================

Yep! it would appear that we have a bunch of fields from one table.

Let us keep going in this marathon Wizard exercise ;-)

Clicking next brings up this :-

========================================================================================================

And I suggest we leave the default (Tabular) as it is and press 'Finish' before we get any more dialogs to

have to wade through ;O))

Oh! no, even clicking 'Finish' brings up another one - see below :-

=========================================================================================================

I suggest we give the report a name, I have used 'RVone' and this has 'finally' brought us some results.

See below for what I now have on my screen at this end :-

Look at the results of all of our hard work above. Wouldn't it be nice if we now chose to build and run our app

and we got some results ?

Well, life isn't that easy ;O)) See below for what we get when we try :-

===========================================================================================================================

I suppose some optimistic souls would say the app did run, be I meant to see a table of data, returned results.

However all may not be as bad as it seems, lets us return to the design screen and see what options we now

have in the Report Viewer input box - see below :-

===================================================================

Not surprisingly, we now have an extra option, that of our first report form our project / solution.

Select the ' .rdlc' file name and see what we then get :-

==============================================================================================================

The Forms designer has been reduced in size to make it easier to see, but the main area to focus

your attention is that towards the bottom.

Notice we now have three extra objects, a 'DataSet', a 'BindingSource', and a 'Table Adapter'.

Now when I choose to build and run (I used 'Ctrl+F5') I see we have a more positive result that before. This is what

we get now - see below :-

=====================================================================================================

Now it seems an appropriate time go and change and sort out the control layout, and the docking or

anchoring settings in PP. It may also be a good idea to change the size of the Form to something

slightly larger.

Bring the Form into focus and change the size settings in the PP to 720 for the width, and 433 for the

height.

Also dock the whole control by selecting the blue central region as seen below :-

=========================================================

The 'Fill' selection setting will be applied.

Okay then guys, how does this improve things, let re-run the app and see :-

Well, much better, but it would appear as if the report itself is now in need of some adjustment.

If we stretch the Form at the right hand side we will see another indication that the Form needs adjusting.

Here is what we see when I drag the RHS edge :-

==============================================================

In the designer for the report (not the Forms designer), bring the table into focus and then adjust each column

width as seen below :-

=========================================================================================================================================================================

Also notice that I have edited the column header for 'CustomerID' so it fits the narrower width.

Now when we run the report we see that things have dramatically improved, as shown below :-

Notice that we can also export our 9 pages to an Excel compatible file, just as we have done in earlier

sections of these notes.

Before we close this section, and move on to the next subsection, let use see that the Report Designer

is just as we got to know in earlier chapters, and that we can add the same sort of elements and global

data too - see below :-

==========================================================================================================================================================================

Be careful with the spacing of the ' & ' characters in the expression for the 'Page' text box. It needs to look

as shown below :-

=====================================================================================================

I found my first attempt did not work (errors!) because I had not included the correct spaces, in the correct

places - so be warned ;-)

If you get it right it looks like this :-

=============================================================================================================================

Okay guys, enough for now, lets move onto the next subsection where we will look at making more complex

Datasets similar to those we have used earlier when designing for the Report Manager.

Just before we leave, let us see the code that the designer has now added to the C# class for us :-

====================================================================================================================================================

The DataAdapter has been filled with the Customer dataset.

See you in the next section - time for a fresh cup of tea ;O))

PHH - July 1st. 2010.


Taking Dataset Design Further

So far we have made a simple report from a single table in the 'AWLT' database.

But most often we are needing to use de-normalised data where a few tables have been 'JOINed'.

Let us take for example the Customer Orders report we did earlier in these notes, using the BIDS2005 shell.

Below is the code we placed in the dataset called 'CustomerOrders' :-

==========================================================================================================

In the example above we are joining two tables 'SalesOrderHeader' and 'Customer'.

And in the same project we have another report called 'SalesByCategoryThenProduct' in which we join three

separate tables and the T-SQL script for that is as follows :-

========================================================================================================

In the script shown above we are joining tables 'Product' with 'ProductCategory', the with 'SalesOrderDetail'.

Making our life easier :

Since the last section of these notes I have been experimenting with the design tools in VS2008 as far as

creating new reports for the Report Viewer is concerned. i have to confess that the whole business is not as

intuitive as it feels it ought to be. I put this down to my lack of experience, and the fact that I am having to my

way through all this in isolation !

So my whole approach is find a simple, clean and tidy way to achieve my aims and objectives ;-)

And, here is one such tip. Create a 'View' object in the database which has script combining table data. This

way as far as the VS designer surface and tools is concerned, we are using a single table. At least this way

we get more complex results while buying ourselves time to get more familiar with the 'ins and outs' of the IDE.

Creating a 'View' in the database :

Okay then guys, how about we make ourselves a couple of view objects in the AWLT database, one from two

tables, and another from three ? All based on the detail shown above.

Since I am much more familiar with SSMS as a shell in which to work, I will use this to create the 'Views'.

Go to SSMS (version 2008 if you have it) and gain access to your SQL Server 2005 data tables, then expand

the 'Views' folder in the Object Explorer (OE) pane.

This is what I have at the moment :-

====================================================================

In the image above you can see three Views, let us make more ;-)

Right click the View item in the OE pane, as shown below :-

================================================

Selecting the 'New View ...' option we see this following stuff on screen :-

=======================================================================================================================

At this point we must resist the temptation to be led by dialog screens, so just Close it without doing anything !

We are going to supply own our SELECT statement very shortly.

Highlight and remove what select statement exists - like so :-

==============================================

Now open the earlier project and copy the select script text for the Customer Orders report (or if you wish just copy

the code from here), you should have :-

==============================================================

If you are typing the script you may like to check the syntax etc. - so right click to bring up this context menu :-

================================================================

My code results in this below :-

=============================================================================================================

Now for a VERY scary bit !!!

As soon as you click OK the designer system working in the background, changes my script to look like

that shown below :-

======================================================================================================================================================

So right click in the new script, and bring up the context menu, so we can execute the SQL script.

My context menu looks like this :-

===============================================================

After choosing to execute I get results returned from the SQL Server and they look like this :-

==============================================================================================

Saving the new View :

To save our new and tested view into the database itself - making a new 'object' - we simply need to

right click the tab at the top of the IDE as shown below :-

==============================================================================================================================================

Notice in the image above I have also chosen to adjust and display the schematic which the designer

makes from the SELECT statement we have supplied.

Choose the appropriate option and provide a suitable name for the new DB object. Below is my entry ;-

===================================================================================

When I click OK I see the OE immediately display the new items as follows :-

=====================================================================================================

However, it is good practice to Refresh the folder being affected by changes in the designer, and quite

often with other SSMS changes, you need to refresh in order to see the new changes. So don't get

caught out ;-)

Naming the View :

You may have your own approach to naming your DB objects, I use ' v ' for view and ' f ' for function etc.

I also have been 'encouraged' by my client for who I create reports, to add my own initials also. So the

'PHH' is to show that I made the object, they have a third party consultant who is 'FDS', and so we can

see at a glance who is the author.

Here is a screen shot of part of their Views section to illustrate the point :-

===================================================

I encourage you to devise some sort of similar naming approach to what you create, right from the start.

Also don't be afraid to include sufficient detail in the name to clearly indicate its function - i.e. what it does -

not that it is a 'function' as an object !

To reinforce this 'naming' approach, here is a screen shot of the Programmability section of the same DB :-

==================================================================

As yet I have not decided on whether I will always show the report for which the object was created. Above 'GEN'

is for general, and 'MUBN' is "Manufacturer Usage by Nurse".

Make the second View :

Now that you have been completely through the process of making a new View, try a second one based on the

three tables we discussed above.

Here is a short record of what I am about to do :-

================================================================

After clicking anywhere outside of the script editor section I now see this (slightly adjusted for size / layout) :-

==============================================================================================================================

And now going through the saving process we see this :-

======================================================================================================================================================

After choosing a name we get this :-

===================================================================================================

Which in turn produces a new DB object, or item, in the OE pane as shown below :-

========================================================

Using the new Views in our reports :

It may be best if you start a new project, this way any existing code snippets (anywhere in the support files)

do not get in the way of our design and success !

I have cleaned up my designer panes, surfaces, and code behind ( I think ! ) and now have this shown below :-

====================================================================================================================================================================================

In the above image you will see that I have even changed the Form caption to ' : Example 2 '.

Making a Data Source from our View :

What we are to do now is use our View (or one of them) to create a new Data Source.

In looking at my Views I see a spelling mistake - so in SSMS I will correct this now - See below :-

====================================================================

And now I have this :-

==================================================

Luckily we seem to be able to rename View objects at an OE level - I hope !

Going back to VS2008, in the Data Sources pane, click the blue 'Add New Data Source' link.

Make the required choices as we did previously, until you get to the Configuration Wizard as shown

below - note I have selected just the one View :-

It is probably a good idea to change the name of the dataset to 'CustomerOrders' as I have done above.

This produces to obvious changes, one in the Data Sources (DS) pane, the other in the Solution Explorer (SE) pane.

Here is what I have :-

========================================================================================================

Making a report from this :

Okay then, go to the toolbox and find the 'MicrosoftReportViewer' control and drag a copy into the Form.

[ * note * this is found under 'Reporting' in the Toolbox. ]

The image shown below has no report available for selection as yet, we need a new one !

==================================================================================================================

After choosing to start a new report make appropriate selections to get to the Report Wizard -

'Select the Data Source' dialog as shown below :-

Select next and then in a following screen the Tabular option (not Matrix just yet).

You will get a RW - 'Design the Table' dialog as follows :-

For our simple table we are to create, it is sufficient for now to drag the required fields into the 'Details' box.

I have chosen six fields to place in the 'Details' - see below :-

==========================================

The field have hidden is 'TotalDue'.

Then move on to select 'Block' as layout, and 'Forest' as style.

Finally give the report a name as shown below :-

As you can see from the summary screen - just before we press Finish - the information looks as though it is

what we require in our simple report.

Lets move on to see if we can get this to show ;O)

When I click Finish I see this :-

===============================================================================================================

As well as this :-

==========================================================

Don't be fooled ! :

However, remember we may have made a new report item in our SE pane, but as yet we have not 'pointed' our Report

Viewer control at this report.

Go back to the Forms designer and select the new report using the little (tiny) arrow icon at the top right of the control.

Here is what mine looks like :-

==================================================================================================================

If you like, check that the data source is also correctly associated, and I have this in my system :-

==========================================================================

Now close the dialogs and place the RV control in focus, then use the PP to set the docking property to 'Fill'.

You may also wish to enlarge the width of the Form itself.

As a further check, look at the bottom of the design area to see you have the extra report related objects

as shown below :-

===========================================================================================================================================

Now cross your fingers, and hold your thumbs, and click 'Crtrl+F5' to build and run - will it work ?

Here is what I get at my end of things :-

====================================================================================================================================

Okay, we have a report - GREAT - but it could do with some tidying work ;O)

Since the general tidying is not the main focus of this section, I will leave things to you as far as most improvements

are concerned.

To get you on your way, bring up the DO pane (Document Outline) and with this and the report designer try to

do as shown below :-

========================================================================================================================

Above I have added an aggregate to the table footer - which I also added - and edited the report title.

Here are some useful details :-

=========================================================================================================================================================

Here is now what you need to format and tidy up :-

Now lets move on to learn some more about creating report from a View, and possibly try some grouping too.

Making a second report :

To make a second report we are going to add one via the SE pane.

Right click the project item in the SE pane to bring up the context menu :-

====================================================================

After selecting to add a new item, change the Categories and Templates input boxes as shown below :-

I have chosen to use the Wizard, possibly surprisingly to you, as I would like to try an easy way to choose

a grouping for a table report done this way. Fingers crossed !

When you get to the 'Select the Data Source' dialog as shown below, you will realise that we need to add a

further 'Data Source' , or as we call it afterwards, a dataset :-

=========================================================================================================

Click the add source button and navigate to this dialog below :-

This produces a new entry in the 'Project data sources' box as shown :-

=========================================================================================================

Now click next and then select Tabular as the report type.

This takes us to the 'Design the Table' dialog similar to that shown below :-

Click next and then choose 'Block' as layout, and Ocean as Table Style.

This should get you to the summary screen with settings similar to those shown beneath :-

Finally, finishing the process, leads us to these new entries in the shell :-

=========================================================

And the report itself :-

==============================================================================================================

Before we do anything further, lets go to the RV control and select our new report :-

===============================================================================================

Once you select the new report the VS shell flickers a time or two as it is doing something behind the scenes.

Then at the bottom of the Forms designer we see the following group of objects :-

=======================================================================================================================================================================================

Yes, three extras are now in evidence, and relate to our new report.

So without ado lets give it a try and 'Ctrl+F5' it to build and run.

This is what I get, and we appear to have a grouping :-

Finding another section on page four we see this :-

I have yet to look in detail at what the Wizard has done for us, but we do seem to have some grouping effect.

And, it was quite painless really.

Grouping or Groups !? :

My lack of experience with RS and the VS shell report designer is starting to show - not to mention my inexperience

with SQL also ;-)

It would appear by looking at our table control in the new report, that we have not got any 'groups' in existence, like

those we created in earlier chapters. Look at the image below :-

============================================================================================================

The left hand end grey icons are missing a pair to indicate a group.

However, the table properties show the following :-

Could our problem in understanding be that the 'Include group' settings for header and footer are unchecked ?

Shall we see what happens when we check these ? lets do this now :-

Yep!, that problem is solved, one less thing we don't know ;0)

Now see what we have done to our table - see below :-

==============================================================================================================================================

And while we are in the layout grid let us copy and paste the contents of the cell in the table footer, to the

group footer - like so :-

======================================================

I just used a standard context menu copy and paste technique.

This now produces output like that below :-

====================================================================================================================================

I will format the currency - even though you may have done this already as your homework ;-) It will just

make it easier to to read and display here.

Okay then, a couple of formatting changes all along the lines of what we did in much earlier chapters, and

this is what we now have :-

===========================================================================================================

To Summarise :

Okay then, let's stand back. the group choice we made in the Wizard dialogs did create a real 'Group'.

However, the default settings for the wizard process seem to turn off the group header and footer.

We still need to merge cells for group heading details, and to aggregate (Sum) the group value fields.

We have also no got two reports which we can switch between at the top RHS of the control in the Designer.

It would be desirable for us to be able to make a drop list from which different report could be displayed. This

aspect needs more research and work.

However, we have created datasets which are based on database 'View' objects and which have multiple joins too.

Lets go into the next short subsection where we will explore more to do with the datasets.

See you there.

PHH - July 1st. 2010.


Looking at the DataSources

Look at the image below before we start any discussion :-

========================================================

Although the pane header shows 'Data Sources' we have been calling these Datasets, in line with the

wording we became accustomed too in the BIDS 2005 shell, where we had a datasets editor panel / tab.

PHH - July 2nd. 2010.


From Table to Matrix

Okay you guys, hold onto your hats ;-)

If you think Table controls were interesting in what they could do, then have a look at the Matrix

control, and what that has to offer.

Below is a real live example of what can be done in a few minutes, it was done in the half time

period between the two halves of the World Cup soccer match between Germany and Argentina.

Here is what I have done, following along the lines of Mark Long in the VTC training DVD I mentioned

in the 'Learning and Support Materials' section in the Introduction.

In the image above, see how we have product (name) in the rows, and State / Province as the columns.

Right form the very beginning of using the Matrix data region, realise that if there were to be another region added to the

database, and data captured for this new region, then the report as it is would cope with this and add an extra column.

Obviously, as this default report (and it formatting) came out of a Wizard 'session' it still needs some tidying, but hey!

it seems to look fine - as a starting point to a fine report ;O))

Now, back to the second half of the soccer - hold your thumbs Dieter !!

====================================================

Wow!! 4-0 Germany, Dieter's team marches on ;O))

====================================================

Before we get into improving the look of this simple Matrix data region, let us see what I had to do to create the report

we have seen displayed above.

The text for the query mark Long used in his simple example (also with 'AWLT' database) is included as text in the

next subsection - you can copy and paste it.

The text included was used in SSMS to make a View as shown below :-

=====================================================================================================================================================================================================================

And testing this new View in SSMS we see this output below :-

====================================================================================================================================================================================================

Now that we see we have made a suitable View in our test (AWLT) database, we can return to the VS 2008 shell

where we can attempt to make our third report to display in our report Viewer control on our Form - in C#.

First, follow the previous instructions for making a new data source based on a View object in the DB.

Make one called 'MarkLongMatrix' or similar, as shown in my screen capture image shown below :-

=====================================================

As you can see in the above image of the Data Sources pane we have a total of eight fields available for use.

If you do as I have done, then you will see and extra ' .xsd ' entry in the SE pane, but not yet a report ( .rdlc ).

here is what my SE pane looks like :-

=========================================================

Okay, fine, what we need to do now is add a new item to our project, by right clicking the project in the SE pane

as shown below :-

======================================================================

Make the relevant changes and inputs as shown below :-

==========================================================================================================================================

Now select the Data Source from the three available as seen below :-

=========================================================================================================

Then press 'Next' to go to the 'Select the Report Type' dialog as shown below :-

=========================================================================================================

Click the radio button alongside the 'Matrix' option, and press 'Next'.

This then brings up the 'Design the Matrix' dialog as seen below :-

=========================================================================================================

In the image shown above, notice this looks different to what we got for the Table control (data region).

This time the Wizard allows us by default to select a dynamic row and a dynamic column - so the number

of both rows and columns will be determined by the data set.

The on 'fixed' choice, as it were, is the Details selection, and here we simply choose 'OrderQty'.

Click next to give us the 'Matrix Style' dialog, and choose 'Bold', a nice bright colour combination ;-)

==========================================================================================================

And before we finish, we get a summary screen - it is good practice to check the contents of this screen, as

there are still options to move backwards using 'Previous' if we have something not quite right.

==========================================================================================================

Choose 'Finish' when you are happy with what you see. And hopefully you get this image on screen :-

======================================================================================================================================================

Notice that we have an new entry in the SE pane as well as a Design tab showing.

*** Remember *** That we also need to 'point' the RV control on our Form at the new report, before it will

show on the viewer when we build and run the applet.

Here is how this part of the process looks :-

============================================================================================================

And just to confirm you have done things right, look at the bottom of the design area to see that

there are now nine objects similar to those shown below :-

===============================================================================================================================================================================

Okay then, press 'Ctrl+F5' to build and run - after crossing your fingers and holding your toes etc. ;0)

You should see the image at the start of this section !

After a few small changes to column width, and the text of the report header I see this on my screen :-

This is starting to look more presentable, and the designer details are as below :-

=====================================================================================================

Bring up the context menu for the report and set the grid spacing to 0.20cm, as we have done previously :-

========================================================================================================

Please notice that in the image below the column headers appear repeated at the head of each page :-

[ again this must be a default setting ]

===============================================================================================================================================

OK you guys, if you think that was cool, wait till you see the next quick trick ;O))

With the app running, select the File / Export option as shown below :-

==============================================================================

Navigate the dialog screens and save the file to a folder you can easily re-find, then when the dialogs close, find

the file and double click it to do a 'shell execute' - be ready to be amazed - this is what I get, shown below :-

Yes, this is what shows in Excel - just like 'Magic', or should I say 'matrix' - sorry, British humour !!

So with another click we could have a PDF and so print the report too - as easy as that !

That is enough for now, I need a break. See you in another section or Chapter.

PHH - July 3rd. 2010.


Report Query - Mark Long

SELECT SalesLT.Customer.FirstName, SalesLT.Customer.LastName, SalesLT.Address.City, SalesLT.Address.StateProvince, SalesLT.Address.CountryRegion,

SalesLT.Product.Name, SalesLT.SalesOrderDetail.OrderQty, SalesLT.SalesOrderHeader.OrderDate

FROM SalesLT.Customer INNER JOIN

SalesLT.Address INNER JOIN

SalesLT.SalesOrderDetail INNER JOIN

SalesLT.Product ON SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID INNER JOIN

SalesLT.SalesOrderHeader ON SalesLT.SalesOrderDetail.SalesOrderID = SalesLT.SalesOrderHeader.SalesOrderID ON

SalesLT.Address.AddressID = SalesLT.SalesOrderHeader.ShipToAddressID AND

SalesLT.Address.AddressID = SalesLT.SalesOrderHeader.BillToAddressID ON SalesLT.Customer.CustomerID = SalesLT.SalesOrderHeader.CustomerID

=================================================================================================

Copied from his Training DVD - by VNC [ Introduction to Microsoft reporting Services ]

PHH - July 3rd. 2010.


Chapter 17 - Reports in VS 2008

So far we have been seeing how reports can created from the BIDS 2005 'shell'.

This was used because my clients have SQL Server 2005, and I had to get going with generating reports

for them, before I came to anything different.

So far it has made sense to get to to a high level of reporting skills with the 2005 VS reporting tools, because

although there are some IDE changes and differences, the basic idea is still the same.

However, like all issues regarding complex application use, it takes a bit of an effort to change over etc.

The Plan for Getting Comfortable :

It would seem to make sense to me if we take the rest of this chapter to find out and highlight the major and

operational changes we may face in coming from RS 2005 creation to the RS 2008 'shell'.

Once we have found our feet within VS 2008, we can then move on to actively creating new reports for both

invoices and delivery notes. And, if that goes to plan, we can move on to business letters too.

Once you see how to create Invoices and other documents in the VS 2008 environment, it should be quite easy

for you to go back to BIDS 2005 and do something similar.

PHH - July 5th. 2010


First Obvious Changes

PHH - July 5th. 2010


Detailed Changes in the Design Surface

PHH - July 5th. 2010


Previewing Reports

PHH - July 5th. 2010


Chapter 18 - Invoices, Delivery Notes

Most people think of reports as something which is based on data and 'tabular' in nature.

However, we can have a document style report, which is basically built around a single record of data.

And, equally, we could have a letter style / memo document which has either no data fields, or very

few within the body of the fixed detail.

Now that we have looked in so much detail at the table and matrix report we need to turn our attention

to these other styles.

I think we ought to start with the invoice as a report. Particularly since I have a live WPF app which

creates and prints invoices. The data has now been transferred over into SQL Server format, so I have

no excuse not to do this report ;-)

At the moment I manually print the report - meaning I hard code it with dotNet code in Vulcan syntax

which then uses some data fields from an SQL table record. The code uses my 'BestDocument' dll

which has all the necessary printing classes and methods.

Using a report based approach, if we do things right, we can design things so it is easy for any of you

out there to adapt both the report invoice, and the invoice app. as well, to suit your own needs, so you

can then use them for your own invoices, or those of your clients.

Enough talking, lets get to making an invoice style report.

Creating an Invoice Report :

Before we focus on any detail of the design of an Invoice report, let us get a few simple ideas straight

first of all.

If we are to access data fields from an SQL database, then we need to have at least one report item on

the layout grid which is a Data Region (DR). Only DR's use a specified dataset.

We will have the data for one invoice altogether in one data record, this is the way I save my invoices.

We need to 'think beyond a single invoice', and design the report so that it could do multiple invoices in

a correct manner, even if we choose to only ever use it in 'single' mode.

This is rather crucial to the design process, as it means we need to handle the DRs more correctly.

What we must NOT do is just bumble along (using quick fixes) and use whatever report layout item we

see fit, in order to get something on screen (and paper) which looks like an acceptable single invoice.

Testing this with two or more invoice records will tell us if we have done things correctly.

Which Report Item as a Data Region ? :

Surprisingly enough, we have already done some work with List items which is really the solution we

require for our Invoice.

In the image below from chapter 8 we have the following :-

=================================================================================================================

Above we have two records from a returned dataset, we just decided to place the information in two areas

side by side, so this suggests we could use the List report item to give us free form layout of both data

fields and static label information.

And what is more, we can place a company logo on our invoice, just as we did a 'picture' in our chapter 8

exploration of the List control, or Data Region - see below to remember :-

===========================================================

In order to force a page throw in the required place, so our invoices appear on separate pages, we may have to

simply get our rectangle and included information, to fill enough space vertically as to get a new page automatically.

This is part of why we need a multiple test, otherwise invoices (or parts thereof) will appear on the same page.

Controlling Layout within the Invoice :

Remember that rectangles can be very useful in keeping together other report elements, and that once we get to

placing a number of similar and grouped items on our invoice, this feature will be very helpful to us.

As a container for other report items, the rectangle will allow us to reposition the 'group' of report items at a more

precise location within our invoice layout area.

I am sure that by repositioning our rectangles in this fashion we can force a page throw to suit our needs !

The Report Designer :

To allow us to further branch out from our earlier beginnings with report design in BIDS 2005, we will try to use the

newer designer within the VS 2008 shell. So in this section you will see examples of points and discussion

first mentioned in the previous chapter.

However, do keep in mind that the overall report design process is similar across designers in VS or RB2. We

just need to become familiar with the improved way of moving and accessing the parts and features.

Hopefully, familiarity will not bread contempt ! - but admiration ;0)

Start a New Report :

With the VS 2008 'shell' open (you may be using BIDS 2008) close all existing report in your Report Server

solution.

Now select to add a new item and select report as shown below :-

==========================================================

Which opens a dialog as shown below :-

In our new designer we see the following :-

==========================================================================================================================================================

Above we see a nice clean start to our proposed new report !

Let us start to add the necessary parts.

Dataset being Used :

First we need to point at a data source, and then select a dataset.

Lets get going with a Data Source - see below :-

===================================================================

Making the selection above we get the now familiar dialogs below - make inputs and selections similar

to those shown :-

[ I will make a demo sample of the Invoices DB available on my ClickStart site .... ]

======================================================================================================================

Using the dialogs as you can see me do, I have noticed that in VS2008 i seem to be able to reduce the size of the

dialog screens, when previously they seemed fixed.

here is the 'Data Source Properties' screen after some reduction is size :-

Clicking OK produces the following entry in the 'Report data' pane :-

========================================

Oh! I see I forgot to name it appropriately, so I went back into the DS properties screen and changed the

name to something more appropriate!

Now right click within the new item name to bring up the context menu as shown below :-

=====================================================================

This produces the following dialog at which I have entered a query as shown :-

==========================================================================================================

Click the button to enter the 'Query Designer' and check that a 'Run' brings back suitable data.

Once again the dialog has been reduced in size a little - it helps with these notes !

=======================================================================================================

Now if you have done everything right you should get a whole bunch of new entries in the left hand pane.

Here is how the Report Data pane looks now :-

======================================

There are a lot more fields on show than this, as you will find out !

Starting the Report Layout :

Onto our otherwise blank layout area drag from the toolbox a 'List' data region report item, as

shown below :-

==============================================================

Now we can resize the List with the handles, and make it fill the layout area.

The DO pane now shows us what elements we have in the 'growing' report. Here is

what mine looks like :-

========================================================================

In the image shown above, notice that the List item is actually a 'Tablix' object. In the new 2008 namespace

for the report classes it would appear that 'Tablix' is replacing Table, Matrix, and List classes.

Report Properties - Setting :

It now looks as though we could do with changing (or setting) some report properties.

Anywhere outside the layout area in the design surface, right click the mouse to bring up the context

menu as shown below :-

==================================================

When the dialog 'Report Properties' appears change the settings appropriately, as shown below :-

==================================================================================================================

Check out the other tabs, and confirm that there is nothing much else we need to be using, or setting, at the moment.

However, we could do with a ruler being displayed in the designer.

Study the image below to see how to use the main menu bar and drop selection, to do just that :-

===========================================================================================

Yep! this seems to work okay as you will see very shortly.

Now wee need to drag a single Textbox item into the rectangle, and set the expression as shown in the series

of images shown below :-

=========================================

See the little icon for a drop list in the top RH corner of the 'wire and handles' image ?

Click the image to bring down the drop list as so :-

========================================================

After selecting the invoice reference number field, right click in the wire frame of the Textbox and

as shown below, bring up the context menu :-

==========================================================

From the Expression dialog selection you will be able to see this :-

====================================================================================================

So we ought to be able to run the report, even though there is VERY little to see except this below :-

=============================================

OK, I hear you shout - "we MUST be able to do better than that ! "

I agree, but before we move on to do something about it, please notice that in this new VS 2008 designer

environment the Report Data pane greys out when we have open the Preview tab, as so :-

============================================================================

Here are a few small images to show you what I have changed in the layout to get a full page and then

a throw which places the next invoice number in the correct (and same) place on the page :-

=========================================================================================================================================

=============================================================================

And when we now run the report we get the second page of two, with the invoice number in the same place

as on the first page.

See below for the sample output, and remember that the numerical value is set to be in DESC order ! :-

[ so 2455 is on page one and 2454 on page two .... ]

=========================================================================================================================

*** Note *** I have re-entered the report properties dialog and set all margins to 0.8 cm, so be default the

report when run will have margins which work as we require the output.

The image below shows that the system lessens these values to fit its requirements :-

=========================================================================================

Summary so far :

We would appear to have a useable Data Region and a report which produces invoices (albeit simple ones) at

a spacing of one per page.

All we need to do now is add all the rest of the other rectangles and textboxes, as well as company logo,

and maybe one or two other bits and bobs.

So in modern parlance, " we have lift-off ! ".

To get us on our way, see how easy it is to add an embedded company logo :-

==============================================================================================

It may need some size adjustment, but it only took seconds ;-)

This is probably more like it - see below :-

============================================================================================================

Before we plough on, and get focused on detail, we ought to get a real printed invoice and mark it

up with pen to show the areas and elements which will go into the inter-related rectangles.

Sounds like a job for pen and scanner ;-)

OK then, another day, and I have used the scanner to produce the following image :-

Above I have marked the possible regions for inclusion into separate rectangles, so we can keep

the sub-elements together more easily.

It may be that we even make another small rectangle of the three totals labels and data fields.

The point about using rectangles is that, if we build them correctly, we ought to be able to reposition

the content items easily by adjusting the position of the rectangle itself, and all the items within the

rectangle will stay as they were placed, relative to one another.

Making a start - first Rectangle :

Before we make our first rectangle we need to bring up the Document Outline (DO) pane with

'View, Other Windows, Document Outline' from the main menu, or 'Ctrl+Alt+T'.

We will also slide and reposition the one Textbox item to below the logo for the moment.

Our layout area now looks like this :-

========================================================================================================================================

In the image above notice that we already have one rectangle, and that fills the full page - keep this as it is !

We can now add a further rectangle to within the first, and call this 'ToInv' as it will hold the details of to whom

the invoice is to be sent.

In the following image I have also added a couple of Textboxes, some with static information (labels) and some

with dynamic information (data fields). See what this now looks like below :-

====================================================================================================================================

Look at the new items in the image above, these have been dragged from the Toolbox.

Also note that I used the DO pane extensively so that I could make sure that all the new 'sub-items'

of the rectangle stayed within the rectangle, and hence in the DO are 'linked' with a line to 'ToInv' as

shown above :-

I have to confess that more than once the textbox needed to be 'Cut and Pasted' to get it to move from the

outer level to within the 'ToInv' rectangle. The process takes a bit of practice and you do need to be both

patient and vigilant at the same time. Keep visually checking the DO to see all is well.

In placing the new textbox items as seen above, I used the 'line-up' features of the designer, which is

fully explained in an earlier chapter (Chapter 17).

You will have noticed that each Textbox when placed etc. shows a little list icon in blue - see below :-

=========================================================

When 'clicked' the icon opens up this drop list of all the available data fields :-

==========================================================================

On the other hand, the textbox containing " <<Expr>> " has an expression which has been enter in the

usual manner and looks like this below :-

========================================================================

All the available fields ought to be on offer in the 'Values' list on the Expression dialog, as seen

in the image below :-

=============================================================================================

A quick preview shows us this below :-

============================================================================================================================

However, in 'Print Layout' mode we get this - see if you can spot the differences below :-

==============================================================================================

Obviously we have some tidying, adjustment, and formatting still to do, but we have a few labels and fields

showing in roughly the right place, and showing the correct data.

Also notice that our loan invoice number is still showing, just under the logo, and that also our data seems

to be insufficient in the company field as the Query Designer shows us below :-

=================================================================================

And now, due to one simple adjustment of the textbox with 'To :-' in it, and the LH edge of the six

textboxes, we get this :-

====================================================================================

And as a bonus the paging seems to be correct, in that we are not getting 'creep', where the

next page starts one or two lines further down the page. It is in fact starting in the same place

for page two as page one - a good sign ;-)

To copy exactly the existing paper invoice I have added the email address to the bottom of the same

'ToInv' rectangle items.

It was a bit of a 'fight' as the designer seemed to want to keep placing the extra textbox in the higher

level rectangle. However, after some cut and paste within the DO pane, and moving the textbox to the

lower portion of the report and then back again into the rectangle, I somehow managed to get it as

shown below :- [ you will have to experiment ]

===============================================================================================================================

And the sample output when we do a run, looks like this :-

==========================================================

In the image above you will notice that I have changed the font size and weight to a couple of the static,

or label textboxes, as well as changing the font family to Times new Roman for the 'Specialist' line.

Starting a second Rectangle :

Okay then, according to my pen annotated paper sample invoice, we have completed our first rectangle and

need to now move on to a second.

Before we get focused on the middle, or body of the invoice, let us try our hand at the lower one with static

information. This will add a lower 'bound' to the invoice itself, and may help us control paging - if necessary.

Look at the image below to see what has been so far :-

==============================================================================================

Notice that the rectangle has been called (or renamed) 'GenInv' which I did in the DO pane itself.

In the image below notice that right clicking the rectangle item we are discussing, gives us a context menu

with an option for renaming.

=============================================================================================

Look at the image below to see what has been added. Four textboxes, which are all within the new

rectangle for 'general' information.

I have changed a few properties of font, style and weight, as you will notice :-

===================================================================================================================================================

And a size reduced preview image now looks like this :-

=====================================================================

Also, as a bonus, the pagination seems to hold up - no creep or other problems.

So far so good - now onto the possibly most complex rectangle, that of the 'body'.

To make things easier for ourselves, as we work on the next rectangle, collapse the entries in the DO pane

for both the previous rectangles, as shown below :-

====================================================================

Now we will try to add two rectangle report items from the toolbox. One for the main body of the invoice

detail section, and a smaller one for the totals section. See below to see what I have done :-

=====================================================================================================================================

It may well be a good idea to rename these using the context menu brought up from right clicking the

items in the DO pane - see below :-

========================================================================

Here is what we have changed to, now you do the same :-

====================================================================

In order to gradually ease ourselves into the task of adding and aligning lots of textbox elements to our

body of the invoice report, let us start with the smaller rectangle, the one for the Totals.

Click on the Textbox item in the toolbox and the in the 'Totals' rectangle hold down the 'Ctrl' key and click

the mouse in different places within the rectangle. Do not release the 'Ctrl' key till after the fifth textbox has

been placed - this means that when the sixth one has been added, the multiple addition function is complete

and will not try to force any further textboxes to be added !

Here is what I have at the moment :-

========================================================================

Now the 'Life Skill' part of the design phase ;-) You need to adjust the positions and size of each

textbox as well as the size of the rectangle. This takes some practise and you may have to delete

a few textbox items and replace them, before you get things right !

Also you may find it easier to position the textboxes if you take the bottom edge of the large rectangle

up a little as seem in the image below :-

=========================================================================

Notice in the above image that I have added the static labels and made them bold, as well

as right aligning them. Remember that multiple selection (by the marquee style of action)

can mean we can change or set properties for many similar items at once.

The above image illustrates the label cells being set to right align.

Also notice that the relevant data fields have been selected for the total value cells.

Now check that all text box items are still within the required rectangle as seen below :-

=====================================================================

You will notice from the higher values of the remaining unnamed text box items that I had to clear

some textboxes (delete them) as I tried to get my alignments correct within the rectangle.

Hopefully the experience of doing this smaller rectangle will help us get things right easier, when we

come to the 'Main Details' larger one.

For now, lets rename the remaining three text boxes accordingly, and then see what the preview

has to show us. See below for my efforts :-

==========================================================

The first thing we notice is that we need to have a border if we are to copy exactly the invoice we

displayed above earlier - the one with the annotations.

If we marquee select with the mouse, the six text box items, and then in the Properties Pane (PP)

set the border inputs as shown below :-

==========================================================================================================================================================

And if you finally marquee select only the three label textboxes, and set their background

colour to 'Gainsboro' we get this when we preview :-

======================================================================================

We obviously need to do something about the numerical representation of our money, or cash values.

Try marquee selecting the three value cells and then adding 'C' as the 'Number / Format' in the PP.

I get this :-

========================================

PHH - July 11th. 2010.


Chapter 19 - Business Letters

Enter topic text here.


Chapter 20 - Reports in VS 2010

PHH - July 8th. 2010


Chapter 21 - RS 2008 Configuration

=================================================================================================================================

======================================================================================================================================================================

====================================================================================================================================================================

================================================================================================================

============================================================================================================================

============================================================================================================================

Deployment Attempts :

===========================================================================================================================

PHH - July 6th. 2010.


Chapter 22 - Using Report Parameters

Simple tip - get into using these ASAP !

*** Warn of not available in RV on a Form :( !!

PHH - July 8th. 2010.


Chapter 23 - Reports in dotNet apps

For quite a while I thought the only way to see reports (from Reporting Services) from within a dotNet

application was by using a Report Viewer control on a Form.

However, not that I have researched into RS more thoroughly I realise that in fact the reports available

to the Windows browser are indeed the 'full' reports. These allow parameters to be used, and the query

based dataset in the RDL file is used at run time to extract data from the SQL Server in order to

populate the report tablix etc.

In fact the RDLC file is somewhat of a cut-down version of a full report, it can't use parameters and it

needs to make a strongly typed dataset for the 'ReportViewer' control.

For all of these reasons it would seem VERY useful indeed to be able to use the RDL files designed in

the BIDS full report design surface, to display in a Web Browser control on a Form or WPF window.

In fact if we get this method to work then we ought to be able to also use the browser in VO style GUI

screens in Vulcan, and even in VO 2.8 apps themselves.

Forms 2.0 Web Browser :

Let me make this very clear from the start, the hard work to get this to work is not in the dotNet and

Visual Studio part of things, but in getting the Report Server setup correctly so that the Web Services

URL will allow the user in focus to see the reports, and use the web service available. Windows security

issues with rights of access, are a major part of all this.

However, if you are working on site with a client's (or customer's) network, the chance is that the Report

Server has already been setup by someone who is familiar with what needs to be done to give a range of

users access to their reports.

In the example below, all I have done is make a very simple Forms app with VS2010, and in the properties

pane supply a 'Url' with the following value :-

I can't show you the PP itself as this is on another PC platform, the URL shown above is from the dotNet

code supplied by myself in the WPF example which follows later.

After apologising in advance for the blue colouration of my screen shot 'capture', here is the Forms app which

first worked as I had hoped :-

As you can see in the image above, the web browser control opens with the simple report in focus,

but for this example I had to select a date before the report would display - so a datetime needed to be

selected as an input parameter. Then the 'Tablix' was filled out.

Notice too that I am starting to use VS10 as my development platform.

WPF window Web Browser :

Now that we have looked at the Forms app and the success of displaying our RDL file via the browser,

let us turn our attention to the WPF 'flavour' of the same thing.

All I did was start a new WPF solution and drag from the toolbox a web browser control onto the design

surface in the WPF designer. I did not do any XAML, but in the PRG file ( here it is Windows1.xaml.prg )

I added some standard dotNet code as shown below :-

=====================================================================================================================================================================

Rather than place the URL in an attribute in XAML (which is one alternative) I decided to keep all dotNet in

my approach and do it as shown above.

It is as simple as that - once you know the correct URL to use, and set the access rights up correctly.

And if it is all done right, then you see something similar to that shown below :-

Obviously, the report is the same, and therefore it needs to be launched and run the same way

as for the Forms app.

When I get around to it I will make a ClickStartXAML volume on my web site and we will see

how we may add an attribute in XAML for the 'Uri' in place o f the dotNet Vulcan syntax code.

Do remember however, that only the declarative parts of our GUI can be written with XAML, that

is to say only those bits that say ... place a 'thingy' here, and make it color 'blue', and place a

drop list here, and fill it with names ... 'Faith', 'Hope' and 'Charity'.

No logic flow, or program flow can be placed in XAML - whereas we need to be able to know how to

fill a combo box with dotNet code, as well may do this as part of the program flow, say when a

user selects a person and another drop list needs to be filled with that person's possessions,

or friends, for example.

Anyway, back to our WPF app - its works fine and we are not faced with any issues of strongly

type datasets as the RV control would throw up at the moment. Future updates of the VN compiler

will give us this feature - BUT - do remember that even in C# at the moment the report display is

limited to RDLC use and this is not a full report facility.

And on top of that the RDLC files use the 2005 namespace and not the 2008 one as do RDL files.

All in all the Web Browser seems an attractive way to display, even if we may need to setup things

using the Report Manager, so that only certain sets of reports are available.

Time will tell.

See you in the next section.

PHH - September 1st. 2010


Appendix A - Expressions - using and writing

Before we look at the details in the Expression script itself, let us look at some useful images

which are shown below :-

=======================================================================

And the expanded view of this :-

================================================================================================================================================================================

You may be able to see that some figures are in red, some in blue and others in black.

Now let us try to see what I have already entered as an Expression into the 'Color' property input box.

Below is seen a portion of the PP with the Matrix cell 'detail' in focus :-

============================================================================================================================================

You may see a tooltip with an Expression string, as well as the top option in the drop list of the Web colours,

as ' <Expression...> ', selecting this brings up the 'Edit Expression' dialog as seen below :-

=====================================================================================================================================

Notice the expression editing box, with a couple of nested IF statements.

This is what sets the colour in a 'data driven' way. Depending on the cell value from the 'OrderQty' field, the colour of the

cell contents changes.

This is a very useful thing to be able to do, and a similar thing may be tried for formatting and font weight and style etc.

Now look at this image below - check-out the weight of the font for the cell data value :-

=========================================================================================================

In the image above you may be able to work out that any value below five is in Bold (font weight).

Before we look at the Expression script let us see what happens to this data when we collapse the same small

region of the report :-

==============================================================

I am not too sure just what is happening here, I think I need more experience and also more knowledge too.

But at least we are on our way ! Maybe it is that the total get displayed in a style, colour, weight etc. of what the

majority (or at least 50%) appears to be. Check that out !!

Here is the Expression for the above :-

=====================================================================================

And now the Expression editor itself :-

==================================================================================================================================

Now that we know how to apply data driven cell content formatting I suggest we do it as often as it makes

sense, to improve the look of our reports. it does not take much effort to greatly improve things for the user, and

our clients and customers.

To find out more on this I suggest you look at BoL (Books on Line) to see more of what is possible.

Here is a screen shot as a flavour :- (remember just click Help and select Search to get this up)

====================================================================================================================================================================

PHH - June 13th. 2010.


Appendix B - What will Cause Grief !

In using Reporting Services (RS) we are working in a development environment (IDE) which brings

together both the world of SQL Server and T-SQL and also the world of .NET and the dot.Net data types

and framework classes.

If we are not careful we can feel a bit like the "two headed beast" as we have to handle problems like those

from DateTime data, and sometimes it seems one way to code things, and other times another :(

A good example of some possible 'grief' is shown below with regard to DateTime functions etc.

'DateTime' related values, functions and general operations :

=====================================================================================================================================================

==========================================

=================================================================================================

Report Builder - Date and Time Functions :

=======================================================================================================================================================

=======================================================================================================================================================

========================================================================================================

===========================================

PHH - June 13th. 2010.


Appendix C - Reports at a Distance

The simple fact that the RDL file is in XML script suggests that it was designed to be moved over the

internet. So reports can be run at any distance we require, even if that is on the same PC, and no

real distance at all.

The RDL file seems to be used by the Report Manager (RM) installed on an internal network, in a similar

way to running the report from a web service using ASP.NET pages to control the report opening etc.

The RM is browser based and a typical report may look like this below :-

[ this is an actual non-confidential report from my client's DBS in Cardiff ]

====================================================================================================================================================

I will see if I can get a subsequent image which will include some of the browser image at the top of screen.

One aspect of my research which has so far been lacking - well non-existent really - is to set up the RM on

my local system so that I can deploy and view reports without need for the VS shell, or my C# and Vulcan

applications.

PHH - June 13th. 2010.


Appendix D - Tips, Tricks & Gotchas etc.

Expressions :

For the Expression for a single colour, like black or white, the contents of the editing box do not

start with an ' = ' sign.

Here is a real live example :-

====================================================================

This, as all the other colors is a constant.

However, when we add an Expression the first character needs to be an ' = ' sign, and also the color

constants are then enclosed in double quotes as shown below :-

=================================================================================================================================

This is also true for other constants, like Bold and Normal, as seen below :-

===============================================================================

**************************************************************************************************************

Report Builder Versions :

Be warned ! Report Builder 2.0 is a very different 'beast' to version 1.0 !!!

I suggest you give version 1.0 a miss completely if you can, and move on to the new and modern 2.0, which allows

you to design and create reports for both SQL Server 2005 & 2008.

Unfortunately, Mark Long in his training CD material seems to dismiss the RB2 application, and yet it does appear

to be very well produced, and have a full range of facilities.

Did ML just not have time to plumb the depths of RB2 before he had to make his videos ?

or, did the clean and simple lines of the dedicated package hide the fact that it does have full functionality, and

allows users access to all the features required for a full range of report types.

My suggestion is - give it a serious go ;O))

**************************************************************************************************************

PHH - June 13th. 2010.


Appendix E - Conversion of 2005 to 2008

A few Important things to Understand First :

When we are discussing the general topic of 'flavours' of reports for 2005 and/or 2008 we need to be

quite clear of a few important points, and then it becomes less confusing.

Database Server 'Flavours' :

At the time of writing this we have 2005 and 2008 versions of the SQL Server, and this is usually specified

within the connection string which determines which 'flavour' of SQL Server is used to open and process

the target database.

SQL Server databases themselves are also either '2005' or '2008', as of when I am writing these notes. So

a '2005' database needs to be used with a SQL Server 2005 instance, and likewise a '2008' DB needs to be

used with a '2008' instance of SQL Server.

It would appear that which of these server instances does not matter, as long as we are consistent, and stick

with what we first select. Since the reporting display seems to be based on the returned dataset from an SQL

query script, this is generally version agnostic. So either will work. More proof on this later.

However, what does seem to matter, and matter a great deal, is the 'Report Designer' itself. If we focus on

the VS 2008 IDE 'shell' we get a very good example of this on which to base the rest of our ideas.

Visual Studio (VS) - two Report Designer 'Flavours' :

Within the VS 2008 IDE 'shell' we have two designers for reports, one for the 'Report Viewer' control which is

used with Forms coding, and your own apps, and another which I tend to think of as 'the Full Designer' which

is used when the BIDS 'shell' is used. This designer is used to make '.RDL' files for use with the 'Report

Manager' and display of the reports in the browser environment. These RDLs are deployed to the Report Server

which provides users with a service across the network - all browser based.

The Forms report designer makes '.RDLC' files which are for use with the 'ReportViewer' control and usually

stored locally, for use by the application alone.

Now the fun bit ;-)

With VS 2008, the date of shipping to the public caused Microsoft to do something understandable, but

which is still causing us developers grief in 2010. They shipped VS2008 with the two report designers

creating report files for different platforms.

The full BIDS designer creates RDLs for the 2008 platform, based upon a newer namespace with newer and

different classes used for the 2008 report items/controls, and other support 'things'.

However, the designer for the Forms RV control - making '.rdlc' files, still used the older namespace for

the 2005 platform. This information is supplied at the top of the script within the report definition files

themselves.

One of my first encounters with Reporting Services (RS) was to have my working report on my local PC

give errors on my client's network system - why ? Because I had used 2008 (BIDS) for them to display

on a Report Manager 2005 report server system. I got an error about the wrong namespace or whatever.

Proof and Examples :

Below is the first few lines of a report made in VS 2008 with the Forms report designer :-

In the image above notice the second line and the '2005' in the first schema entry.

Now let us see a report definition which is made in the BIDS shell (2008), for the full Report Manager system

working with a Report Server :-

Notice in the image above that schema string is second, and that it now contains '2008' within it. The

order does not matter as these are attribute (or property) strings, but the '2008' does matter as this is what

points at a different namespace.

Now, just in case you get any clever ideas (as I did) that you can fool the system by changing manually

this string, the next error if you do, is that the class of object specified can't be found in the namespace

being used etc. etc. !

If we look into some of the detail we see that a table in 2005 is seen as 'Table' below :-

================================================================

So we seem to be dealing with a 'Table' class in 2005, but look at a simple 2008 report script below :-

==============================================

It would appear that in the 2008 offering from Microsoft the 'Table' class (in the 2005 namespace) has been

replaced by a 'Tablix' class. So if I was to guess I would say that the newer 'Tablix' class also does the

older 'Matrix', and this would figure in how the new Report Builder 2.0 screen looks - remember this

designer app/tool was designed for 'SQL Server 2008' and released after the VS 2008 shipping date I believe.

Here is an image from the entry to RB2 :-

===================================================================================================

In the above image notice that the one icon is used to launch either a table or a matrix style of report.

It would appear that this is because the newer 2008 class of report object used for both the older 'Table'

and 'Matrix' is now one class, that of the 'Tablix'.

Checking what RB2 does with 2005 and 2008 Data Sources :

If we are right in what we have been saying above regarding data sources and reports, then the newer

tool, Report Builder 2.0 should be able to use either of the SQL Server 2005 or 2008 databases, and

then when it makes an RDL file make it based on the newer namespace with a 'Tablix' class.

Study this image below, of my First Impressions sample test in RB2 :-

Yep! sure enough, we are using a 'SQL Server 2005' instance to provide our dataset, with RDL script which

is of the newer 2008 'flavour'.

Moving down the script lines of this same 'First Impressions' example we see the following :-

As you can see the 'Tablix' class is being used in place of the older 'Table' class, just as we anticipated.

Summary of what we have Seen :

Be careful when reading web and Googled stuff about conversion of RDLC to RDL and vice versa.

Very often this material is only considering the case of the 2005 namespace, for these files generated from

within BIDS 2005 and the Forms report designer in VS 2005.

When we get to today's world we need to consider the fact that RDLC are based on a different namespace

to the RDL files - from within VS 2008.

Unless you are doing VERY complex report layouts, this is NOT a big deal - the dataset and data source

specification will be the same, and often this is the largest part of the report design effort. Also the way the

Data Regions are used will also be already sorted out by the developer of the earlier report.

So just get down to it and remake the report in the latest tools available, as long as your target platform will

accept this specification.

Then you will get good with handling the designer, and understand reporting better too ;0)

Good Luck !

PHH - July 11th. 2010.

==========================================================================

This was written BEFORE the material above !!!

It would seem like a good idea to try moving Reports from the VS 2005 environment to the newer 2008

one by adding a copy of the target report to the Solution Explorer pane, then opening and rebuilding etc.

I would guess that MS have built in some sort of forward compatibility, but obviously NOT backwards.

One thing I did try early days, and that was to convert a report from VS 2008 and make it work on the

2005 platform - don't bother is my suggestion ! Even hacking at the XML code - the namespaces

used, and hence the report objects, are different in the two cases.

We will try some other approaches to see if our knowledge of RS is as good as it might be. However,

my approach may be to have two VS shells open at once.

Also, it may be worth experimenting with the Report Builder 2.0 application from Microsoft. If anything

is going to be able to help us, that modern tool ought to have it.

Finally, I suggest we look into the RS options available in VS 2010 - since that is the latest, it may

provide ways of coming from 2005 and 2008 (and more ?) - we shall see.

=======================================================================

After some Googling efforts and finding little or nothing, and then seeing how fiddly it is to convert

files (see a later Appendix - 'G' ) from simply RDLC / RDL either way, I suggest you give up the idea

before it bites you ;-)

Spend your time going forward and not backwards - and realise how lucky you are Grafxsoft made

the Transporter to convert VO to VN, a task on a HUGELY different scale altogether.

Since the biggest part of the report design is often knowing and understanding the data, and creating

a suitable dataset and/or query, I suggest that you simply copy over the data bits to the newer

design platform, and redo the report layout.

Quite honestly, the new IDEs are quite easy to handle and I suggest any new report is done in Report

Builder 2.0 and based on the same dataset as previously.

You may like to visit a subsequent subsection on RDLC and RDL inter-conversion, but that also seems a

bit of a problem, although not quite as bad ?!

Simple idea - get good with the Report Designer tools, use the most recent which makes life a heap

easier, and make a new version.

Good Luck !

PHH - June 18th. 2010.


Appendix F - Some 'DateTime' discussion

If you are going to do any serious work with Reporting Services, and designing new reports,

you will have to come to terms with understanding the 'DateTime' data type, as well as ways

of using each and every function associated with it.

Unfortunately for most of us developers, there seems to be some major differences in dotNet

functions and T-SQL, and then again there seems to be RS ways of formatting DateTime for

the report display.

I have to confess that I am still learning, and have a long way to go - BUT - at least I know where

to look when I have a need, and I know that with issues related to dates and times I need to do

some thorough research in SSMS first.

*** Finding the information *** I have to be honest and say that although I have a lot of books on

modern computing and IT, and programming generally, the 'Books on Line' and SDK stuff is

much more detailed when it comes to 'DateTime' related issues.

Formating at an RS Level :

=======================================================================================

==========================================================================

=====================================

==========================================================================

==========================================================================

==========================================================================

PHH - June 25th. 2010.


DateTime Formatting in RS - Lookup table

Here is a useful table which was supplied by Mark Long on his training CD.

I strongly suggest you get and use both of his VTC training disks sets. They are relatively inexpensive as

well as being very easy to digest for the newcomer to RS and T_SQL. I have learned a lot from the hours

sitting watch and learning.

Mark has a nice style, so go and give him a try !

========================================================================================================

Don't forget to tap into BoL too, as this has a wealth of knowledge just waiting for you to find it ;o)

PHH - July 5th. 2010.


Appendix G - who put the 'c' into '.rdl' ?

 Top Previous

Subtitled - Converting RDL and RDLC Files :

It seems to be a bit of a myth going around developer circles that RDL and RDLC files can be used almost

interchangeably, and all you need to do is change the secondary file name (file extension).

Well, that is just not so. If they were that close in nature then why would Microsoft have given two name

'flavours' to them ;-)

Below is what I have found from the web, but do not panic while reading this, there does seem to be a way

to minimise the impact on our good selves, and that is to roll all our Query Script into a 'View' object in the

SQL database. This then makes Data Source and Dataset creation very easy indeed.

Then at least we can share some of the report layout work over both the 'Report Viewer' and 'Report Manager'

scenarios.

Let us take some time first of all to read about the situation.

http://msdn.microsoft.com/en-us/library/ms252109(VS.90).aspx

web information converting RDL and RDLC Files

===================================================================================================================

===================================================================================================================

=============================================================================================================================

=============================================================================================================================

=============================================================================================================================

============================================================================================================================

******************************************************************************************************************************************************

http://msdn.microsoft.com/en-us/library/ms252109.aspx

Converting RDLC to RDL - web live link ...

=====================================================================================================================================

=====================================================================================================================================

Summary as it Appears to me :

This 'inter' conversion is not as simple and as ideal as VOPS colleagues made out it was. 'Piece of cake' is hardly

as I would describe it ! And confusing it can be, especially to those coming to RS as new guys.

In many ways it is not difficult to understand, or even appreciate, but definitely 'messy' to deal with. That is more

true for complex reports, simple test and one table reports are easy to get around.

It would appear to me that any effort in getting reports to show in the browser is a good idea, and then displaying

a browser on a Form or WPF could get around any need to convert. Then all files could be created as RDLs and

show in report manager or the Forms / WPF browser.

Be warned however, I have not tried this yet ;-) But I certainly will !

Helpful Suggestion ? :

If you get into the habit of making 'View' objects in the DB, then accessing these and not using query scripts directly,

this will help in this process of having one report which can be used in your application, or indeed the Report Manager.

Hope this suggestion is as good as it feels !

But it also goes to say - don't believe everything you read in the NG ;O))

PHH - July 7th. 2010.