none
Custom Code in SSRS

    Question

  • Hi, I am extremely new to SSRS.  I was wondering if anyone knew if you could write SQL inside the Custom Code box and then call the results then to be displayed in the field by the normal methods?  If you can, could you show a brief example of syntax?  Any help is appreciated!  Thanks guys and gals!
    Thursday, July 23, 2009 8:33 PM

Answers

  • Sure.  When you say "write SQL in the custom code box" I'm taking that to mean "writining Visual Basic.NET code in the Code Window to concatonate together a SQL string and then execute the dynamically-built SQL statement for a data set."

    Here's how:
    Write a query in the TSQL query designer that returns the fields you need.  The logic, filtering and sorting don't matter.  Execute the query once to define members of the fields collection.  Close the query designer and on the Dataset Properties dialog click the little function key (fx) by the query window.
    Delete the query text or cut and paste it to build the query any way as you want.  The query must return the same fields as before - but you can sort, filter and write conditional TSQL branch logic til the cows come home. (I've personally never had a cow come home to my place in the subburbs.)

    VB concatonation looks like this:

    ="SELECT Field1, Field2, Foo, Bar FROM <<some complicated query>> " &
      " WHERE Foo > 19 and Bar <" & Parameters!FooBar.Value &
      " ORDER BY Foo, Bar DESC;"
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Friday, July 24, 2009 5:13 AM
  • Yes you can define multiple datasets in each report.  You can then define multiple data regions (list, table, matrix, chart, etc.), each bound to a particular dataset.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 28, 2009 4:15 PM
  • When you create a dataset based on a SQL Server data source, you define the query type as Text, Table or a Stored Procedure.  If the latter, a list of stored procedures in the database are provided for selection.  Once you have designed this dataset, there is nothing that differentiates a dataset based on a stored procedure from a table, query or any other database object.  Each data region (i.e. Table, Matrix, List or Chart) has a DatasetName property that gets set when you use the designer to bind fields to its cells or groups.

    If you are having trouble with this, can you list the steps taken and the specific issue you are seeing?
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Wednesday, July 29, 2009 3:52 PM

All replies

  • Sure.  When you say "write SQL in the custom code box" I'm taking that to mean "writining Visual Basic.NET code in the Code Window to concatonate together a SQL string and then execute the dynamically-built SQL statement for a data set."

    Here's how:
    Write a query in the TSQL query designer that returns the fields you need.  The logic, filtering and sorting don't matter.  Execute the query once to define members of the fields collection.  Close the query designer and on the Dataset Properties dialog click the little function key (fx) by the query window.
    Delete the query text or cut and paste it to build the query any way as you want.  The query must return the same fields as before - but you can sort, filter and write conditional TSQL branch logic til the cows come home. (I've personally never had a cow come home to my place in the subburbs.)

    VB concatonation looks like this:

    ="SELECT Field1, Field2, Foo, Bar FROM <<some complicated query>> " &
      " WHERE Foo > 19 and Bar <" & Parameters!FooBar.Value &
      " ORDER BY Foo, Bar DESC;"
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Friday, July 24, 2009 5:13 AM
  • Thank you Paul!  I am gonna give it a shot today!  Thanks for your help!
    Friday, July 24, 2009 12:54 PM
  • Paul,

    I have another question, can you draw information from more than one dataset per report?  Whether it be a Stored Procedure or other type of DataSet?

    Thanks for your help!

    Shannon
    Tuesday, July 28, 2009 3:46 PM
  • Yes you can define multiple datasets in each report.  You can then define multiple data regions (list, table, matrix, chart, etc.), each bound to a particular dataset.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 28, 2009 4:15 PM
  • Thank you Robert,

    I can insert multiple Data Regions, but I am having trouble assigning the different DataSets to the different regions.  Is there a link to a tutorial that you could send me that I could read on how to do that?

    I have 2 data tables that need to pull from 2 stored procedures.  Both use the same parameter.

    THANKS FOR ALL YOUR HELP!

    Shannon
    Wednesday, July 29, 2009 1:11 PM
  • You can assign a dataset to data table or any data region by following steps,

    1) Click anywhere inside the data region once, you will see extra panel in top side and left side of the data region
    2) now right click on the "square box" on the top-left corner
    3) select "tablix properties"
    4) choose the appropriate data set from Data set Name drop down box

    I hope it answers your quesion!


    Thanks, Praveenkumar Dayanithi MCTS (SQL Server 2005 BI)
    Wednesday, July 29, 2009 1:25 PM
  • Thank you Praveen,

    I have it pointed to the right Dataset (DataBases), but I need to use a stored procedure associated with that database and it is not giving me the option to pick it.

    Any thoughts?

    Thanks!
    Wednesday, July 29, 2009 3:36 PM
  • When you create a dataset based on a SQL Server data source, you define the query type as Text, Table or a Stored Procedure.  If the latter, a list of stored procedures in the database are provided for selection.  Once you have designed this dataset, there is nothing that differentiates a dataset based on a stored procedure from a table, query or any other database object.  Each data region (i.e. Table, Matrix, List or Chart) has a DatasetName property that gets set when you use the designer to bind fields to its cells or groups.

    If you are having trouble with this, can you list the steps taken and the specific issue you are seeing?
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Wednesday, July 29, 2009 3:52 PM
  • Paul, Thanks for your input.

    It worked!  I am displaying my data from 2 different procedures.  Now I am working on a Join to attach a column from each procedure together to tie the info together.  I do appreciate everyones input. 

    I am glad there is some place to go to for beginners like me.

    Thanks again!
    Shannon
    Thursday, July 30, 2009 1:58 PM
  • Good.  Just keep in mind that it's much easier to join tables in a query than to join the results of multiple stored procedures.  Perform your joins in the stored procedure definition and not between existing stored procedures.
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Monday, August 03, 2009 4:18 PM
  • Guys,

    If I wanted to refer to the contents of a textbox in another table that is a member of another dataset, by using the VB.net custom code box,  what syntax would I use?

    Thanks,
    Shannon

    Tuesday, August 04, 2009 6:11 PM
  • You can refer to a textbox (or any other report item) in an expression, like:
    =ReportItems!SomeTextBoxName.Value

    If the textbox is in a table and there are mulitple instances, you can refer to one instance of the repeated textbox, using aggregate functions, like:
    =FIRST(ReportItems!SomeTextBoxname.Value)
    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Tuesday, August 04, 2009 6:24 PM
  • Hi Paul,

    I keep getting this error: "Error 1 [rsReportItemReference] The Value expression for the textbox ‘textbox33’ refers to the report item ‘ReturnTypeTable1’.  Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. c:\documents and settings\huddls1\desktop\report project5\report project5\Report2.rdl 0 0 ".

    I take this to mean that you cannot refer to a textbox containing a value that is derived from another DataSet.  Thus giving me the "Scope" error.  I was going to attempt to draw a comparison between the 2 columns using the Embeded Code section of the Report Properties window.  As far as I know, there is not a problem using VB.net Logic to compare 2 columns from 2 different DataSets is there?

    Thanks Again,
    Shannon
    Tuesday, August 04, 2009 7:46 PM
  • SSRS Report data region (List, Matrix, table) can support only data from one data set at a time which is mentioned in the tablix properties. If you would like to refer to another dataset from current data region the only option we have is sub report i believe.


    Thanks, Praveenkumar Dayanithi MCTS (SQL Server 2005 BI)
    Tuesday, August 04, 2009 7:50 PM