Subtitle for this post probably should be: Yet More Fun with Report Parameters, or Sometimes the magic needs a little help.

Vij wrote in response to one of my dynamic reporting posts:

Hi Lisa, I need help in creating columns dynamically in report viewer, but i have created RDLC statically. I am reading the data from XML file and only at run time I will be knowing columns count. Please guide me in this. I am working on C#. Thanks in advance, Vij

This is one of those things you can do a whole lot of different ways.  I'm going to show you one, with a few suggestions for variants. 

Before we begin

Use the method you see here to get, well, a quick report.

This method is designed to take care of unknown data contents. To be that generic, you have to give up some frills.  Because we don't know the contents of the columns being delivered, we're not going to know how wide to make each column, or much of anything else, such as formatting instructions.  We'll make some slight exceptions for personalizing column headers, as you'll see. 

Here's a thought for you: the generic nature of our Table control's display of columns will be simlar to what you see the output of a Matrix control — because that control has exactly the same problem-to-solve.

I'll end this discussion with some thoughts about times when you want finer control.

Ready?

I don't know anything about Vij's XML file, and I don't know how much Vij knows at runtime before receiving the file. 

I'm going to assume only that he can stipulate a maximum number of columns possible for his data, or at least a maximum number of columns that will be allowed to show in the report.  This makes sense for pagination reasons, in any type of generic report.

Also, because Vij says "I am working on C#" I am going to use as little report code as possible (although I don't see what difference it can possibly make that the trivial embedded report code will be in VB).

 

1. Start with a parameter

Steve Martin used to have a routine about being a millionaire… without ever paying taxes.  "First," he would say, "get a million dollars".  Luckily our first step is easier to accomplish. 

We'll add a single parameter of String type, called WhichColsUnknown, for no particular reason, except I'd like to distinguish it from a second scenario in which the columns are determined at runtime but you know the full set of possible columns from the beginning.  In any case, million dollars or no, our single parameter will buy us a lot.

We'll use this parameter to pass a comma-delimited list of the field names that should be displayed.  In this scenario, because Vij is using an RDLC (a local report), he has control of the dataset before he runs the report.  He can check the dataset and build this comma-delimited list of whatever field names happen to be present in it. 

If you're playing along at home, just make the SQL Command for your dataset SELECT * FROM <some table>, expose the parameter, and type in a delimited list of field names at will, in the parameter value, to test the premise. Don't put any extra spaces in your delimited list of field names, or add trimming statements to the expressions you see here.  And don't worry; if you're working with the data on a Report Server rather than an RDLC, everything here still applies.  

2. Add some generic behavior

Now you need to tell the report how many columns are present in this report run.  Well, strictly speaking, as you'll probably be able to figure out from the next steps in this walkthrough, you don't need to do this.  You could make the determination on each expression.  It's just more efficient to do it once.

I'll use a variable to do that, called HowMany.  You can make HowMany a second parameter and pass the value if you wish, but I think it will be easier to make the method consistent between RDLs and RDLCs if you embed this behavior in the report.   

We'll initialize this variable, in the Page Header. If for some reason you don't have any existing content in the page header, you can use an expression such as the following in a very tiny Page Header (the expression returns ""):

=Code.InitializeColCount(Parameters)

Assuming you have content in your Page Header, just attach the expression you see above to any textbox, like this:

=Code.InitializeColCount(Parameters)
   & "My Real Report Header" 

Here's what the code looks like to match what you see above:

Public HowMany As Integer =0

Function InitializeColCount(p AS Parameters) AS String
   If HowMany = 0 Then
     ' initialize only once
     HowMany = p("WhichColsUnknown").Value.Split(",").Length
   End If
   Return ""
End Function

… not too much of a VB burden to impose, is it? <g>

  

3. Hide the extra columns

Add your stipulated "maximum number of columns" to this report, in a table with whatever formatting you want.  And, now, as you've probably guessed,  you need to use Visibility to determine how many columns should show in the report, using this variable.

Being careful to put the attribute on the column level — not on a textbox — use expressions like the following on each column (this one is for column #3):

=Code.HowMany < 3

But what should you actually put in each column, as expressions?  I'll show you a basic version and then we'll probably iterate a bit.

  

4. Add generic expressions to show column headers and column content

Remember: our parameter has the instructions providing field names.  So each column shows the contents of the appropriate field using an expression similar to the following.  This one is, again, for column #3 (remember the Fields collection is a 0-based array, so we're one off).

=Fields(
   Parameters!WhichColsUnknown.Value.Split(",")(2)).Value

Yes it's a bit tedious to put in all these expressions by hand with the index change on each entry.  You can make it less tedious a couple of ways, such as pasting them all in and then editing them in the XML directly, in my case.  Or maybe you would prefer a hidden parameter that ties textbox names to index values, and you'll write a function to figure out what index belongs to each column.  Go ahead…

We can use a similar expression in our most "basic" version of this report to dump the field name in as the column header for each column (again this example shows the correct expression for column #3)

=Parameters!WhichColsUnknown.Value.Split(",")(2)

  

5. You're basically done.  Want fancier column headers?

We could also add just a little more code to make a smarter determination of what our column headers should look like.  Instead of the last expression above, write something like this into each column header (still using column #3 as an example):

=Code.GetColHeader(2, Parameters )

You can probably imagine the code that goes into this function:

Function GetColHeader(index AS Integer, p As Parameters) AS String
   Dim h As String = ""
   If HowMany >= index + 1 Then
      h =  p("WhichColsUnknown").Value.Split(",")(index)
      ' do more work here
   End If
   Return h
End Function

Where it says 'do more work here, you might (for example) replace underscores with spaces, change casing, or reference a second parameter holding your preferred column header labels in the same order.  Whatever you'd like.

I'm not going to belabor this idea, especially not the "second parameter" one, because I'd like to offer an alternative: if you're going to bring information in from the "outside world" via a parameter regarding custom column labels, you might prefer to do it as a second dataset you attach to the report, with the same number of columns as your maximum number of columns.  That dataset might look something like the following (in a case where 8 is my maximum number of columns and I have a primary data set with 6 columns available on this run):

SELECT 'Sales Number' AS Field1,
            'SalesPerson' AS Field2, 
            'Order Type' AS  Field3,
            'Qty' AS  Field4,
            'Customer' AS  Field5,
             'Active' AS  Field6,
             SPACE(0) AS Field7,
             SPACE(0) AS Field8

Now my column header expression for the column #3 looks like this (where "ColHeaders" is the name of my extra dataset):

=First(Fields("Field3").Value, "ColHeaders")

  

6. What can I improve if I do know all about the full set of possible columns?

Sometimes you might have more control over the dataset — you know the full set, but not which ones are being delivered, or which ones a certain requestor is allowed to see, or in what order they should be displayed, until runtime. 

In that case, you can use a simpler version of this technique, with a multi-valued parameter that provides both field names (as its "values") and column headers (as its "labels").  This is the second scenario I mentioned earlier; let's call this multi-valued parameter WhichCols.

Your column visibiity expression for column #3 now looks like this:

=Parameters!WhichCols.Value.Length < 3

… the column header expression for column #3 now looks like this:

=Parameters!WhichCols.Label(2)

… and the expression to show the data in column #3 now looks like this:

=Fields(Parameters!WhichCols.Value(2)).Value

  

7. What if I want more "tuned" formatting for each column?

A generic report basically provides good value for money.

If you want finer control over the behavior of each column, you can adapt what you see here somewhat, by providing different sets of columns with different widths and a bit more metadata instructions about how to treat each field in the dataset, using similar techniques to what you see above. Have some columns "suitable to currency display", some for "long text display", etc.  Then you can assign your data columns to the table columns that are "most like" the expected contents. 

But really, my friend, IMHO this is where you should get off this particular bus and accept a transfer pass to a different route: adjusting the report definition dynamically at runtime. 

You can even create the full RDL or RDLC from scratch if you want — you'll find instructions on http://gotreportviewer.com/.

Alternatively, keep a couple of dummy report definitions around, again holding column definitions "suitable for" different types of data.  Add and subtract those columns directly into a template report definition, as appropriate for your current data set, before running the report. 

It might not be a million dollars, but, effort-wise, you pays your money and you takes your choice. As Mr. Universe would say, That's my motto, or it might be if I start having a motto.