none
fake data source RRS feed

  • Question

  • I need to display some dynamic info on the report. The only way I've found to do that is create a stored procedure on the SQL server, expect a parameter, and pass the parameter through the report viewer. It works, but it's cludgy, and I must put a text file in the project containing the content of each stored procedure, for reference. Imagine how many silly little stored procedures I will end up with on the SQL server this way. How can I make a fake data source so I can create records or rows in code, without going to the SQL server? Thanks
    Tuesday, July 17, 2007 6:21 PM

Answers

  • I don't mind whether you use a stored procedure or not <s> and you don't have to justify it. 

     

     I use dynamic datasets with reports quite a bit, and I need to set up a dummy within the report definition just to... design the report <s>.  That's why I use literals. At runtime I construct the dataset again and re-bind.

     

    If you are getting that error, you are making some mistake in how you are constructing and re-binding at runtime.  There are a lot of possible reasons, and some of them stem from how much of the original binding is "automagically" done for you, which you need to replace with proper code at runtime if you expect your code to replace the designer cr*p.

     

    Here is one of many, many threads on this forum dealing with the subject.

     

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

     

    >L<

    Wednesday, July 18, 2007 4:44 PM

All replies

  • This is ordinarily very easy to do.  You don't need any stored procedure.  I can't actually tell which (of several possible) reasons you need a fake dataset in this instance, so I'll just give you some simple examples and you can tell me if and y don't apply <g>.

     

    In the simplest case you can build up a dummy dataset using literals, for example you can set up one row of data with a querystring like this:

     

    Code Snippet

    SELECT 'XXX' AS MyTextColumn, 1 AS MyNumericColumn

     

     

    ... with the only requirement being that you faithfully mimic the datatypes and column names that you expect to have at runtime in the "real" data.  If you need several rows you just use a union.

     

    There are all kinds of fun tricks you can use to get a dataset attached to a report with more complexity.  For example, if the "dynamic" data you're providing is the result of parameters, you can reference them in your SELECT along with literals:

     

    Code Snippet
    SELECT 'XXX' AS MyTextColumn,
            1 AS MyNumericColumn,
            CAST(@RPDate AS DateTime) AS Now

     

    ...and if your report has other, "real" datasources you can join a statement like this with other "real" tables, for example:

     

    Code Snippet

    SELECT Recipient,Derived.* FROM dbo.Recipient

       INNER JOIN

    (SELECT 'NVDA' AS MyTextColumn,
              1 AS MyNumericColumn,
              CAST(@RPDate AS DateTime) AS Now) Derived

      ON MyTextColumn = Locale

     

     

    ... and you can do a lot of variations of interesting joins with "real" tables to get a set of rows of any size.

     

    So... if this doesn't give you any ideas, be more specific about your situation <s>. It's almost certainly do-able.

     

    >L<

     

     

    Wednesday, July 18, 2007 5:30 AM
  • I don't like to use stored procedures because I no longer have a discreet data source. I prefer to have whatever the stored procedure provides, in application code, or some application configuration or property. If I use a stored procedure I always copy it to a text file and place in the project for reference.

     

    This report has a conditional where clause. Instead of making it conditional I might use "where (field=@var) or (@var=0)". That might work. But I want the report header to change based on this value. Since it seems rather silly making stored procedures for each thing, I want to make a data source in code, dynamically.

     

    After starting this thread I found a URL where this is explained pretty well: http://codeidol.com/visual-basic/visual-basic-2005/Reporting/Using-Reporting-Controls-in-.NET/ . It is written for a VB desktop app, but was easily changed for a web app. I put the following code in my app (more comments following):

     

      Protected Sub View12_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles View12.PreRender
          If ReportViewer1.Visible Then
             Dim source As New Collections.Generic.List(Of DataSourceOnCallReportHeader)
             Dim j& = Val(DropDownReportAgency.Text)
             Dim a$ = "All Agencies"
             If j > 0 Then
                Dim command As New Data.SqlClient.SqlCommand("SELECT AgName FROM Agency WHERE AgUnique=" & j, connect)
                Dim reader As Data.SqlClient.SqlDataReader = command.ExecuteReader()
                If reader.HasRows Then
                   reader.Read()
                   a = Trim(SqlString(reader, "AgName"))
                Else
                   a = "key not found in agency table"
                End If
                reader.Close()
                source.Add(New DataSourceOnCallReportHeader(a))
                Dim reportSource As New Microsoft.Reporting.WebForms.ReportDataSource
                reportSource.Name = "DataSet2_DataTable1"
                reportSource.Value = source
                On Error Resume Next
                ReportViewer1.LocalReport.DataSources.Remove(ReportViewer1.LocalReport.DataSources.Item("DataSet2_DataTable1"))
                On Error GoTo 0
                ReportViewer1.LocalReport.DataSources.Add(reportSource)
                ReportViewer1.LocalReport.Refresh()
             End If
          End If
       End Sub

       Public Class DataSourceOnCallReportHeader
          Private StoredReportHeader01 As String

          Public Sub New(ByVal value As String)
             ReportHeader01 = value
          End Sub

          Public Property ReportHeader01() As String
             Get
                Return StoredReportHeader01
             End Get
             Set(ByVal value As String)
                StoredReportHeader01 = value
             End Set
          End Property
       End Class

    I ran this and it worked fine, but, when I deleted the data source from the page, and deleted reference to the extra data source from the report viewer, the report would no longer work. It said no data source was being supplied for DataSet2_DataTable1. Obviously, the report is being generated before it got to my code where the data source is added, and where the refresh method is called.

     

    I like your approach, Lisa. I might be able to use a similar data source having a constant value, yet change the command or SQL statement through code to make the value be what I want. That way, the data source will be there when the report first runs, before it gets to my code and does the refresh.

    Wednesday, July 18, 2007 3:58 PM
  • I don't mind whether you use a stored procedure or not <s> and you don't have to justify it. 

     

     I use dynamic datasets with reports quite a bit, and I need to set up a dummy within the report definition just to... design the report <s>.  That's why I use literals. At runtime I construct the dataset again and re-bind.

     

    If you are getting that error, you are making some mistake in how you are constructing and re-binding at runtime.  There are a lot of possible reasons, and some of them stem from how much of the original binding is "automagically" done for you, which you need to replace with proper code at runtime if you expect your code to replace the designer cr*p.

     

    Here is one of many, many threads on this forum dealing with the subject.

     

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

     

    >L<

    Wednesday, July 18, 2007 4:44 PM
  • I used the following code which works real well:

     

    Protected Sub View12_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles View12.PreRender

    If ReportViewer1.Visible Then

    Dim j& = Val(DropDownReportAgency.Text)

    If j > 0 Then

    OnCallReportDataSource2.SelectCommand = "SELECT AgName FROM Agency WHERE AgUnique=" & j

    Else

    OnCallReportDataSource2.SelectCommand = "SELECT 'All Agencies' AS AgName"

    End If

    ReportViewer1.LocalReport.Refresh()

    End If

    End Sub

     

    I really like that solution. Thanks for helping me with it. I think the report is running an extra time. I should not have to do the refresh method. If I put the code above on the ReportViewer1_PreRender event, the report should come out OK without having to do a refresh. Before I pursued this dynamic data source stuff, if I changed any of the controls, the report would always come back the same, unless I did the refresh method. It makes me think of when I did Crystal Reports version 6 some time ago, and I had to tell it not to save the data with the report. Why would anybody want to save data with a report, anyway? I can't find any place in the project the data might be saved, and I see no option to save data. Why do I need to do the refresh method to make the report come out right?

    Wednesday, July 18, 2007 5:42 PM
  • >>Why do I need to do the refresh method to make the report come out right?

     

    Was that a rhetorical question <g>?

     

    IAC, the answer is probably that the automagic code was triggered to re-bind by being hooked to the refresh event.  I don't know and I really don't care, I don't like to use it <g>.

     

    >L<

    Wednesday, July 18, 2007 5:47 PM