Solved

Creating a report dynamically (SQL Reporting services)

Posted on 2004-08-23
10
339 Views
Last Modified: 2008-01-09
I have a requirement in which the Report needs to be generated dynamically based on the input parameter. The DataTable (with N number of columns) will be constructed in the .Net layer. I need to generate a report based on this DataTable.
Note : The column size will increase or decrease. Column names will be known in the runtime only.
0
Comment
Question by:prashanthrh
  • 3
  • 3
10 Comments
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11880359
I would suggest using Crystal Reports.  It ships with VS.NET and is intended for this functionality.

Regards,

Aeros
0
 

Author Comment

by:prashanthrh
ID: 11899574
My requirement is using SQL Reporting service i have to do this.

Anybody can help ??????????
0
 
LVL 1

Expert Comment

by:Gangles
ID: 12162355
Hey prashanthrh,
I had the exact same problem.  You have to do two things to make this work.  First you must create your own customer data extension which takes your dataset as the connection string, and then you create a function within your extension to turn that dataset(in xml format) into a dataset that Reporting Services can access.  Once you have done this you should create a template report/RDL file for that dataset.  Now within your ASP.Net application, when you have your dataset you must load up your template RDL locally into a XMLDocument and customise it to suit your dataset, adding in Column names etc.  Then you must upload this RDL to the server as a new report, also adding a new datasource(using your custom extension) which has your dataset in xml as the connection string, if you then open your URL in a browser you should be able to see it in the fully rendered.  Sorry for only giving you a rough overview, but it would take me a few pages to explain it all fully.

Good Luck,
Gangles.
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:prashanthrh
ID: 12178501
Hi Gangles,
    That sounds cool. But the problem here is that when multiple users are viewing at the same time there will be a clash between the report shown to different users. There will be a slight delay when u change the rdl and upload it to the Server. So if two users request for two different reports at the same time the second user may see the report which has been requested by the first guy.


0
 
LVL 1

Expert Comment

by:Gangles
ID: 12180151
Hey,
The way you do this depends on your senario.  For example, I am using Reporting Services for a reporting site that I am creating, I am not using the portal that comes with Reporting Services, so the users can never see the list of reports on the portal site.  The report that a user will want will always be, 90% of the time anyway, dynamic.  So each time I create a report I name the RDL using a GUID, this way there should never be a clash of names, I then create the path/url for the new report and open it in a new browser window.  As for the delay, unless you are using fibre optics there will always be a one, the delay for me currently is about 2 seconds at the most, very minimal.   I hope this helps, the way I have done it here makes sure that there can be no clash between names, originally I had one name for the dynamic report and I realised pretty quickly that, as you mentioned, there could possibly be some sort of clashes, but the guid naming scheme has worked fine for me so far.  By the way remember to create a job on the db to delete the dynamic reports at the end of every day, otherwise it could get pretty messey.
0
 

Author Comment

by:prashanthrh
ID: 12198849
Thats cool man. Even though its a round about way to the requirement, it solves the purpose. But actually i wanted some solid method to execute this one without any physically creating the files and then uploading during the runtime. One more thing,  if u r having the application web server on one machine and the sql reporting servers web directory on other machine and, on a network where the traffic is heavy its preety slow.
I think may be in the next version of the sql reporting service, MS will have some solutions for dynamic things.
Any way thanks for it.
0
 
LVL 1

Accepted Solution

by:
Gangles earned 500 total points
ID: 12199149
The first thing I tried to do aswell was to try and have Reporting Services dynamically create the report, even dynamically size the columns, but both of these features are non existant in the current version, I am sure that they will include it in the next version, I am sure we are not the only two people who have wanted to use RS for this purpose.  Another feature I would like to see is to be able to display data in newspaper style columns inside a list.  I am sure if you have heavy traffic if wil slow it down, there is not much you can do about that, I do have my app on one server and RS on another, but our network is quite fast so you don't really notice any delay.

Good look with development,
Gangles.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now