Can columns in a report be reordered programmatically?

Question

Can columns in a report be reordered programmatically?

BowlerMonk

SSC Journeyman

Points: 93
More actions
June 7, 2007 at 7:12 am

#179359

I have a need to be able to change the order of the columns from within a .Net application. We have reports generated in the application from our reporting server. An example column set from a generated report:
Date Time Name Addr
Now, we want to be able to have an option in our .Net app that allows the report to be generated the next time as:
Name Addr Date Time
I have a feeling this forum is going to be my new home away from home.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

My first thought would be to pull the RDL file into memory and manipulate it then save and call the report. Since they're nothing more than XML files, I'm sure it can be done. But I don't have much experience using XML.

Just a thought.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

BowlerMonk SSC Journeyman Points: 93 More actions · Answer 2

That was the only idea I could come up with, but then I wasn't sure how to do that, as the reports reside on the report server. I would need (I think) to pull the report down from the server, edit the XML, then republish it back up to the server. I can handle the XML part, but the rest I'm not sure how to do.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

I can't help you there, I've only just started .NET ...

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Rick Todd Hall of Fame Points: 3302 More actions · Answer 4

Hmm...if you have access to the SP you could create a temp table which includes an OrderBy Column. Then you pass in a parameter to the SP building the report, use that parameter to populate the OrderBy column, and always have the returnset ordered by the OrderBy column.

Cheers,

Rick Todd

Bill Scott SSCrazy Points: 2338 More actions · Answer 5

If you fetch your data from a stored procedure, it could return the columns moved into the ordering you want.

You could have a second data source to tell you the ordering of the columns you want and use a function in the Code section of the report to return the right value.

Or, you could do it in code, but that would be slow at run time, it usually takes several seconds to establish a connection to talk to the reporting server. Here's some C# that I use to talk with the reporting server.

After connecting to the WSDL via Add Web Reference. Mine is called SRSReportServer in the code below. Add a using for System.Web.Services.Protocols so you can get any error messages

using System.Web.Services.Protocols; // to get SOAP exception classes

In your code establish a connection to the reporting web service

SRSReportServer.ReportingService TemplateSRSServerRS = new SRSReportServer.ReportingService(); // our connection to the reporting server

TemplateSRSServerRS.Url = @"http://" + txtTemplateSRSServer.Text + "/ReportServer/ReportService.asmx"; // Connect to the SRS server with current credentials

try

{

TemplateSRSServerRS.Credentials = System.Net.CredentialCache.DefaultCredentials;

}

catch (Exception e)

{ handle any errors }

To put the report into the SRS server you just create it.

TemplateSRSServerRS.CreateReport("ReportName", "/MyReports/Work", true, ThisReportDefinition, null);

"ReportName" is the name as you want it to appear in the report web page, and refer to it in code by

"/MyReports/Work" is the path you want it in your reporting server. Note forward slashes for pathing

True - Overwrite flag

ThisReportDefinition is the bytearray containing the report XML

null - this is a collection of properties that you want placed on the report

Then you may need to relink the data sources

DataSourceReference reference = new DataSourceReference();

reference.Reference = NewDataSourceName;

ds.Item = (DataSourceDefinitionOrReference) reference;

ds.Name = OldDataSourceName;

dataSources[0] = ds;

TemplateSRSServerRS.SetReportDataSources(SRSDirectory + SRSName, dataSources);

The BOL for Reporting Service has all the details on the individual reporting service calls you can make and some sample code to get you started.

Kathy Goad SSC Rookie Points: 33 More actions · Answer 6

Are you using Reporting Services 2005? I just read an article by Vincent Rainardi on new Reporting Services 2005 features (posted on 6/14/2007) that says it now supports interactive sorting. It doesn't really answer your question but might be an option you could use.

If all else fails, head for the beach!

Kathy Goad

Bill Scott SSCrazy Points: 2338 More actions · Answer 7

The code I posted is for 2000, haven't had an opportunity to look at the setup for 2005 yet.

Jamey Livingston SSC Journeyman Points: 92 More actions · Answer 8

I was poking around a little and found a sproc:

GetReportForExecution

It is in the ReportingServices Database. Unfortunately I can't do more investigation with my current setup but it may lead you down the path you want.