none
How to create dynamic RDL report with different column header? RRS feed

  • Question

  • I have to create one report with different column header.

     

    eg.

    Name                Description
      0                         Access Administrator Role
      Portfolio           Access Privilege                  Transaction Privilege              Individual Portfolio Collection
      Global access   Read/Update/Insert/Delete     Read/Update/Insert/Delete

    Global acc1       Read/Update/Insert/Delete     Read/Update/Insert/Delete

     

    About this report , Name , Description,Portfolio,Access Privilege,Transaction Privilege,Individual Portfolio Collection are column header.

     

    will somebody can help me? I didn't know how to create the report.

     

    thanks.

     

    Wednesday, June 6, 2007 1:35 AM

Answers

  • >> there is one layout table and one dataset

     

    So you decide at runtime which query to run, out of the two possibilities, right?

     

    Do you make this decision in code that runs within (inside) the report, or outside the report?

     

    If this is a local mode report, you probably decide which data you need outside the report.  If server mode it could be either inside or outside -- you could pass a parameter to the report so it dynamically builds a query, etc.

     

    Either way -- you *know* which query you ran, at some point, whether outside or inside the report.

     

    So what I was trying to say earlier, Joel, was that you can decide which column header on the same basis as you decided which query to run.

     

    Let's say you decide outside the report which query to run, either by creating a different data set (for a local report) or by passing a parameter (for a server report).  In this case, you can set a parameter that is the value of the column header, too.  You could have as many as you wanted, one for each column header, and use the value of the parameter for each header, or you could just have one boolean parameter that represented a "switch" between the two sets of column headers and use IIF()s for the column header expressions.

     

    Let's say that code within the report decides which query to run.  You can use code within the report to set up variables representing the column headers at the same time.

     

    Does this make sense?

     

     

    Thursday, June 14, 2007 1:12 AM
  • Hi there,

     

    Here is what you need to do

     

    1. Set up two parameters for the report, representing your two dates

     

    2. In the report design, display those two dates; in expressions you can use parameters like this:

     

    Parameters!startDate.Value

     

    3.  At runtime, when you are ready to display the report, send both of the dates to the RDLC as parameters.  Check the LocalReport class in the docs -- you will see a SetParameters method.  It takes a list, so you can set both those items .  Now the values are available to the report execution process for any calculations and display.

     

    OK?

     

    >L<

     

     

    Thursday, June 21, 2007 12:54 PM

All replies

  •  

    You can write a little VB code that changes the column header text based on the Collection.

     

    In Report Properties go to the "Code" tab.   Create a function like this:

     

    Public Function GetColumnName(String KeyValue) As String

    Select Case KeyValue
     Case "Val1"

            Return "Header 1"

        Case "Val2"
            Return "Header 2"

        Case "Val3"
           Return "Header 3"

    Case Else
       Return KeyValue
    End Select

    End Function

     

    In the Properties for each column header, create an expression like this:

     

    =Code.GetColumnName(Fields!ColumnName.Value)

     

    I hope that helps!

     

    -Chaz


    Thursday, June 7, 2007 6:57 PM
  • Thanks for your reversion,but I'm not understand with your method.

     

    1.How control the print data? Is that should print column with data together?

    2.Use that method , How to define the column?

     

    thx.

    Friday, June 8, 2007 12:51 AM
  • I was guessing that solution might solve your problem.  It is not clear from your message exactly what you are trying to accomplish.

     

    Can you try again to describe specifically what the problem is?

    Friday, June 8, 2007 6:50 PM
  •  I am sorry that my english is not very well. I will try again.

     

    I should use two query to finish this report.So those two query result need two header text.

     

    I have use one method to settle this problem.At first print data with one column to record data type(first query or second query.),then at UI set Header column visible with datatype.

     

    Sunday, June 10, 2007 9:49 AM
  • The answer depends on exactly how your report "knows" what query was used, during the run.

     

    Some possibilities might be (this is not clear from your question):

     

    *Scenario #1 -- you send a parameter to the report telling it what to do and it dynamically adjusts what view or query it runs

     

    *Scenario #2 -- you are using a stored procedure and this stored procedure decidees what view or query to run, completely independent of the report request.

     

    There are probably other ways you could be doing it, and we need to know which to give you the right answer.

     

    For example, in Scenario #1 above, you could do this in your column header textbox expression:

     

    Code Snippet

    = IIF (@MyViewParameter = 1, "Column Header 1","Column Header 2")

     

     

    ... however that solution would not work for Scenario #2.  In that scenario, the answer would be that the report would have to look for some clues in the data to tell it what the right column header would be.  For example, if one query has a column that the other one does not have, you could check Fields!FieldName.IsMissing to decide which query had been run.  If there is truly no way to tell from the existing datasets, then perhaps the two queries could be adjusted to have some extra columns returning this information, so that the report knows what to do. 

     

     

    >L<

     

    Sunday, June 10, 2007 6:03 PM
  • I have use there query:

    oneTongue Tiedelect Name,Description,"Type1" as DataType from table1;(detail table)

    twoTongue Tiedelect Portfolio,AccessPrivilege,TransactionPrivilege,IndividualPortfolioCollection,"Type2" as DataType  from Table2;(detail table)

    TotalTongue Tiedelect Name,Portfolio from tableTotle;(main table)

     

    When Print data:

    foreach(datadetail in tableTotle)

    {

        print table1;(use some relation)

        print table2;(use some relation)

    }

     

    Then at RDL UI,I can use DataType to judge whether set some header to hidden.

    Monday, June 11, 2007 12:23 AM
  • I'm sorry, Joel, are these two queries datasets in the report?  Are they in a stored procedure that the report calls? or what?

     

    Are there two tables in the report, one for each query?  Or just one table?

     

    >L<

    Monday, June 11, 2007 3:44 AM
  •  Yes,there are two tables data in the report(but I think one table is better,because those data detail has relationship,so I use one table).

     And at the same time ,I should told that I use a third tools for print data,just like query data in procedure, and in produrce I use two select sentence.

     

    Wednesday, June 13, 2007 1:47 AM
  • I don't think we're communicating very well, but it is not your fault <s>.  My apologies.  I will ask my question again:

     

    Are there two LAYOUT TABLES (not DATABASE TABLES) in your report, when you design it?

     

    Or is there one LAYOUT TABLE and you switch between the two queries, to decide which you are going to display, at runtime?

     

    >L<

     

     

    Wednesday, June 13, 2007 2:34 PM
  • My wrong, my expression is not clarity.

    There is one Layout Table and there is only one dataset for it.

    As I know, if there are two Layout Table, there should have two dataset for it.

    Thursday, June 14, 2007 12:01 AM
  • >> there is one layout table and one dataset

     

    So you decide at runtime which query to run, out of the two possibilities, right?

     

    Do you make this decision in code that runs within (inside) the report, or outside the report?

     

    If this is a local mode report, you probably decide which data you need outside the report.  If server mode it could be either inside or outside -- you could pass a parameter to the report so it dynamically builds a query, etc.

     

    Either way -- you *know* which query you ran, at some point, whether outside or inside the report.

     

    So what I was trying to say earlier, Joel, was that you can decide which column header on the same basis as you decided which query to run.

     

    Let's say you decide outside the report which query to run, either by creating a different data set (for a local report) or by passing a parameter (for a server report).  In this case, you can set a parameter that is the value of the column header, too.  You could have as many as you wanted, one for each column header, and use the value of the parameter for each header, or you could just have one boolean parameter that represented a "switch" between the two sets of column headers and use IIF()s for the column header expressions.

     

    Let's say that code within the report decides which query to run.  You can use code within the report to set up variables representing the column headers at the same time.

     

    Does this make sense?

     

     

    Thursday, June 14, 2007 1:12 AM
  • hi

    Please tell me how to add Runtime value into Report.rdlc. I have two date(for start & end date) Controls on web page. I want to display those date on Report.rdlc using c#.

     

    thanking you in advance

     

    Thursday, June 21, 2007 7:21 AM
  • Hi there,

     

    Here is what you need to do

     

    1. Set up two parameters for the report, representing your two dates

     

    2. In the report design, display those two dates; in expressions you can use parameters like this:

     

    Parameters!startDate.Value

     

    3.  At runtime, when you are ready to display the report, send both of the dates to the RDLC as parameters.  Check the LocalReport class in the docs -- you will see a SetParameters method.  It takes a list, so you can set both those items .  Now the values are available to the report execution process for any calculations and display.

     

    OK?

     

    >L<

     

     

    Thursday, June 21, 2007 12:54 PM
  • Hi Nicholls.

    that's the way which I needed.

     

    Thx a lot.

    Thursday, June 21, 2007 2:05 PM
  • hi mam

    I am bigner so please tell me how to create expression. Please tell me step by step .

     

    thanking you in advance

     

    Friday, June 22, 2007 4:59 AM
  • thanks a lot

    I have Solve.

    thanks

    Friday, June 22, 2007 6:50 AM
  • What if the number of parameters are also runtime generated? Is there any solution for that as well?

     

    Tuesday, December 4, 2007 7:53 AM
  • You mean, parameters to the report or parameters to your stored procedure?

     

    I will assume parameters to stored procedure, because the number of parameters to a report is always known, even if some are optional.

     

    There are several ways to handle this, but it will depend on what you mean exactly by "number of parameters is runtime generated". 

     

    If you mean that you have a lot of parameters and some are optional, there is still a maximum number of parameters that can be used, and you can set them all up as parameters of the report too.  I find that it is easiest to have an "impossible" value for an optional parameter, meaning that this parameter is not chosen for this run, rather than a null.

     

    If you mean "it's not a stored procedure with parameters, it's straight dynamic sql", this is in some ways easier! But if you give me an example of what you are trying to do and how you are trying to do it, I can explain more clearly.

     

    >L<M

    Tuesday, December 4, 2007 9:12 AM
  • Thanks Nicholls for your prompt answer.

     

    Ok. I will make my situation clearer..

     

    I have a datatable(in a dataset) which, at runtime, gets individual names as column names. I need to now show these column headers(individual names) onto the report table. I cannot predict what the column names would be or the number of names I will get (as this is the result of a stored proc). Below is a rough representation of how the report should look like.

     

    Name1

    Name2

    Name3

    Name-n

    Value1,1

    Value1,2

    Value1,3

    Value1,n

    Value2,1

    Value2,2

    Value2,3

    Value2,n

     

    Tuesday, December 4, 2007 1:00 PM
  • Dynamic column names isn't difficult and has been shown in this forum many times.  If you tell me *how* the datatable receives instructions about its contents I will either illustrate with one possible solution or point you to an appropriate thread.

     

    The other part of the problem is dealing with not knowing how many columns you have.  Is there a maximum number of names/columns you allow?  If not, and if a matrix is not suitable for your needs, this can be difficult.  I have written up several solutions to a "sideways" report (where the number of columns is variable (versus the number of rows) but if I understand your representation in this case both rows and columns are variable in number, is that correct?.

     

    >L<

    Tuesday, December 4, 2007 3:36 PM
  • For the first part of your answer, I have a list which contains the individual names. I read that and generate the columns in the datatable. I have dictionaries which store the values with the keys as the column names and hence can populate the datatable. Hence, in a way, that answers the second part of your question - i have variable columns and fixed set of rows. I also dont have a maximum limit on the number of columns.

     

    I am a bit pressed for time as well - deadline looming!! Is there a way I can have a chat with you to get this sorted?

     

    Thanks

    Wednesday, December 5, 2007 3:01 AM
  • >>

    I am a bit pressed for time as well - deadline looming!! Is there a way I can have a chat with you to get this sorted?

    <<

     

    You have absolutely no idea how funny that is .  You're lucky I'm even able to get on line.  I am typing this on the bare floor of my house, in the middle of moving, truck comes tomorrow and we'll be driving all day the next day.  I can't possibly schedule a chat and my deadlines are pretty intense at the moment.

     

    Let me see what I can do to help, though:

     

    1) Part one of the problem, as I said, is pretty simple.  You have a list that generates the individual names, and this list provides some way to find out the appropriate column names for this report run.  

     

    Therefore it is dead simple for you to create a second dataset to attach to this report, containing a single row and the same number of columns as the first dataset, where each column contains the metadata (header label) for that column instead of actual data.  Your table column headers each use an expression that references that dataset -- you will need to use the First() function because you're referencing a second dataset that is not attached to the table, but that's okay, the second dataset only has one row.  So, for example, the third column header textbox might have an expression that looks like this:

     

    Code Block


    =First(Fields!column3.Value, "MetadataDataSet")

     

     

    There are, as I said, lots of ways to do this.  For example, you could have a parameter in the report instead of a second dataset, passing a delimited string of all the column header labels.  I'm sure you can see what the expression for each textbox would be (use the Split function with the appropriate index for each column). 

     

    2) No matter how you do it, these methods of passing the metadata into the report definition only work if you know how many columns you have, max.  You basically set up that many columns and then use Visibility on the column level, interrogating the same metadata, to figure out which columns you wish to hide for a given run, because they are "excess to requirements".  You can't do this if you don't know the max number of columns, so you need to take a completely different approach.

     

    The best way to do it, in my opinion, is to dynamically load and alter the RDLC definition to suit each run.

     

    This is not as hard as it may sound.  The RDLC is an XML document; you can load it as a document and insert appropriate nodes for your columns. The base document that you would alter for each run would probably be the minimum number of allowed columns (perhaps two), and you would insert additional nodes for the columns as required for each run. 

     

    In this approach you really don't have to read the metadata for the column headers within the report, i because you can set the column header labels for each column, as literals rather than expressions if you want to, as you insert each column's RDLC definition data. 

     

    Again, the RDL schema is straightforward.  Within your Table element, you will be adding a TableCell in the Details, a TableCell with the column header label in the Headers, and a column definition in TableColumns, for each non-base column you need to add.  You will also adjust the column header information for the "base" two columns, along with those you insert.

     

    If you want to see code doing this, there is a good example (look for "Load Report Dynamically" or "Generate Table" or something like that) on http://www.gotreportviewer.com/.  It includes appropriate/generic generation code for those of you (most of you <sigh>) who like to do things that way.  I personally would do the same thing by writing a simple piece of XSLT and using it on the base RDL.  I would pass the XSLT parameters telling it about the current dataset, possibly simply by serializing the dataset itself.

     

    Once you have adjusted the report definition -- however you choose to do it -- you load the dynamically-created report using the LoadReportDefinition method of ReportViewer.LocalReport.  You will see appropriate code in the http://www.gotreportviewer.com/ example.

     

    I hope this helps,

     

    >L<

     

     

     

    Wednesday, December 5, 2007 5:47 AM
  • Thanks Nicholls for your answer.. it has definitely given me a direction. I didnt intend to give you any impression of ungratefulness - was just super-desperate, thats all!! Appreciate the effort that you are taking to help me out.. Hope you have a great time at your new place. Will update you if I am successful with this issue!

    Wednesday, December 5, 2007 6:15 AM
  • Don't worry; I didn't think you were ungrateful, and I didn't expect you to know about my current situation!

     

    Please report back whether you are successful or not, and I'll respond at some point (just probably not right away <g>).  Other people will be helped by whatever you report (success or failure), and other people probably will chime in to help you if you get stuck.

     

    >L<

    Wednesday, December 5, 2007 3:48 PM
  • Thanks for your posts Lisa, I'm going to be building some dynamic reports as well, but I will be able to use a fixed column structure.   So using the column visiblity property and a metadata concept shouldn't be too hard to implement, however I was wondering if you could recommend a book, or something that I could read on using XSLT.     Since I know little to nothing about it, and I've seen a few commentaries on using it for modifying xml.

    Wednesday, December 5, 2007 5:31 PM
  • Well, like I said, most people will prefer the object model as exposed in the generator code in that gotreportviewer.com sample -- so this isn't the only way to handle changing RDLs -- but I'm very happy to answer your question because in the end XSLT will serve a lot more needs you might have, reporting-related and otherwise, beyond this single issue.,, **

     

    I would check out two WROX books -- one by Jeni Tennison and one by Michael Kay -- as the best ones for learning about XSLT in general.

     

    ** for more about what I think on this subject:

     

    reporting specific:

     

    http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx and

    http://spacefold.com/lisa/post/After-you-learn-to-walk%2c-you-can-run-a-postscript-to-the-QnD-XSLT-walkthrough.aspx 

     

    general:

     

    http://spacefold.com/lisa/post/XSLT-and-other-declarative-languages-neither-elven-bread-nor-kibble.aspx

     

    >L<

     

     

     

     

    Wednesday, December 5, 2007 5:44 PM
  • Thanks a lot,  I saw the object model, but I've got a strange feeling that as time goes by, my boss will want me to stand these reports on their head and make them do the chacha...so I like to know all my options.  This will help out a bunch.

    Wednesday, December 5, 2007 5:47 PM
  • In the blog posts I pointed you to that were reporting-specific, I am actually talking about using xslt to transform the data xml output of the RDL or RDLC, not the RDL or RDLC itself. 

     

    However, I have written a utility that does what you're talking about (uses XSLT on RDLs and RDLCs).  You can find out more about that by checking for RDLDocumenter on my blog (http://spacefold.com/lisa/?q=rdldocumenter) .  It's pretty specialized -- it extracts documentation information or metadata about reports -- so it doesn't do exactly what you want, but it's the reason I know that the structures of RDLs and RDLCs are rational and easy to deal with. 

     

    Plus, you can take a look at the XSLT that I'm actually using in RDLDocumenter.  (Check our downloads page for the deployment file set.) It's sort of "advanced" and it's going from the RDL schema into a completely different schema, rather than adjusting the RDL, but again you'll see that the process is not rocket science and that RDLs are clearly laid out internally. 

     

    Yours would actually be a lot easier to write.  You'd probably use xslt:copy and xslt:copy-to quite a bit, because you'd be replicating a lot of parts of the original RDL as-is.

     

    >L<

     

    Wednesday, December 5, 2007 5:57 PM
  • Hey Lisa

     

    I could get this report working.. But I have to say(very sheepishly!) that I didnt really need to dynamically generate the rdl file as i could use matrix!! I did try it before and it wasnt working for me and I tried it again after that and lo behold! its working! Sorry for wasting your time on a differnent tangent - but learnt so much more though! Thanks!

     

    Thursday, December 6, 2007 8:42 AM
  • Well, I'm glad you got it working -- you know, I actually thought of asking "why not just use a matrix" given your description but figured there was some reason why it wasn't appropriate. There often is.  For example, you can't easily vary the widths of the columns...

     

    You're very welcome, good luck!

     

    >L<

    Thursday, December 6, 2007 9:21 AM
  • Hi Asle81,

    Can you please let me know the steps you followed to get the report working using the matrix.I am also working on a similar report in report server project in VS 2008. Thanks

    Thursday, May 6, 2010 7:13 AM