September 6, 2012 at 10:11 am
Gettings Everyone !! 🙂
I'm new to SSRS and really need some help from you experts in this Forum -- I created a SSIS package and in a "Script Task" (using C# as language) I've established connection using web service to 'talk' to a report -- code below:
public void Main()
{
ReportingService2010 rs = new ReportingService2010();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = http://somesever:8080/ReportServer_SQL01/reportservice2010.asmx?wsdl;
string report = "/ABM/ABM_REPORTS/Master/SSIS Linked Monthly Report";
bool forRendering = false;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ItemParameter[] parameters = null;
int counter = 0;
try
{
parameters = rs.GetItemParameters(report, historyID, forRendering, values, credentials);
if (parameters != null)
{
foreach (var rp in parameters)
{
counter++;
Console.WriteLine(counter.ToString());
Console.WriteLine("Name: {0}", rp.Name);
Console.WriteLine("Prompt: ", rp.Prompt);
if (rp.PromptUser == true)
Console.WriteLine("PromptUser is true");
}
}
}
catch (SoapException e)
{
Console.WriteLine(e.Detail.InnerXml.ToString());
}
Dts.TaskResult = (int)ScriptResults.Success;
}
Output:
1
Name: databaseName
Prompt:
PromptUser is true
2
Name: centre
Prompt:
PromptUser is true
3
Name: processDate
Prompt:
PromptUser is true
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
I was able to print out everything retrived from the GetItemParameters Method HOWEVER I don't know how to SET (assign a defauilt value to the first parameter and then Hide it so when user access thereport they'll be promoted form 2nd parameter onward) the first parameter "databaseName" -- what I want to Achieve is assign the database name to a spesific one and hide it from user , so user only need to enter 'centre' and 'process date' when they try to run this report.
HELP!! HELP!! Greatly Appreciated!!!!!!
Tags:
September 7, 2012 at 8:12 am
I'm going to reply to myself 😛
I got this far -- but the last piece having problems:
Code:
parameters = rs.GetItemParameters(report, historyID, forRendering, values, credentials);
Console.WriteLine("parameters[0].Name = " + parameters[0].Name);
Console.WriteLine("parameters[0].DefaultValues = " + parameters[0].DefaultValues);
The above 3 lines of code worked and I got the output:
parameters[0].Name = databaseName
parameters[0].DefaultValues =
Now All I want to do is to assign a Default Falue for this first parameter (parameters[0], databaseName) so I go:
rs.SetItemParameters(report, parameters[0].DefaultValues.SetValue("My_DB_Name", ???));
??For the '???' part no matter what I put (tried '2', 'parameters[0]' ) it wouldn't satisfy the complier -- keeps compalining "The best overloaded method match for 'System.Array.SetValue(object, params long[])' has some invalid arguments" ...
?Can someone give me some tips on WHAT should be inside the ( ) for SetValue ??? THAHK YOU SO MUCH!!!
September 7, 2012 at 9:13 am
Further -- when I I put in code:
rs.SetItemParameters(report, parameters[0].DefaultValues.SetValue("My_DB_Name", 0));The compiler compalins:
Argument '2': cannot convert from 'void' to 'ST_a6ff246c0xxxc.csproj.ReportService2010.ItemParameter[]'
The long sting is the namespace
Any iders/hints/tips anything will be greatly appreciated 😉 !!!
September 20, 2012 at 5:43 am
You need to create an array of ItemParameter and pass it to the function. Below is the sample code:
ItemParameter[] param = new ItemParameter[1];
param[0] = new ItemParameter();
param[0].Name = "parameter1"; //Give parameter name here
param[0].DefaultValues = new string[] {"0"}; //Give parameter value here
rService.SetItemParameters("ReportPath/ReportName", param);
Hope this helps..!!!
September 20, 2012 at 5:53 am
Your code should be like below:
parameters[0].DefaultValues = new string[] {"My_DB_Name"};
rs.SetItemParameters(report, parameters);
September 20, 2012 at 10:00 pm
THANK YOU SO Much Ravi -- it totally worked! :-)I was able to assign value to the first parameter of a (freshly created) Linked Report and then 'hide' this parameter so that when user pull this report they can only select form 2nd parameter onward --
......
parameters[0].DefaultValue[0] = "My_dbName_1";
parameters[0].PromptUser = false;
rs.SetItemParameters(report, parameters);
This is a critical step for our solution since we have over 20 different SSRS reports and against 10 clients -- each client is a database on the same SQL Server engine (My_dbName_1, My_dbName_2 ....with Identical Schema) so we've created the 20 reports in a generic way by having DB Name as very first parameter and deploy to a "Master" location on report server, and then, using the "Script Task" in a SSIS package via Web Service we create a report folder for each client and then generate 20 "Linked Reports" for each DB from 'master" to that folder -- the last step would be assign the dbName as default value for all reports for each client/database so that user can't see other client's data (security controlled by URL permission) ..... Now with your help our solution is completely automated 🙂 Thanks again!
This pretty heavy stuff for a Sybase/SQL Server DBA 😎 But I'm glad it worked !!
September 21, 2012 at 12:56 am
Good to know it helped and you have something automated 🙂
June 24, 2013 at 7:04 am
Can you help me too with setting the parameters. We don't have a default parameter that can be assigned, but need to use a field from the previous step in the SSIS package that has imported data into a table for a specific period. Then want to use this period as the parameter for the report, but I can't find the syntax in the Value field to assign anything other than a fixed value.
December 17, 2014 at 7:24 pm
Hello mikehe.info, Ravi,
I am stuck in the exact same step trying to set linked reports default values and such trying to use the SetItemParameters method. My syntax is VB and here is what I do in the code below.
I would really appreciate your assistance on this,
Take care,
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim report As String = "/MyFolder/MySubFolder/Test Linked Report"
Try
dim param as new ItemParameter()
param.Name = "LastNameContains"
dim myDefaultValues(0) as String
myDefaultValues(0) = "oo"
param.DefaultValues = myDefaultValues
rs.SetItemParameters(report, param)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try
End Sub 'Main
May 3, 2017 at 5:58 pm
Hi all,
i am facing the same problem, passing parameter by powershell.
If any body can help me please?
rRegards,
Ash.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply