Solved

Creating a report dynamically (SQL Reporting services)

Posted on 2004-08-23
10
338 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
File Upload Control on a ASP.NET Overlay Page 1 43
Jquery autocomplete 10 68
Please explain "Multi-Tenant Services" 5 64
Error in page 3 46
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

15 Experts available now in Live!

Get 1:1 Help Now