Saturday, July 4, 2009

Dynamic Reports in SQL Server Reporting Services 2008

Problem Summary:
My last assignment was on SQL Server Reporting Services 2008. I was given to create/export direclty (into excel or pdf format) reports from ASP.NET web interface basis the search criterias where number of the columns were not fixed; columns were supposed to come from the rows of queries.
Now prabibly a question tosses in your mind Why SSRS, not the traditional way of COM/office component to write excel file instead of exporting by SSRS things. The answer is to uniform the development/runtime environment with stronger .NET based things. and off course to implement security for report access with SSRS 2008. Also reports can be bind any time with Report Viewer Control.

So what I did:
1. Wrote stored procedure to result data passing the search criterias; off course lots of dynamic sql to make columns based on the rows in the query results for specific input parameters and then populating desired data to those columns based on the certain manipulations.
2. Generated memory stream of .rdl (Report definition language) based on the dynamic schema being resulted after executing the stored procedures created in the first step. It involves lots of C# code to write XML format of rdl formatting and binding the data elements to the report skeleton.
3. Exported report in the desired format(.xls/pdf etc.) using the compiled proxy class of the Report Server Web service Library (ReportingService2005.dll).

Yes It was really a challenging stuff recently I faced.
I would like to share with you in detail if you are doing the similar kind of stuff.

Cheers!!
Sudhir
Posted by at
Labels:

7 comments:

  1. UnknownJuly 18, 2009 at 6:59 AM

    Hi Sir,

    Its nice to hear about you and your detailed technology you working. I have a very basic issue on reporting. I am using reportviewer and rdlc(Report Defination Language Client) which is embedded with visual studio 2008.

    The problem I am facing is- I have to put two header on a order report, Header A for 1st page and header B for rest of the pages. Header A is about 4 inch long and has contain global variable like page number and total page. Now when i hide Header A for 2nd page using visibility expression =IIf(!PageNumber().equals(1),false,true) it works fine it hide the Header A for 2nd page and rest of the pages, but its remain a big white space of 4inch when it get hide.

    Conclusion- Header part doesnot shrink automatically.

    I cannt use the header on body part, because we cannt use global variable Page number on body.

    I hope you understand my problem and find out a way to solve this issue.

    If you guide about grouping of Tables in report it will be great. I have some confusion on table grouping.

    Thanks
    Sanjit

    ReplyDelete
  2. Sudhir Dwivedi's .NET BlogJuly 21, 2009 at 6:24 AM

    Hi Sanjit,

    It's nice to see your comments. I am sorry I couldn't reply you sooner as I was busy in a new project.

    Well, You are creating two headers and setting the visibility by expression which is not the right thing as it left out the blank space for the hidden header.

    So here my suggestion indicates you to use single header not 2. It's short of cover page design based on expressions. so use an additional table to format the Report Cover page and set the Page after break if desired. Again you cannot use global variables in the Table/Tablix. Now you know the limitations and capabilities...

    For more just send your rdlc to me. I 'll suggest you better.
    --Sudhir

    ReplyDelete
  3. VeluworldJuly 24, 2009 at 3:14 AM

    Hi Sudhir, nice to know that you were worked on same kind of assignment.I want to Export to Excel .rdl file content which is published with server. without pressany button & just select on report name it should download into specified location.Can you please let me know how to do that?.

    thanks in advance

    ReplyDelete
  4. Sudhir Dwivedi's .NET BlogJuly 24, 2009 at 5:35 AM

    Velu,

    You didn't mentioned platform you are working.
    However, I have done this on SSRS 2008 and It should work for 2005 as well.

    So, you may follow like this:

    Add webservice reference to your project(say a web based project where you have pages to generate reports):
    http://ServerNameOrIP:Port/ReportServer/ReportExecution2005.asmx.

    The solution is based on Reporting Services WebService. Please browse the above Uri (after putting the right values for the server address) on your browser to check if it's emitting wsdl properly.

    and follow this link for more: http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render(SQL.90).aspx.

    Find the code snippet in the sample class given there:
    result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
    Here format param is file format it accept XML, EXCEL etc.

    Hope it helps.

    Best Regards
    Sudhir

    ReplyDelete
  5. AnonymousOctober 5, 2010 at 1:37 AM

    Hi Sanjit,

    Nice post!

    Can you share with me the approach you have taken in generating the rdl xml schema dynamically? Hvae you used RDL object model, or XML Writer etc?

    Best Regards,

    Mae

    ReplyDelete
  6. UnknownJanuary 19, 2011 at 5:17 AM

    my name is sudhir allso.my id skd0101@gmail.com
    pl. sir

    ReplyDelete
  7. AnonymousApril 13, 2011 at 11:01 PM

    Hi Sudhir,

    I am using SSRS 2005.When the tries to print the report using print button, it is giving me error "unable to load rsclientprint control".

    Can you give me solution for this.
    I have hundreds of online users who use the reports.

    Regards,
    Sanjay Verma

    ReplyDelete

Subscribe to: Post Comments (Atom)

Followers