In feedback of my previous blog, Thomas asked:
Mike - I remember some review of this thing talking about it not being programmable with C# or VB.NET. Did you see anything in regards to that?
Yes and no...
You work with report definitions in VS.NET - there are new project templates for building reports.
The VS.NET integration consists of a visual designer for the RDF file, which is in pure XML format. The RDF gets published to the Report Server (via http) and available to clients by navigating to the report server website (http://localhost/ReportServer). There is no "code view" when working in a report project, except for the XML view of the RDF. Its not like an ASP.NET page, there is no VB.NET or C# code-behind file for the report.
Ok, so can I program a report through a C# or VB.NET project? Uh, well, kinda the same answer - yes and no. When I created a new C# project and went to add a reference, there was no Microsoft.ReportingService object space listed in the GAC. I did browse to the assemblies in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ and added a reference to ReportingServicesLibrary.dll (there was a ton of other assemblies there too, this seemed like the most likely candidate. The object browser shows a bunch of classes in the namespaces Microsoft.ReportingServices.Library and Microsoft.ReportingServices.Library.Soap. I'm not sure that is where I would start if I wanted to program these things.
More useful was adding a web reference to http://localhost/ReportServer/ReportService.asmx - the WSDL shows this:
"ReportingService" Description
Documentation
The Reporting Services Web Service enables you to manage a report server and its contents including server settings, security, reports, subscriptions, and data sources.
Methods
- CancelBatch()
- CancelJob(JobID As string) As boolean
- CreateBatch() As string
- CreateDataDrivenSubscription(Report As string, ExtensionSettings As ExtensionSettings, DataRetrievalPlan As DataRetrievalPlan, Description As string, EventType As string, MatchData As string, Parameters As ArrayOfParameterValueOrFieldReference) As string
- CreateDataSource(DataSource As string, Parent As string, Overwrite As boolean, Definition As DataSourceDefinition, Properties As ArrayOfProperty)
- CreateFolder(Folder As string, Parent As string, Properties As ArrayOfProperty)
- CreateLinkedReport(Report As string, Parent As string, Link As string, Properties As ArrayOfProperty)
- CreateReport(Report As string, Parent As string, Overwrite As boolean, Definition As base64Binary, Properties As ArrayOfProperty) As ArrayOfWarning
- CreateReportHistorySnapshot(Report As string) As string
- CreateResource(Resource As string, Parent As string, Overwrite As boolean, Contents As base64Binary, MimeType As string, Properties As ArrayOfProperty)
- CreateRole(Name As string, Description As string, Tasks As ArrayOfTask)
- CreateSchedule(Name As string, ScheduleDefinition As ScheduleDefinition) As string
- CreateSubscription(Report As string, ExtensionSettings As ExtensionSettings, Description As string, EventType As string, MatchData As string, Parameters As ArrayOfParameterValue) As string
- DeleteItem(Item As string)
- DeleteReportHistorySnapshot(Report As string, HistoryID As string)
- DeleteRole(Name As string)
- DeleteSchedule(ScheduleID As string)
- DeleteSubscription(SubscriptionID As string)
- DisableDataSource(DataSource As string)
- EnableDataSource(DataSource As string)
- ExecuteBatch()
- FindItems(Folder As string, BooleanOperator As BooleanOperatorEnum, Conditions As ArrayOfSearchCondition) As ArrayOfCatalogItem
- FireEvent(EventType As string, EventData As string)
- FlushCache(Report As string)
- GetCacheOptions(Report As string) As boolean
- GetDataDrivenSubscriptionProperties(DataDrivenSubscriptionID As string) As string
- GetDataSourceContents(DataSource As string) As DataSourceDefinition
- GetExecutionOptions(Report As string) As ExecutionSettingEnum
- GetExtensionSettings(Extension As string) As ArrayOfExtensionParameter
- GetItemType(Item As string) As ItemTypeEnum
- GetPermissions(Item As string) As ArrayOfString3
- GetPolicies(Item As string) As ArrayOfPolicy
- GetProperties(Item As string, Properties As ArrayOfProperty) As ArrayOfProperty
- GetRenderResource(Format As string, DeviceInfo As string) As base64Binary
- GetReportDataSourcePrompts(Report As string) As ArrayOfDataSourcePrompt
- GetReportDataSources(Report As string) As ArrayOfDataSource
- GetReportDefinition(Report As string) As base64Binary
- GetReportHistoryLimit(Report As string) As int
- GetReportHistoryOptions(Report As string) As boolean
- GetReportLink(Report As string) As string
- GetReportParameters(Report As string, HistoryID As string, ForRendering As boolean, Values As ArrayOfParameterValue, Credentials As ArrayOfDataSourceCredentials) As ArrayOfReportParameter
- GetResourceContents(Resource As string) As base64Binary
- GetRoleProperties(Name As string) As ArrayOfTask
- GetScheduleProperties(ScheduleID As string) As Schedule
- GetSubscriptionProperties(SubscriptionID As string) As string
- GetSystemPermissions() As ArrayOfString3
- GetSystemPolicies() As ArrayOfPolicy
- GetSystemProperties(Properties As ArrayOfProperty) As ArrayOfProperty
- InheritParentSecurity(Item As string)
- ListChildren(Item As string, Recursive As boolean) As ArrayOfCatalogItem
- ListEvents() As ArrayOfEvent
- ListExtensions(ExtensionType As ExtensionTypeEnum) As ArrayOfExtension
- ListJobs() As ArrayOfJob
- ListLinkedReports(Report As string) As ArrayOfCatalogItem
- ListReportHistory(Report As string) As ArrayOfReportHistorySnapshot
- ListReportsUsingDataSource(DataSource As string) As ArrayOfCatalogItem
- ListRoles() As ArrayOfRole
- ListScheduledReports(ScheduleID As string) As ArrayOfCatalogItem
- ListSchedules() As ArrayOfSchedule
- ListSecureMethods() As ArrayOfString
- ListSubscriptions(Report As string, Owner As string) As ArrayOfSubscription
- ListSubscriptionsUsingDataSource(DataSource As string) As ArrayOfSubscription
- ListSystemRoles() As ArrayOfRole
- ListSystemTasks() As ArrayOfTask
- ListTasks() As ArrayOfTask
- Logoff()
- LogonUser(userName As string, password As string, authority As string)
- MoveItem(Item As string, Target As string)
- PauseSchedule(ScheduleID As string)
- PrepareQuery(DataSource As DataSource, DataSet As DataSetDefinition) As DataSetDefinition
- Render(Report As string, Format As string, HistoryID As string, DeviceInfo As string, Parameters As ArrayOfParameterValue, Credentials As ArrayOfDataSourceCredentials, ShowHideToggle As string) As base64Binary
- RenderStream(Report As string, Format As string, StreamID As string, HistoryID As string, DeviceInfo As string, Parameters As ArrayOfParameterValue) As base64Binary
- ResumeSchedule(ScheduleID As string)
- SetCacheOptions(Report As string, CacheReport As boolean)
- SetDataDrivenSubscriptionProperties(DataDrivenSubscriptionID As string, ExtensionSettings As ExtensionSettings, DataRetrievalPlan As DataRetrievalPlan, Description As string, EventType As string, MatchData As string, Parameters As ArrayOfParameterValueOrFieldReference)
- SetDataSourceContents(DataSource As string, Definition As DataSourceDefinition)
- SetExecutionOptions(Report As string, ExecutionSetting As ExecutionSettingEnum)
- SetPolicies(Item As string, Policies As ArrayOfPolicy)
- SetProperties(Item As string, Properties As ArrayOfProperty)
- SetReportDataSources(Report As string, DataSources As ArrayOfDataSource)
- SetReportDefinition(Report As string, Definition As base64Binary) As ArrayOfWarning
- SetReportHistoryLimit(Report As string, UseSystem As boolean, HistoryLimit As int)
- SetReportHistoryOptions(Report As string, EnableManualSnapshotCreation As boolean, KeepExecutionSnapshots As boolean)
- SetReportLink(Report As string, Link As string)
- SetReportParameters(Report As string, Parameters As ArrayOfReportParameter)
- SetResourceContents(Resource As string, Contents As base64Binary, MimeType As string)
- SetRoleProperties(Name As string, Description As string, Tasks As ArrayOfTask)
- SetScheduleProperties(Name As string, ScheduleID As string, ScheduleDefinition As ScheduleDefinition)
- SetSubscriptionProperties(SubscriptionID As string, ExtensionSettings As ExtensionSettings, Description As string, EventType As string, MatchData As string, Parameters As ArrayOfParameterValue)
- SetSystemPolicies(Policies As ArrayOfPolicy)
- SetSystemProperties(Properties As ArrayOfProperty)
- UpdateReportExecutionSnapshot(Report As string)
- ValidateExtensionSettings(Extension As string, ParameterValues As ArrayOfParameterValueOrFieldReference) As ArrayOfExtensionParameter
This is likely where I would start to programmatically create reports.
Anyway, the first report I tried was a matrix report (like an Access cross-tab). The wizard was pretty straight forward, but I've done cross-tabs before using Access, so I knew what to expect I guess. It turned out that I used Guids as the row and column headers, which was something it didn't like (gave a runtime error), because I didn't know how to add the lookup tables to the query (in the wizard). Once I modified the datasource and joined in the lookup tables, then I was able to use the descriptive column rather than the Guid as the row header and column header. Actually figuring out what property sheets allowed me to do that was tougher - I was getting compile errors and double-clicking on the task didn't bring up any property sheet. Eventually I was able to get it, and ran the report, and got some good results. With drill-down.
And then it hung. Not sure why or what exactly happened, but VS.NET wouldn't shut down. Anyway, when I fired it back up again, it worked no problem. So it could have been me.
So far, I like it.
Mike