Is it possible to format part of an expression in a textbox? I have this
expression which displays the parameter value(s) that are selected by the
user:
="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
I wish to put "Selected Region(s): " in bold. I can use a second textbox in
bold "Selected Region(s): " and leave just the expression in the other one,
but I'm trying to do everything in a single textbox.
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comYou cannot do this in RS 2005 (rich text in a single textbox)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:ON$sP4yQIHA.748@.TK2MSFTNGP04.phx.gbl...
> Is it possible to format part of an expression in a textbox? I have this
> expression which displays the parameter value(s) that are selected by the
> user:
> ="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
> I wish to put "Selected Region(s): " in bold. I can use a second textbox
> in bold "Selected Region(s): " and leave just the expression in the other
> one, but I'm trying to do everything in a single textbox.
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Paremeter question
I have a report parameter: Customers
That parameter is used in main data set to pull data for the specified customer.
In the report header Iâ'd like to show that customerâ's name.
But because parameter has a customer code and not a name I dont know
the name of the customer that a user selects.
What is a solution here?Use the cutsomer code to query the customers name from the database, and
display that name in the header.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:A4AFE9B4-8598-42C0-B60B-873A37904B5F@.microsoft.com...
> I have a report parameter: Customers
> That parameter is used in main data set to pull data for the specified
customer.
> In the report header I'd like to show that customer's name.
> But because parameter has a customer code and not a name I dont know
> the name of the customer that a user selects.
> What is a solution here?
>
That parameter is used in main data set to pull data for the specified customer.
In the report header Iâ'd like to show that customerâ's name.
But because parameter has a customer code and not a name I dont know
the name of the customer that a user selects.
What is a solution here?Use the cutsomer code to query the customers name from the database, and
display that name in the header.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:A4AFE9B4-8598-42C0-B60B-873A37904B5F@.microsoft.com...
> I have a report parameter: Customers
> That parameter is used in main data set to pull data for the specified
customer.
> In the report header I'd like to show that customer's name.
> But because parameter has a customer code and not a name I dont know
> the name of the customer that a user selects.
> What is a solution here?
>
Paremeter Control Greyed out
I have a balance sheet report with only one parameter, Month.
The parameter is a query and the default for this parameter is a query to
determine the current month.
We schedule this report to run every hour because managers don't like to
wait 5 minutes for a report to generate. The problem is, we can't change the
parameter because it is greyed out. Is there a setting that would allow
people to choose a new month from the drop-down control even if the report is
on a schedule?You should be able to go into Report Manager and select Prompt User
checkbox. Or you can set the PromptUser property in the RDL to true.
--
| Thread-Topic: Paremeter Control Greyed out
| thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==| X-WBNR-Posting-Host: 12.38.198.125
| From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
| Subject: Paremeter Control Greyed out
| Date: Tue, 3 May 2005 07:30:19 -0700
| Lines: 10
| Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a balance sheet report with only one parameter, Month.
|
| The parameter is a query and the default for this parameter is a query to
| determine the current month.
|
| We schedule this report to run every hour because managers don't like to
| wait 5 minutes for a report to generate. The problem is, we can't change
the
| parameter because it is greyed out. Is there a setting that would allow
| people to choose a new month from the drop-down control even if the
report is
| on a schedule?
||||You are refering to PROPERTIES>PARAMETERS for the report itself correct?
It's already checked.
""Brad Syputa - MS"" wrote:
> You should be able to go into Report Manager and select Prompt User
> checkbox. Or you can set the PromptUser property in the RDL to true.
> --
> | Thread-Topic: Paremeter Control Greyed out
> | thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==> | X-WBNR-Posting-Host: 12.38.198.125
> | From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
> | Subject: Paremeter Control Greyed out
> | Date: Tue, 3 May 2005 07:30:19 -0700
> | Lines: 10
> | Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a balance sheet report with only one parameter, Month.
> |
> | The parameter is a query and the default for this parameter is a query to
> | determine the current month.
> |
> | We schedule this report to run every hour because managers don't like to
> | wait 5 minutes for a report to generate. The problem is, we can't change
> the
> | parameter because it is greyed out. Is there a setting that would allow
> | people to choose a new month from the drop-down control even if the
> report is
> | on a schedule?
> |
>
The parameter is a query and the default for this parameter is a query to
determine the current month.
We schedule this report to run every hour because managers don't like to
wait 5 minutes for a report to generate. The problem is, we can't change the
parameter because it is greyed out. Is there a setting that would allow
people to choose a new month from the drop-down control even if the report is
on a schedule?You should be able to go into Report Manager and select Prompt User
checkbox. Or you can set the PromptUser property in the RDL to true.
--
| Thread-Topic: Paremeter Control Greyed out
| thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==| X-WBNR-Posting-Host: 12.38.198.125
| From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
| Subject: Paremeter Control Greyed out
| Date: Tue, 3 May 2005 07:30:19 -0700
| Lines: 10
| Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a balance sheet report with only one parameter, Month.
|
| The parameter is a query and the default for this parameter is a query to
| determine the current month.
|
| We schedule this report to run every hour because managers don't like to
| wait 5 minutes for a report to generate. The problem is, we can't change
the
| parameter because it is greyed out. Is there a setting that would allow
| people to choose a new month from the drop-down control even if the
report is
| on a schedule?
||||You are refering to PROPERTIES>PARAMETERS for the report itself correct?
It's already checked.
""Brad Syputa - MS"" wrote:
> You should be able to go into Report Manager and select Prompt User
> checkbox. Or you can set the PromptUser property in the RDL to true.
> --
> | Thread-Topic: Paremeter Control Greyed out
> | thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==> | X-WBNR-Posting-Host: 12.38.198.125
> | From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
> | Subject: Paremeter Control Greyed out
> | Date: Tue, 3 May 2005 07:30:19 -0700
> | Lines: 10
> | Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a balance sheet report with only one parameter, Month.
> |
> | The parameter is a query and the default for this parameter is a query to
> | determine the current month.
> |
> | We schedule this report to run every hour because managers don't like to
> | wait 5 minutes for a report to generate. The problem is, we can't change
> the
> | parameter because it is greyed out. Is there a setting that would allow
> | people to choose a new month from the drop-down control even if the
> report is
> | on a schedule?
> |
>
Paramters in WITH part of MDX
Hi,
I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:
WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......
The @.MyMeasures should be a combobox with the values like
Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.
What's my failure?
Thanks
Hans
Hi Adam,
The error is:
The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of
WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)
is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.
Thanks
Hans
Hi @.All,
After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:
WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....
The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!
Hans
ParametersUsed Always Polulated after calling ReportingService.Ren
In SSRS 2000 SP2 the documentation for ReportingService.Render states that
the ParametersUsed output parameter should only be populated only if the
report being rendered is a report history snapshot. I'm invoking the Render
method with a null HistoryId as follows, so I would expect the ParametersUsed
output parameter not to get populated.
results = m_RptService.Render(m_RptCatalogItem.Path /*Report*/,
m_Format /*Format*/,
null /*HistoryID*/,
null /*DeviceInfo*/,
m_RptParameters /*Parameters*/,
null /*Credentials*/,
null /*ShowHideToggle*/,
out encoding /*Encoding*/,
out mimeType /*MimeType*/,
out historyParms /*ParametersUsed*/,
out warnings /*Warnings*/,
out streamIDs /*StreamIds*/);
The call generates the following HTTP traffic. As you can see the HistoryId
is not sent in the request yet ParametersUsed is populated in the result. If
I'm interpreting the documentation correctly this value should not be
populated in the response. I really don't what ParametersUsed to be
populated in the response. Is this the correct behavior?
POST /ReportServer/ReportService.asmx HTTP/1.1
Content-Type: text/xml; charset=utf-8
SOAPAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render"
Content-Length: 524
Expect: 100-continue
Host: localhost
Cookie: sqlAuthCookie=****; UsrTkn=>****; AppTkn=>****
<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><Render
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007
7:32:50
PM</Value></ParameterValue></Parameters></Render></soap:Body></soap:Envelope>
HTTP/1.1 200 OK
Date: Mon, 12 Feb 2007 00:32:50 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
X-AspNet-Version: 1.1.4322
Cache-Control: private, max-age=0
Content-Type: text/xml; charset=utf-8
Content-Length: 9021
<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Header><ServerInfoHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><ReportServerVersionNumber>Microsoft
SQL Server Reporting Services Version
8.00.1038.00</ReportServerVersionNumber><ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader><SessionHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Result>
Base 64 Encoded response removed to shorten post
</Result><Encoding>Unicode
(UTF-8)</Encoding><MimeType>text/html</MimeType><ParametersUsed><ParameterValue><Name>p1</Name><Value>2/11/2007
7:32:50 PM</Value></ParameterValue></ParametersUsed><StreamIds
/></RenderResponse></soap:Body></soap:Envelope>Reporting Services 2005 exhibits the same behavior. The following
application produces the following output "Length: 3" indicating that the
ParametersUsed output parameter was populated even though no histryid was
provided. Is this the correct behavior? If so how do I turn it off?
According to the following documentation this does not seem like the correct
behavior (http://msdn2.microsoft.com/en-us/library/aa258532(SQL.80).aspx).
Notice that the description for ParametersUsed states that the output
parameter is only populated of a history snapshot is being rendered. This
causes fairly significant performance issues for me because in some cases I
am passing large parameters. Instead of these parameters just being in the
request, they are in both the request and the response causing the response
to be much larger than I desire.
using System;
using System.Collections.Generic;
using System.Text;
using ConsoleApplication9.rs;
namespace ConsoleApplication9
{
class Program
{
static void Main(string[] args)
{
string encoding, mimeType;
ParameterValue[] pout;
Warning[] w;
string[] s;
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ParameterValue[] pin = new ParameterValue[3];
pin[0] = new ParameterValue();
pin[0].Name = "stdt";
pin[0].Value = "2/19/2007";
pin[1] = new ParameterValue();
pin[1].Name = "enddt";
pin[1].Value = "2/20/2007";
pin[2] = new ParameterValue();
pin[2].Name = "sopco";
pin[2].Value = "A";
rs.Render("/VRU Reports/VRUAppSuccessPercent" /*Report*/,
"PDF" /*Format*/,
null /*HistoryID*/,
null /*DeviceInfo*/,
pin /*Parameters*/,
null /*Credentials*/,
null /*ShowHideToggle*/,
out encoding /*Encoding*/,
out mimeType /*MimeType*/,
out pout /*ParametersUsed*/,
out w /*Warnings*/,
out s /*StreamIds*/);
Console.WriteLine("Length: " + pout.Length);
Console.ReadLine();
}
}
}
"Jerry" wrote:
> In SSRS 2000 SP2 the documentation for ReportingService.Render states that
> the ParametersUsed output parameter should only be populated only if the
> report being rendered is a report history snapshot. I'm invoking the Render
> method with a null HistoryId as follows, so I would expect the ParametersUsed
> output parameter not to get populated.
> results = m_RptService.Render(m_RptCatalogItem.Path /*Report*/,
> m_Format /*Format*/,
> null /*HistoryID*/,
> null /*DeviceInfo*/,
> m_RptParameters /*Parameters*/,
> null /*Credentials*/,
> null /*ShowHideToggle*/,
> out encoding /*Encoding*/,
> out mimeType /*MimeType*/,
> out historyParms /*ParametersUsed*/,
> out warnings /*Warnings*/,
> out streamIDs /*StreamIds*/);
> The call generates the following HTTP traffic. As you can see the HistoryId
> is not sent in the request yet ParametersUsed is populated in the result. If
> I'm interpreting the documentation correctly this value should not be
> populated in the response. I really don't what ParametersUsed to be
> populated in the response. Is this the correct behavior?
> POST /ReportServer/ReportService.asmx HTTP/1.1
> Content-Type: text/xml; charset=utf-8
> SOAPAction:
> "http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render"
> Content-Length: 524
> Expect: 100-continue
> Host: localhost
> Cookie: sqlAuthCookie=****; UsrTkn=>****; AppTkn=>****
> <?xml version="1.0" encoding="utf-8"?><soap:Envelope
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="<soap:Body><Render">http://www.w3.org/2001/XMLSchema"><soap:Body><Render
> xmlns="<Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007
> 7:32:50
> PM</Value></ParameterValue></Parameters></Render></soap:Body></soap:Envelope>
> HTTP/1.1 200 OK
> Date: Mon, 12 Feb 2007 00:32:50 GMT
> Server: Microsoft-IIS/6.0
> X-Powered-By: ASP.NET
> X-AspNet-Version: 1.1.4322
> Cache-Control: private, max-age=0
> Content-Type: text/xml; charset=utf-8
> Content-Length: 9021
> <?xml version="1.0" encoding="utf-8"?><soap:Envelope
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="<soap:Header><ServerInfoHeader">http://www.w3.org/2001/XMLSchema"><soap:Header><ServerInfoHeader
> xmlns="<ReportServerVersionNumber>Microsoft">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><ReportServerVersionNumber>Microsoft
> SQL Server Reporting Services Version
> 8.00.1038.00</ReportServerVersionNumber><ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader><SessionHeader
> xmlns="<SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse
> xmlns="<Result>">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Result>
> Base 64 Encoded response removed to shorten post
> </Result><Encoding>Unicode
> (UTF-8)</Encoding><MimeType>text/html</MimeType><ParametersUsed><ParameterValue><Name>p1</Name><Value>2/11/2007
> 7:32:50 PM</Value></ParameterValue></ParametersUsed><StreamIds
> /></RenderResponse></soap:Body></soap:Envelope>
>
the ParametersUsed output parameter should only be populated only if the
report being rendered is a report history snapshot. I'm invoking the Render
method with a null HistoryId as follows, so I would expect the ParametersUsed
output parameter not to get populated.
results = m_RptService.Render(m_RptCatalogItem.Path /*Report*/,
m_Format /*Format*/,
null /*HistoryID*/,
null /*DeviceInfo*/,
m_RptParameters /*Parameters*/,
null /*Credentials*/,
null /*ShowHideToggle*/,
out encoding /*Encoding*/,
out mimeType /*MimeType*/,
out historyParms /*ParametersUsed*/,
out warnings /*Warnings*/,
out streamIDs /*StreamIds*/);
The call generates the following HTTP traffic. As you can see the HistoryId
is not sent in the request yet ParametersUsed is populated in the result. If
I'm interpreting the documentation correctly this value should not be
populated in the response. I really don't what ParametersUsed to be
populated in the response. Is this the correct behavior?
POST /ReportServer/ReportService.asmx HTTP/1.1
Content-Type: text/xml; charset=utf-8
SOAPAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render"
Content-Length: 524
Expect: 100-continue
Host: localhost
Cookie: sqlAuthCookie=****; UsrTkn=>****; AppTkn=>****
<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><Render
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007
7:32:50
PM</Value></ParameterValue></Parameters></Render></soap:Body></soap:Envelope>
HTTP/1.1 200 OK
Date: Mon, 12 Feb 2007 00:32:50 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
X-AspNet-Version: 1.1.4322
Cache-Control: private, max-age=0
Content-Type: text/xml; charset=utf-8
Content-Length: 9021
<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Header><ServerInfoHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><ReportServerVersionNumber>Microsoft
SQL Server Reporting Services Version
8.00.1038.00</ReportServerVersionNumber><ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader><SessionHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Result>
Base 64 Encoded response removed to shorten post
</Result><Encoding>Unicode
(UTF-8)</Encoding><MimeType>text/html</MimeType><ParametersUsed><ParameterValue><Name>p1</Name><Value>2/11/2007
7:32:50 PM</Value></ParameterValue></ParametersUsed><StreamIds
/></RenderResponse></soap:Body></soap:Envelope>Reporting Services 2005 exhibits the same behavior. The following
application produces the following output "Length: 3" indicating that the
ParametersUsed output parameter was populated even though no histryid was
provided. Is this the correct behavior? If so how do I turn it off?
According to the following documentation this does not seem like the correct
behavior (http://msdn2.microsoft.com/en-us/library/aa258532(SQL.80).aspx).
Notice that the description for ParametersUsed states that the output
parameter is only populated of a history snapshot is being rendered. This
causes fairly significant performance issues for me because in some cases I
am passing large parameters. Instead of these parameters just being in the
request, they are in both the request and the response causing the response
to be much larger than I desire.
using System;
using System.Collections.Generic;
using System.Text;
using ConsoleApplication9.rs;
namespace ConsoleApplication9
{
class Program
{
static void Main(string[] args)
{
string encoding, mimeType;
ParameterValue[] pout;
Warning[] w;
string[] s;
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ParameterValue[] pin = new ParameterValue[3];
pin[0] = new ParameterValue();
pin[0].Name = "stdt";
pin[0].Value = "2/19/2007";
pin[1] = new ParameterValue();
pin[1].Name = "enddt";
pin[1].Value = "2/20/2007";
pin[2] = new ParameterValue();
pin[2].Name = "sopco";
pin[2].Value = "A";
rs.Render("/VRU Reports/VRUAppSuccessPercent" /*Report*/,
"PDF" /*Format*/,
null /*HistoryID*/,
null /*DeviceInfo*/,
pin /*Parameters*/,
null /*Credentials*/,
null /*ShowHideToggle*/,
out encoding /*Encoding*/,
out mimeType /*MimeType*/,
out pout /*ParametersUsed*/,
out w /*Warnings*/,
out s /*StreamIds*/);
Console.WriteLine("Length: " + pout.Length);
Console.ReadLine();
}
}
}
"Jerry" wrote:
> In SSRS 2000 SP2 the documentation for ReportingService.Render states that
> the ParametersUsed output parameter should only be populated only if the
> report being rendered is a report history snapshot. I'm invoking the Render
> method with a null HistoryId as follows, so I would expect the ParametersUsed
> output parameter not to get populated.
> results = m_RptService.Render(m_RptCatalogItem.Path /*Report*/,
> m_Format /*Format*/,
> null /*HistoryID*/,
> null /*DeviceInfo*/,
> m_RptParameters /*Parameters*/,
> null /*Credentials*/,
> null /*ShowHideToggle*/,
> out encoding /*Encoding*/,
> out mimeType /*MimeType*/,
> out historyParms /*ParametersUsed*/,
> out warnings /*Warnings*/,
> out streamIDs /*StreamIds*/);
> The call generates the following HTTP traffic. As you can see the HistoryId
> is not sent in the request yet ParametersUsed is populated in the result. If
> I'm interpreting the documentation correctly this value should not be
> populated in the response. I really don't what ParametersUsed to be
> populated in the response. Is this the correct behavior?
> POST /ReportServer/ReportService.asmx HTTP/1.1
> Content-Type: text/xml; charset=utf-8
> SOAPAction:
> "http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render"
> Content-Length: 524
> Expect: 100-continue
> Host: localhost
> Cookie: sqlAuthCookie=****; UsrTkn=>****; AppTkn=>****
> <?xml version="1.0" encoding="utf-8"?><soap:Envelope
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="<soap:Body><Render">http://www.w3.org/2001/XMLSchema"><soap:Body><Render
> xmlns="<Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007
> 7:32:50
> PM</Value></ParameterValue></Parameters></Render></soap:Body></soap:Envelope>
> HTTP/1.1 200 OK
> Date: Mon, 12 Feb 2007 00:32:50 GMT
> Server: Microsoft-IIS/6.0
> X-Powered-By: ASP.NET
> X-AspNet-Version: 1.1.4322
> Cache-Control: private, max-age=0
> Content-Type: text/xml; charset=utf-8
> Content-Length: 9021
> <?xml version="1.0" encoding="utf-8"?><soap:Envelope
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="<soap:Header><ServerInfoHeader">http://www.w3.org/2001/XMLSchema"><soap:Header><ServerInfoHeader
> xmlns="<ReportServerVersionNumber>Microsoft">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><ReportServerVersionNumber>Microsoft
> SQL Server Reporting Services Version
> 8.00.1038.00</ReportServerVersionNumber><ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader><SessionHeader
> xmlns="<SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse
> xmlns="<Result>">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Result>
> Base 64 Encoded response removed to shorten post
> </Result><Encoding>Unicode
> (UTF-8)</Encoding><MimeType>text/html</MimeType><ParametersUsed><ParameterValue><Name>p1</Name><Value>2/11/2007
> 7:32:50 PM</Value></ParameterValue></ParametersUsed><StreamIds
> /></RenderResponse></soap:Body></soap:Envelope>
>
Parameters: How to set null value
Hi, I have a problem.
I have two parameters: Group and Selection
I tryed System.DbNull.Value but give me this error : DbNull is a type in system and cannot be used as an expression
but following your example and this link forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766574&SiteID=1
it works
thanks
I have two parameters: Group and Selection
In the first I have 2 choices: Total and Day
And the value of the second parameter is dynamic and is sets to allow null value
The behaviour I want is this: If a user select Total in the first parameter, the second must be set to NULL, if the selection is Day, the second must be set with a data set.
How I can set the value of the second parameter with null, I tryed
Have anyone an idea?
One way I have done this in the past is using '-1' as the Total value, and then letting the stored procedure pass in a null value by setting all '-1' parameters to null.
if @.param1 = '-1' set @.param1=null
System.DbNull.Value may work too.
Yet another option that may be possible is to use an expression to convert the parameter to a null value, and pass in the expression instead of the parameter to the report.
cheers,
Andrew
|||thanks,I tryed System.DbNull.Value but give me this error : DbNull is a type in system and cannot be used as an expression
Perhaps I wrong something:
I wrote this in the datasets I use for the second parameter
=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull )
Another question, where I have to place this expression, in the store procedure?
if @.param1 = '-1' set @.param1=null
but I use a dataset.
Thanks in advance
|||
Not sure exactly how you are passing in a dataset as a parameter but looks interesting.
Instead of null perhaps you could use "SELECT null as DateID" instead? Or maybe an empty string?
The syntax for getting dbnull is to use System.DBNull.Value
=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull.Value )
cheers,
Andrew
|||If I use this expression "System.DBNull.Value" something don't work but I don't understand what.but following your example and this link forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766574&SiteID=1
it works
thanks
Parameters!FiledName in Custom Code
Hi All,
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>
Parameters with no prompt
Can I have a parameter that wouldn't have a text for the prompt?
Thanksno
but you could use a colon or a period as a prompt - next best thing :)
"Mark Goldin" wrote:
> Can I have a parameter that wouldn't have a text for the prompt?
> Thanks
>
>
Thanksno
but you could use a colon or a period as a prompt - next best thing :)
"Mark Goldin" wrote:
> Can I have a parameter that wouldn't have a text for the prompt?
> Thanks
>
>
Monday, March 12, 2012
Parameters visibility
Hi,
In Report Parameters, there is a settings "Hidden". Once it is clicked, the parameter will be hidden from users.
However, can I make its visibility according to some conditions? Such as when some items in the reports being clicked, or when the parameter drop down list only contain one option?
Thanks in advance.
If you are using the reportviewer controls, you can do this by using the ReportViewer.ServerReport.SetParameters method. The ReportParameter object has a property for visibility.
|||Can you elaborate on this? I'm not sure how/where to use the SetParameters method.I assumed there would be the ability to use expressions for parameter visibility.
|||
There are two places you can control parameter visibility.
The first is from the report definition itself. This is the hidden flag you mentioned. This setting is constant for the report and can't be set to an expression.
The second place to set visibility depends on how you are viewing the report. If you are using URL access directly to the report server or Report Manager, you can't set parameter visibility on an individual parameters basis (you can turn off the entire parameters area using url access to the server). If you are using the ReportViewer controls (either winforms or webforms) that ship with VS 2005, you can use the ReportViewer.ServerReport.SetParameters() call to tell the control to override the visibility setting in the definition. Note that you can only force the parameter hidden, you can't force a parameter to be visible if the report definition has hidden set.
Parameters visibility
Hi,
In Report Parameters, there is a settings "Hidden". Once it is clicked, the parameter will be hidden from users.
However, can I make its visibility according to some conditions? Such as when some items in the reports being clicked, or when the parameter drop down list only contain one option?
Thanks in advance.
If you are using the reportviewer controls, you can do this by using the ReportViewer.ServerReport.SetParameters method. The ReportParameter object has a property for visibility.
|||Can you elaborate on this? I'm not sure how/where to use the SetParameters method.I assumed there would be the ability to use expressions for parameter visibility.
|||
There are two places you can control parameter visibility.
The first is from the report definition itself. This is the hidden flag you mentioned. This setting is constant for the report and can't be set to an expression.
The second place to set visibility depends on how you are viewing the report. If you are using URL access directly to the report server or Report Manager, you can't set parameter visibility on an individual parameters basis (you can turn off the entire parameters area using url access to the server). If you are using the ReportViewer controls (either winforms or webforms) that ship with VS 2005, you can use the ReportViewer.ServerReport.SetParameters() call to tell the control to override the visibility setting in the definition. Note that you can only force the parameter hidden, you can't force a parameter to be visible if the report definition has hidden set.
Parameters using like
Hi,
I have a parameter named County. County, can have multiple values in it -
ex: 01, 02, 03.
I need to somehow use a like statement with this parameter so that it will
find all the counties in this field. I tried like%@.County% but this doesn't
work.
Anybody have any suggestions'
ThanksJill,
I suspect that what you'd like to do is something like this:-
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (1,2,3,4,5)
and then your thinking probably goes that you'd want to replace (1,2,3,4,5)
with a parameter such as:
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (@.CountyParameter)
You could make this work like I'm about to show you. BUT DONT!
EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (' + @.CountyParameter+ ')')
The reason not to do this is that this is very insecure from attacks from
SQL INJECTION. Just consider what would happen if someone passed
4);INSERT INTO CountiesTable(CountyName,
CountyID)VALUES('DisneyFantasyCounty',667)--
as the value for @.CountyParameter (or something far worse).
So having told you what not to do - the correct thing to do is create a
function on the Server such as this one taken directly from the
'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages 534 -
537)
CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
RETURNS @.paramtable
TABLE ( Id int )
AS BEGIN
DECLARE @.len int,
@.index int,
@.nextindex int
SET @.len = DATALENGTH(@.delimString)
SET @.index = 0
SET @.nextindex = 0
WHILE (@.len > @.index )
BEGIN
SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
INSERT @.paramtable
SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
SET @.index = @.nextindex + 1
END
RETURN
END
What this function does is that you pass it a parameter of a delimited
string such as your counties '1;2;6;' and returns a table. This table can
then be joined into your query. This approach is safer from SQL Injection
attacks. And so your Query for the DataSet becomes:
SELECT CountyID, CountyName FROM CountiesTable
INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
ON CountiesTable.CountyID= ParamParserFn.Id
I hope this is able to help you. More details as I mentioned are available
in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
Services. (see http://www.sqlreportingservices.net ). I would heartily
recommend our book to you as a valuable resource that comes with 2.5 GB of
DVD content and video demonstrations, and we especially concentrate on
security matters throughout.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
> Hi,
> I have a parameter named County. County, can have multiple values in it -
> ex: 01, 02, 03.
> I need to somehow use a like statement with this parameter so that it will
> find all the counties in this field. I tried like%@.County% but this
> doesn't
> work.
> Anybody have any suggestions'
> Thanks
>|||We will also natively support multiple values in queries in the next release
of Reporting Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Blackburn (www.sqlreportingservices.net)"
<http://www.sqlreportingservices.net> wrote in message
news:u6Py%239gxEHA.2040@.tk2msftngp13.phx.gbl...
> Jill,
> I suspect that what you'd like to do is something like this:-
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (1,2,3,4,5)
> and then your thinking probably goes that you'd want to replace
> (1,2,3,4,5) with a parameter such as:
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (@.CountyParameter)
> You could make this work like I'm about to show you. BUT DONT!
>
> EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (' + @.CountyParameter+ ')')
> The reason not to do this is that this is very insecure from attacks from
> SQL INJECTION. Just consider what would happen if someone passed
> 4);INSERT INTO CountiesTable(CountyName,
> CountyID)VALUES('DisneyFantasyCounty',667)--
> as the value for @.CountyParameter (or something far worse).
>
>
> So having told you what not to do - the correct thing to do is create a
> function on the Server such as this one taken directly from the
> 'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages
> 534 - 537)
>
> CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
> RETURNS @.paramtable
> TABLE ( Id int )
> AS BEGIN
> DECLARE @.len int,
> @.index int,
> @.nextindex int
> SET @.len = DATALENGTH(@.delimString)
> SET @.index = 0
> SET @.nextindex = 0
> WHILE (@.len > @.index )
> BEGIN
> SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
> if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
> INSERT @.paramtable
> SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
> SET @.index = @.nextindex + 1
> END
> RETURN
> END
> What this function does is that you pass it a parameter of a delimited
> string such as your counties '1;2;6;' and returns a table. This table can
> then be joined into your query. This approach is safer from SQL Injection
> attacks. And so your Query for the DataSet becomes:
> SELECT CountyID, CountyName FROM CountiesTable
> INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
> ON CountiesTable.CountyID= ParamParserFn.Id
>
> I hope this is able to help you. More details as I mentioned are available
> in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
> Services. (see http://www.sqlreportingservices.net ). I would heartily
> recommend our book to you as a valuable resource that comes with 2.5 GB of
> DVD content and video demonstrations, and we especially concentrate on
> security matters throughout.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
>
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
>> Hi,
>> I have a parameter named County. County, can have multiple values in
>> it -
>> ex: 01, 02, 03.
>> I need to somehow use a like statement with this parameter so that it
>> will
>> find all the counties in this field. I tried like%@.County% but this
>> doesn't
>> work.
>> Anybody have any suggestions'
>> Thanks
>
I have a parameter named County. County, can have multiple values in it -
ex: 01, 02, 03.
I need to somehow use a like statement with this parameter so that it will
find all the counties in this field. I tried like%@.County% but this doesn't
work.
Anybody have any suggestions'
ThanksJill,
I suspect that what you'd like to do is something like this:-
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (1,2,3,4,5)
and then your thinking probably goes that you'd want to replace (1,2,3,4,5)
with a parameter such as:
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (@.CountyParameter)
You could make this work like I'm about to show you. BUT DONT!
EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (' + @.CountyParameter+ ')')
The reason not to do this is that this is very insecure from attacks from
SQL INJECTION. Just consider what would happen if someone passed
4);INSERT INTO CountiesTable(CountyName,
CountyID)VALUES('DisneyFantasyCounty',667)--
as the value for @.CountyParameter (or something far worse).
So having told you what not to do - the correct thing to do is create a
function on the Server such as this one taken directly from the
'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages 534 -
537)
CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
RETURNS @.paramtable
TABLE ( Id int )
AS BEGIN
DECLARE @.len int,
@.index int,
@.nextindex int
SET @.len = DATALENGTH(@.delimString)
SET @.index = 0
SET @.nextindex = 0
WHILE (@.len > @.index )
BEGIN
SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
INSERT @.paramtable
SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
SET @.index = @.nextindex + 1
END
RETURN
END
What this function does is that you pass it a parameter of a delimited
string such as your counties '1;2;6;' and returns a table. This table can
then be joined into your query. This approach is safer from SQL Injection
attacks. And so your Query for the DataSet becomes:
SELECT CountyID, CountyName FROM CountiesTable
INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
ON CountiesTable.CountyID= ParamParserFn.Id
I hope this is able to help you. More details as I mentioned are available
in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
Services. (see http://www.sqlreportingservices.net ). I would heartily
recommend our book to you as a valuable resource that comes with 2.5 GB of
DVD content and video demonstrations, and we especially concentrate on
security matters throughout.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
> Hi,
> I have a parameter named County. County, can have multiple values in it -
> ex: 01, 02, 03.
> I need to somehow use a like statement with this parameter so that it will
> find all the counties in this field. I tried like%@.County% but this
> doesn't
> work.
> Anybody have any suggestions'
> Thanks
>|||We will also natively support multiple values in queries in the next release
of Reporting Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Blackburn (www.sqlreportingservices.net)"
<http://www.sqlreportingservices.net> wrote in message
news:u6Py%239gxEHA.2040@.tk2msftngp13.phx.gbl...
> Jill,
> I suspect that what you'd like to do is something like this:-
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (1,2,3,4,5)
> and then your thinking probably goes that you'd want to replace
> (1,2,3,4,5) with a parameter such as:
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (@.CountyParameter)
> You could make this work like I'm about to show you. BUT DONT!
>
> EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (' + @.CountyParameter+ ')')
> The reason not to do this is that this is very insecure from attacks from
> SQL INJECTION. Just consider what would happen if someone passed
> 4);INSERT INTO CountiesTable(CountyName,
> CountyID)VALUES('DisneyFantasyCounty',667)--
> as the value for @.CountyParameter (or something far worse).
>
>
> So having told you what not to do - the correct thing to do is create a
> function on the Server such as this one taken directly from the
> 'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages
> 534 - 537)
>
> CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
> RETURNS @.paramtable
> TABLE ( Id int )
> AS BEGIN
> DECLARE @.len int,
> @.index int,
> @.nextindex int
> SET @.len = DATALENGTH(@.delimString)
> SET @.index = 0
> SET @.nextindex = 0
> WHILE (@.len > @.index )
> BEGIN
> SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
> if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
> INSERT @.paramtable
> SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
> SET @.index = @.nextindex + 1
> END
> RETURN
> END
> What this function does is that you pass it a parameter of a delimited
> string such as your counties '1;2;6;' and returns a table. This table can
> then be joined into your query. This approach is safer from SQL Injection
> attacks. And so your Query for the DataSet becomes:
> SELECT CountyID, CountyName FROM CountiesTable
> INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
> ON CountiesTable.CountyID= ParamParserFn.Id
>
> I hope this is able to help you. More details as I mentioned are available
> in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
> Services. (see http://www.sqlreportingservices.net ). I would heartily
> recommend our book to you as a valuable resource that comes with 2.5 GB of
> DVD content and video demonstrations, and we especially concentrate on
> security matters throughout.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
>
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
>> Hi,
>> I have a parameter named County. County, can have multiple values in
>> it -
>> ex: 01, 02, 03.
>> I need to somehow use a like statement with this parameter so that it
>> will
>> find all the counties in this field. I tried like%@.County% but this
>> doesn't
>> work.
>> Anybody have any suggestions'
>> Thanks
>
Parameters to SP ?
How can I pass a table variable as a parameter to a stored procedure ?
eg.
create proc procname ( @.param table (col1 int ) )
as
select ....
I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.
Is there any alternate way to do this ?
Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.
- CB
Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?
eg.
create proc procname ( @.param table (col1 int ) )
as
select ....
I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.
Is there any alternate way to do this ?
Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.
eg.
create proc procname ( @.param table (col1 int ) )
as
select ....
I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.
Is there any alternate way to do this ?
Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.
- CB
Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?
eg.
create proc procname ( @.param table (col1 int ) )
as
select ....
I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.
Is there any alternate way to do this ?
Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.
Parameters to run different reports?
Is it possible to have a parameter to run different reports depending on what
the user select for the parameter?
For example I want to have Employee, Customers, Inventory, etc for the
report parameter. When the user clicks on Employee it will bring the
employee report with all the parameters needed for that report. If it's
possible, what's the syntax.It sounds more like you're wanting separate actions, that is different
elements on a page that hyperlink to different reports. Check out the
Action property of text boxes, for example. You can specify the various
parameters in the action. Does that do what you want?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"chang" <chang@.discussions.microsoft.com> wrote in message
news:467B6B2C-5D16-4107-B81C-BC4AC06E5711@.microsoft.com...
> Is it possible to have a parameter to run different reports depending on
> what
> the user select for the parameter?
> For example I want to have Employee, Customers, Inventory, etc for the
> report parameter. When the user clicks on Employee it will bring the
> employee report with all the parameters needed for that report. If it's
> possible, what's the syntax.|||Jeff, thanks for replying. It's not related to the Hyperlink action I think.
From my understanding of the Hyperlink Actioin, I can have a textbox jump to
a report, but is it possible to use that as a drop down box that has the name
of each report? Probably I can play with the Hyperlink Action and see if
this will work.
Thanks for the suggestion.
"Jeff A. Stucker" wrote:
> It sounds more like you're wanting separate actions, that is different
> elements on a page that hyperlink to different reports. Check out the
> Action property of text boxes, for example. You can specify the various
> parameters in the action. Does that do what you want?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:467B6B2C-5D16-4107-B81C-BC4AC06E5711@.microsoft.com...
> > Is it possible to have a parameter to run different reports depending on
> > what
> > the user select for the parameter?
> >
> > For example I want to have Employee, Customers, Inventory, etc for the
> > report parameter. When the user clicks on Employee it will bring the
> > employee report with all the parameters needed for that report. If it's
> > possible, what's the syntax.
>
>
the user select for the parameter?
For example I want to have Employee, Customers, Inventory, etc for the
report parameter. When the user clicks on Employee it will bring the
employee report with all the parameters needed for that report. If it's
possible, what's the syntax.It sounds more like you're wanting separate actions, that is different
elements on a page that hyperlink to different reports. Check out the
Action property of text boxes, for example. You can specify the various
parameters in the action. Does that do what you want?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"chang" <chang@.discussions.microsoft.com> wrote in message
news:467B6B2C-5D16-4107-B81C-BC4AC06E5711@.microsoft.com...
> Is it possible to have a parameter to run different reports depending on
> what
> the user select for the parameter?
> For example I want to have Employee, Customers, Inventory, etc for the
> report parameter. When the user clicks on Employee it will bring the
> employee report with all the parameters needed for that report. If it's
> possible, what's the syntax.|||Jeff, thanks for replying. It's not related to the Hyperlink action I think.
From my understanding of the Hyperlink Actioin, I can have a textbox jump to
a report, but is it possible to use that as a drop down box that has the name
of each report? Probably I can play with the Hyperlink Action and see if
this will work.
Thanks for the suggestion.
"Jeff A. Stucker" wrote:
> It sounds more like you're wanting separate actions, that is different
> elements on a page that hyperlink to different reports. Check out the
> Action property of text boxes, for example. You can specify the various
> parameters in the action. Does that do what you want?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:467B6B2C-5D16-4107-B81C-BC4AC06E5711@.microsoft.com...
> > Is it possible to have a parameter to run different reports depending on
> > what
> > the user select for the parameter?
> >
> > For example I want to have Employee, Customers, Inventory, etc for the
> > report parameter. When the user clicks on Employee it will bring the
> > employee report with all the parameters needed for that report. If it's
> > possible, what's the syntax.
>
>
Parameters to reports that only show is previous parameters filled
I don't know if I can do this with Reporting Services. I have ten parameters to a stored procedure. However, each parameter only shows when the previous one is selected. For sake of this discussion, the queries from which the parameters come are completly unrelated.
Initially, only the pull-down for parameter 1 appears. When the user selects a value for parameter 1 using the initial pull-down, the pull down for parameter 2 should appear. When the user selects a value from this pull down, a pull down from parameter 3 should appear.
At any point, the user can click View Report and pass the parameters he selected and all other parameters null.
Thanks in advance for any help.
JustinYes, RS supports sequenced parameter prompting. You build the valid values
query for a parameter using a query that includes previously selected
parameter values. I believe there is a sample called 'Product Line Sales'
that illustrates this technique.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin30519" <Justin30519@.discussions.microsoft.com> wrote in message
news:01FC2CE7-C435-47EC-AD00-E2762510E522@.microsoft.com...
>I don't know if I can do this with Reporting Services. I have ten
>parameters to a stored procedure. However, each parameter only shows when
>the previous one is selected. For sake of this discussion, the queries from
>which the parameters come are completly unrelated.
> Initially, only the pull-down for parameter 1 appears. When the user
> selects a value for parameter 1 using the initial pull-down, the pull down
> for parameter 2 should appear. When the user selects a value from this
> pull down, a pull down from parameter 3 should appear.
> At any point, the user can click View Report and pass the parameters he
> selected and all other parameters null.
> Thanks in advance for any help.
> Justin
>|||> Yes, RS supports sequenced parameter prompting. You build the valid values
> query for a parameter using a query that includes previously selected
> parameter values. I believe there is a sample called 'Product Line Sales'
> that illustrates this technique.
>
Thank you very much for your reply. It was helpful. I can do what my boss asked now. :)
Justin
Initially, only the pull-down for parameter 1 appears. When the user selects a value for parameter 1 using the initial pull-down, the pull down for parameter 2 should appear. When the user selects a value from this pull down, a pull down from parameter 3 should appear.
At any point, the user can click View Report and pass the parameters he selected and all other parameters null.
Thanks in advance for any help.
JustinYes, RS supports sequenced parameter prompting. You build the valid values
query for a parameter using a query that includes previously selected
parameter values. I believe there is a sample called 'Product Line Sales'
that illustrates this technique.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin30519" <Justin30519@.discussions.microsoft.com> wrote in message
news:01FC2CE7-C435-47EC-AD00-E2762510E522@.microsoft.com...
>I don't know if I can do this with Reporting Services. I have ten
>parameters to a stored procedure. However, each parameter only shows when
>the previous one is selected. For sake of this discussion, the queries from
>which the parameters come are completly unrelated.
> Initially, only the pull-down for parameter 1 appears. When the user
> selects a value for parameter 1 using the initial pull-down, the pull down
> for parameter 2 should appear. When the user selects a value from this
> pull down, a pull down from parameter 3 should appear.
> At any point, the user can click View Report and pass the parameters he
> selected and all other parameters null.
> Thanks in advance for any help.
> Justin
>|||> Yes, RS supports sequenced parameter prompting. You build the valid values
> query for a parameter using a query that includes previously selected
> parameter values. I believe there is a sample called 'Product Line Sales'
> that illustrates this technique.
>
Thank you very much for your reply. It was helpful. I can do what my boss asked now. :)
Justin
Parameters that depend on each other
I'm developing a report using SQL2000 which has Agent and SubAgent
parameters. The client wants the Agent parameter selection to filter the
SubAgent, which is easy using cascading parameters, but they also want the
SubAgent selection to filter the Agent list to show those Agent's the
selected SubAgent has worked with. Is there any way of referencing parameters
which are higher up the parameter list without getting a "Forward
dependencies are not valid." error?That sounds like a circular reference which would not work, of course...
you might add a third parameter, to list the Agents the subagent has worked
with... continuing down with the cascading parameter...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Swamp Dog" wrote:
> I'm developing a report using SQL2000 which has Agent and SubAgent
> parameters. The client wants the Agent parameter selection to filter the
> SubAgent, which is easy using cascading parameters, but they also want the
> SubAgent selection to filter the Agent list to show those Agent's the
> selected SubAgent has worked with. Is there any way of referencing parameters
> which are higher up the parameter list without getting a "Forward
> dependencies are not valid." error?
>|||Thanks for the response, Wayne.
That sounds like the only way to do it. I'll check with the client to see if
they are happy with having two different Agent parameters, or if they'd
prefer a more usual Agent-SubAgent relationship.
Regards,
Simon.
"Wayne Snyder" wrote:
> That sounds like a circular reference which would not work, of course...
> you might add a third parameter, to list the Agents the subagent has worked
> with... continuing down with the cascading parameter...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Swamp Dog" wrote:
> > I'm developing a report using SQL2000 which has Agent and SubAgent
> > parameters. The client wants the Agent parameter selection to filter the
> > SubAgent, which is easy using cascading parameters, but they also want the
> > SubAgent selection to filter the Agent list to show those Agent's the
> > selected SubAgent has worked with. Is there any way of referencing parameters
> > which are higher up the parameter list without getting a "Forward
> > dependencies are not valid." error?
> >
parameters. The client wants the Agent parameter selection to filter the
SubAgent, which is easy using cascading parameters, but they also want the
SubAgent selection to filter the Agent list to show those Agent's the
selected SubAgent has worked with. Is there any way of referencing parameters
which are higher up the parameter list without getting a "Forward
dependencies are not valid." error?That sounds like a circular reference which would not work, of course...
you might add a third parameter, to list the Agents the subagent has worked
with... continuing down with the cascading parameter...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Swamp Dog" wrote:
> I'm developing a report using SQL2000 which has Agent and SubAgent
> parameters. The client wants the Agent parameter selection to filter the
> SubAgent, which is easy using cascading parameters, but they also want the
> SubAgent selection to filter the Agent list to show those Agent's the
> selected SubAgent has worked with. Is there any way of referencing parameters
> which are higher up the parameter list without getting a "Forward
> dependencies are not valid." error?
>|||Thanks for the response, Wayne.
That sounds like the only way to do it. I'll check with the client to see if
they are happy with having two different Agent parameters, or if they'd
prefer a more usual Agent-SubAgent relationship.
Regards,
Simon.
"Wayne Snyder" wrote:
> That sounds like a circular reference which would not work, of course...
> you might add a third parameter, to list the Agents the subagent has worked
> with... continuing down with the cascading parameter...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Swamp Dog" wrote:
> > I'm developing a report using SQL2000 which has Agent and SubAgent
> > parameters. The client wants the Agent parameter selection to filter the
> > SubAgent, which is easy using cascading parameters, but they also want the
> > SubAgent selection to filter the Agent list to show those Agent's the
> > selected SubAgent has worked with. Is there any way of referencing parameters
> > which are higher up the parameter list without getting a "Forward
> > dependencies are not valid." error?
> >
Parameters Status in Store Procedure
Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.sqlmonster.com
You can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.c om...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.sqlmonster.com
You can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.c om...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com
Parameters Status in Store Procedure
Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedure.
Please Try To Find it.
--
Message posted via http://www.sqlmonster.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedure.
Please Try To Find it.
--
Message posted via http://www.sqlmonster.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com
Parameters Status in Store Procedure
Hi,
Actually we want to know the type of parameters in stored procedure. Means a
re they Default Parameter or Mandatory. Where this information is store? I c
an get ISNULLABLE Column from syscolumns table but it is only applicable to
table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.droptable.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via droptable.com" <forum@.droptable.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQ
droptable.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.droptable.com
Actually we want to know the type of parameters in stored procedure. Means a
re they Default Parameter or Mandatory. Where this information is store? I c
an get ISNULLABLE Column from syscolumns table but it is only applicable to
table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.droptable.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via droptable.com" <forum@.droptable.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQ
droptable.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.droptable.com
Parameters Select one or all
I have 2 datasets. One contains the information for the report and the other
contains a list of available companies.
In the report parameter area I have the company as a parameter and 'from
query' selected.
When the report runs, the user is prompted to 'Select a Company' from a drop
down list.
I want the user to be able to select one company or all companies. How do I
make this happen?On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> I have 2 datasets. One contains the information for the report and the other
> contains a list of available companies.
> In the report parameter area I have the company as a parameter and 'from
> query' selected.
> When the report runs, the user is prompted to 'Select a Company' from a drop
> down list.
> I want the user to be able to select one company or all companies. How do I
> make this happen?
Hi donna...what version of SSRS are you using? In SSRS 2005 you can
make the drop down a multi-value select by checking the 'multi-value'
box inside the Report Parameters window. This also would require that
your stored procedure be able to process multiple values inside that
parameter. If this is an option I would suggest following the steps
layed out by Bruce Loehle-Cogner here:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
Otherwise if you are using SSRS 2000, or don't want to allow a multi-
select, you could make the query returning the values for the drop
down include a NULL. So something like...
/***/
select NULL as 'value', ' - All - ' as 'label'
union
/* Normal select for list of companies here */
select ... from ...
/***/
then in your stored proc that returns the main set of data have
something like the following in your where clause
/***/
select
...
from
...
where
...
(@.company = company_column or @.company is null)
/***/
This should then return results for all companies if @.company is
null.
Hope this helps!
--
Ben Sullins|||Thanks Ben,
I'm on RS 2000. Your comments were helpfull. I successfully used the UNION
statement on varchar fields. I now have this issue. On a int datatype field
I am receiving the following message: Syntax error converting the varchar
value 'ALL PROJECTS' to a column of data type int. Following is my code.
SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
FROM ProjectActualCost
UNION
SELECT - 1, 'ALL PROJECTS'
Any ideas?
Thanks,
Donna
"sullins602" wrote:
> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> > I have 2 datasets. One contains the information for the report and the other
> > contains a list of available companies.
> >
> > In the report parameter area I have the company as a parameter and 'from
> > query' selected.
> >
> > When the report runs, the user is prompted to 'Select a Company' from a drop
> > down list.
> >
> > I want the user to be able to select one company or all companies. How do I
> > make this happen?
> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> make the drop down a multi-value select by checking the 'multi-value'
> box inside the Report Parameters window. This also would require that
> your stored procedure be able to process multiple values inside that
> parameter. If this is an option I would suggest following the steps
> layed out by Bruce Loehle-Cogner here:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>
> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> select, you could make the query returning the values for the drop
> down include a NULL. So something like...
> /***/
> select NULL as 'value', ' - All - ' as 'label'
> union
> /* Normal select for list of companies here */
> select ... from ...
> /***/
> then in your stored proc that returns the main set of data have
> something like the following in your where clause
> /***/
> select
> ...
> from
> ...
> where
> ...
> (@.company = company_column or @.company is null)
> /***/
> This should then return results for all companies if @.company is
> null.
> Hope this helps!
> --
> Ben Sullins
>|||SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
FROM ProjectActualCost
UNION
SELECT - 1 as value, 'ALL PROJECTS' as label
Also, I like to use value and label when creating this, it makes it real
obvious which one you are using when you are filling in the properties for
the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DONNA" <DONNA@.discussions.microsoft.com> wrote in message
news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> Thanks Ben,
> I'm on RS 2000. Your comments were helpfull. I successfully used the
> UNION
> statement on varchar fields. I now have this issue. On a int datatype
> field
> I am receiving the following message: Syntax error converting the varchar
> value 'ALL PROJECTS' to a column of data type int. Following is my code.
> SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> FROM ProjectActualCost
> UNION
> SELECT - 1, 'ALL PROJECTS'
> Any ideas?
> Thanks,
> Donna
>
> "sullins602" wrote:
>> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> > I have 2 datasets. One contains the information for the report and the
>> > other
>> > contains a list of available companies.
>> >
>> > In the report parameter area I have the company as a parameter and
>> > 'from
>> > query' selected.
>> >
>> > When the report runs, the user is prompted to 'Select a Company' from a
>> > drop
>> > down list.
>> >
>> > I want the user to be able to select one company or all companies. How
>> > do I
>> > make this happen?
>> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> make the drop down a multi-value select by checking the 'multi-value'
>> box inside the Report Parameters window. This also would require that
>> your stored procedure be able to process multiple values inside that
>> parameter. If this is an option I would suggest following the steps
>> layed out by Bruce Loehle-Cogner here:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>>
>> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> select, you could make the query returning the values for the drop
>> down include a NULL. So something like...
>> /***/
>> select NULL as 'value', ' - All - ' as 'label'
>> union
>> /* Normal select for list of companies here */
>> select ... from ...
>> /***/
>> then in your stored proc that returns the main set of data have
>> something like the following in your where clause
>> /***/
>> select
>> ...
>> from
>> ...
>> where
>> ...
>> (@.company = company_column or @.company is null)
>> /***/
>> This should then return results for all companies if @.company is
>> null.
>> Hope this helps!
>> --
>> Ben Sullins
>>|||How do I manage this same concept with a datetime value? I'm having some
problems with an error related to converting to datetime from string.
Query parameter:
SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
sent_date
FROM o_dpl_deployment
WHERE sent_date IS NOT NULL
UNION
SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
Main Query:
...
((D.sent_date = @.sent_date) OR (@.sent_date ='1753-01-01')) AND
...
"Bruce L-C [MVP]" wrote:
> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
> FROM ProjectActualCost
> UNION
> SELECT - 1 as value, 'ALL PROJECTS' as label
> Also, I like to use value and label when creating this, it makes it real
> obvious which one you are using when you are filling in the properties for
> the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> > Thanks Ben,
> >
> > I'm on RS 2000. Your comments were helpfull. I successfully used the
> > UNION
> > statement on varchar fields. I now have this issue. On a int datatype
> > field
> > I am receiving the following message: Syntax error converting the varchar
> > value 'ALL PROJECTS' to a column of data type int. Following is my code.
> >
> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> > FROM ProjectActualCost
> > UNION
> > SELECT - 1, 'ALL PROJECTS'
> >
> > Any ideas?
> >
> > Thanks,
> >
> > Donna
> >
> >
> >
> > "sullins602" wrote:
> >
> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> >> > I have 2 datasets. One contains the information for the report and the
> >> > other
> >> > contains a list of available companies.
> >> >
> >> > In the report parameter area I have the company as a parameter and
> >> > 'from
> >> > query' selected.
> >> >
> >> > When the report runs, the user is prompted to 'Select a Company' from a
> >> > drop
> >> > down list.
> >> >
> >> > I want the user to be able to select one company or all companies. How
> >> > do I
> >> > make this happen?
> >>
> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> >> make the drop down a multi-value select by checking the 'multi-value'
> >> box inside the Report Parameters window. This also would require that
> >> your stored procedure be able to process multiple values inside that
> >> parameter. If this is an option I would suggest following the steps
> >> layed out by Bruce Loehle-Cogner here:
> >>
> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
> >>
> >>
> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> >> select, you could make the query returning the values for the drop
> >> down include a NULL. So something like...
> >>
> >> /***/
> >> select NULL as 'value', ' - All - ' as 'label'
> >> union
> >> /* Normal select for list of companies here */
> >> select ... from ...
> >> /***/
> >>
> >> then in your stored proc that returns the main set of data have
> >> something like the following in your where clause
> >>
> >> /***/
> >> select
> >> ...
> >> from
> >> ...
> >> where
> >> ...
> >> (@.company = company_column or @.company is null)
> >> /***/
> >>
> >> This should then return results for all companies if @.company is
> >> null.
> >>
> >> Hope this helps!
> >> --
> >> Ben Sullins
> >>
> >>
>
>|||SELECT convert(datetime,'1753-01-01') as value, 'All Deployment Dates' as
sent_date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:49D8B8E1-7657-4D1D-92D5-38D3ED874751@.microsoft.com...
> How do I manage this same concept with a datetime value? I'm having some
> problems with an error related to converting to datetime from string.
> Query parameter:
> SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
> sent_date
> FROM o_dpl_deployment
> WHERE sent_date IS NOT NULL
> UNION
> SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
> Main Query:
> ...
> ((D.sent_date = @.sent_date) OR (@.sent_date => '1753-01-01')) AND
> ...
>
> "Bruce L-C [MVP]" wrote:
>> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
>> FROM ProjectActualCost
>> UNION
>> SELECT - 1 as value, 'ALL PROJECTS' as label
>> Also, I like to use value and label when creating this, it makes it real
>> obvious which one you are using when you are filling in the properties
>> for
>> the parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
>> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
>> > Thanks Ben,
>> >
>> > I'm on RS 2000. Your comments were helpfull. I successfully used the
>> > UNION
>> > statement on varchar fields. I now have this issue. On a int datatype
>> > field
>> > I am receiving the following message: Syntax error converting the
>> > varchar
>> > value 'ALL PROJECTS' to a column of data type int. Following is my
>> > code.
>> >
>> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
>> > FROM ProjectActualCost
>> > UNION
>> > SELECT - 1, 'ALL PROJECTS'
>> >
>> > Any ideas?
>> >
>> > Thanks,
>> >
>> > Donna
>> >
>> >
>> >
>> > "sullins602" wrote:
>> >
>> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> >> > I have 2 datasets. One contains the information for the report and
>> >> > the
>> >> > other
>> >> > contains a list of available companies.
>> >> >
>> >> > In the report parameter area I have the company as a parameter and
>> >> > 'from
>> >> > query' selected.
>> >> >
>> >> > When the report runs, the user is prompted to 'Select a Company'
>> >> > from a
>> >> > drop
>> >> > down list.
>> >> >
>> >> > I want the user to be able to select one company or all companies.
>> >> > How
>> >> > do I
>> >> > make this happen?
>> >>
>> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> >> make the drop down a multi-value select by checking the 'multi-value'
>> >> box inside the Report Parameters window. This also would require that
>> >> your stored procedure be able to process multiple values inside that
>> >> parameter. If this is an option I would suggest following the steps
>> >> layed out by Bruce Loehle-Cogner here:
>> >>
>> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>> >>
>> >>
>> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> >> select, you could make the query returning the values for the drop
>> >> down include a NULL. So something like...
>> >>
>> >> /***/
>> >> select NULL as 'value', ' - All - ' as 'label'
>> >> union
>> >> /* Normal select for list of companies here */
>> >> select ... from ...
>> >> /***/
>> >>
>> >> then in your stored proc that returns the main set of data have
>> >> something like the following in your where clause
>> >>
>> >> /***/
>> >> select
>> >> ...
>> >> from
>> >> ...
>> >> where
>> >> ...
>> >> (@.company = company_column or @.company is null)
>> >> /***/
>> >>
>> >> This should then return results for all companies if @.company is
>> >> null.
>> >>
>> >> Hope this helps!
>> >> --
>> >> Ben Sullins
>> >>
>> >>
>>
contains a list of available companies.
In the report parameter area I have the company as a parameter and 'from
query' selected.
When the report runs, the user is prompted to 'Select a Company' from a drop
down list.
I want the user to be able to select one company or all companies. How do I
make this happen?On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> I have 2 datasets. One contains the information for the report and the other
> contains a list of available companies.
> In the report parameter area I have the company as a parameter and 'from
> query' selected.
> When the report runs, the user is prompted to 'Select a Company' from a drop
> down list.
> I want the user to be able to select one company or all companies. How do I
> make this happen?
Hi donna...what version of SSRS are you using? In SSRS 2005 you can
make the drop down a multi-value select by checking the 'multi-value'
box inside the Report Parameters window. This also would require that
your stored procedure be able to process multiple values inside that
parameter. If this is an option I would suggest following the steps
layed out by Bruce Loehle-Cogner here:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
Otherwise if you are using SSRS 2000, or don't want to allow a multi-
select, you could make the query returning the values for the drop
down include a NULL. So something like...
/***/
select NULL as 'value', ' - All - ' as 'label'
union
/* Normal select for list of companies here */
select ... from ...
/***/
then in your stored proc that returns the main set of data have
something like the following in your where clause
/***/
select
...
from
...
where
...
(@.company = company_column or @.company is null)
/***/
This should then return results for all companies if @.company is
null.
Hope this helps!
--
Ben Sullins|||Thanks Ben,
I'm on RS 2000. Your comments were helpfull. I successfully used the UNION
statement on varchar fields. I now have this issue. On a int datatype field
I am receiving the following message: Syntax error converting the varchar
value 'ALL PROJECTS' to a column of data type int. Following is my code.
SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
FROM ProjectActualCost
UNION
SELECT - 1, 'ALL PROJECTS'
Any ideas?
Thanks,
Donna
"sullins602" wrote:
> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> > I have 2 datasets. One contains the information for the report and the other
> > contains a list of available companies.
> >
> > In the report parameter area I have the company as a parameter and 'from
> > query' selected.
> >
> > When the report runs, the user is prompted to 'Select a Company' from a drop
> > down list.
> >
> > I want the user to be able to select one company or all companies. How do I
> > make this happen?
> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> make the drop down a multi-value select by checking the 'multi-value'
> box inside the Report Parameters window. This also would require that
> your stored procedure be able to process multiple values inside that
> parameter. If this is an option I would suggest following the steps
> layed out by Bruce Loehle-Cogner here:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>
> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> select, you could make the query returning the values for the drop
> down include a NULL. So something like...
> /***/
> select NULL as 'value', ' - All - ' as 'label'
> union
> /* Normal select for list of companies here */
> select ... from ...
> /***/
> then in your stored proc that returns the main set of data have
> something like the following in your where clause
> /***/
> select
> ...
> from
> ...
> where
> ...
> (@.company = company_column or @.company is null)
> /***/
> This should then return results for all companies if @.company is
> null.
> Hope this helps!
> --
> Ben Sullins
>|||SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
FROM ProjectActualCost
UNION
SELECT - 1 as value, 'ALL PROJECTS' as label
Also, I like to use value and label when creating this, it makes it real
obvious which one you are using when you are filling in the properties for
the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DONNA" <DONNA@.discussions.microsoft.com> wrote in message
news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> Thanks Ben,
> I'm on RS 2000. Your comments were helpfull. I successfully used the
> UNION
> statement on varchar fields. I now have this issue. On a int datatype
> field
> I am receiving the following message: Syntax error converting the varchar
> value 'ALL PROJECTS' to a column of data type int. Following is my code.
> SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> FROM ProjectActualCost
> UNION
> SELECT - 1, 'ALL PROJECTS'
> Any ideas?
> Thanks,
> Donna
>
> "sullins602" wrote:
>> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> > I have 2 datasets. One contains the information for the report and the
>> > other
>> > contains a list of available companies.
>> >
>> > In the report parameter area I have the company as a parameter and
>> > 'from
>> > query' selected.
>> >
>> > When the report runs, the user is prompted to 'Select a Company' from a
>> > drop
>> > down list.
>> >
>> > I want the user to be able to select one company or all companies. How
>> > do I
>> > make this happen?
>> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> make the drop down a multi-value select by checking the 'multi-value'
>> box inside the Report Parameters window. This also would require that
>> your stored procedure be able to process multiple values inside that
>> parameter. If this is an option I would suggest following the steps
>> layed out by Bruce Loehle-Cogner here:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>>
>> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> select, you could make the query returning the values for the drop
>> down include a NULL. So something like...
>> /***/
>> select NULL as 'value', ' - All - ' as 'label'
>> union
>> /* Normal select for list of companies here */
>> select ... from ...
>> /***/
>> then in your stored proc that returns the main set of data have
>> something like the following in your where clause
>> /***/
>> select
>> ...
>> from
>> ...
>> where
>> ...
>> (@.company = company_column or @.company is null)
>> /***/
>> This should then return results for all companies if @.company is
>> null.
>> Hope this helps!
>> --
>> Ben Sullins
>>|||How do I manage this same concept with a datetime value? I'm having some
problems with an error related to converting to datetime from string.
Query parameter:
SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
sent_date
FROM o_dpl_deployment
WHERE sent_date IS NOT NULL
UNION
SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
Main Query:
...
((D.sent_date = @.sent_date) OR (@.sent_date ='1753-01-01')) AND
...
"Bruce L-C [MVP]" wrote:
> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
> FROM ProjectActualCost
> UNION
> SELECT - 1 as value, 'ALL PROJECTS' as label
> Also, I like to use value and label when creating this, it makes it real
> obvious which one you are using when you are filling in the properties for
> the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> > Thanks Ben,
> >
> > I'm on RS 2000. Your comments were helpfull. I successfully used the
> > UNION
> > statement on varchar fields. I now have this issue. On a int datatype
> > field
> > I am receiving the following message: Syntax error converting the varchar
> > value 'ALL PROJECTS' to a column of data type int. Following is my code.
> >
> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> > FROM ProjectActualCost
> > UNION
> > SELECT - 1, 'ALL PROJECTS'
> >
> > Any ideas?
> >
> > Thanks,
> >
> > Donna
> >
> >
> >
> > "sullins602" wrote:
> >
> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> >> > I have 2 datasets. One contains the information for the report and the
> >> > other
> >> > contains a list of available companies.
> >> >
> >> > In the report parameter area I have the company as a parameter and
> >> > 'from
> >> > query' selected.
> >> >
> >> > When the report runs, the user is prompted to 'Select a Company' from a
> >> > drop
> >> > down list.
> >> >
> >> > I want the user to be able to select one company or all companies. How
> >> > do I
> >> > make this happen?
> >>
> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> >> make the drop down a multi-value select by checking the 'multi-value'
> >> box inside the Report Parameters window. This also would require that
> >> your stored procedure be able to process multiple values inside that
> >> parameter. If this is an option I would suggest following the steps
> >> layed out by Bruce Loehle-Cogner here:
> >>
> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
> >>
> >>
> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> >> select, you could make the query returning the values for the drop
> >> down include a NULL. So something like...
> >>
> >> /***/
> >> select NULL as 'value', ' - All - ' as 'label'
> >> union
> >> /* Normal select for list of companies here */
> >> select ... from ...
> >> /***/
> >>
> >> then in your stored proc that returns the main set of data have
> >> something like the following in your where clause
> >>
> >> /***/
> >> select
> >> ...
> >> from
> >> ...
> >> where
> >> ...
> >> (@.company = company_column or @.company is null)
> >> /***/
> >>
> >> This should then return results for all companies if @.company is
> >> null.
> >>
> >> Hope this helps!
> >> --
> >> Ben Sullins
> >>
> >>
>
>|||SELECT convert(datetime,'1753-01-01') as value, 'All Deployment Dates' as
sent_date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:49D8B8E1-7657-4D1D-92D5-38D3ED874751@.microsoft.com...
> How do I manage this same concept with a datetime value? I'm having some
> problems with an error related to converting to datetime from string.
> Query parameter:
> SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
> sent_date
> FROM o_dpl_deployment
> WHERE sent_date IS NOT NULL
> UNION
> SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
> Main Query:
> ...
> ((D.sent_date = @.sent_date) OR (@.sent_date => '1753-01-01')) AND
> ...
>
> "Bruce L-C [MVP]" wrote:
>> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
>> FROM ProjectActualCost
>> UNION
>> SELECT - 1 as value, 'ALL PROJECTS' as label
>> Also, I like to use value and label when creating this, it makes it real
>> obvious which one you are using when you are filling in the properties
>> for
>> the parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
>> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
>> > Thanks Ben,
>> >
>> > I'm on RS 2000. Your comments were helpfull. I successfully used the
>> > UNION
>> > statement on varchar fields. I now have this issue. On a int datatype
>> > field
>> > I am receiving the following message: Syntax error converting the
>> > varchar
>> > value 'ALL PROJECTS' to a column of data type int. Following is my
>> > code.
>> >
>> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
>> > FROM ProjectActualCost
>> > UNION
>> > SELECT - 1, 'ALL PROJECTS'
>> >
>> > Any ideas?
>> >
>> > Thanks,
>> >
>> > Donna
>> >
>> >
>> >
>> > "sullins602" wrote:
>> >
>> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> >> > I have 2 datasets. One contains the information for the report and
>> >> > the
>> >> > other
>> >> > contains a list of available companies.
>> >> >
>> >> > In the report parameter area I have the company as a parameter and
>> >> > 'from
>> >> > query' selected.
>> >> >
>> >> > When the report runs, the user is prompted to 'Select a Company'
>> >> > from a
>> >> > drop
>> >> > down list.
>> >> >
>> >> > I want the user to be able to select one company or all companies.
>> >> > How
>> >> > do I
>> >> > make this happen?
>> >>
>> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> >> make the drop down a multi-value select by checking the 'multi-value'
>> >> box inside the Report Parameters window. This also would require that
>> >> your stored procedure be able to process multiple values inside that
>> >> parameter. If this is an option I would suggest following the steps
>> >> layed out by Bruce Loehle-Cogner here:
>> >>
>> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>> >>
>> >>
>> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> >> select, you could make the query returning the values for the drop
>> >> down include a NULL. So something like...
>> >>
>> >> /***/
>> >> select NULL as 'value', ' - All - ' as 'label'
>> >> union
>> >> /* Normal select for list of companies here */
>> >> select ... from ...
>> >> /***/
>> >>
>> >> then in your stored proc that returns the main set of data have
>> >> something like the following in your where clause
>> >>
>> >> /***/
>> >> select
>> >> ...
>> >> from
>> >> ...
>> >> where
>> >> ...
>> >> (@.company = company_column or @.company is null)
>> >> /***/
>> >>
>> >> This should then return results for all companies if @.company is
>> >> null.
>> >>
>> >> Hope this helps!
>> >> --
>> >> Ben Sullins
>> >>
>> >>
>>
parameters really only a filter?
Am I missing a trick here? When i set up my parameter in reporting
services and hit preview, the prompt apears and asks for the specified
column - however the sql generated is not refined any. it brings back
all the data and then filters on the selected column in the report.
By contrast in business objects if i used the @.prompt function the sql
generated would be refined with the input of the end user ...eg
cost_centre_code = 1234 as opposed all cost centres. What reporting
services seems to be doing is bringing back all cost centres then
filtering on 1234.
If im right it has some pretty serious implications in terms of speed
of reporting -as my query is returning all data values rather than
one...
any ideas? any input? Isnt a parameter really just a filter rather
than a condition?
thanks for any ideas
gregIt depends on what you are doing. If you use the parameter in the WHERE
clause of your SQL then you would indeed be doing the 'filtering' on the
Database Server. If you are putting your parameter in a filter expression -
then yes this means that the filtering is done in the Report Server, and all
the data is requested from the Database Server.
You are right it is better to optimise queries to have the data pre-filtered
on the Database Server where advantage can be taken of Indexes.
We cover these issues in depth in Chapter 6 of our book - "Hitchhiker's
Guide to SQL Server 2000 Reporting Services"
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Greg, I answered this very completely at about 1:30 in your other posting.
Just to reiterate. Filters and query parameters are two different things and
both can use report parameters. You are using filters (which retrieve and
then filter) versus query parameters that are part of the where clause.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Hi all -
Thanks for the info. I have now a parameter working on the database
server - specified in the where clause as you say - thats great.
However What I want is a prompt for the user - to enter in an
assignment id. When I pass the parameter to the where clause and hit
run in the Data tab I am asked for the Parameter value (on the Data
tab ) here I want a drop down list of values - which I would like
replicated when in the preview screen - so that the user can enter a
value from the drop down list of values box. At the moment I can only
enter in the preview panel what i have specified in the Data tab and
bring back the correct result. How can I get a drop down Lovs
todisplay when the parameter is in the where clause. I tried to point
both label and value to the specific data set but it would not allow -
saying:
"A label expression used for the report parameter ?ApplicationID'
refers to a field. Fields cannot be used in report parameter
expressions."
Any ideas
help is very much appreciated
Greg
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> Greg, I answered this very completely at about 1:30 in your other posting.
> Just to reiterate. Filters and query parameters are two different things and
> both can use report parameters. You are using filters (which retrieve and
> then filter) versus query parameters that are part of the where clause.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <greg_cochrane@.hotmail.com> wrote in message
> news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > Am I missing a trick here? When i set up my parameter in reporting
> > services and hit preview, the prompt apears and asks for the specified
> > column - however the sql generated is not refined any. it brings back
> > all the data and then filters on the selected column in the report.
> >
> > By contrast in business objects if i used the @.prompt function the sql
> > generated would be refined with the input of the end user ...eg
> > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > services seems to be doing is bringing back all cost centres then
> > filtering on 1234.
> >
> > If im right it has some pretty serious implications in terms of speed
> > of reporting -as my query is returning all data values rather than
> > one...
> >
> > any ideas? any input? Isnt a parameter really just a filter rather
> > than a condition?
> >
> >
> > thanks for any ideas
> >
> > greg|||You are missing a concept here. You only use the data tab to test out your
query. To test your report you use the preview tab. That is where you have
control over the parameters. The parameters can be free form, based on a
list or based on a dataset. In the layout tab go to Report menu, report
parameters and you'll see where you do this. The report parameters have to
be mapped to the query parameters as well (they might be already). You can
check this by clicking on the ... in the data tab and going to the
parameters tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411100801.51f5bf73@.posting.google.com...
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter 'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other
posting.
> > Just to reiterate. Filters and query parameters are two different things
and
> > both can use report parameters. You are using filters (which retrieve
and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg|||If you want the drop-down you need to do the following:
1) Create a second Dataset that returns the list of Valid Assignment ID's
and a second column for the labels you want in the drop-down
2) On the main menu, click on Report -> Report Parameters
3) Click on the Assignment ID parameter
4) In the Available Values area, click on From Query
5) Choose the new Assignment ID DataSet, Value Field, and Label Field
Now try the Preview tab.
Hope this helps
"Greg" wrote:
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter â'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other posting.
> > Just to reiterate. Filters and query parameters are two different things and
> > both can use report parameters. You are using filters (which retrieve and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg
>|||Hi there - sorry Im really new to reporting services and this is going
to sound like a simple question! but how do I create a new data set
within a current report' I am trying to build a list of values for a
column in my first report - see below.
Many thanks - help is appreciated.
Greg
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> If you want the drop-down you need to do the following:
> 1) Create a second Dataset that returns the list of Valid Assignment ID's
> and a second column for the labels you want in the drop-down
> 2) On the main menu, click on Report -> Report Parameters
> 3) Click on the Assignment ID parameter
> 4) In the Available Values area, click on From Query
> 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> Now try the Preview tab.
> Hope this helps
> "Greg" wrote:
> > Hi all -
> >
> > Thanks for the info. I have now a parameter working on the database
> > server - specified in the where clause as you say - thats great.
> > However What I want is a prompt for the user - to enter in an
> > assignment id. When I pass the parameter to the where clause and hit
> > run in the Data tab I am asked for the Parameter value (on the Data
> > tab ) here I want a drop down list of values - which I would like
> > replicated when in the preview screen - so that the user can enter a
> > value from the drop down list of values box. At the moment I can only
> > enter in the preview panel what i have specified in the Data tab and
> > bring back the correct result. How can I get a drop down Lovs
> > todisplay when the parameter is in the where clause. I tried to point
> > both label and value to the specific data set but it would not allow -
> > saying:
> > "A label expression used for the report parameter â'ApplicationID'
> > refers to a field. Fields cannot be used in report parameter
> > expressions."
> >
> > Any ideas
> >
> > help is very much appreciated
> >
> > Greg
> >
> >
> >
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > Just to reiterate. Filters and query parameters are two different things and
> > > both can use report parameters. You are using filters (which retrieve and
> > > then filter) versus query parameters that are part of the where clause.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > services and hit preview, the prompt apears and asks for the specified
> > > > column - however the sql generated is not refined any. it brings back
> > > > all the data and then filters on the selected column in the report.
> > > >
> > > > By contrast in business objects if i used the @.prompt function the sql
> > > > generated would be refined with the input of the end user ...eg
> > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > services seems to be doing is bringing back all cost centres then
> > > > filtering on 1234.
> > > >
> > > > If im right it has some pretty serious implications in terms of speed
> > > > of reporting -as my query is returning all data values rather than
> > > > one...
> > > >
> > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > than a condition?
> > > >
> > > >
> > > > thanks for any ideas
> > > >
> > > > greg
> >|||Q Do i "add a new item" and write the sql free hand in there? or do I
create a new report? if so how do I link the result set achieved
through to the parameter on my first report?
Greg
greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> Hi there - sorry Im really new to reporting services and this is going
> to sound like a simple question! but how do I create a new data set
> within a current report' I am trying to build a list of values for a
> column in my first report - see below.
> Many thanks - help is appreciated.
> Greg
>
>
> "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > If you want the drop-down you need to do the following:
> >
> > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > and a second column for the labels you want in the drop-down
> > 2) On the main menu, click on Report -> Report Parameters
> > 3) Click on the Assignment ID parameter
> > 4) In the Available Values area, click on From Query
> > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> >
> > Now try the Preview tab.
> >
> > Hope this helps
> >
> > "Greg" wrote:
> >
> > > Hi all -
> > >
> > > Thanks for the info. I have now a parameter working on the database
> > > server - specified in the where clause as you say - thats great.
> > > However What I want is a prompt for the user - to enter in an
> > > assignment id. When I pass the parameter to the where clause and hit
> > > run in the Data tab I am asked for the Parameter value (on the Data
> > > tab ) here I want a drop down list of values - which I would like
> > > replicated when in the preview screen - so that the user can enter a
> > > value from the drop down list of values box. At the moment I can only
> > > enter in the preview panel what i have specified in the Data tab and
> > > bring back the correct result. How can I get a drop down Lovs
> > > todisplay when the parameter is in the where clause. I tried to point
> > > both label and value to the specific data set but it would not allow -
> > > saying:
> > > "A label expression used for the report parameter â'ApplicationID'
> > > refers to a field. Fields cannot be used in report parameter
> > > expressions."
> > >
> > > Any ideas
> > >
> > > help is very much appreciated
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > Just to reiterate. Filters and query parameters are two different things and
> > > > both can use report parameters. You are using filters (which retrieve and
> > > > then filter) versus query parameters that are part of the where clause.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > column - however the sql generated is not refined any. it brings back
> > > > > all the data and then filters on the selected column in the report.
> > > > >
> > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > generated would be refined with the input of the end user ...eg
> > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > services seems to be doing is bringing back all cost centres then
> > > > > filtering on 1234.
> > > > >
> > > > > If im right it has some pretty serious implications in terms of speed
> > > > > of reporting -as my query is returning all data values rather than
> > > > > one...
> > > > >
> > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > than a condition?
> > > > >
> > > > >
> > > > > thanks for any ideas
> > > > >
> > > > > greg
> > >|||go to the data tab and click on the drop down nesxt to your dataset name.
one of the options will be <new dataset> you will need to code the SQL but
for a parameter list it should be straightforward.
"Greg" wrote:
> Q Do i "add a new item" and write the sql free hand in there? or do I
> create a new report? if so how do I link the result set achieved
> through to the parameter on my first report?
>
> Greg
>
> greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > Hi there - sorry Im really new to reporting services and this is going
> > to sound like a simple question! but how do I create a new data set
> > within a current report' I am trying to build a list of values for a
> > column in my first report - see below.
> >
> > Many thanks - help is appreciated.
> >
> > Greg
> >
> >
> >
> >
> > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > If you want the drop-down you need to do the following:
> > >
> > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > and a second column for the labels you want in the drop-down
> > > 2) On the main menu, click on Report -> Report Parameters
> > > 3) Click on the Assignment ID parameter
> > > 4) In the Available Values area, click on From Query
> > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > >
> > > Now try the Preview tab.
> > >
> > > Hope this helps
> > >
> > > "Greg" wrote:
> > >
> > > > Hi all -
> > > >
> > > > Thanks for the info. I have now a parameter working on the database
> > > > server - specified in the where clause as you say - thats great.
> > > > However What I want is a prompt for the user - to enter in an
> > > > assignment id. When I pass the parameter to the where clause and hit
> > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > tab ) here I want a drop down list of values - which I would like
> > > > replicated when in the preview screen - so that the user can enter a
> > > > value from the drop down list of values box. At the moment I can only
> > > > enter in the preview panel what i have specified in the Data tab and
> > > > bring back the correct result. How can I get a drop down Lovs
> > > > todisplay when the parameter is in the where clause. I tried to point
> > > > both label and value to the specific data set but it would not allow -
> > > > saying:
> > > > "A label expression used for the report parameter â'ApplicationID'
> > > > refers to a field. Fields cannot be used in report parameter
> > > > expressions."
> > > >
> > > > Any ideas
> > > >
> > > > help is very much appreciated
> > > >
> > > > Greg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > then filter) versus query parameters that are part of the where clause.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > all the data and then filters on the selected column in the report.
> > > > > >
> > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > generated would be refined with the input of the end user ...eg
> > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > filtering on 1234.
> > > > > >
> > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > of reporting -as my query is returning all data values rather than
> > > > > > one...
> > > > > >
> > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > than a condition?
> > > > > >
> > > > > >
> > > > > > thanks for any ideas
> > > > > >
> > > > > > greg
> > > >
>|||Hi John - thanks very much!! got it working now!
Greg
"johnE" <johnE@.discussions.microsoft.com> wrote in message news:<70BEB3D8-6A8D-4609-A7D5-D387A4CFE865@.microsoft.com>...
> go to the data tab and click on the drop down nesxt to your dataset name.
> one of the options will be <new dataset> you will need to code the SQL but
> for a parameter list it should be straightforward.
> "Greg" wrote:
> > Q Do i "add a new item" and write the sql free hand in there? or do I
> > create a new report? if so how do I link the result set achieved
> > through to the parameter on my first report?
> >
> >
> > Greg
> >
> >
> > greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > > Hi there - sorry Im really new to reporting services and this is going
> > > to sound like a simple question! but how do I create a new data set
> > > within a current report' I am trying to build a list of values for a
> > > column in my first report - see below.
> > >
> > > Many thanks - help is appreciated.
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > > If you want the drop-down you need to do the following:
> > > >
> > > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > > and a second column for the labels you want in the drop-down
> > > > 2) On the main menu, click on Report -> Report Parameters
> > > > 3) Click on the Assignment ID parameter
> > > > 4) In the Available Values area, click on From Query
> > > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > > >
> > > > Now try the Preview tab.
> > > >
> > > > Hope this helps
> > > >
> > > > "Greg" wrote:
> > > >
> > > > > Hi all -
> > > > >
> > > > > Thanks for the info. I have now a parameter working on the database
> > > > > server - specified in the where clause as you say - thats great.
> > > > > However What I want is a prompt for the user - to enter in an
> > > > > assignment id. When I pass the parameter to the where clause and hit
> > > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > > tab ) here I want a drop down list of values - which I would like
> > > > > replicated when in the preview screen - so that the user can enter a
> > > > > value from the drop down list of values box. At the moment I can only
> > > > > enter in the preview panel what i have specified in the Data tab and
> > > > > bring back the correct result. How can I get a drop down Lovs
> > > > > todisplay when the parameter is in the where clause. I tried to point
> > > > > both label and value to the specific data set but it would not allow -
> > > > > saying:
> > > > > "A label expression used for the report parameter â'ApplicationID'
> > > > > refers to a field. Fields cannot be used in report parameter
> > > > > expressions."
> > > > >
> > > > > Any ideas
> > > > >
> > > > > help is very much appreciated
> > > > >
> > > > > Greg
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > > then filter) versus query parameters that are part of the where clause.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > > all the data and then filters on the selected column in the report.
> > > > > > >
> > > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > > generated would be refined with the input of the end user ...eg
> > > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > > filtering on 1234.
> > > > > > >
> > > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > > of reporting -as my query is returning all data values rather than
> > > > > > > one...
> > > > > > >
> > > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > > than a condition?
> > > > > > >
> > > > > > >
> > > > > > > thanks for any ideas
> > > > > > >
> > > > > > > greg
> > > > >
> >
services and hit preview, the prompt apears and asks for the specified
column - however the sql generated is not refined any. it brings back
all the data and then filters on the selected column in the report.
By contrast in business objects if i used the @.prompt function the sql
generated would be refined with the input of the end user ...eg
cost_centre_code = 1234 as opposed all cost centres. What reporting
services seems to be doing is bringing back all cost centres then
filtering on 1234.
If im right it has some pretty serious implications in terms of speed
of reporting -as my query is returning all data values rather than
one...
any ideas? any input? Isnt a parameter really just a filter rather
than a condition?
thanks for any ideas
gregIt depends on what you are doing. If you use the parameter in the WHERE
clause of your SQL then you would indeed be doing the 'filtering' on the
Database Server. If you are putting your parameter in a filter expression -
then yes this means that the filtering is done in the Report Server, and all
the data is requested from the Database Server.
You are right it is better to optimise queries to have the data pre-filtered
on the Database Server where advantage can be taken of Indexes.
We cover these issues in depth in Chapter 6 of our book - "Hitchhiker's
Guide to SQL Server 2000 Reporting Services"
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Greg, I answered this very completely at about 1:30 in your other posting.
Just to reiterate. Filters and query parameters are two different things and
both can use report parameters. You are using filters (which retrieve and
then filter) versus query parameters that are part of the where clause.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Hi all -
Thanks for the info. I have now a parameter working on the database
server - specified in the where clause as you say - thats great.
However What I want is a prompt for the user - to enter in an
assignment id. When I pass the parameter to the where clause and hit
run in the Data tab I am asked for the Parameter value (on the Data
tab ) here I want a drop down list of values - which I would like
replicated when in the preview screen - so that the user can enter a
value from the drop down list of values box. At the moment I can only
enter in the preview panel what i have specified in the Data tab and
bring back the correct result. How can I get a drop down Lovs
todisplay when the parameter is in the where clause. I tried to point
both label and value to the specific data set but it would not allow -
saying:
"A label expression used for the report parameter ?ApplicationID'
refers to a field. Fields cannot be used in report parameter
expressions."
Any ideas
help is very much appreciated
Greg
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> Greg, I answered this very completely at about 1:30 in your other posting.
> Just to reiterate. Filters and query parameters are two different things and
> both can use report parameters. You are using filters (which retrieve and
> then filter) versus query parameters that are part of the where clause.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <greg_cochrane@.hotmail.com> wrote in message
> news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > Am I missing a trick here? When i set up my parameter in reporting
> > services and hit preview, the prompt apears and asks for the specified
> > column - however the sql generated is not refined any. it brings back
> > all the data and then filters on the selected column in the report.
> >
> > By contrast in business objects if i used the @.prompt function the sql
> > generated would be refined with the input of the end user ...eg
> > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > services seems to be doing is bringing back all cost centres then
> > filtering on 1234.
> >
> > If im right it has some pretty serious implications in terms of speed
> > of reporting -as my query is returning all data values rather than
> > one...
> >
> > any ideas? any input? Isnt a parameter really just a filter rather
> > than a condition?
> >
> >
> > thanks for any ideas
> >
> > greg|||You are missing a concept here. You only use the data tab to test out your
query. To test your report you use the preview tab. That is where you have
control over the parameters. The parameters can be free form, based on a
list or based on a dataset. In the layout tab go to Report menu, report
parameters and you'll see where you do this. The report parameters have to
be mapped to the query parameters as well (they might be already). You can
check this by clicking on the ... in the data tab and going to the
parameters tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411100801.51f5bf73@.posting.google.com...
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter 'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other
posting.
> > Just to reiterate. Filters and query parameters are two different things
and
> > both can use report parameters. You are using filters (which retrieve
and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg|||If you want the drop-down you need to do the following:
1) Create a second Dataset that returns the list of Valid Assignment ID's
and a second column for the labels you want in the drop-down
2) On the main menu, click on Report -> Report Parameters
3) Click on the Assignment ID parameter
4) In the Available Values area, click on From Query
5) Choose the new Assignment ID DataSet, Value Field, and Label Field
Now try the Preview tab.
Hope this helps
"Greg" wrote:
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter â'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other posting.
> > Just to reiterate. Filters and query parameters are two different things and
> > both can use report parameters. You are using filters (which retrieve and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg
>|||Hi there - sorry Im really new to reporting services and this is going
to sound like a simple question! but how do I create a new data set
within a current report' I am trying to build a list of values for a
column in my first report - see below.
Many thanks - help is appreciated.
Greg
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> If you want the drop-down you need to do the following:
> 1) Create a second Dataset that returns the list of Valid Assignment ID's
> and a second column for the labels you want in the drop-down
> 2) On the main menu, click on Report -> Report Parameters
> 3) Click on the Assignment ID parameter
> 4) In the Available Values area, click on From Query
> 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> Now try the Preview tab.
> Hope this helps
> "Greg" wrote:
> > Hi all -
> >
> > Thanks for the info. I have now a parameter working on the database
> > server - specified in the where clause as you say - thats great.
> > However What I want is a prompt for the user - to enter in an
> > assignment id. When I pass the parameter to the where clause and hit
> > run in the Data tab I am asked for the Parameter value (on the Data
> > tab ) here I want a drop down list of values - which I would like
> > replicated when in the preview screen - so that the user can enter a
> > value from the drop down list of values box. At the moment I can only
> > enter in the preview panel what i have specified in the Data tab and
> > bring back the correct result. How can I get a drop down Lovs
> > todisplay when the parameter is in the where clause. I tried to point
> > both label and value to the specific data set but it would not allow -
> > saying:
> > "A label expression used for the report parameter â'ApplicationID'
> > refers to a field. Fields cannot be used in report parameter
> > expressions."
> >
> > Any ideas
> >
> > help is very much appreciated
> >
> > Greg
> >
> >
> >
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > Just to reiterate. Filters and query parameters are two different things and
> > > both can use report parameters. You are using filters (which retrieve and
> > > then filter) versus query parameters that are part of the where clause.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > services and hit preview, the prompt apears and asks for the specified
> > > > column - however the sql generated is not refined any. it brings back
> > > > all the data and then filters on the selected column in the report.
> > > >
> > > > By contrast in business objects if i used the @.prompt function the sql
> > > > generated would be refined with the input of the end user ...eg
> > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > services seems to be doing is bringing back all cost centres then
> > > > filtering on 1234.
> > > >
> > > > If im right it has some pretty serious implications in terms of speed
> > > > of reporting -as my query is returning all data values rather than
> > > > one...
> > > >
> > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > than a condition?
> > > >
> > > >
> > > > thanks for any ideas
> > > >
> > > > greg
> >|||Q Do i "add a new item" and write the sql free hand in there? or do I
create a new report? if so how do I link the result set achieved
through to the parameter on my first report?
Greg
greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> Hi there - sorry Im really new to reporting services and this is going
> to sound like a simple question! but how do I create a new data set
> within a current report' I am trying to build a list of values for a
> column in my first report - see below.
> Many thanks - help is appreciated.
> Greg
>
>
> "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > If you want the drop-down you need to do the following:
> >
> > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > and a second column for the labels you want in the drop-down
> > 2) On the main menu, click on Report -> Report Parameters
> > 3) Click on the Assignment ID parameter
> > 4) In the Available Values area, click on From Query
> > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> >
> > Now try the Preview tab.
> >
> > Hope this helps
> >
> > "Greg" wrote:
> >
> > > Hi all -
> > >
> > > Thanks for the info. I have now a parameter working on the database
> > > server - specified in the where clause as you say - thats great.
> > > However What I want is a prompt for the user - to enter in an
> > > assignment id. When I pass the parameter to the where clause and hit
> > > run in the Data tab I am asked for the Parameter value (on the Data
> > > tab ) here I want a drop down list of values - which I would like
> > > replicated when in the preview screen - so that the user can enter a
> > > value from the drop down list of values box. At the moment I can only
> > > enter in the preview panel what i have specified in the Data tab and
> > > bring back the correct result. How can I get a drop down Lovs
> > > todisplay when the parameter is in the where clause. I tried to point
> > > both label and value to the specific data set but it would not allow -
> > > saying:
> > > "A label expression used for the report parameter â'ApplicationID'
> > > refers to a field. Fields cannot be used in report parameter
> > > expressions."
> > >
> > > Any ideas
> > >
> > > help is very much appreciated
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > Just to reiterate. Filters and query parameters are two different things and
> > > > both can use report parameters. You are using filters (which retrieve and
> > > > then filter) versus query parameters that are part of the where clause.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > column - however the sql generated is not refined any. it brings back
> > > > > all the data and then filters on the selected column in the report.
> > > > >
> > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > generated would be refined with the input of the end user ...eg
> > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > services seems to be doing is bringing back all cost centres then
> > > > > filtering on 1234.
> > > > >
> > > > > If im right it has some pretty serious implications in terms of speed
> > > > > of reporting -as my query is returning all data values rather than
> > > > > one...
> > > > >
> > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > than a condition?
> > > > >
> > > > >
> > > > > thanks for any ideas
> > > > >
> > > > > greg
> > >|||go to the data tab and click on the drop down nesxt to your dataset name.
one of the options will be <new dataset> you will need to code the SQL but
for a parameter list it should be straightforward.
"Greg" wrote:
> Q Do i "add a new item" and write the sql free hand in there? or do I
> create a new report? if so how do I link the result set achieved
> through to the parameter on my first report?
>
> Greg
>
> greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > Hi there - sorry Im really new to reporting services and this is going
> > to sound like a simple question! but how do I create a new data set
> > within a current report' I am trying to build a list of values for a
> > column in my first report - see below.
> >
> > Many thanks - help is appreciated.
> >
> > Greg
> >
> >
> >
> >
> > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > If you want the drop-down you need to do the following:
> > >
> > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > and a second column for the labels you want in the drop-down
> > > 2) On the main menu, click on Report -> Report Parameters
> > > 3) Click on the Assignment ID parameter
> > > 4) In the Available Values area, click on From Query
> > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > >
> > > Now try the Preview tab.
> > >
> > > Hope this helps
> > >
> > > "Greg" wrote:
> > >
> > > > Hi all -
> > > >
> > > > Thanks for the info. I have now a parameter working on the database
> > > > server - specified in the where clause as you say - thats great.
> > > > However What I want is a prompt for the user - to enter in an
> > > > assignment id. When I pass the parameter to the where clause and hit
> > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > tab ) here I want a drop down list of values - which I would like
> > > > replicated when in the preview screen - so that the user can enter a
> > > > value from the drop down list of values box. At the moment I can only
> > > > enter in the preview panel what i have specified in the Data tab and
> > > > bring back the correct result. How can I get a drop down Lovs
> > > > todisplay when the parameter is in the where clause. I tried to point
> > > > both label and value to the specific data set but it would not allow -
> > > > saying:
> > > > "A label expression used for the report parameter â'ApplicationID'
> > > > refers to a field. Fields cannot be used in report parameter
> > > > expressions."
> > > >
> > > > Any ideas
> > > >
> > > > help is very much appreciated
> > > >
> > > > Greg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > then filter) versus query parameters that are part of the where clause.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > all the data and then filters on the selected column in the report.
> > > > > >
> > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > generated would be refined with the input of the end user ...eg
> > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > filtering on 1234.
> > > > > >
> > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > of reporting -as my query is returning all data values rather than
> > > > > > one...
> > > > > >
> > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > than a condition?
> > > > > >
> > > > > >
> > > > > > thanks for any ideas
> > > > > >
> > > > > > greg
> > > >
>|||Hi John - thanks very much!! got it working now!
Greg
"johnE" <johnE@.discussions.microsoft.com> wrote in message news:<70BEB3D8-6A8D-4609-A7D5-D387A4CFE865@.microsoft.com>...
> go to the data tab and click on the drop down nesxt to your dataset name.
> one of the options will be <new dataset> you will need to code the SQL but
> for a parameter list it should be straightforward.
> "Greg" wrote:
> > Q Do i "add a new item" and write the sql free hand in there? or do I
> > create a new report? if so how do I link the result set achieved
> > through to the parameter on my first report?
> >
> >
> > Greg
> >
> >
> > greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > > Hi there - sorry Im really new to reporting services and this is going
> > > to sound like a simple question! but how do I create a new data set
> > > within a current report' I am trying to build a list of values for a
> > > column in my first report - see below.
> > >
> > > Many thanks - help is appreciated.
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > > If you want the drop-down you need to do the following:
> > > >
> > > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > > and a second column for the labels you want in the drop-down
> > > > 2) On the main menu, click on Report -> Report Parameters
> > > > 3) Click on the Assignment ID parameter
> > > > 4) In the Available Values area, click on From Query
> > > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > > >
> > > > Now try the Preview tab.
> > > >
> > > > Hope this helps
> > > >
> > > > "Greg" wrote:
> > > >
> > > > > Hi all -
> > > > >
> > > > > Thanks for the info. I have now a parameter working on the database
> > > > > server - specified in the where clause as you say - thats great.
> > > > > However What I want is a prompt for the user - to enter in an
> > > > > assignment id. When I pass the parameter to the where clause and hit
> > > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > > tab ) here I want a drop down list of values - which I would like
> > > > > replicated when in the preview screen - so that the user can enter a
> > > > > value from the drop down list of values box. At the moment I can only
> > > > > enter in the preview panel what i have specified in the Data tab and
> > > > > bring back the correct result. How can I get a drop down Lovs
> > > > > todisplay when the parameter is in the where clause. I tried to point
> > > > > both label and value to the specific data set but it would not allow -
> > > > > saying:
> > > > > "A label expression used for the report parameter â'ApplicationID'
> > > > > refers to a field. Fields cannot be used in report parameter
> > > > > expressions."
> > > > >
> > > > > Any ideas
> > > > >
> > > > > help is very much appreciated
> > > > >
> > > > > Greg
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > > then filter) versus query parameters that are part of the where clause.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > > all the data and then filters on the selected column in the report.
> > > > > > >
> > > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > > generated would be refined with the input of the end user ...eg
> > > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > > filtering on 1234.
> > > > > > >
> > > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > > of reporting -as my query is returning all data values rather than
> > > > > > > one...
> > > > > > >
> > > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > > than a condition?
> > > > > > >
> > > > > > >
> > > > > > > thanks for any ideas
> > > > > > >
> > > > > > > greg
> > > > >
> >
Subscribe to:
Posts (Atom)