Walkthrough: A Simple-ish Runtime Quick Report

by Lisa Nicholls Sun, May 25 2008 21:21

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.

Tags:

Reporting | Reporting | SQL Server | SQL Server

Comments (27) -

8/10/2008 8:46:16 AM #

Hasina Syed Mohammed

Using the above i was able to take column header dynamically. but i dont get the corresponding colunmn content while using the following:

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

i am getting the content as #Error

Any suggestions please

Hasina Syed Mohammed India

8/10/2008 1:45:27 PM #

L

Hi there,

Could this be something simple like a capitalization issue?

L United States

12/28/2008 9:02:44 PM #

Speed Dating

It's really cool that you wrote such a thorough response to Vij's question. Next time I need some help I will let you know. I am an ASP.net newbie. I like the fact that you even gave some advice on fancier column headers. Too cool.

Speed Dating United States

12/29/2008 10:25:03 PM #

&gt;L&lt;

Well, Speed, honestly I do this 'cause it's fun for me <g>.  Enjoy!

>L< United States

3/29/2009 6:58:04 AM #

Cash Advance Loans

XML is very nice. I like how sitemaps and XML work very well hand in hand!

Cash Advance Loans United States

4/24/2009 3:04:37 PM #

Eyad

Thank u Lisa for your nice working Smile

This was a great post...

Thank u alot, ur the best

Eyad U.A.E.

7/15/2009 6:10:14 AM #

Sergio

Hi.

The article s very usefull! but I'm just a starter programing in VB and ASP and I don't know where should I write this code:


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


Thank you so much!

Best regards.

Sergio Mexico

7/15/2009 9:19:03 AM #

&gt;L&lt;

Hi Sergio,

I'm going to quote from another post on this blog...

spacefold.com/.../...amic-SQL-in-and-for-RDLs.aspx

Start writing your function by going into the Report Properties window (from the Report menu popup ) and choosing the Code tab. spacefold.com/.../image.axd

Good luck!

>L<

9/1/2009 7:13:47 AM #

&gt;L&lt;

Monica, what is the expression you used and what was the error?

>L<

9/30/2009 2:38:42 AM #

sgrajan

The article is very useful and interesting.
Could you explain the Visibility code a little bit more.
I really don't know where to put "=Code.HowMany < 3"
If I put it in Columns Format Property, it is not working.
If I put it in Visibility toggle property, I am getting errors.
I appreciate your help.
Thanks in advance.

sgrajan United States

9/30/2009 4:00:21 AM #

sgrajan

Never mind, my previous request. I solved it. The visibility->hidden property takes the expression. Thank you.

sgrajan United States

9/30/2009 11:24:36 AM #

&gt;L&lt;

Hi Sgrajan,

Appreciate your writing back!  Good luck!

>L< United States

1/18/2010 5:43:47 AM #

Boat Party

Creating dynamic columns is actually pretty cool in the way you explain it.

Boat Party United States

2/3/2010 5:43:01 PM #

Julio Valencia

Hi I have a problem with Reportviewer in asp.net
1. I have set up the reportviewer control with the server url and the report path.

2. I connect with not issues to the server, I am sure about this because I see the report paramenters values (a dropdown list that I have created).

3. The report should run in Remote Mode.

My issue is when I click the View Report button, nothing happens.

I got not error message or what so ever. Please help I have no idea what is the issue.

If you think the problems are the credentials. I have create my on clases and it works fine.

This is a project with VS 2008 and SQL 2008

Julio Valencia Ecuador

2/4/2010 4:01:38 AM #

&gt;L&lt;

Hi Julio,

If you see the parameters, you know for sure that you have the correct report loaded.  Or, at least I hope so.  


But... it's probably irrelevant.  Ordinarily you're going to see the report as soon as the page loads; the button is just to refresh if you've set new parameters or whatever.

You can send me your code for the ASPX page e-mail if you want, in case I can see anything.

>L< United States

5/8/2010 11:53:46 AM #

&gt;L&lt;

Sure, you could skip the column hiding but then... the extra columns that didn't really belong in your set would show <s>.  You might see #Error! or just nothing as a result of each expression (I can't remember, and if #Error! it could be surpressed) but it wouldn't look very good.  It would tip your hand that you were using a generic report layout, rather than looking like the layout "belonged" to this dataset and its contents.

Hope this helps,

>L< United States

7/6/2010 9:16:22 AM #

Mazda

I think its great that you go into such depth to answer Vij's question. I have spent most my time with PHP and am just getting to grips with ASP and could definately have done with someone liuke yourself guiding me so helpfully.

Mazda United States

7/25/2010 11:44:49 PM #

error fix

It's really cool that you wrote such a thorough response to Vij's question. Next time I need some help I will let you know. I am an ASP.net newbie. I like the fact that you even gave some advice on fancier column headers.

error fix United States

11/3/2010 5:18:23 PM #

Pink Ring

Good reporting automation can save so much time. It's great when you can just click a button, metaphorically, and have your report arranged just the way you want, with all of (and only) the relevant data.

Pink Ring Canada

11/20/2010 10:55:05 AM #

Pronostic PMU

This is helpuful runtime quick report for my work ! Thanks

Pronostic PMU France

11/25/2010 12:22:02 AM #

Genuine Cheap Viagra

Very good tutorial based on C#. I have learnt many things from your article.

Genuine Cheap Viagra United Kingdom

11/25/2010 5:07:59 AM #

Pandora Peaks

The user did not specified the value. But,I have implemented as an experiments. Thank you very much.

Pandora Peaks United Kingdom

6/27/2011 5:21:00 AM #

Abraham

Many thanks for this thorough response and step-by-step guide.  You managed to explain it in a way that made perfect sense, and I am new to all of this.

Abraham Australia

7/24/2011 8:11:34 PM #

comfort shoe stores


Could this be something simple like a capitalization issue?

comfort shoe stores Uruguay

12/26/2011 4:54:43 AM #

dineron urgente

I like that about generic behavior

dineron urgente Spain

3/8/2012 7:09:27 AM #

pitius

here doesn't seem to be anything simple about this, but thank you for sharing.

pitius United States

3/8/2012 7:10:19 AM #

pitius

This is helpuful runtime quick report for my work ! Thanks

pitius United States

Pingbacks and trackbacks (2)+