April 30, 2010 at 2:23 am
I'm working on a C# web application that will have a reporting module that will call reports in SSRS. The application login screen requires the user to specify the server and database name together with their login credentials - there are clients that have a number of databases on different servers and they need to specify which one the app needs to connect to.
With this in mind, when the report is selected in the web app I need to pass the server and DB name (specified by the user at login) to SSRS so that it can run the report against the correct DB.
I've got a clear picture of the process:
1. User A logs in to database X on server Y and user B logs in to database A on server B
2. User A transacts as normal on database X while user B does the same on database A
3. User A runs end-of-day reports on database X while user B does the same on database A
What I don't have a clear picture of is how to create the report in SSRS (what datasource type to use) without having a specific server or database? I've been playing around with passing parameters to a report I created using a single database as a datasource and have got this working fine:
Report Viewer
public class TestReportViewer
{
public string ReportServer { get; set; }
public string SqlServer { get; set; }
public string Database { get; set; }
public string Report { get; set; }
public bool ShowParameters { get; set; }
private Dictionary<string, string> dictParameters = new Dictionary<string, string>();
public TestReportViewer()
{
}
public void SetParameter(string parameter, string value)
{
this.dictParameters[parameter] = value;
}
public void Execute()
{
string sUrl = string.Empty;
sUrl += (this.ReportServer);
sUrl += ("?");
sUrl += (this.Report);
//&rs:Command=Render&rs:format=HTML4.0&rc:Parameters=true&DayOfTheWeek=Tuesday";
sUrl += ("&rs:Command=Render&rs:format=HTML4.0");
if(this.ShowParameters == true)
{
sUrl += ("&rc:Parameters=true");
}
sUrl += ("&rc:Section=0");
//sUrl += ("&Server_Name=" + this.SqlServer);
//sUrl += ("&Database_Name=" + this.Database);
foreach (KeyValuePair<string, string> kvp in this.dictParameters)
{
sUrl += ("&" + kvp.Key + "=" + kvp.Value);
}
HtmlWindow hw = HtmlPage.Window;
hw.Navigate(new Uri(sUrl), "_blank", "");
}
}
Report Button Click Code
TestReportViewer TestReportViewer = new TestReportViewer ()
{
SqlServer = @"TEST",
Database = "AdventureWorks",
ReportServer = "http://LOCALHOST:88/ReportServer",
Report = "/TEST/Sales Orders"
};
TestReportViewer .SetParameter("DayOfTheWeek", "Wednesday");
TestReportViewer .Execute();
Ultimately the SqlServer and Database will be passed from the web app itself and ReportServer will be picked up from a configuration table in the database and that's all fine - what I'm stumped with is how to create the report that I can deploy on any report server and without having to specify the SQL Server and database name.
I'm a bit of a newbie with SSRS and haven't worked with any of the other datasource options. Any help would be great. I'm thinking XML might be the way to go but have no idea where to start.
Thanks.
April 30, 2010 at 2:56 am
Hi,
This is a vague requirement. To populate your report you will require a data repository
Raunak J
April 30, 2010 at 3:00 am
Raunak Jhawar (4/30/2010)
Hi,This is a vague requirement. To populate your report you will require a data repository
Could you give me some details on creating the data repository? And how to connect to it in SSRS?
April 30, 2010 at 3:14 am
Hi,
The data repository is your database or any other equivalent data source.
You need a data container to hold your report data
Raunak J
May 9, 2010 at 11:45 pm
You can specify a datasource that uses the value of a parameter. In the parameter pass the connection string,
for example:
"Data Source=[SSInstance];Initial Catalog=[database];User Id=;Password=;Trusted_Connection=False;" (this one passes a username/pass = unsafe) or whatever you require. (You can get SSRS to prompt for the username and password by setting that option in the datasource of the report on the server.)
When you initially build the report, build it with a single datasource connected to one of those databases. Then when dev work is done you set the datasource to use the parameter. This allows you to develop easily. Unfortunately BIDS 05 (not sure that 08 does either) or VS don't seem to refresh dataset fields when you have a parameter as the connection string. So I find it easier to build the report first and then change the datasource later.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply