Cascading Parameter Values in SSRS

Author: Jeremy Smyth

In SQL Server Reporting Services (SSRS), you might want your choice in the first parameter to limit the choices available in the second (and subsequent) parameters.

This works automagically if you order your parameters and datasets correctly

  • First, set up a primary (report) dataset, then a dataset for each parameter dropdown. Code the WHERE clause in the datasets to make the dependencies correct across parameter variables
  • Secondly, order your parameters in the Report | Parameters menu so that the first variable/parameter you want the user to fill in is at the top, and make the second dataset depend on that parameter. Follow this ordering through the parameters; the final parameter(s) should be the one(s) the actual report dataset depends on.
  • Repeat for subsequent parameters

This will work if your WHERE clause in the second and subsequent datasets have variables that SSRS knows are populated from earlier parameters.

As an example, I have three datasets from the venerable pubs database.

pubslist is used to populate the @p parameter, and looks like this:

SELECT pub_id, pub_name FROM publishers

titleslist populates the @t parameter, and looks like this:

SELECT title_id, title FROM titles WHERE pub_id = @p

Finally, reportdataset looks like this:

SELECT title, price, ytd_sales FROM titles WHERE title_id = @t

The order of the parameters in the Report | Report Parameters menu is crucial; because the datasets must be executed in the order shown above, and the @t parameter is in a dataset that relies on the @p parameter being set first, we move @p to the top of the list.

Now, SSRS evaluates the dataset needed to fill the dropdown for the first parameter with labels. It relies on a dataset that doesn’t need a parameter, so can be produced immediately.

Then, having got that parameter value, it can populate the second parameter’s dropdown. That in turn results in the report being produced.

Tables, tables, tables

Author: Jeremy Smyth

So, what’s a temporary table? or an in-memory table? or a pivot table?

An in-memory table is a table in some platforms that’s stored entirely in memory. These don’t really exist in MS SQL, although you could say that it is a table that’s been entirely cached, and so doesn’t result in any physical (hard disk) reads when queried. In earlier versions, the DBCC PINTABLE command allowed the “pinning” of tables in memory, but this was deprecated in SQL Server 2005.

Often, a table-valued variable, @tablename, might be stored in memory (although this is not guaranteed), and declared in a batch or function, with no persistence.

A temporary table is a table that will be automatically dropped when it’s no longer needed, usually when the creating session is terminated. In MS SQL, they begin with a # (or two hashes if they’re global temporary tables, shared between multiple sessions), and are often created with a SELECT INTO #TEMPTABLE … style query.

A pivot table is a special form of query where the values in several rows are summarised, “pivoted” on an axis, and become columns, where the summary data then becomes the rows. Frequently this happens where you’ve rows sorted on dates; these may then be “pivoted” so you end up with a column for January, one for February, one for March, etc.

PHP: require and include

Author: Jeremy Smyth

Another simple feature that’s good to pick up on!

When you want to run the same functions from several pages, but don’t want to copy/paste (because that’s BAD), you do this.

Usually, you’d use require or include to include the code from one PHP script in another.

If you hadn’t done this before, or designed it in, you’ll probably need to extract the functions from your script(s), place them in a central file, and include that in all scripts that need it (including, of course, the one they were originally in!). Remember that includeing a file in another causes its embedded HTML to be included too, so you’ll typically include PHP files that have functions only, to achieve the desired result.

Then you just call the function, as if it were written in the script in the first place :)

SQL Server: Database Diagrams

Author: Jeremy Smyth

A little feature that some people don’t know about but would find very very useful: database diagrams in SQL Server. If a picture paints a thousand words, then this feature will help you pick up on the structure of data much more easily.

Within SQL Server, there’s the “Database Diagram” container in each database, in Management Studio.

You can create a new diagram in there, and include all (or some) of your tables, so showing where the relationships are, and what the tables are.

You could also create several different diagrams, each showing a subset of tables and relationships useful to a particular use case.

Quality of Service: Hosting

Author: Jeremy Smyth

There are several useful QoS parameters, although not all are as easily measured as e.g. 99.98% uptime, which is easily derived – 99.98% of the time, your server is available.

Some other useful metrics:

  • What sort of bandwidth do they support? How about if it’s a shared pipe and they’ve multiple high-volume clients? You’re unlikely to get an honest answer to this from the vendor.
  • If you’re on a shared host, what sort of competition for resources will you have on the box? Again, this is competitive information, so you’re unlikely to get an honest response.
  • How independent is each application? i.e. if another application on a co-hosted box loops MySQL, will your app suffer?
  • How often do they upgrade dependencies, and does that affect their 99.98% uptime?

Then there are the fluffy not-entirely-QoS related hosting problems:

  • Will they do your backups etc.?
  • How responsive are they to queries? Do they have a guaranteed response time e.g. 3 hours from first email to first response, 24/7?
  • Are they efficient? Have they resolved issues within a useful timeframe? A quick response-time is nice and feelgood, but if they don’t fix the problem, that’s not quite so good!
  • Are they patient with you when you suggest issues, but due to your own technical background are slightly off-base?

GROUP BY and single-row values

Author: Jeremy Smyth

So, you’ve constructed a SQL statement with a GROUP BY clause, and you’re getting this message:

Column col is invalid in the select list because it is not
contained in either an aggregate function or the
GROUP BY clause.

Bear in mind that the result of a GROUP BY statement, or of a statement where one or columns uses an aggregate function, has each row containing a summary of other rows.

This means that if you try to include a column in your select clause that isn’t a summary (this includes values by which you’re grouping), then the server is going to have difficulties returning it; remember, it’s going to return one row per group, and any value that can’t be reduced to a single row per group will fail.

This gets interesting even when you’re hoping to return a single value per group, for example with CASE.

If the CASE expression you’re using is dependent on values of individual rows rather than summaries, you’ll also get the above message; you may only reference non-aggregate values (i.e. single-row values) in an aggregate function, or in the WHERE clause, so the solution would involve placing your CASE inside an aggregate function, in this case your SUM.

The same applies for the HAVING clause, as it is effectively selecting rows in the grouped resultset based on values in each group.

Returning a copy of an object

Author: Jeremy Smyth

Consider an object passed (by reference) into a method, which then modifies the object. Should the method return the object on completion?

Well, it’s redundant. Here’s why.

There are two approaches to doing this – the classic way (caller chooses what to manipulate/change), and callee changes (which is what happens when you pass in an object by reference:

  • If the caller chooses what to manipulate, then your method will operate on a copy of the dataset, and return that copy, leaving the original untouched. Then the caller may overwrite the original, if desired. Many array operations in Perl work like this.
  • The alternative lets the called method do the in-place operation, in which case the return value is more typically a boolean (”Yes, I’ve succeeded!”) or an int containing the number of values affected. This is how database updates work.

Of course, if the object is not passed by reference (i.e. a copy is passed into the method) or if the method explicitly returns a modified copy, then returning an object is the right way to do it.

Redacting Text the Dan Brown Way.

Author: Jeremy Smyth

Dan Brown’s The Lost Symbol makes a passing reference to some “redacted text”, where only a highlighted portion of the text becomes available.

In the story, we find a redacted text where results appear like this:

######## secret location UNDERGROUND where the ########
######## somewhere in WASHINGTON D.C., the coordinates ########
######## uncovered an ANCIENT PORTAL that led ###########
######## warning the PYRAMID holds dangerous ###########
######## decipher this ENGRAVED SYMBOLON to unveil #######

…so Trish Dunne, a metasystems expert, constructs a spider to find the document for Katherine Solomon, but the document itself had been redacted.

Now, constructing a “search spider” is not exactly the technical exercise Dan Brown makes it out to be, but the redacted text the search spider produced got me thinking.

A while back, I put together a regular expression for someone who wanted their search engine to produce a summary of searched text, only showing the surrounding words. This isn’t exactly what Trish achieves, but it gave me an idea….

Although it’s not a common requirement, redacting text looks like a bit of fun. Let’s try it in Perl. Given the following input text, let’s do a bit of searching and redacting:

“Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum”

Right. Let’s look for the words “dolor”, “nisi”, and “officia deserunt”:

#!/usr/bin/perl -w $match = "dolor|nisi|officia deserunt"; while(<>){     while(m!((?:\w+\b\s*){2})($match)(\w*\s*(?:\w+\b\s*){2})!g){         $x = $`;         $y = "$1\U$2\E$3";         $_ = $';                 $x =~ tr/A-Za-z/#/;         print "$x$y";     }     tr/A-Za-z/#/;     print; }

Now this isn’t quite as complex as we’d need it to be; although it allows multiple search words, and catches them all, it doesn’t catch longer strings of them. Nevermind, let’s see how it works.

Unsurprisingly, the match is the magic part. Here’s it exploded:

m!                      # start the match     (                   # find and remember the two words before the match         (?:             # define a word (but don't remember it)             \w+         # ...as a bunch of letters/numbers             \b          # (stop matching letters/numbers!)             \s*         # ...followed by zero or more spaces/tabs/etc.         ){2}            # Now we know what a word is, we want two of them.     )                   # that's the two words...     (                   # Now remember the matched word itself.         $match          # Use the $match variable above.         )                   #     (                   # Now the next two words         \w*\s*          # Any number of alphanum (even zero, because "dolor"                         # should also match "dolore")                   (?:             # second word (but don't remember it)             \w+\b\s*    # same as above         ){2}     ) !gx                     # the "!" matches "m!" above, g matches globally                         # The "x" is explained below.

Little aside first: the exploded code above is perfectly valid Perl, and would work just as well as the code in the complete program above. The magic is performed by the x suffix on the expression; this tells perl to ignore whitespace and comments within a regular expression, and lets people like me explain what’s going on right there in the code. Handy, eh?

So, once we’ve identified each matching pattern, we display it (the $&) after showing “#” for each letter that comes before the match (the $`). Then we reset the text to match ($_) to the remainder of the string, and go again.

(Another aside: if we wanted to replace the spaces and punctuation with ‘#’, as well as all letters, we could do this instead of the tr///: $x = '#' x length($x);)

At the end of matching, we fail to match any more, so we simply translate the remainder to hashes again.

After running it against our file, we get this:

Lorem ipsum DOLOR sit amet, ########### ########### ####, ### ## ####### ###### ########## ## labore et DOLORe magna aliqua. ## #### ## ##### ######, #### ####### ############ ullamco laboris NISI ut aliquip ## ## ####### #########. #### aute irure DOLOR in reprehenderit ## ######### ##### esse cillum DOLORe eu fugiat ##### ########. ######### #### ######## ######### ### ########, #### ## culpa qui OFFICIA DESERUNT mollit anim ## ### #######.

Job done.

I’ve come across a lot of people having a problem when representing data from their databases; hierarchical data provides us with a particular problem when displaying the relationships and containers required.

An example of hierarchical data

As an example, let’s say you have forums within categories. The category “Web Programming” may have forums like “PHP”, “JavaScript” and so on, so we want to list each forum under each category. The problem here is that you’ll have:

  • a table for categories
  • one for forums (with a foreign key for categories)
  • one for posts, with a foreign key for forums
  • and probably one for comments.

This fits quite neatly into a hierarchy: each comment belongs to a post, which belongs to a forum, which belongs to a category.

Using multiple resultsets

If you want to display the Categories with Forums under them in your page, one way to do this is by setting up multiple resultsets, each focusing on one level of the hierarchy. To achieve this, you’d need to get a resultset for the categories first, and then iterate over this list with another loop for your forum list.

The following code is an example of iterating using nested queries:

$cat_rs = mysql_query("select id, name from categories"); while($cat_row = mysql_fetch_array($cat_rs)){      // print category name from $cat_row[1]      $forum_rs = mysql_query("select name... "                           . "from forums "                           . "where cat_id = '" . $cat_row[0] ."'");      while($forum_row = mysql_fetch_array($forum_rs)){         //print forum stuff      } }

Although this matches the hierarchical nature of the data, and is pretty straightforward and intuitive to understand, it is somewhat inefficient; it issues a select statement for each category, plus one to list the categories in the first place. If there are many categories, the page might take some time to load as all queries are issued and processed.

Using a SQL JOIN: a single resultset

Another way to solve the problem would be to retrieve all relevant data in a single resultset, and use PHP to iterate over the rows, deciding on when to print each individual section.

A join will give you a single recordset with the category in one column, and the forum in another, giving you many rows for a single category.

$join_rs = mysql_query("select c.id, c.name, f.name,... "                         . "from categories c inner join forums f "                         . "on c.id = f.cat_id "                         . "order by c.id"); // this line is crucial $current_category = ""; while($join_row = mysql_fetch_array($join_rs)){         if($join_row[0] != $current_category){                 //set up new category headings here                 $current_category = $join_row[0];         }         //print the forum stuff in the current category }

The PHP is a bit more complex, because you have to close divs or tables correctly, within the loop. The order by line in the above example is vital, because without it, the categories may be dotted throughout the resultset. As we want all forum data related to a single category to appear together, we want rows belonging to that category to be adjacent in the resultset. The order by clause achieves this.

Despite the complexity, there are still benefits to this approach. The earlier example was simple and intuitive, using nested queries for each parent element. On the other hand, in this example we’ve only issued one select statement, so the page is likely to load more quickly, given that the number of round trips to MySQL will be substantially fewer.

Sometimes, when processing a form, you need to retrieve a number of values for a single variable. This is often a cause for confusion when beginning PHP. The particular problem faced: how do we process multiple values coming from a form to a databse, when using PHP?

Imagine we had a drop-down box containing a number of elements, and we want the user to select one or more. We’d use the select element in HTML.

<select name="taglist[]" multiple="multiple">

Note the square brackets: for parameter arrays — parameters that allow multiple values — PHP requires that you name a GET or POST variable accordingly, so the select above needs to be named taglist[] in order to tell PHP that it will contain an array rather than a single value.

In order to process this, we then have to iterate over that array.

To iterate over the array in PHP code, we use something like this:

foreach($_POST["taglist[]"] as $s) {     mysql_query("INSERT INTO articlestagged (article_id, tag) " .        "VALUES ('$id','$s')")         or die("Insert Error: ".mysql_error()); }

Obviously this example relates to adding new entries to a database, but the same would apply if we were querying a database, displaying multiple images, or indeed anywhere else we needed to operate on multiple concurrent values.