none
Sideways Report RRS feed

  • Question

  • I have a subreport that is going to return at the most 4 rows.  I want to display them horizontally instead of vertically.  I've done this before by having multiple columns and adding a filter (not the best way).  But this time I do not have any other columns coming back that I can filter by (and now I can't add them to what is being returned).  So does anyone have any suggestions for either of these reports?  Thanks.

    Derek
    Wednesday, July 18, 2007 9:11 PM

Answers

  • Oh ! Thank you for describing your layout more clearly, sorry I guessed wrong.

     

    Yes, actually, I do know how to filter by rownumber.  As I said you can use the SQL Server 2005 method row_number() here to get a generated column that will give you the right information to filter by, and it is my preference to do it this way.  If you are not using SQL Server 2005 there are other ways.  In Oracle there has been a ROWNUMBER function much longer, btw. 

     

     I showed a way to do derive this generated column on another thread... you can check the example of doing it in SQL 2000 as well as the 2005 way there; I'm not going to try to work out another 2000 example for *anybody* .

     http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1740217&SiteID=1

     

    In your current case, you can't change the proc, and I understand that.  What about doing the SQL directly embedded in the report, instead?

     

    But let's talk about your layout for this report.  The right way to do what you want to do is with 4 lists, not tables, in the subreport, each one designed to show only a single row, and laid out as you want it to be laid out.  Do you see why? 

     

    You can put your "column headers" over on the left as single textboxes or whatever.

     

    Now you're still left with the question of how to suppress the potential-other rows for each list.  So here's how you do that, the in-report way : 

    • you probably have to make sure that each layout object in each list is marked "can shrink" to make this work...
    • add the following visibility expression into the first list on the left, which has the name (say) list1 -- and be surethe name is case-sensitively correct, otherwise you'll get a *very* misleading error <g>:
    Code Snippet
    =Rownumber("list1") <> 1
    •  I think you know what to do for list2, list3, and list4 <g>.

    >L<

     

     

     

     

    Thursday, July 19, 2007 3:34 PM

All replies

  • By "display horizontally", you mean that if you have (say) 3 columns in the subreport and all 4 rows are in the subreport dataset, you would like to show 12 columns across?

     

    What if you designed the subreport the normal way, and had 4 instances of the subreport going across? 

     

    I am just thinking out loud but something like this:

     

    Each subreport would only ever display one row.  The subreport would have a parameter indicating which row should be displayed.   It could work two ways: Either this parameter would tune the subreport query so that each subreport suppressed all but the appropriate row, *or* all for subreports could return all four rows but use visibility to only display the proper one.

     

    I would prefer the first approach personally; easiest way would be to use row_number() in sql server there if you don't have an obvious alternative in your data.

     

    Would something like this work?

     

    >L<

     

    Wednesday, July 18, 2007 11:33 PM
  • I basically want to have a "table" but use it sideways.  I want the headers to be aligned on the left side of the report, and have each row be a new "column" going to the right.  So if I have three fields and it returns four rows, I would want a column with 4 columns and 3 rows.  I hope that helps describe it more.

    I can't change the proc or create a new one because we need to "minimize database code changes" because we need to have this one proc support our legacy app and the new one we are developing.  It sucks.

    I've done what you suggested (four instances of the same report that return all four rows) and use filtering to get the right row for each subreport.  But I don't know how to filter based on rowcount (and I don't return any other "fields" that I can filter by).  The "script" language RDLC uses isn't very user friendly, so do you know how to filter by row number?

    Thanks for the help,

    Derek
    Thursday, July 19, 2007 1:02 PM
  • Oh ! Thank you for describing your layout more clearly, sorry I guessed wrong.

     

    Yes, actually, I do know how to filter by rownumber.  As I said you can use the SQL Server 2005 method row_number() here to get a generated column that will give you the right information to filter by, and it is my preference to do it this way.  If you are not using SQL Server 2005 there are other ways.  In Oracle there has been a ROWNUMBER function much longer, btw. 

     

     I showed a way to do derive this generated column on another thread... you can check the example of doing it in SQL 2000 as well as the 2005 way there; I'm not going to try to work out another 2000 example for *anybody* .

     http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1740217&SiteID=1

     

    In your current case, you can't change the proc, and I understand that.  What about doing the SQL directly embedded in the report, instead?

     

    But let's talk about your layout for this report.  The right way to do what you want to do is with 4 lists, not tables, in the subreport, each one designed to show only a single row, and laid out as you want it to be laid out.  Do you see why? 

     

    You can put your "column headers" over on the left as single textboxes or whatever.

     

    Now you're still left with the question of how to suppress the potential-other rows for each list.  So here's how you do that, the in-report way : 

    • you probably have to make sure that each layout object in each list is marked "can shrink" to make this work...
    • add the following visibility expression into the first list on the left, which has the name (say) list1 -- and be surethe name is case-sensitively correct, otherwise you'll get a *very* misleading error <g>:
    Code Snippet
    =Rownumber("list1") <> 1
    •  I think you know what to do for list2, list3, and list4 <g>.

    >L<

     

     

     

     

    Thursday, July 19, 2007 3:34 PM
  • Lisa you ROCK!  It works perfectly now!
    Thursday, July 19, 2007 5:27 PM
  • Cool beans <s>.

     

    Looking at what you said in your message again, btw, I am not sure if this is a case of "the RDLC script language not being friendly", since I don't know exactly what you were thinking of...

     

    but it sure confuses the heck out of *me* that you have to use an expression saying when you want something *not* to show in a property labelled "Visibility" <g>.  Why isn't it called "Hidden" and with a default value of false!?!  Oh well...

     

    >L<

    Thursday, July 19, 2007 6:18 PM
  • What I meant was that it's hard to know what I can do in the scripting language.  I've never seen a tutorial or any help that lets me know what capabilities are available and how to use those capabilities.

    I do agree that the label of Visiblity and the expression of what NOT to show is confusing as well.  I thought you had the expression wrong at first, but after I tried it and it worked I just shook my head and muttered "...uh...ok...."

    Thanks again for your help Lisa!

    Derek
    Saturday, July 21, 2007 2:47 AM