CONversion of an input parameter for a SP in the desired form.

hi All ,

I am getting one param for a SP as list of states from the Front End as :

@.states = 'NY,NJ,CA,Fl,MA' . Now i have to convert this param in the form :

@.states_for_SP = 'NY','NJ','CA','Fl','MA' . Is there any efficient way to do it except using REPLACE function. As this portion in our SP is taking a lot of time in converting in the desired form.

Plz suggest to do this.

Thanks.

I don't understand why a simple replace, e.g.

Code Snippet

declare @.a varchar(100)
set @.a = '''NY,NJ,CA,Fl,MA'''

declare @.b varchar(100)
set @.b = replace(@.a, ',', ''',''')

select @.a, @.b

Gives 'NY,NJ,CA,Fl,MA' > 'NY','NJ','CA','Fl','MA'

Should be slow. Is this similar to what you're trying to do?

Greg.

|||

Mohit,

This is a slightly different approach.

Using the function below you can convert the list into a table and then join the table into your query.

Code Snippet

IFEXISTS(

SELECT*FROMsys.objects

WHEREobject_id=OBJECT_ID(N'[dbo].[list2set]')

ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')

)

DROPFUNCTION [dbo].[list2set];

GO

CREATEFUNCTION dbo.list2set( @.list nvarchar(max), @.delim nvarchar(10))

RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))

AS

BEGIN

IFlen(@.list)<1 RETURN;

DECLARE @.xList XML;

-- no validity tests are performed, depending on input this could fail

SET @.xList =Convert(XML,''+REPLACE(@.list, @.delim,'')+'')

INSERTINTO @.resultset

SELECT data.listitem.value('.','nvarchar(max)')as item

FROM @.xList.nodes('/list/item') data(listitem)

RETURN

END

GO

You'd then use it as such:

Code Snippet

SELECT adr.state

FROM Address adr

innerjoin dbo.list2set(@.states, N',') sel

on adr.state = sel.item

|||

here the code..

Code Snippet

Create Table #Numbers(

Number Int

);

Declare @.I as int;

Set @.I = 1

While @.I<100

Begin

Insert Into #Numbers values(@.I);

Set @.I = @.I + 1;

End

Declare @.states varchar(100)

Set @.states = 'NY,NJ,CA,Fl,MA'

Declare @.StatesTable Table

(

State Varchar(100)

)

Insert Into @.StatesTable

Select Substring(',' + @.states + ',', Number, CharIndex(',',',' + @.states + ',',Number) - Number)

From

#Numbers

Where

Number<=Len(',' + @.states + ',')

And Substring(',' + @.states + ',',Number-1 ,1) = ','

--As you wise for concatination..

Set @.states = ''

Select @.states = @.states + ',''' + State + '''' From @.StatesTable

Select Substring(@.states,2,8000)

--Now You can use this @.StatesTable on any query for IN operator..

--Select * From SomeTable Where States in (Select State From @.StatesTable)

Controlling the Chart Axis Interval

Greetings,
I have a chart indicating sales metrics.
The $ axis has no interval set, so it is automatic.
Sometimes, depending on what parameter the report is given, the
interval is very small and there are so many major grid lines that it
is nearly impossible to make sense of the chart.
This chart is not very big; it is only part of the report.
Is there some setting to tell the chart to be a little smarter about
the axis interval?
PLEASE NOTE: setting the interval manually would result in a HORRIBLE
result because depending on the parameter passed in, the chart
displays very different ranges of numbers.
Thank you in advance for your help.
JerryFor RS 2000, you might look into having multiple charts side by side. One
would use auto intervals, another one would use specific interval settings
for situations where auto intervals don't work well (e.g. if all data point
values are between 0 and 1). You would then hide all charts
(Visibility.Hidden property) except the one with the appropriate interval
settings for the current data.
BTW: we plan to have expression-based intervals in the next version.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410111513.120f5d32@.posting.google.com...
> Greetings,
> I have a chart indicating sales metrics.
> The $ axis has no interval set, so it is automatic.
> Sometimes, depending on what parameter the report is given, the
> interval is very small and there are so many major grid lines that it
> is nearly impossible to make sense of the chart.
> This chart is not very big; it is only part of the report.
> Is there some setting to tell the chart to be a little smarter about
> the axis interval?
> PLEASE NOTE: setting the interval manually would result in a HORRIBLE
> result because depending on the parameter passed in, the chart
> displays very different ranges of numbers.
> Thank you in advance for your help.
> Jerry

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

Control the size of a text box via a report parameter

We have some note fields that are very large so reports take up too many
pages.
However we would like to either limit the field to lets say 4 lines or all
lines via some parameter.
Is there any way to do this?Did you look into using the Left() function to limit the field content to a
certain length based on a report parameter? You could use an expression
similar to this for the textbox value property:
=iif(Parameters!RestrictLength.Value = True,
Left(Fields!LongDescription.Value, 200), Fields!LongDescription.Value)
MSDN documentation for Left():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctLeft.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:eMmW3XeFFHA.3272@.TK2MSFTNGP10.phx.gbl...
> We have some note fields that are very large so reports take up too many
> pages.
> However we would like to either limit the field to lets say 4 lines or all
> lines via some parameter.
> Is there any way to do this?
>

Control parameter help......

I'm writing a page to do some reporting off of one of our databases, and I'm using 3 control parameters for my sql query that feeds my datagrid. 2 of the ControlParameters are from dropdownlists, and one is from a RadioButtonList. The ControlParameters that reference the dropdownlists are both working well, but the one for the RadioButtonList is not.

The error I am getting is:Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near'@.Booga'. (I changed my ControlParameter name from DateStr to Booga to try to avoid any conflicts with reserved words.)

Any ideas? Here is a snippet of my code:

<

formid="form1"runat="server"><tablewidth="100%"><tr><tdstyle="text-align: center"><strong>DataCenter</strong></td><tdstyle="text-align: center"><strong>Time Scope</strong></td><tdstyle="text-align: center"><strong>Call Status</strong></td></tr><tr><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList1"DataSourceID="SqlDataSource2"AutoPostBack="true"DataTextField="LocationName"runat="server"/></td><tdstyle="height: 47px; text-align: center"><asp:RadioButtonListID="RadioButtonList1"runat="server"AutoPostBack="true"Font-Size="Smaller"><asp:ListItemSelected="True"Value=" 1 = 1 ">All</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 1) >= (CAST(GETDATE() AS smalldatetime)))">Last Day</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 7) >= (CAST(GETDATE() AS smalldatetime)))">Last Week</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 30) >= (CAST(GETDATE() AS smalldatetime)))">Last 30 Days</asp:ListItem><asp:ListItemValue="((CAST(CallLog.RecvdDate AS smalldatetime) + 120) >= (CAST(GETDATE() AS smalldatetime)))">Last 120 Days</asp:ListItem></asp:RadioButtonList></td><tdstyle="height: 47px; text-align: center"><asp:DropDownListID="DropDownList2"AutoPostBack="true"runat="server"><asp:ListItemSelected="True"Value="Open">Open</asp:ListItem><asp:ListItemValue="Closed">Closed</asp:ListItem></asp:DropDownList></td></tr></table><br/><pstyle="text-align: center"><strong>Displaying All Open Tickets</strong><br/></p><asp:SqlDataSourceID="SqlDataSource2"runat="server"SelectCommand="SELECT DISTINCT [locationname] FROM [profile]"ConnectionString="<%$ ConnectionStrings:Heat %>"/><tablewidth="100%"><trwidth="100%"><tdvalign="top"width="100%"><asp:GridViewID="GridView1"AllowSorting="True"runat="server"DataSourceID="SqlDataSource1"DataKeyNames="CallID"AutoGenerateColumns="False"Font-Size="Smaller"Width="100%"><Columns><asp:CommandField/><asp:BoundFieldDataField="CallID"HeaderText="Call ID"ReadOnly="True"SortExpression="CallID"/><asp:BoundFieldDataField="CustID"HeaderText="Customer ID"ReadOnly="True"SortExpression="CustID"/><asp:BoundFieldDataField="CallType"HeaderText="Call Type"ReadOnly="True"SortExpression="CallType"/><asp:BoundFieldDataField="Priority"HeaderText="Priority"ReadOnly="True"SortExpression="Priority"/><asp:BoundFieldDataField="Cause"HeaderText="Cause"ReadOnly="True"SortExpression="Cause"/><asp:BoundFieldDataField="CallDesc"HeaderText="Call Description"ReadOnly="True"SortExpression="CallDesc"><ItemStyleWidth=40%/></asp:BoundField><asp:BoundFieldDataField="RecvdBy"HeaderText="Received By"ReadOnly="True"SortExpression="RecvdBy"/><asp:BoundFieldDataField="RecvdDate"HeaderText="Call Date"ReadOnly="True"SortExpression="RecvdDate"><ItemStyleWrap="False"/></asp:BoundField><asp:BoundFieldDataField="RecvdTime"HeaderText="Call Time"ReadOnly="True"SortExpression="RecvdTime"><ItemStyleWrap="False"/></asp:BoundField></Columns></asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "ConnectionString="<%$ ConnectionStrings:Heat %>"><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="LocationName"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="DropDownList2"Name="CallStatus"PropertyName="SelectedValue"Type="String"/><asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource></td></tr></table><br/><br/><br/>

<br />

</form>

Your SQL looks incomplete:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"SelectCommand="SELECT * FROM CallLog INNER JOIN Profile ON CallLog.CustID = Profile.CustID WHERE (Profile.LocationName = @.LocationName) AND (CallLog.CallStatus = @.CallStatus) AND @.Booga "

What is @.Boonga supposed to be related to?

|||

That's where the ControlParameter problem is:

<asp:ControlParameterControlID="RadioButtonList1"Name="Booga"PropertyName="SelectedValue"Type="String"/>

That should be making the SQL command end with one of the date comparisons that are controlled by the radiobuttonlist control.

Control of Parameter Field Layout

Greetings

Is it possible to control how the Parameter fields are displayed on report at the point of selection? At the moment they are equally dropped on the form in two columns. I would like to place the Alpha Numerical ones at the top and then organise the date parameters underneath in some kind of logical order.

Regards

The parameter layout is not configurable. But you can order the parameters in your report to get two columns in a more logical order.

|||

Oh well that is a shame.

Thank you anyway

Regards

Control number of options selected in a multi select parameter

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.

Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.


Many Thanks
Delli
I will try an expression for the query parameter (Parameters tab in the dataset properties) which passes your parameter to code-behind function that in turn filters out the parameter values accordingly.|||

After looking into this some more, I’ve found a split

function for MS SQL server, and in PL/SQL. Have system in both databases

:( grrrrrrr

The split function takes in the multi select parameter as a comma separated

list, and creates a virtual table of the results.

By using SQL code similar to the following: select top 10 element

dbo.split('string,split,code',',') I could stop the SQL engine running

for too many selected parameters. A search on Google or msn search ;) will find

codes examples for these functions. Keywords: SQL split function or PL/SQL

split function.

Also helps to inform your users on the front page of the report you have

done this!!!

Containstable variable usage

I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....

Can you not use a variable as a column list? I have a variable for search criteria and it works fine...

Here is my syntax

containstable([tablename],@.columnlist,@.srch)

I have been looking online and can't seem to find anything that says I can or cannot use a variable.

Column list cannot be replaced by variable. You have to use dynamic SQL to form and execute the SELECT statement if you want to parameterize CONTAINS/CONTAINSTABLE column list.

CONTAINS vs. CONTAINSTABLE performance

We are implementing an FTS to support google-like drop down boxes.
When I originally constructed my query using a "Contains" parameter,
performance was in the neighborhood of 4 seconds. However, when we
implemented CONTAINSQUERY, performance was on-par with our
expectations (far less than a second).
The table being searched contains < 500 rows total, and the final
results set is < 10 rows at this juncture. But this is just on a
development database, and ultimately we expected thousands of rows
instead of hundreds.
Can anyone tell me why the performance is so dramatically different
and is there something common in the building of FTS queries that we
could do differently?
I'd post the queries, but really there is no significant difference
between them and the example queries except that there are multiple
tables involved.
Thanks!
Both of them can produce very different execution plans. Containstable also
allows you to limit your results set to a definite amount which has
performance benefits.
"Kerry" <maclean.kerry@.gmail.com> wrote in message
news:1177958407.967589.256680@.l77g2000hsb.googlegr oups.com...
> We are implementing an FTS to support google-like drop down boxes.
> When I originally constructed my query using a "Contains" parameter,
> performance was in the neighborhood of 4 seconds. However, when we
> implemented CONTAINSQUERY, performance was on-par with our
> expectations (far less than a second).
> The table being searched contains < 500 rows total, and the final
> results set is < 10 rows at this juncture. But this is just on a
> development database, and ultimately we expected thousands of rows
> instead of hundreds.
> Can anyone tell me why the performance is so dramatically different
> and is there something common in the building of FTS queries that we
> could do differently?
> I'd post the queries, but really there is no significant difference
> between them and the example queries except that there are multiple
> tables involved.
> Thanks!
>
|||We did some internal testing on the indexed-table using both CONTAINS
and CONTAINSTABLE. In both cases, the execution plan was virtually
identical, and when querying just the single table, performance is
fast.
However, when used in our live query, which joins 9 tables from two
different databases, the CONTAINSTABLE join maintains the original
speed, but CONTAINS takes the 4 seconds I mentioned. I used SET
SHOWPLAN_ALL when running the queries both ways, and there seems to be
a negligible difference in the execution plans, although the
CONTAINTABLE plan is marginally faster than CONTAINS.
The bottom line is that this is a very dramatic difference and if we
could understand why the performance difference between the two, we
would be much more comfortable taking this to our production machines.
Thanks for both your responses!

Consuming Stored Procedure Output Param

This is my SProc:

CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @.numberRows */
@.selectDate datetime
,@.selectCountry int
,@.numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_smc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDate and RequestDateTime < dateadd(dd,1, @.selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @.selectCountry
ORDER BY configname

/* @.@.ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SELECT @.numberRows = @.@.RowCount

GO

And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue("@.selectDate", dateEntered)

myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))

Dim myParamAsNew SqlParameter("@.numberRows", SqlDbType.Int)

myParam.Direction = ParameterDirection.Output

myCommand.Parameters.Add(myParam)

myCommand.Connection = con

con.Open()

Dim readerAs SqlDataReader = myCommand.ExecuteReader()Dim rowCountAsInteger = reader.RecordsAffected

numberParts.Text = rowCount.ToString

con.Close()

EndSub

What should I fix?

label1.Text = myCommand.Parameters("@.numberRows").Value

|||

If I remember, I had this same problem, and found that you can't use the DataReader if you want to get the output parameter. I think you have to use DataSet.

|||

Read the following for an explanation of why it is happening and how to get around it.

http://p2p.wrox.com/archive/aspx/2001-12/24.asp

|||

How do I do the DataSet approach?

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection("Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx")

Dim dbDataSet =New DataSet()Dim dbAdapterAsNew SqlDataAdapter

dbAdapter.Fill(dbDataSet)

|||

You can do the following

Dim dbDataSet =New DataSet()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataSet,"tablename")

dbDataSet.Tables("tablename").rows.count

In case you have only one table, you can use a datatable instead of a dataset

Dim dbDataTable =New DataTable()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataTable)

dbDataTable.rows.count

consuming parameters

Hi
I open a report using an url. In the end of the url i add &TEST=123. In
Reporting Services I have added the @.TEST parameter. I would like this
parameter to get the value of 123. How do I consume the parameter in the url?
Do I use the Report Parameters dialog in Reporting Services?
Please Help
JuliaJulia:
I'll take a stab at answering this for you... I hope this will help give you
a push in the right direction.
ok, you are sorta on the right track... but what you are going to need to do
is look into the reporting services documentation and specifically at passing
values to the report service. For instance, if you go to your report manager
and click on a report, you will see at the top that there are indeed
parameters being passed to the report service like your query string example
that you have. However, there is a specific format that you need to follow,
that is where the documentation on the reporting service will help.
See, there essentially 2 parts to the parameters that get passed in... the
first being the options for how the report will display (like showing
different options in the toolbar, showing the parameter prompts, ect) and the
second being values that you are supplying to parameters in your report.
The documentation on this is a little confusing at first, but stick with
it... you will be able to get it working.
I "hope" that this helps.
"Julia" wrote:
> Hi
> I open a report using an url. In the end of the url i add &TEST=123. In
> Reporting Services I have added the @.TEST parameter. I would like this
> parameter to get the value of 123. How do I consume the parameter in the url?
> Do I use the Report Parameters dialog in Reporting Services?
> Please Help
> Julia|||One other point. Julia. You are putting the cart before the horse. Before
making any attempt to call a report via a URL you should first get the
report working. You need to create a report with query parameters. RS
automatically creates the report parameters for you when you do this. It is
important to realize the difference between query and report parameters.
Until you have a working report don't go anywhere near URL integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:5493C3E7-4070-4018-960B-776EF9EDC4DD@.microsoft.com...
> Julia:
> I'll take a stab at answering this for you... I hope this will help give
> you
> a push in the right direction.
> ok, you are sorta on the right track... but what you are going to need to
> do
> is look into the reporting services documentation and specifically at
> passing
> values to the report service. For instance, if you go to your report
> manager
> and click on a report, you will see at the top that there are indeed
> parameters being passed to the report service like your query string
> example
> that you have. However, there is a specific format that you need to
> follow,
> that is where the documentation on the reporting service will help.
> See, there essentially 2 parts to the parameters that get passed in... the
> first being the options for how the report will display (like showing
> different options in the toolbar, showing the parameter prompts, ect) and
> the
> second being values that you are supplying to parameters in your report.
> The documentation on this is a little confusing at first, but stick with
> it... you will be able to get it working.
> I "hope" that this helps.
> "Julia" wrote:
>> Hi
>> I open a report using an url. In the end of the url i add &TEST=123. In
>> Reporting Services I have added the @.TEST parameter. I would like this
>> parameter to get the value of 123. How do I consume the parameter in the
>> url?
>> Do I use the Report Parameters dialog in Reporting Services?
>> Please Help
>> Julia|||VHi
And thanks for the answers!
Ok, I have a working report that I can open from Report Manager. The report
shows data for one order that I have in my databse. In the databse I have
many orders so I would like to send the orderId to the select string (select
a, b, c from Order where OrderId = @.TEST). This is working from Report
Manager but then the user needs to add the orderId manually and press View
Report.
The user will work with an asp.net application and select an order from a
list and then press a button (or link) to view the order. I need to send the
orderId to the report. I have tried to add ?TEST=123 in the end of the URL
but that doesn't work. I would really need an example that I could run in my
development environment to see how this should work.
Thanks again
Julia
"Bruce L-C [MVP]" wrote:
> One other point. Julia. You are putting the cart before the horse. Before
> making any attempt to call a report via a URL you should first get the
> report working. You need to create a report with query parameters. RS
> automatically creates the report parameters for you when you do this. It is
> important to realize the difference between query and report parameters.
> Until you have a working report don't go anywhere near URL integration.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:5493C3E7-4070-4018-960B-776EF9EDC4DD@.microsoft.com...
> > Julia:
> >
> > I'll take a stab at answering this for you... I hope this will help give
> > you
> > a push in the right direction.
> >
> > ok, you are sorta on the right track... but what you are going to need to
> > do
> > is look into the reporting services documentation and specifically at
> > passing
> > values to the report service. For instance, if you go to your report
> > manager
> > and click on a report, you will see at the top that there are indeed
> > parameters being passed to the report service like your query string
> > example
> > that you have. However, there is a specific format that you need to
> > follow,
> > that is where the documentation on the reporting service will help.
> >
> > See, there essentially 2 parts to the parameters that get passed in... the
> > first being the options for how the report will display (like showing
> > different options in the toolbar, showing the parameter prompts, ect) and
> > the
> > second being values that you are supplying to parameters in your report.
> >
> > The documentation on this is a little confusing at first, but stick with
> > it... you will be able to get it working.
> >
> > I "hope" that this helps.
> >
> > "Julia" wrote:
> >
> >> Hi
> >>
> >> I open a report using an url. In the end of the url i add &TEST=123. In
> >> Reporting Services I have added the @.TEST parameter. I would like this
> >> parameter to get the value of 123. How do I consume the parameter in the
> >> url?
> >> Do I use the Report Parameters dialog in Reporting Services?
> >>
> >> Please Help
> >> Julia
>
>

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!