none
dynamic report RRS feed

  • Question

  • For my web application, I did trial and error a long time trying to have a dynamic where clause in a report SQL statement. I wanted the where clause to change, but I only wanted it used if the value was non zero. My solution was to create a stored procedure with a parameter, put the decision logic there, and let the report viewer wizard fill in the parameter for me, tying it to the proper control on the form. I do not like that solution because code ends up being scattered everywhere, and I no longer have a discreet data source. I had to add a text file to the web project containing the content of the stored procedure, for reference.

    Now I need the report title to reflect the parameter passed. For instance if a zero is passed the title should say "all agencies" and no where clause will be used. If a one is passed the title should be "human services" and a where clause will be used.

    I'm really glad the report viewer wizard was available, and it properly set up passing the parameter to the stored procedure. But this completely bypasses the report. The report has no parameters in it, and the report just uses whatever data source. I do not understand how the parameter gets from the control, goes through the report viewer, and ends up at the stored procedure.

    Another difficulty is the many versions in existence now. Is it SQL Server Report Services? Is it Crystal Reports? Is it Visual Studio? Is it ASP.NET? Is it 2003? 2005? Express? Research produces different answers.

    I am an expert with VB6. For this application I'm using Visual Studio 2005 Web Developer Express. Thank you Microsoft for providing it for free, and my agency will purchase whatever I need, I just want to get this report working. Thanks

    Tuesday, July 10, 2007 2:42 PM

Answers

  • To resolve this I added another stored procedure to return a single row, single field recordset, being either an agency name if the key is non zero, or "all agencies". Then I added another data source to the web page and included that data source in the report viewer. Good? No, not yet. I found out I cannot add a field to the report header. I must add a hidden field to the detail and reference that in the header. What a screwy convoluted way to write an application. And I had to put another text file in the web project containing the stored procedure, for reference. One would think Microsoft could do a better job with their product.
    Tuesday, July 10, 2007 7:28 PM
  • After learning more about data sources, I found the SQL string can be changed dynamically in code, and no stored procedures are needed. Multiple data sources might be necessary, but it is not necessary to connect each one to a stored procedure. I'm much happier with ASP.NET !!!
    Wednesday, July 18, 2007 7:00 PM

All replies

  • To resolve this I added another stored procedure to return a single row, single field recordset, being either an agency name if the key is non zero, or "all agencies". Then I added another data source to the web page and included that data source in the report viewer. Good? No, not yet. I found out I cannot add a field to the report header. I must add a hidden field to the detail and reference that in the header. What a screwy convoluted way to write an application. And I had to put another text file in the web project containing the stored procedure, for reference. One would think Microsoft could do a better job with their product.
    Tuesday, July 10, 2007 7:28 PM
  • After learning more about data sources, I found the SQL string can be changed dynamically in code, and no stored procedures are needed. Multiple data sources might be necessary, but it is not necessary to connect each one to a stored procedure. I'm much happier with ASP.NET !!!
    Wednesday, July 18, 2007 7:00 PM