Solved

Creating a report dynamically (SQL Reporting services)

Posted on 2004-08-23
10
335 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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.…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 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

19 Experts available now in Live!

Get 1:1 Help Now