Solved

Creating a report dynamically (SQL Reporting services)

Posted on 2004-08-23
10
341 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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