I’ve been saying that I was going to post about what I learned while writing the RDLDocumenter utility, its test form, and other XMLRSDocs-related bits, because that journey represents a practical FAQ for many different the Reporting Services programming areas.
One of those areas is how you load local report definitions (RDLCs) dynamically in a ReportViewer control. When this question was asked again this week in any “any ideas?” or “can you post an example” sort of way, I decided it was time to write a walkthrough of how RDLDocumenter’s demo and test interface handles these chores.
Look 
at the various buttons in the form, and you get some idea of why RDLDocumenter’s test harness form gives dynamic report loading such a workout:
- second button from the top needs to switch between data sources, loading data from different XML data files, each representing a different RDL to be documented
- third button from the top switches to reporting using a SQL Server instance, rather than an XML data file, to load data about multiple RDLs to be documented
- second and third button also switch between RDLCs (Report1.RDLC handling data for a single report and ReportMany.RDLC, slightly more complex, for multiple reports)
- fifth button switches between locations for the RDLCs, so that you can substitute your own documenting layouts and format from a set on disk from the two embedded resources included in the test form executable.
There’s one additional challenge, which you can’t see in the buttons, forcing the test harness to go “that extra mile”:
- when it switches between data sources, TestHarness.EXE must also dynamically load data connection objects, as well as data sources, in some cases.
Here’s why: I wanted to allow for the possibility of multiple reports without necessarily expecting an instance of SQL Server to be available, so I hit on the idea of delivering a sample database implementation in SQL Server Compact Edition along with the RDLDocumenter files. (Of course you can use a configuration switch to connect to other SQL Server editions if you wish.)
However, CE connections use different. NET classes and I couldn’t be sure that these classes would be available. (As a developer’s tool and teaching device, RDLDocumenter doesn’t install anything on your box; it gives you the information and options you need but can’t assume anything beyond the standard .NET framework.) As a result, it has to do some persnickety error testing, trapping, and user feedback.
That’s the problem to solve — parts of which you may not need in your scenario — from end-to-end.
How does it work?
Here’s your walkthrough of how RDLDocumenter handles these various tasks.
1. Defining the form elements in the layout
The form has two associated datasets, representing the different schemas required by the Report1 and ReportMany RDLCs. They’re actually pretty similar but DataSet1 lacks DataSetMany’s columns representing each RDL’s report filename and datetime stamp of its upload into the database. The form also has two binding sources, one for each dataset, although I suspect this is just for design convenience and not required.
There is also a SQLConnection member object visually defined (dragged and dropped onto the form layout with the other components), with some default properties. This does no harm if it’s not ever used.
The form does not have a SQLCEConnection explicitly defined, since if the required libraries were not available this would bomb at form startup. Instead, I just defined a member of the VB as an object, like this:
Private SqlCeConnection1 As Object = Nothing
‘ see notes in InitializeCEDataConnection
An additional member of the form provides a way to store the user’s chosen override for location of the two reports on disk, should they edit the external copies provided and prefer their versions to the two embedded report resources:
Private reportLocation As String = “”
OK so far?
2. Initializing the ReportViewer
At the form Shown event — note that the Load event appears to be too early — I store the EXE location for later use and override whatever information I’ve used for the ReportViewer control at design time, by issuing a call to a method I’ll use for similar changes throughout the life of the form. In this first call I set the viewer up for the most innocuous default use (the embedded Report1.rdlc resource):
Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
Me.InitializeDefaultXSLT()
‘ the XSLT in use, like report resources, is editable by the user
Me.exeLocation = System.Reflection.Assembly.GetExecutingAssembly().Location
Me.InitializeViewer(“TestHarness.Report1.rdlc”)
End Sub
Notice the use of the executable name in the reference to the embedded resource above. This was the source of confusion in the question I answered this morning, FWIW; if a resource is embedded you have to tell the ReportViewer where it is (in this case, inside an EXE named TestHarness). This requirement has absolutely nothing to do with reports in particular; the call you see above to InitializeDefaultXSLT() has to do exactly the same thing when choosing to load the default XSLT embedded resource rather than an external transform selected by the user from XSLT files on disk.
3. Ready to dynamically switch between RDLCs
Here’s how the viewer is initialized, or, later, re-initialized to use a different report. There are several significant activities demonstrated here:
- First, the ReportViewer’s Reset method is called and a new datasource object is defined on the fly. At this time, no data is actually loaded, we’re just distinguishing between the reports and their potential datasets. We can load different datasets later without necessarily changing the schema or the report.
- The ReportViewer.LocalReport.ReportEmbeddedResource is filled as requested in the method call, to indicate one of the two embedded report resources. Again, in the test form’s scenario, the two reports serve to indicate which of the two schemas and types of data sources are requested. As noted in the code comments (below), the value of this property will be overridden if ReportPath is also supplied. We’ll be filling ReportPath if the user supplied a ReportLocation value by browsing to a directory using the fifth button in the dialog.
- We determine which schema, one report or many, was requested, and bind our new datasource object appropriately.
- We add our new datasource to the ReportViewer.LocalReport.DataSources collection.
Private Sub InitializeViewer(ByVal tsReport As String)
Dim ReportDataSourceX = _
New Microsoft.Reporting.WinForms.ReportDataSource()
Me.ReportViewer1.Reset()
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = tsReport
Me.ReportViewer1.LocalReport.EnableExternalImages = True
Me.ReportViewer1.LocalReport.EnableHyperlinks = True
If Me.reportLocation.Length = 0 Then
Me.ReportViewer1.LocalReport.ReportPath = “”
Else
‘ this will take precedence if used
Me.ReportViewer1.LocalReport.ReportPath = _
Me.reportLocation & System.IO.Path.DirectorySeparatorChar & _
tsReport.Split(“.”)(1) & “.RDLC”
End If
If tsReport.Contains(“ReportMany”) Then
ReportDataSourceX.Name = “DataSetMany_RDLDocPrimary”
ReportDataSourceX.Value = Me.rdlDocPrimaryBindingSourceDatabase
Else
ReportDataSourceX.Name = “DataSet1_RDLDocPrimary”
ReportDataSourceX.Value = Me.rdlDocPrimaryBindingSource
End If
Me.ReportViewer1.LocalReport.DataSources.Add(ReportDataSourceX)
End Sub
4. Dynamic data load, parameter handling, and report display
Once the appropriate report and datasource is available, the test interface can display a report each time the user clicks the second or third button in the dialog.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button2.Click
Me.ReportFromDocRDLXML()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button3.Click
If My.Settings.DatabaseIsCompact Then
Me.CheckSQLCeInitialization()
‘ doing the above as late as possible
If Not IsNothing(Me.SqlCeConnection1) Then
Me.ReportFromDocRDLData()
End If
Else
Me.ReportFromDocRDLData()
End If
End Sub
The only difference between two subs referenced in the button clicks above is that loading from DocRDLXML asks the user to pick the DOCRDL XML file containing the data to load, so I won’t include them here. They both call the same DisplayReport method, which does the work of deciding which type of data was meant and what to do about it.
You can see that the viewer is re-initialized, if necessary, in the process. (The necessity to reinitialize is determined by whether the report name has changed since the last call.) You can also see a simple example of parameterizing the report run programmatically for a local report in this method.
Private Sub DisplayReport(ByVal tsSource As String, _
Optional ByVal tsSourceIsServerInfo As Boolean = False)
Dim p(0) As Microsoft.Reporting.WinForms.ReportParameter
Try
Array.Resize(p, 1)
If tsSourceIsServerInfo Then
Me.GetDataSetFromServer()
If Me.ReportViewer1.LocalReport.ReportEmbeddedResource.Contains( _
“Report1”) OrElse _
Me.ReportViewer1.LocalReport.ReportPath.Contains(“Report1”) Then
Me.InitializeViewer(“TestHarness.ReportMany.rdlc”)
End If
Else
Me.DataSet1.Reset()
Me.DataSet1.ReadXml(tsSource)
If Me.ReportViewer1.LocalReport.ReportEmbeddedResource.Contains( _
“ReportMany”) OrElse _
Me.ReportViewer1.LocalReport.ReportPath.Contains(“ReportMany”) Then
Me.InitializeViewer(“TestHarness.Report1.rdlc”)
End If
End If
p.SetValue( _
New Microsoft.Reporting.WinForms.ReportParameter(“RDLDocInfo”, tsSource), 0)
Me.ReportViewer1.LocalReport.SetParameters(p)
Me.ReportViewer1.RefreshReport()
Catch Ex As Exception
‘ I’ll omit my error message feedback stuff here
Finally
p = Nothing
End Try
End Sub
5. Switching between types of data connections at will
You can see the DocRDL XML being read directly into DataSource1 in the DisplayReport method; this is possible because DocRDL XML is.. dataset shaped. So that makes this pretty easy.
The GetDataSetFromServer method you see referenced when the test form is loading data for ReportMany is just about what you’d expect for loading any dataset, except that it defers to a separate method, GetDataSetFromCEServer if the dialog’s config settings says that it should.
if the SQLCeConnection property has not previously been initialized during the life of the form, the separate CE-handling method must check for availability of the appropriate classes, instantiating the CEConnection object if it can:
Private Sub CheckSQLCeInitialization()
Try
If My.Settings.DatabaseIsCompact AndAlso _
Me.SqlCeConnection1 Is Nothing AndAlso _
Me.Button3.Enabled Then
Me.InitializeCEDataConnection()
End If
Catch
Me.DisableDatabaseReporting()
End Try
End Sub
Notice the test for Me.Button3.Enabled above. We’re doing a fairly expensive check here, so the DisableDatabaseReporting method you see in the Catch above disables the button for the life of the form, along with providing other user feedback. This ensures we never do it more than once.
And, finally, for your reference, here’s the code that does CE class initialization:
Private Sub InitializeCEDataConnection()
If My.Settings.DatabaseIsCompact AndAlso _
IsNothing(Me.SqlCeConnection1) Then
‘ this part is taken care of by Designer-driven code for the other connection
‘ SqlCeConnection1 — we are handling this differently from a normal
‘ SqlConnection in case CE support files not installed.
Dim x As System.Reflection.Assembly = _
System.Reflection.Assembly.GetEntryAssembly(), _
y As System.Reflection.Assembly = Nothing
Try
For Each objDepAssembly As System.Reflection.AssemblyName In _
x.GetReferencedAssemblies()
If objDepAssembly.ToString().ToUpper().Contains(“SQLSERVERCE”) Then
Try
y = System.Reflection.Assembly.Load(objDepAssembly.FullName)
Catch
y = Nothing
End Try
End If
Next
If Not IsNothing(y) Then
Dim ox As New System.Data.SqlServerCe.SqlCeConnection()
ox.ConnectionString = _
Global.TestHarness.My.MySettings.Default.RDLDocCompactDatabase
Me.SqlCeConnection1 = ox
ox = Nothing
Else
Me.SqlCeConnection1 = Nothing
End If
Catch Ex As Exception
Me.SqlCeConnection1 = Nothing
Finally
x = Nothing
y = Nothing
If IsNothing(Me.SqlCeConnection1) Then
Me.DisableDatabaseReporting()
End If
End Try
End If
End Sub
And there you have it
That’s pretty much all the generalizable code in TestHarness.EXE, covering a large number of the most frequently asked questions about dynamic report loading, as I said up-top.
Enjoy.
At some point I’ll post some of what I learned by writing RDLDocumenterDesigner, and by choosing to decorate RDL schemas with custom data — a much longer journey, of which TestHarness.EXE represented a final step.
Please get me the coding for WebForm, LocalReport, DataProvider=sqloledb ,Northwind, selection by Passing Parameter CustomerID to Report and that
all at runtime.
Please Help me.
I need Urgent, Please.
hi there,
What part are you stuck on? Passing the parameters to a webform version of the ReportViewer control is the same as passing it to the winform version except you use Microsoft.Reporting.WebForms.ReportParameter. And whether or not it’s a localreport versus serverreport shouldn’t make much difference either. So… what have you tried that didn’t work?
Hi Lisa,
So I am still stuck with this. Firstly, I am working with .RDLC – I am not having trouble switching between reports and their data-sources (which is what you are explaining in your post). I do this the same way:
ReportDataSource.Name = “ReportsDataSet_Schedule”
ReportDataSource.Value = Me.ScheduleBindingSource
Me.ReportViewer.LocalReport.DataSources.Add(ReportDataSource)
Me.ReportViewer.LocalReport.ReportEmbeddedResource = “ReportSchedule.rdlc”
I have one dataset (ReportsDataSet.xsd) in which I have multiple TableAdapters (one for each report)
Each TableAdapter has one ‘Fill’ Query which is basically just a SELECT statement without any WHERE clause; so it selects everything and fills in the report.
Now, Users can select a criteria/filter for a report – the reports can have multiple filters (ex. Date range, balance amount, status… etc). I don’t want to create the ‘Fill’ queries for each of these possible combination of WHERE clauses for each of the report (these can be too many) therefore I need to create the WHERE clause in code and build the query, which will be used to get the data to fill in the report, and then somehow pass this query to either the report or the dataset.
This way I don’t have to create the many possible combination of the WHERE clause in the dataset but can dynamically build it during runtime.
Hope this makes sense.
Thanks for all your help.
You can filter the <b>table adapter in the dataset</b> using a dynamic <i>RowFilter</i> string, if I remember correctly (there is some member for this IAC) — you don’t have to change the .Fill Query.
If you want to do it with the report, you do it by creating a Filter clause, which is not exactly done in SQL, for the <b>table dataregion</b>. This will be a tab in its Properties dialog. You should have a parameter for each possible criterion, optimally with a value for that paramter specifying “don’t use this criterion at all on this run” — I explain this a bit in my “dynamic sql” post. Each item in the filter will have an IIF that checks for this value and returns True for that cas.
Hi,
I am doing a report in which i have to create Wall chart kind of layout and for that i have to place Text Boxes on the
report at run time.Now In my Layout i have some Text Boxes which are going Out of the page(Starts from page 1 and Ending on page 2)but when i am looking my report in the Print Preview the Report Viewer is shifting the entire Text Box to Page 2.
Can any one help me out with this…….
Hi Amit,
As a general comment: If you are placing your textboxes at runtime, you probably have to leave a wider “margin for error”, because the different renderers will have different tolerance for small rounding errors.
Some renderers, in addition, must deal with “unprintable areas” of the page or other special constraints for their output target, and this includes the print renderer. I think that Print Preview positions items based on where they would actually print, so this comment about “unprintable areas” applies to it as well.
I can’t really say more than that without knowing more about your exact layout and situation. Also, I can’t remember creating a box that “goes through” two different pages, so you would have to describe exactly how you configured and placed the box.
This is a good question to ask on the MSDN forums, rather than my blog; many people may have experiences to share that are relevant to your layout <s>.
HTH.
Hi i want to display subtotal of first page in the top of next page in rdlc report and i am using page break of 10 in every page.
Thanks
uma,
Sure, uma. I’ll post a quick item about that. It’s an easy one <s>.
Hi L
plz… send me the code soon its urgent.
Thanks
uma,
Hi L
how can i do paging frm one report to another report in rdlc report i am using whatever they have given in this websites
http://www.codeproject.com/KB/aspnet/DrillThroughReport.aspx
http://geekswithblogs.net/robz/archive/2007/11/21/Reporting-Services-2005-BUG-Report-Linking-and-Paging—ASP.NET.aspx
but i am getting these errors
Failed to convert parameter value from a SqlParameter to a Int32.
plz…explain it properly.
Thanks,
suma,
Suma, re:
>>plz… send me the code soon its urgent.
I wrote the answer to your question in a separate blog post.
http://spacefold.com/lisa/Sometimes-the-magic-works
As far as the other question you’re asking… why don’t you ask the authors of those posts? I will help if you really need it but I’ve done more than my share of debugging other people’s books in the past and don’t wish to start a new career debugging other people’s blog posts <g>.
hi,
subject :- ReportViewer cant open Report simulteniously why(data passes is same only report type change bar to pie or pie to bar)? plz see
i’m showing report on basis of dropdownlist(Bar/pie) but it shows only 1st time(correct), second time it not show second report,but shows data in 1st report only why ?
plz check my following code & guide me to solve this
ReportViewer1.ProcessingMode = ProcessingMode.Local
If DropChartType.SelectedValue = “Bar” Then
ReportViewer1.LocalReport.ReportPath = “Report1.rdlc”
ElseIf DropChartType.SelectedValue = “Pie” Then
ReportViewer1.LocalReport.ReportPath = “Report2.rdlc”
End If
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(reportDSCab)
ReportViewer1.DataBind()
ReportViewer1.DocumentMapCollapsed = True
ReportViewer1.LocalReport.Refresh()
ReportViewer1.Visible = True
bhushan
Hi Bushan,
I have no idea <g>. But if you e-mail me a runnable example, I’ll try to find out, okay? Best I can do.
Wait — Bushan — could you be missing a Reset? Please note that in step #3 of my narrative and code, above.
And please don’t tell me there is no such method. That’s an Intellisense problem, not a “real” problem…
hi lisa
your report swaping thing, page break and subtotal on next
page was realy help me lot. Thanks for you efforts.
Pratik
Oh, thank you for writing Pratik. I am sorry that I have not had much time to blog in the last couple of months (starting new job) and hope to be writing more regularly soon.
Hi Lisa,
I need help in creating columns dynamically in report viewer, but i have created RDLC statically. I am reading the data from XML file and only at run time I will be knowing columns count. Please guide me in this.
Thanks in advance,
Vij
Hi Lisa,
I need help in creating columns dynamically in report viewer, but i have created RDLC statically. I am reading the data from XML file and only at run time I will be knowing columns count. Please guide me in this.
I am working on C#.
Thanks in advance,
Vij
Hi Vij,
Dynamic column counts are one of those things there are a lot of different ways to take care of. I will try to post something about this as a separate article.
Lisa,
Thanks for your reply. It will be great help if you post article on dynamic column adding in static RDLC. We need this badly in our project.
Thanks,
Vij
It’s done already, Vij <g>. See http://spacefold.com/lisa/Walkthrough-A-Simple-ish-Runtime-Quick-Report — hope it helps.
Lisa,
Thanks a lot for your quick reply. I am sorry that I didn’t give you XML details. This post was helpful.
Thanks,
Vij
No prob, Vij, even with your XML details I was likely to try to make the response as “generic” as possible ;-).
Hi,
This is in respect to the SSRS returning dataset through code assembly and capturing it on server reports and then making use of it. I am able to return the dataset to the rdl file through an assembly. But I am trying to figure out a way to use that dataset information in table. So that I can use the same returned dataset in rdl file table to get the output. Is that possible, if yes then please tell me the better approach to make it happen. Thanks in advance.
Regards,
Bhupesh
Hi Bhupesh,
The only way I can think of right off is to have a generic RDL that is similar to what you see here, for Vij http://spacefold.com/lisa/Walkthrough-A-Simple-ish-Runtime-Quick-Report
In that post, where you see expressions like this:
=Fields(
Parameters!WhichColsUnknown.Value.Split(“,”)(2)).Value
… you would instead be using calls into your custom code which would figure out what to display for each column.
On the other hand, the exact approach described in that post, which brings the dynamic dataset preparation *outside* the realm of the RDL entirely, might work quite well for you, as is.
If neither suggestion works for you, and if you write me an e-mail describing your situation, I’ll try to think of something else.
Can we open the SSRS reports in the print preview mode directly from the code instead of clicking the print button?
Hi
I want to display multiple reports in one report viewer in different Report Title..
Hi Bhavna and Natarajan,
I’m not sure I understand Natarajan’s question, do you mean you want to combine reports? Can you be a little more explicit about the presentation you’re looking for?
Bhavna, I think the answer is “yes” but can we start by clarifying: is this a WinForm or a WebForm report viewer object — what do you have in mind?
Hi Lisa,
I hope the topic is still open for discussion though. Someone said that the Reset method is the key to solving the “A data source instance has not been supplied for the data source…” problem. But how do you do a Reset when no such methods exists for the WebForm vesion of the ReportViewer control?
Then, would the ReportViewer have problems when you add datasources to it this way?:
ReportViewer.LocalReport.DataSources.Add(New ReportDataSource(“DataSetName_DataTableName”,”ObjectDataSourceID”))
Thanks.
Faris.
hi Faris,
There *is* such a method for the WebForm version of the ReportViewer. As I explained to Bushan in comments in this very post, it’s just an intellisense problem, not a real one.
I have definitely added datasources for local mode in the web version of ReportViewer also. I’m not sure what your issue is here, what you’re concerned about?
Hi Lisa,
How can I access reports from another project directory.
I get “..report definition not provided..” error even after using name space for that project and fully qualified report name. Using localReports.ReportPath=”c:\dir\dir\report.rdlc” works fine but path gets hard coded in source file.
With Crystal reports I am able to do by creating instance of that report and using namespace for the report.dll(reports project). Any suggestions …
Pravin
Hi Pravin, I think I’ve done this but would have to check. It should work with the same exact requirements as any other built-in resource.
When you build in a resource by navigating to it from elsewhere, even if it is an icon file, typically a copy is created in your project automatically and it is that copy that you are referencing, not the original. So there can be pathing, but it is relative inside the DLL.
If ReportViewer doesn’t work with resources built into other DLLs (and I really don’t remember off-hand, I’ll have to check) you could take the stream approach: get a copy of the resource from another assembly at runtime, stream it to the control. Does this help?
Hi Lisa,
Thank you very much for replying. As with the Crystal reports, it generates .cs file and I can instantiate report using new once other dll is refenenced in main, which is not the same case with .rdlc as it’s basically xml file. Streaming will work. I have winform in main app with five tabbed reports and trying to avoid streaming and hard coding path like “c:\dir\.\r1.rdlc”. If you have any suggestions let me know.
Thank you
Pravin: sorry I missed this before. I’m not sure why you think you have to hard code the path like that. I said *relative* pathing.
Fremkaldelse: You mean, from the ReportViewer? And this may indeed be something different between Win and Web forms, so which one?
I am trying to work on the webform version of the reportviewer. There is good information in the comments you answered but Somehow I keep getting bugs in running them.
Hi,
Interesting article…..I was recently working on a reporting web application that uses the Report Viewer ( ReportViewer ) control that ships with SQL Server Reporting Services (SSRS). The Report Viewer control was rendering perfectly when I was developing using Visual Studio 2008.
I am trying to work on the webform version of the reportviewer. There is good information in the comments you answered but Somehow I keep getting bugs in running them. Emo Clothes
my English is not good but i will try?
I am a newbie on this asp report server
I have a dropdown list on a asp.net c# and i want to filter by report data with the dropdown list value
here is some code i play around with but it is not working please help
//ObjectDataSource1.FilterExpression = “equip_name like ‘%” + cbo_Equipment.Text + “%'”;
// ReportViewer1.LocalReport.DataSources.Equals(“SqlDataSource1.FilterExpression = equip_id like ‘%” + cbo_Equipment.Text + “%'”);
//ReportViewer1.LocalReport.DataSources.Equals(“ObjectDataSource1.FilterExpression = equip_id like ‘%” + cbo_Equipment.Text + “%'”);
// ReportViewer1.LocalReport.DataSources.Equals(DataSet1.DataTable1DataTable.Equals(“equip_id like ‘%” + cbo_Equipment.Text + “%'”));
Hi SHivas,
Your English is fine but I don’t think the code you’re showing makes any sense <g>.
ReportViewer1.LocalReport.DataSources.Equals(“SqlDataSource1.FilterExpression = equip_id like ‘%” + cbo_Equipment.Text + “%'”);
… to filter a data set, I wouldn’t imagine any code like this. LocalReport.DataSources is a collection of objects. What exactly is supposed to be happening in this code?
Why don’t you “feed” the report the contents of your combobox by using a report parameter, instead? Work out the filter expression inside the report?
Or, instead, filter the dataset outside the report, clone or copy the filtered set to a different one, and the target one (already filtered) to the report? Since you’re using local data already and since you’re more comfortable working outside the report, this would seem to be the natural approach.
I am trying to work on the webform version of the reportviewer. There is good information in the comments you answered but Somehow I keep getting bugs in running them. Emo Clothes…
Well, Ed, if you tell me what’s wrong (symptom? Code you’re using? error message?) I’ll try to help.
hi! Lisa! ican’t figure out whats happening! i simply want to pass a sql query to report to generate dynamic data on fly depending upon user’s choices. I’m new to programming. can u explain in a simpler way.
Hi Lisa,
I’ll keep this short and simple:
In a windows application, I am trying to read a bunch of RDL’s and generate classes based on each report.
I am accessing each report the Winforms.LocalReport by the following code:
LocalReport localReport = new LocalReport();
localReport.AddTrustedCodeModuleInCurrentAppDomain(“System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”);
localReport.AddTrustedCodeModuleInCurrentAppDomain(“Oaccac.Ccm.Report.Framework, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null”);
localReport.ExecuteReportInCurrentAppDomain(AppDomain.CurrentDomain.Evidence);
FileStream f = new FileStream(rptFileNameWithPath, FileMode.Open, FileAccess.Read);
localReport.LoadReportDefinition(f);
my problem is when trying to access the following method:
localReport.GetParameters()
I get an exception ONLY ON SOME Reports, not all of them:
‘Oaccac.Ccm.Report.Framework, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. The system cannot find the file specified
and/or
The report references the code module ‘Oaccac.Ccm.Report.Framework, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’, which is not a trusted assembly.
The ‘Oaccac.Ccm.Report.Framework’ file referes to a custom assembly that some reports use. Some reports that use it work and some dont.
Am i missing something here? do i have the code in the wrong order?
I dont have SSRS set up locally nor do i have a local DB as the purpose to this project is to define what parameters are needed for each report to run.
Thank you,
Rolando
and to add to the last paragraph:
i do not need to generate any data, i just need to see what parameters are needed to execute a report, to allow another program to generate reports.
if you are not sure, are there other techniques to generate reports? doing it this way will allow developers to see what parameters have been changed and also allow quicker loading times from the external application.
Thanks again,
Hi Rolando,
First, I think you’re going through an awful lot of trouble for nothing here. To get the parameters in the RDL or RDLC, why not read the RDL directly ? The schema is published, and it’s very easy to read. Why try to read it into a LocalReport object?
FWIW finding out what parameters are in each report is one of the many things that I designed RDLDocumenter and xmlRSDocs to do.
I don’t know why you’re getting that error, but it’s entirely possible there is a difference between your reports based on how and when (in which version of the Designer or Builder) they were originally created. I have to use a different transform on the 2005 and 2008 versions of the RDL/RDLCs format, and i don’t yet handle tablix controls if I remember right, but you don’t care about that. You just want the params and they are *really* easy to find, so why not do it this way?
Wait — one more reason why you might get that error is that some parameters cascade, using code that references the assembly, and some don’t. So the GetParameters() might be actually invoking that code for some reports and not for others. Just a guess.
In any case, just use a little XPath on the XMl document that the RDL/RDLC really is. Once you have the proper nodeset containing the params you can do whatever you want with them from an object model POV.
My aim in the xmlRSDocs format was to smooth out the original schema to make it easy to report on the metadata in the RDL, so I was thinking “table shaped”, rather than “object shaped” for the best result. But no matter what you want, this is truly the best way.
You ask: are there other techniques to generate reports? And I have to ask: other than what? There are a number of different ways to do it, but it depends on your background, your needs and goals, etc. It also depends on whether you’re doing RS 2005 or 2008 work (because they exposed the object model better in 2008) and I suppose 2010 will be different again.
I don’t really have a clear idea of what you are doing or why you are loading the report in order to read it in for this particular purpose. But I think — if I understand your goal at all — that you should be looking at RS Scriptor to see how this stuff can be taken care of, at least to start. I’m not positive, because RS Scriptor is really designed to “speak” to an RS server. But you’ll see how you could d/l the RDLs from the server and also interrogate them directly from the server to find out what their params are.
I think RS Scripter will show you how to get what you need by talking to a server, at least. Here you go http://www.sqldbatips.com/showarticle.asp?ID=62
If not, you’re sort of in luck on that point, in that I am working on a blog post that does something similar and definitely messes with looking at the report parameter definitions from a bunch of different angles. It didn’t start out to be an important part of the post but it is turning out that it’s the biggest deal of the solution to the original question <g>. There are actually three different parameter representations being addressed in that scenario: the “full set” (which is the one you want), the “current set” (including user’s current choices), and the “default set”. I do load the report to a ReportViewer.ServerReport in that scenario, because the user is actually looking at it, and then I call the ServerReport.GetParameters() method to get the “full set”. You don’t need any RS locally deployed for that, so I’m not sure I see your problem.
Hi Lisa,
Thank you for your quick and informative reply.
The reason we need to get the parameters ahead of time is because we have various teams, and many developers on each one.
Keep in mind reporting team is secluded to just that.
If our reporting team changes a report and adds/removes a parameter, they can just run this tool which will generate a class for each RDL, allowing other teams to be notified there has been a change (via compile time error). If we didnt have this we would have to depend on verbal notifications or emails, QA, etc… Which can then have the possibility of having a runtime error.
Anyway, the good news is that we managed to figure out the issue. 🙂
I removed this line of code from the above sample:
localReport.ExecuteReportInCurrentAppDomain(AppDomain.CurrentDomain.Evidence);
and added the following line of code to the custom assemblies (Oaccac.Ccm.Report.Framework) AssemblyInfo.cs class:
[assembly: System.Security.AllowPartiallyTrustedCallers]
The order of my code was changed also, not sure if this matters:
LocalReport localReport = new LocalReport();
FileStream f = new FileStream(rptFileNameWithPath, FileMode.Open, FileAccess.Read);
localReport.LoadReportDefinition(f);
localReport.AddTrustedCodeModuleInCurrentAppDomain(“System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”);
localReport.AddTrustedCodeModuleInCurrentAppDomain(“Oaccac.Ccm.Report.Framework, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null”);
ReportParameterInfoCollection rptParamInfoCollection = localReport.GetParameters();
foreach (ReportParameterInfo _reportParameterInfo in rptParamInfoCollection)
{
//… some code ….
}
In the future, when I get some spare cycles, I will read the RDL’s as XML, and get the ReportParameters. This will be much faster, but since this is only being generated at compile time, it is not as high priority.
Thanks again for your help, take care,
Hi lisa,
I have created report with subreport.The subreport contains 3 to 4 tables which are static.2 tables columns are dynamic.Till static i finished.din’t know how to proceed with dynamic columns.
based on stored procedure table columns will get changed.
How can i do this in code behind.
Thank you
Logi
Logi,
1 – Where are the dynamic columns? In the subreport or the main report? IOW, what difference does the subreport make here?
2 – Is this local or remote mode? >L<
Hi Lisa,
Thanks for your reply.Dynamic columns are with subreport.doing group by in Main Report ,based on that subreport will show the detail.
It is in local mode
Sorry, Logi, can we try this again please:
>>Dynamic columns are with subreport.doing group by in Main Report ,based on that subreport will show the detail.
>>It is in local mode
You’re saying that the subreport shows the summary and the Main report will show a dynamic set of detail based on what occurred in the subreport? Or do I have this backwards?
If I have it right, how does the subreport know what to show? IOW, what kind of parameters do you send from the main to the subreport?
Considering that you are in local mode, your main report should have everything it needs to evaluate what the local will show, but that is probably because I don’t yet know how you invoke the sub report (what choices the user is making at that point, and what type of information is sent to the sub as a result).
I think it would be best if you sent me details e-mail.
Hi Lisa,
After much searching I can’t find an answer to my question and I thought maybe you could help.
I am querying a measurements database in my web app that returns a table for a product where column 1 is an integer ID number and the rest of the columns are different measurements for that ID number. Note there may be several sets of measurements per ID number. I don’t know how many columns of measurements will be returned as different products have different tests. I want to dynamically create a reportviewer scatterplot for each measurement vs ID number. How do I do this with VS2005 and VB? The end result I want is a page full of seperate graphs for each measurement. I am making 2 versions of the app (for learning purposes) one web based and one stand alone form. Its the web one I really need as I can use a chart object in the form, but I guess it will be similar, i.e. created in the VB code. I have created other controls dynamically but this one is a different beast.
You are my last hope Lisa… lol… (by the way upgrading VS2005 isn’t an option, its a corporate thing).
Many thanks…
Paul.
very good info for who learn codes also made me find my solution inmy codes thank you
Lisa, your article is very informative. But I am working of an interesting nature. Here is what I got and was wondering if you can help.
I have a report that is using in the references system.security and calling a code block do decrypt a SSN. The report works fine on the server and locally but when I switch databases and call a different one with out switching the report data defination to the new database I get the error in the report viewer for the local processing that the report references the code module “system.Security, blah, blah”, which is not a trusted assembly. I have added to the assemblyinfo.vb the following: <Assembly: AllowPartiallyTrustedCallers()>
I have added to the report viewer module the following:
rptViewer.LocalReport.ExecuteReportInCurrentAppDomain(AppDomain.CurrentDomain.Evidence)
rptViewer.LocalReport.AddTrustedCodeModuleInCurrentAppDomain(“System.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3aZ”)
I have added to the rssrvpolicy.config the follow:
<PermissionSet
class=”NamedPermissionSet”
version=”1″
Name=”TestPermissions”>
<IPermission
class=”SecurityPermission”
version=”1″
Unrestricted=”true”
/>
</PermissionSet>
<CodeGroup
class=”UnionCodeGroup”
version=”1″
PermissionSetName=”TestPermissions”
Name=”TestCodeGroup”
Description=””>
<IMembershipCondition
class=”UrlMembershipCondition”
version=”1″
Url=”C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Security.dll”
/>
</CodeGroup>
The only thing I did not do is the assert and I am not sure where that goes.
If anyone has done this before it would be a great help.
Thanks
Thank you very much for replying. As with the Crystal reports, it generates .cs file and I can instantiate report using new once other dll is refenenced in main, which is not the same case with .rdlc as it’s basically xml file. Streaming will work. I have winform in main app with five tabbed reports and trying to avoid streaming and hard coding path like “c:\dir\.\r1.rdlc”. If you have any suggestions let me know.
Well spoken Lisa, thanks Nice tutorial thanks for the code snippets. Its very helping.
You might want to take a look at http://www.RptGen.com
Thanks
Thanks Myron, I did take a look, and it’s interesting.I think I would need the source version to judge whether it was flexible enough for me to adapt to my use, so I probably won’t be investigating much further.
But it’s definitely one of those products that prove my point about how the designers of SSRS expected us to do some work, and gave us the tools to do it, to come out with the final result — not just rely on the initial work done by MS.
Hi Lisa,
How r u?????
I want to create a report according to the sql query for the selected columns, the number of columns for the tables are in large count….
So, please help me I am waiting for your reply
Hi Lisa,
How r u???????????
I want to create a report from a sql query of oracle database for selected columns.
So, plz help me….
Sandeep
Hi Sandeep,
Are you having trouble creating the datasource to point to Oracle? Or what?
>L<
L,
I’m getting an error on some reports when pressing the find control button in the report viewer tool box. Object reference not set to an instance of an object. The report loads fine and prints fine, but will get the error if you search the document. I created a test application and added two reports. One that requires no parameters searches the document fine, but the second that has parameters throws the error.
This form produces the error
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
this.usp_LotListingByAgeTableAdapter.Fill(this.tKMapDataSet.usp_LotListingByAge, “xdr”, “01”, “All”, “09/06/2010”, “09/06/2011”, “All”);
this.rptViewer.LocalReport.SetParameters(ViewerParamters());
this.rptViewer.RefreshReport();
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}
private void LoadReport()
{
try
{
this.usp_LotListingByAgeTableAdapter.Fill(this.tKMapDataSet.usp_LotListingByAge, “xdr”, “01”, “All”, “01/12/2011”, “09/06/2011”, “All”);
this.rptViewer.Reset();
ReportDataSource reportSource = new ReportDataSource();
reportSource.Name = “usp_LotListingByAge”;
reportSource.Value = this.uspLotListingByAgeBindingSource;
this.rptViewer.LocalReport.DataSources.Add(reportSource);
this.rptViewer.LocalReport.ReportEmbeddedResource = “TestReportApplication.LotListingByAge.rdlc”;
this.rptViewer.LocalReport.SetParameters(ViewerParamters());
this.rptViewer.Name = “rptViewer”;
this.rptViewer.RefreshReport();
}
catch (Exception ex)
{
MessageBox.Show(this, Convert.ToString(ex), “Error Report”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public Microsoft.Reporting.WinForms.ReportParameter[] ViewerParamters()
{
Microsoft.Reporting.WinForms.ReportParameter[] rp = new Microsoft.Reporting.WinForms.ReportParameter[7];
try
{
rp[0] = new ReportParameter();
rp[0].Name = “companyList”;
string CompanyList = “xdr”;
rp[0].Values.Add(CompanyList);
rp[1] = new ReportParameter();
rp[1].Name = “yardList”;
string YardList = “01”;
rp[1].Values.Add(YardList);
rp[2] = new ReportParameter();
rp[2].Name = “OwnerList”;
string OwnerList = “All”;
rp[2].Values.Add(OwnerList);
rp[3] = new ReportParameter();
rp[3].Name = “StartDate”;
string StartDate = “09/07/2010”;
rp[3].Values.Add(StartDate);
rp[4] = new ReportParameter();
rp[4].Name = “EndDate”;
string EndDate = “09/07/2011”;
rp[4].Values.Add(EndDate);
rp[5] = new ReportParameter();
rp[5].Name = “SexCodeList”;
string SexCodeList = “All”;
rp[5].Values.Add(SexCodeList);
rp[6] = new ReportParameter();
rp[6].Name = “DisplayParameters”;
Boolean displayP = true;
rp[6].Values.Add(displayP.ToString());
}
catch (Exception ex)
{
MessageBox.Show(this, Convert.ToString(ex), “Error Report”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return rp;
}
private void btnReload_Click(object sender, EventArgs e)
{
LoadReport();
}
}
This form is fine
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
this.vwLotsActiveTableAdapter.Fill(this.tKMapDataSet.vwLotsActive);
this.rptViewer.RefreshReport();
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}
private void LoadReport()
{
try
{
this.rptViewer.RefreshReport();
}
catch (Exception ex)
{
MessageBox.Show(this, Convert.ToString(ex), “Error Report”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnReload_Click(object sender, EventArgs e)
{
LoadReport();
}
private void btnOtherReport_Click(object sender, EventArgs e)
{
Form2 frm2 = new Form2();
frm2.Show();
}
}
Sorry for the c# I can recreate in vb I have two applications both are doing it one is vb the other is C# Any advice hold be helpful.
Thanks,
Brad
Hey Brad,
I don’t mind C# at all :-).
I doubt I am going to figure out what’s wrong based on the code, though. I wouldn’t spot it whether it was C# or VB <shrug>.
Here are some questions I have for you:
0 – you’re renaming the report viewer control in the LoadReport method in the “bad” form. What’s its name before, and what does the Load method “think” the object is called? Just a thought.
1 – are the parameters cascading? (is there a dependency from one parameter to the next)?
2 – if you hide the parameters and set some default values, do you still see the same error?
3 – if you *add* some parameters to the other report, even if they have no use, do you see the same error on the other report? If you don’t, and if the first report’s params are either (a) query-driven or (b) cascading, can you repro the situation by adding either (a) or (b) to the dummy params?
4 – What happens if you move the code that you currently have in the Load to the Init, or someplace else? I would actually try moving it to a “Refresh” button, first, so that it’s not in the chain of events that happens when the form is rendered at all. I realize that this is not acceptable for production use, it’s just for a test.
5 – Does the error happen *every* time in the “bad” form, or only when you’ve used the ‘reload’ button, or only when you have *not* used the ‘reload’ button? I think you can see where I’m going with this.
Those are my thoughts in the middle of a workday, anyway. I could try debugging if you wanted to send me a runnable example. I can probably fake the data part.
Lisa,
Thanks for your response I kept playing with the test report knowing I had done something small and stupid until I found it. In the actual report I had sized a field with a width of one in the group row side of the tablix. Deleting the row fixed my problem, but I appreciate the quick response and the help. Your article helped me out alot when I first got started and when I hit a wall like this one I often come back to review it.
Thanks,
Brad
Brad, I’m glad you found your problem. Sometimes those “1 pixel off” problems in an RDL/RDLC are impossible to find or fix without recreating the whole report from scratch!
hi,
i am stuck while using ms report viewer.. how can i use the where clause in the dataset or the report..
i dont have any idea i have tried a lot but no any positive response.. plz help me with this
arpan man sainju :
[quote]hi,
i am stuck while using ms report viewer.. how can i use the where clause in the dataset or the report..
i dont have any idea i have tried a lot but no any positive response.. plz help me with this[/quote]
I will try to help but… I can’t tell from what you’re saying here whether it’s a local or server report, etc. Also, as I have said consistently on this blog, you need to tell me WHAT you tried and WHAT happened. This way I can figure out what went wrong without wasting time, and I’ll also have a better idea of what you are actually trying to accomplish. Feel free to use my contact page to write to me with details if you need to.
Nice comments – really funny !