Getting a list of the queried parameters for a report (via soap)

What I'd liek to be able to do is display dropdowns (like the dropdowns in
the standard reportviewer component with queried parameters (eg, values come
from a datasource).
For example Employee Sales Summary in SampleReports includes a field for
employee name/id... which is automatically populated.
When i run GetReportParameters, I end up with nothing other dthan a type.
Thanks
Weston WeemsWeston,
Your code to call GetReportParameters should look similar to this:
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(report, historyID, forRendering, values,
credentials);
Then you just have to loop through the ValidValues of parameters.
foreach (ReportParameter parameter in parameters)
{
/// insert code to evaluate which parameter type/name, if dropdown
foreach (ValidValue vv in parameter.ValidValues)
{
LI = new System.Web.UI.WebControls.ListItem(vv.Label, vv.Value);
///see if this value is the same with the default value
///in which case we make the current list item selected
if (vv.Value == parameter.DefaultValues[0] && parameter.State ==ParameterStateEnum.HasValidValue)
{
LI.Selected = true;
}
this.ProcessorID.Items.Add(LI);
}
This code works for the one parameter that I have setup as a query based
parameter in the report.
Hope that helps,
Steve
"Weston Weems" wrote:
> What I'd liek to be able to do is display dropdowns (like the dropdowns in
> the standard reportviewer component with queried parameters (eg, values come
> from a datasource).
> For example Employee Sales Summary in SampleReports includes a field for
> employee name/id... which is automatically populated.
> When i run GetReportParameters, I end up with nothing other dthan a type.
> Thanks
> Weston Weems
>
>sql

Getting "extremely complex query" error when using xml data type parameters

Good morning, I am trying to figure out, and understand, why I am receiving the error that I am when I use xml data type parameters. If you look at the proc below, I have a proc with 8 xml data type parameters. When i call this proc adn pass in the values for the parameters, I recieve is the following:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

In debugging, I found that I DON'T get this error if i have only 3 or 4 xml data type parameters, but if I add more than 4, I get the above error.

The proc looks like the following:

CREATE PROCEDURE dbo.dosomething

@.BegDate DateTime,

@.EndDate DateTime,

@.InClass xml,

@.InDept xml,

@.InCat xml,

@.InPayPer xml,

@.InEmp xml,

@.InLoc xml,

@.InLeave xml,

@.InSalClass xml

AS

SELECT blah, blah

FROM blah

WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above

I call the stored proc by the following...and notice that i am not passing anything very large for each value...

exec dosomething

'20060101','20060101',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

,N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

Does anyone have any suggestions as to what might be causing this? Am i doign my compare in my proc incorrectly? Any insight would be greatly appreciated...

Scott

Hi Scott

This error message normally means that you should rewrite your query. Could you please post a complete repro to either my work address (mrys at the microsoft.com address) or post it here and we will take a look.

Thanks

Michael

PS: Sorry I thought I posted this reply yesterday but my travel connectivity at the moment seems to be shakey.

|||

I guess the problem is not you have lots xml data type parameters. It's caused by the WHERE clause in your query :

"WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above"

You can try to define some temp tables and shred the ID values in the xml into those tables. Jojn those temp tables in your query.

GetReportParameters method not updating ReportParameters array.

I have developed a report that uses two 'datatime' parameters ('start date'
and 'end date'). I then introduced a third parameter ('Range') which
provides a difinitive list of integer values to select from.
On selecting one of the integer values from the 'Range' parameter, the
'start date' and 'end date' are updated accordingly (by applying
"=Code.GetStartDate(Parameters!Range.Value)" as the non-queried Default
Values of the 'start date' parameter properties, and similar for the 'end
date'). This works perfectly when run from the Report Manager (i.e. If I
select the third option (last month) from the 'Range' parameter, the code
will update the 'start date' value to the beginning of last month and the
'end date' value to the last day of last month).
I have also developed a C# user control that acts as a client side utility
for viewing reports on a specific server (using the Reporting Services
WebService provided). This applicatiuon works perfectly for all reports
with the exception of that described above. The problem lies in obtaining
the updated dates when selecting a 'Range' using the 'GetReportParameters'
method. I have no problem returning updated parameter values from Query
Dependant parameters, which causes me to believe that I am calling the
Method correctly, however the 'start date' and 'end date' default values do
not seem to be updated by the webservice despite passing the updated value
of the 'Range' parameter to the method.
Any information as to why the parameter values are not being updated
correctly would be helpful.
Below is a code snippet:
Thanks in advance.
----
ReportServer.ReportParameter[] parameters;
try
{
parameters = rs.GetReportParameters("/" +
reportPath,null,true,currentParamValues,null);
}
catch(System.Web.Services.Protocols.SoapException sex)
{
SoapException(sex);
return null;
}
----I am seeing something similar, only with a cascading parameter. I've
stripped it down to the simplest example I can think of... the report has 2
parameters, A and B. B has a valid values list based on a dataset that
requires A.
I call GetReportParameters( ReportName, false, null, null, null ) and both
parameters come back with parameter 2 dependent upon parameter 1. I then
provide a ParameterValue array with 1 entry for parameter A. I call
GetReportParameters( ReportName, true, null, parameterValues, null ) and I
get both parameters back but B has no valid values and the parameter's state
is HasOutstandingDependencies.
Have you determined what your parameter's state is after the
GetReportParameters call?
"Paul B" wrote:
> I have developed a report that uses two 'datatime' parameters ('start date'
> and 'end date'). I then introduced a third parameter ('Range') which
> provides a difinitive list of integer values to select from.
> On selecting one of the integer values from the 'Range' parameter, the
> 'start date' and 'end date' are updated accordingly (by applying
> "=Code.GetStartDate(Parameters!Range.Value)" as the non-queried Default
> Values of the 'start date' parameter properties, and similar for the 'end
> date'). This works perfectly when run from the Report Manager (i.e. If I
> select the third option (last month) from the 'Range' parameter, the code
> will update the 'start date' value to the beginning of last month and the
> 'end date' value to the last day of last month).
> I have also developed a C# user control that acts as a client side utility
> for viewing reports on a specific server (using the Reporting Services
> WebService provided). This applicatiuon works perfectly for all reports
> with the exception of that described above. The problem lies in obtaining
> the updated dates when selecting a 'Range' using the 'GetReportParameters'
> method. I have no problem returning updated parameter values from Query
> Dependant parameters, which causes me to believe that I am calling the
> Method correctly, however the 'start date' and 'end date' default values do
> not seem to be updated by the webservice despite passing the updated value
> of the 'Range' parameter to the method.
> Any information as to why the parameter values are not being updated
> correctly would be helpful.
> Below is a code snippet:
> Thanks in advance.
> ----
> ReportServer.ReportParameter[] parameters;
> try
> {
> parameters = rs.GetReportParameters("/" +
> reportPath,null,true,currentParamValues,null);
> }
> catch(System.Web.Services.Protocols.SoapException sex)
> {
> SoapException(sex);
> return null;
> }
> ----
>
>sql

GetReportParameters - How can I get the default value of the parameter?

I'm using the GetReportParameters method to get the parameters of my report.
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>

GetDate() parameter in Function.... Not working...

I have a function that has the following two parameters. When I try to
compile I get an error on the GetDate().
If I remove the () from GetDate, it compiles but doesn't return the correct
results.
Example.
ALTER FUNCTION dbo.SiteMaxFileByExt
(
@.Days int = -1,
@.Date datetime = GetDate()
)
RETURNS TABLE
AS
RETURN SELECT TOP 100 PERCENT
tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
MAX(dbo.a_SiphonDetail.FileName) AS FileName
from tmSystem.dbo.vw_SiteExtensionList
where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
Thanks,
Roghttp://www.aspfaq.com/2439
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
> I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>|||Look up "user-defined functions, creating" in BOL.
It explains everything there.
In a nutshell, you cannot use GetDate() inside a UDF.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
>I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
> correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

Get value of parameters passed in stored procedure in a trigger

In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>

Get Value from CURSOR

I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...

My question is, how do I get the value out of the Cursor? There's only one field.

Declare @.Day as int
Declare @.Plant as varchar(30)

SET NOCOUNT ON

CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)

Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @.Facility_Cursor as sysname

FETCH NEXT From Facility_CURSOR into @.Facility_Cursor

WHILE @.@.FETCHSTATUS = 1

--YESTERDAY
Set @.Day = -2

Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'

--TODAY
SET @.Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'

FETCH NEXT FROM Facility_CURSOR into @.Facility_Cursor

CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR

SET NOCOUNT OFF

Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESCI see that you are trying to pass a variable "@.Facility" to your sproc without defining it, and the results of the cursor are being placed into @.Facility_Cursor. If you change the variable declaration to @.Facility and then FETCH NEXT From Facility_CURSOR into @.Facility it just might work!|||That did the trick. I knew it was something simple. Thanks!

get Stored Procedures Parameters, how?

hi
i make smal Application to get information from SQL Server 2000 by using
VB6.
now i can get Databases, Tables and Colomn, and Stored Procedures, but my
problem how i can get SP Parametres?
i am thinking to make small function to get Parametres From SP.Text, but i
think its not good solution.
Tarek M. SialaMaybe you could cross-post to a few more groups, or try this search engine
called google before casting such a wide net. Anyway, here is one page that
might help. Followups set accordingly.
http://www.aspfaq.com/2463
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:edmJJ3scGHA.3908@.TK2MSFTNGP04.phx.gbl...
> hi
> i make smal Application to get information from SQL Server 2000 by using
> VB6.
> now i can get Databases, Tables and Colomn, and Stored Procedures, but my
> problem how i can get SP Parametres?
> i am thinking to make small function to get Parametres From SP.Text, but i
> think its not good solution.
> --
> Tarek M. Siala
>

Get Saturday's date...

I am building a report that has two parameters that will always be Sunday to
Sunday but I want a text box to show the Saturdays date for the first
parameter which would be the first sunday. Example:
Parameter 1 - 4/15/2007
Parameter 2 - 4/22/2007
I need the text box to read 4/21/2007 which is the saturday of the week that
begins on 4/15.
Any help will be greatly appreciated, Thanks.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+Hi,
You should create a new dataset to retreive your parameter default. Put this
code in a stored procedure and the result should be the last Saturday:
set datefirst 7
declare @.date datetime
set @.date = getdate()
while datepart(dw,@.date) <> 7
begin
set @.date= dateadd(day,-1,@.date)
end
select @.date
If you are using the datefirst with the default settingd, you should not put
it in the code. Also, you should format your date with CONVERT or CAST
function, if required.
Another way is to write a custom code in SSRS, but I was lazy :)
I hope, it helps for you.
Regards,
Janos
"CipherTeKST" <CipherTeKST@.discussions.microsoft.com> wrote in message
news:3F271DBD-A5C5-4550-90FB-95B862A2C682@.microsoft.com...
>I am building a report that has two parameters that will always be Sunday
>to
> Sunday but I want a text box to show the Saturdays date for the first
> parameter which would be the first sunday. Example:
> Parameter 1 - 4/15/2007
> Parameter 2 - 4/22/2007
> I need the text box to read 4/21/2007 which is the saturday of the week
> that
> begins on 4/15.
> Any help will be greatly appreciated, Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+|||Yes, I can do this in SQL with,
SELECT
CONVERT(VARCHAR(10),DATEADD(wk, DATEDIFF(wk, 5, getdate()), 5),101) as
SATURDAY
I was looking for an expression to use in SSRS.
Thanks for your help though!
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"BERKE Janos" wrote:
> Hi,
> You should create a new dataset to retreive your parameter default. Put this
> code in a stored procedure and the result should be the last Saturday:
> set datefirst 7
> declare @.date datetime
> set @.date = getdate()
> while datepart(dw,@.date) <> 7
> begin
> set @.date= dateadd(day,-1,@.date)
> end
> select @.date
> If you are using the datefirst with the default settingd, you should not put
> it in the code. Also, you should format your date with CONVERT or CAST
> function, if required.
> Another way is to write a custom code in SSRS, but I was lazy :)
> I hope, it helps for you.
> Regards,
> Janos
> "CipherTeKST" <CipherTeKST@.discussions.microsoft.com> wrote in message
> news:3F271DBD-A5C5-4550-90FB-95B862A2C682@.microsoft.com...
> >I am building a report that has two parameters that will always be Sunday
> >to
> > Sunday but I want a text box to show the Saturdays date for the first
> > parameter which would be the first sunday. Example:
> > Parameter 1 - 4/15/2007
> > Parameter 2 - 4/22/2007
> >
> > I need the text box to read 4/21/2007 which is the saturday of the week
> > that
> > begins on 4/15.
> >
> > Any help will be greatly appreciated, Thanks.
> > --
> > CipherTeKST
> > MCSE: Security 2003, CCNA, Security+
>

Get report in Excel

Hi,

I've made C# winform application where the user can enter it's parameters (I did this so I can do some inputcontrol on the users parameters which I can't in reportviewer cause it's Closed Source).

Now when the user presses the button Get report in Excel. The application should pass the parameters to the SQL statement and complete this. Then the report should be exported to Excel and Excel should start and show me the report.

All this should work behind the scenes and the user should just get the report in Excel. I've added the Web Reference from the report server to my project but I can't seem to find the correct method.

thanks in advance

Hello,

Have a look at the ReportExecutionService class for RS 2005:

http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice(SQL.90).aspx

-Chris

Get parameters name and value in report.

Hello,
Is there some way to get list of all parameters that used in report
FROM REPORT and show it in table? I need to have one page after my main
report that show report parameters summary.. I don't want to just
grug-n-drop them.. I have about 15 parameters...
I use Reporting Services 2000.
Thanks,You can use the RS web service methods to do this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3g0y.asp
vetaldj wrote:
> Hello,
> Is there some way to get list of all parameters that used in report
> FROM REPORT and show it in table? I need to have one page after my main
> report that show report parameters summary.. I don't want to just
> grug-n-drop them.. I have about 15 parameters...
> I use Reporting Services 2000.
> Thanks,|||You can display the selected values from a multivalue parameter. The
following example uses the Join function to concatenate the selected values
of the parameter MySelection into a single string that can be set as an
expression for the value of a text box in a report item.
=Join(Parameters!MySelection.Value)http://msdn2.microsoft.com/en-us/library/ms157328.aspx"vetaldj"
<vkochubiy@.gmail.com> wrote in message
news:1153753663.900660.17760@.i42g2000cwa.googlegroups.com...
> Hello,
> Is there some way to get list of all parameters that used in report
> FROM REPORT and show it in table? I need to have one page after my main
> report that show report parameters summary.. I don't want to just
> grug-n-drop them.. I have about 15 parameters...
> I use Reporting Services 2000.
> Thanks,
>|||> You can display the selected values from a multivalue parameter. The
> following example uses the Join function to concatenate the selected
> values of the parameter MySelection into a single string that can be set
> as an expression for the value of a text box in a report item.
= Join(Parameters!MySelection.Value)
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
"vetaldj"
> <vkochubiy@.gmail.com> wrote in message
> news:1153753663.900660.17760@.i42g2000cwa.googlegroups.com...
>> Hello,
>> Is there some way to get list of all parameters that used in report
>> FROM REPORT and show it in table? I need to have one page after my main
>> report that show report parameters summary.. I don't want to just
>> grug-n-drop them.. I have about 15 parameters...
>> I use Reporting Services 2000.
>> Thanks,
>

get parameter and use in SSIS from SSRS parameter

Hi,
I am not sure this is the correct place to post this question, but here it is. I am trying to pass some parameters to SSIS from a report using the report parameter, then SSIS will create the datareaderdest and return to the report to use.
Anyone have any idea, guidance or leads please share it out. Thanks in advance.
Daren

Not quite sure what you want to to do here. If you want to pass data from SSIS to SSRS then look here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/aba8ecb7-0dcf-40d0-a2a8-64da0da94b93.htm

-Jamie

|||Hi Jamie,
what I am trying to do is have a report that has two parameters, lets say param1 and param2. then when user enter the values lets say param1 = 1 and param2 = 5, these two parameters will be passed to SSIS and stored it in the variables I declared there.
do you know how to go about doing something like that? Thanks in advance.
Daren
|||

You can pass values via the command-line to a package when it executes, but those values will not be "stored" in the package so that they are there next time you execute.

-Jamie

|||Hi Jamie,
can you show me an example of how can I send through command-line or give me some guidelines on where I can find examples for them? Thanks in advance.
Daren
|||

Sure, look into the SET option of dtexec:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

-Jamie

|||Thanks Jamie,
I will try that out...
Daren
|||

Hi try using a syntax as follows:

= "/FILE D:\MyProject\\bin\dtsx\myDtsx.dtsx /SET \Package.Variables[var1];" + Chr(34) + Parameters!Var1.Value + Chr(34) + " /SET \Package.Variables[Var2];" + CStr(Parameters!Var2.Value) + " /SET \Package.Variables[Var3];" + CStr(Parameters!Var3.Value) + " /SET \Package.Variables[Var4];" + CStr(Parameters!Var4.Value)

If that works fine in VS environment and if it is giving an error once you deployed to ReportManager? Try specifying a TimeOut value for the SSIS DataSource connection within SSRS.

get parameter and use in SSIS from SSRS parameter

Hi,
I am not sure this is the correct place to post this question, but here it is. I am trying to pass some parameters to SSIS from a report using the report parameter, then SSIS will create the datareaderdest and return to the report to use.
Anyone have any idea, guidance or leads please share it out. Thanks in advance.
Daren

Not quite sure what you want to to do here. If you want to pass data from SSIS to SSRS then look here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/aba8ecb7-0dcf-40d0-a2a8-64da0da94b93.htm

-Jamie

|||Hi Jamie,
what I am trying to do is have a report that has two parameters, lets say param1 and param2. then when user enter the values lets say param1 = 1 and param2 = 5, these two parameters will be passed to SSIS and stored it in the variables I declared there.
do you know how to go about doing something like that? Thanks in advance.
Daren
|||

You can pass values via the command-line to a package when it executes, but those values will not be "stored" in the package so that they are there next time you execute.

-Jamie

|||Hi Jamie,
can you show me an example of how can I send through command-line or give me some guidelines on where I can find examples for them? Thanks in advance.
Daren
|||

Sure, look into the SET option of dtexec:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

-Jamie

|||Thanks Jamie,
I will try that out...
Daren
|||

Hi try using a syntax as follows:

= "/FILE D:\MyProject\\bin\dtsx\myDtsx.dtsx /SET \Package.Variables[var1];" + Chr(34) + Parameters!Var1.Value + Chr(34) + " /SET \Package.Variables[Var2];" + CStr(Parameters!Var2.Value) + " /SET \Package.Variables[Var3];" + CStr(Parameters!Var3.Value) + " /SET \Package.Variables[Var4];" + CStr(Parameters!Var4.Value)

If that works fine in VS environment and if it is giving an error once you deployed to ReportManager? Try specifying a TimeOut value for the SSIS DataSource connection within SSRS.