Ability to Manually Enter a Parameter in a Queried Parameter

I have a queried parameter getting it's parameters from its own dataset.
Works great. Now they want to have the ability to enter their own if they
know what value they want rather than scrolling through the list.
Any ideas on how to go about this?
ThanksAnybody?
"BillD" wrote:
> I have a queried parameter getting it's parameters from its own dataset.
> Works great. Now they want to have the ability to enter their own if they
> know what value they want rather than scrolling through the list.
> Any ideas on how to go about this?
> Thanks
>|||Chris,
Thank You for taking the time to reply. Looks like I'll be getting into some
ASP as I also like to have a calendar for date parameters...
"Chris Baldwin" wrote:
> > "BillD" wrote:
> >
> >> I have a queried parameter getting it's parameters from its own
> >> dataset. Works great. Now they want to have the ability to enter
> >> their own if they know what value they want rather than scrolling
> >> through the list.
> Hello BillD,
> I'm not sure there's a way to do this when rendering through Report Manager.
> If there is I'd love to hear about it. But, I know you could easily create
> a custom webform that accomplishes this goal.
> For example, you can use JavaScript to determine whether the text box has
> been typed into and adjust the form action that requests the report accordingly.
> Here's a snippet that I used in one of my apps that might help:
> <script>
> function process()
> {
> var actionBase = "http://localhost/reportserver/myreport?rs:Command=Render&ParamVal=";
> var theForm = document.getElementById("myForm");
> var txtBox = document.getElementById("myBox");
> var selectBox = document.getElementById("mySelect");
> // If the textbox is empty, use the select box value
> if(txtBox.value == "")
> theForm.action = actionBase + selectBox.value;
> else
> // otherwise, use the text box value
> theForm.action = actionBase + txtBox.value;
> theForm.submit();
> }
> </script>
> <form method="get" id="myForm">
> <select id="mySelect">
> <!-- would be populated from database -->
> <option>Val1</option>
> <option>Val2</option>
> <option>Val3</option>
> </select>
> <input id="myBox" type="textbox">
> <input type="button" value="Submit" onClick="process();">
> </form>
> -chris
>
>sql

A Way To Go To First Row In A Parameter's DataSet?

We have cascading parameters in many of our reports. Many of the subsequent parameters after the previous ones have been selected, will result in a dataset with just one or two rows. Is there anyway to have Reporting Services select that first (and many times the only) record in that dataset?

Thanks!

Unfortunately no, there isn't.

A way to control the # of Transport connections

Is there a configuration parameter that allows me to control the number of Transport connections used by service broker? Right now it seems that it just uses one.

There is no such configuration parameter. The only reason to use more than one TCP connection would be if there were multiple NICs having routes to the destination. But since Service Broker does not really bottleneck on the NIC, there was no reaon to add support for multiple TCP connections. However, we may add this support for other reasons.

Rushi

|||

Ok so what should I do if it feels like I am being constrained at around 100 - 150 messages per second through this transport piece? I have done a couple of tests and at one point it seemed if I enqueued more than 100 messages/sec then they would back up into the sys.transmission_queue on the source side. I realize that is by design, but I have been led to believe that I should get something closer to 1,000 messages/sec of throughput between the 2 instances?

Any thoughts?

sql

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

A view based on a parameter

Hello.

After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.

One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.

So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.

Any help would be appreciated!

Shirley

It sounds like you might want a stored procedure or user-defined function. Both of those allow you to pass parameters, both in and out. I found a really good reference to an Access-to-SQL Server information site here:

http://www.informit.com/discussion/index.asp?postid=6a35b938-8029-40a8-9511-95bd1b7f1255&rl=1

Buck Woody

A simple loop

Hello
Was wondering if you guys could show me how to do a simple loop, Im trying
to execute a sp and pass the parameter CustomerID as parameter from the
select im looping through
/LasseLook for cursors in the BOL, you can also use a template from the QA, choose
Edit --> Add Template --> Using Cursor (then one of the templates)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:%234%23YbMQYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hello
> Was wondering if you guys could show me how to do a simple loop, Im trying
> to execute a sp and pass the parameter CustomerID as parameter from the
> select im looping through
> /Lasse
>|||Instead of thinking about loops, look for a set-based solution first.
Loops aren't generally the best way to accomplish data manipulation
tasks.
The only loop construct in TSQL is the WHILE BEGIN ... END loop.
David Portas
SQL Server MVP
--|||http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%234%23YbMQYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hello
> Was wondering if you guys could show me how to do a simple loop, Im trying
> to execute a sp and pass the parameter CustomerID as parameter from the
> select im looping through
> /Lasse
>

A question about varchar parameters

I've created a stored procedure that takes a varchar(10) as a parameter. However calling this stored procedure from an ASP page, with a string of greater length, generates an error. However this does not happen in Query Analyzer (it simply truncates the string to 10 characters). I was under the previous impression that this truncation was implicit, but now it seems that it is not. Can someone please give me a quick overview of how to work around this issue (is there an SQL setting I can flip on). I know I could pre-truncate every value in my page, but that seems like a design nightmare (seeing as how I would need to know the size of every varchar parameter in every stored procedure old and new, also I'd like to be able to simply increase the size of the data field in the table, at a later point, without having to match it up in every stored procedure and ASP page ).

P.S. I am using SQL Sever 2000

How did you call the stored procedure from your code? I use SqlConnection and SqlCommand to call the sp with a Parameter, it succeeded even I input a string with length greater then the length defined for the stored procedure parameter, as what happened in Query Analyzer.

So I guess your exception came from ASP .NET, not SQL. Did you call the stored procedure using OleDbCommand and specify the length for the parameter on the application side?

A problem getting value out of Stored Procedure

Server management studio does not give error from following query, but output parameter (kokonaissumma) is always NULL. I tested it other ways, by making it return value then it worked. But that required changes to the query, so I really don't know. The problem query is the last.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END
return (SELECT count(*) FROM dbo.t_osto)
select @.kokonaissumma =sum(Yhteensa)FROM dbo.t_osto

sum(Yhteensa)FROM dbo.t_osto

your missing a space:

sum(Yhteensa) FROM dbo.t_osto

|||

Move your SELECTs before the RETURN.

create PROCEDURE [dbo].[kori2]( @.Tuotekoodivarchar(20),@.kokonaissummamoney output)ASBEGIN SET NOCOUNT ON;IFNOT EXISTS(SELECT *FROM dbo.t_ostoWHERE Tuotekoodi=@.Tuotekoodi)BEGIN INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,AlvFROM dbo.t_TuoteWHERE Tuotekoodi= @.TuotekoodiENDELSE BEGIN UPDATE dbo.t_ostoSET Maara=Maara+1WHERE Tuotekoodi=@.TuotekoodiENDselect @.kokonaissumma =sum(Yhteensa)FROM dbo.t_ostoEND

|||

It works. Thank you both. It looks like this now.

...

...

UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)
END

Regards

Leif

|||

You dont need the return statement. The count is being returned through the OUTPUT parameter.

|||

I see. I'll fix that too. Below is part of the query now, it has now all features what I planned.

--

--

select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto --total money
select @.tuotemaara =sum(Maara) FROM dbo.t_osto -- how many items

return (SELECT count(*) FROM dbo.t_osto) --how many lines (and no return)

END

I should learn some sql. It is my weakest point in ASP.NET. I looked at "Books on line", but even first page used so unfamiliar terms, I was not able to go much further than that. Well, search is there and today I found some tutorials in there.

Thanks

Leif

|||

Hi again

I tried my stored procedure without return. Like this. This is my latest a refresh only version.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
SELECT count(*) FROM dbo.t_osto

With this the return value was 0. That was not the correct value. I wonder what else is wrong in my code.

This gave correct value as before. Below.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)

Regards

Leif

A new report history from a report history

Dear Anyone,

I want to create a new report history but with A different parameter selection from an existing report history. Is this possible?

Thanks,
JosephSorry this is not possible.

-Daniel

A filtered parameter list for a report using Cube

I am trying to report against a cube and I want the user to be able to choose the date for the report.

Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.

I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?

You need to apply an MDX filter to the parameter, something like:

EXISTS([Date].[Month].[Month].Members, ,'<measure group name>')

|||

I gathered using an MDX expression for the filter, but I'm unsure as to how to apply it.

Could someone explain exactly how.. there are so many different places for changing parameters and I'm confused as to where to do this.

Documentation is, as ever, as clear as mud...

|||

You select the MDX option in the Operator column on the filter pane. This report uses the Adventure Works sample cube to demonstrate the final result.

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="AdventureWorksAS">

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>389e88fe-3514-42d7-a5b9-9bc3126a67bf</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Height>2in</Height>

</Body>

<rd:ReportID>2c2b18e1-e6e7-4ee2-b5c9-e664fd36f35f</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText> SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( EXISTS([Date].[Month].Members, ,'Reseller Sales') ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Sales Amount</MeasureName><UniqueName>[Measures].[Sales Amount]</UniqueName></ID><ItemCaption>Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems><FilterItem><HierarchyUniqueName>[Date].[Calendar]</HierarchyUniqueName><Condition xsi:type="MDX"><Text>EXISTS([Date].[Month].Members, ,'Reseller Sales')</Text></Condition></FilterItem></FilterItems></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( EXISTS([Date].[Month].Members, ,'Reseller Sales') ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

<Fields>

<Field Name="Category">

<rd:TypeName>System.String</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Category]" /&gt;</DataField>

</Field>

<Field Name="Subcategory">

<rd:TypeName>System.String</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Subcategory]" /&gt;</DataField>

</Field>

<Field Name="Product">

<rd:TypeName>System.String</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Product]" /&gt;</DataField>

</Field>

<Field Name="Sales_Amount">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Sales Amount]" /&gt;</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

a few questions

Hi,
I am new to RSS and I have a few questions that I hope you can help me with.
1. I have a sp with parameter that gets a default value of null
@.misgeretid smallint=null and the where statement is as follows:
WHERE (misgeretcode = @.misgeretid or misgeretcode is null)
In rss I want to add a parameter but allow the user not to select a value
and if he doesn't select a value it will return null to the sp.
2.Also, I noticed that in the employee sales sample the employee dropdown
list gets a value <select a value>. I want to replace that label with all and
set the value to 0. Is it possible?
3. Are there any books on how to user rss with visual studion .net
preferablly vb.net?
ThanksAnswers inline.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:ABC14A10-D68A-41AC-871A-B45B1E57E3B1@.microsoft.com...
> Hi,
> I am new to RSS and I have a few questions that I hope you can help me
> with.
> 1. I have a sp with parameter that gets a default value of null
> @.misgeretid smallint=null and the where statement is as follows:
> WHERE (misgeretcode = @.misgeretid or misgeretcode is null)
> In rss I want to add a parameter but allow the user not to select a value
> and if he doesn't select a value it will return null to the sp.
You need to enable the parameter to "allow nulls". This is done in the
parameter dialog box.
> 2.Also, I noticed that in the employee sales sample the employee dropdown
> list gets a value <select a value>. I want to replace that label with all
> and
> set the value to 0. Is it possible?
Yes, you need to make 0 the default value and add an "all" to the parameter
set.
> 3. Are there any books on how to user rss with visual studion .net
> preferablly vb.net?
Yes, there are several RS books available. See
http://www.microsoft.com/sql/reporting/techinfo/books.asp.
> Thanks
>