Parameters and role

Hi all,

I have a report based on a cube with some parameters. So far, everything works fine, but there is one thing I want to improve for user-friendliness. I have a parameter linked to country selection and the roles are also based on countries.

My issue is that even if a user can only access the data of one country, in the drop-down list of the parameters, all the countries are available (even if no data is linked to these countries).

I'm wondering if there is a way to "filter" the parameters selection, to display only the countries useful for a specific user.

Thanks.

Guillaume

I'm not sure whether you can do this using custom code, but...

I use Visual Studio 2005's dropdownlist on my front-end. Basically, just have a SQL table that let's the dropdownlist know which counties the user should be associated with.

I can see how this would be challenging if you only have BIS.

|||

Thanks for your quick reply.

I have only BIDS... There is not an other way to do that? Any other ideas?

Guillaume

Parameters and ReportViewer

I have a report in which I am trying to get a parameter to be displayed in a textbox. I have declared a parameter in my .rdlc called UserName and the expression in the textbox is as follows...

=Parameters!UserName.Value

With that said, I have code in the window containing my ReportViewer to set the parameters and run my report as shown below...

string UserName = "test";

List<ReportParameter> paramList = new List<ReportParameter>();

paramList.Add(new ReportParameter(UserName));

this.baseReportViewer.LocalReport.SetParameters(paramList);

this.baseReportViewer.RefreshReport();

With these things in mind, can anyone here provide me with a reason as to why my report on comes up with a message stating "An error occurred during local processing. One or more parameters required to run the report have not been specified." ?

If I remove the parameter and text box and strip out the code pertaining to the paramter in my window, the report runs without error.

Any ideas?

Answered my own question and it was a total noob mistake.

Assuming I have a parameter in my report named "UserName," the code in my form would need to look like the following...(I've highlighted the change in red)

string UserName = "test";

List<ReportParameter> paramList = new List<ReportParameter>();

paramList.Add(new ReportParameter("UserName", UserName));

this.baseReportViewer.LocalReport.SetParameters(paramList);

this.baseReportViewer.RefreshReport();

Parameters and Refreshing Data using VB

I have a report. I open it thouigh crystal and refresh data. I then open a vb script to export the report. I want to be able to not have to open crystal to get the latest data. I also have parameters that are entered in crystal. I want the user to be able to run a vb script to get the latest version of the report and to also enter the parameters in vb. Im not using the crystal report viewer but im using reportdocument.
ThanksI have a report. I open it thouigh crystal and refresh data. I then open a vb script to export the report. I want to be able to not have to open crystal to get the latest data. I also have parameters that are entered in crystal. I want the user to be able to run a vb script to get the latest version of the report and to also enter the parameters in vb. Im not using the crystal report viewer but im using reportdocument.
Thanks

Let me know wht frontend u r using for the same u hv explained.....
Are u using ASP (language="vbscript ")?

Parameters and passing values

I am trying to pass two parameters to a stored procedure that works fine whe
n
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas
?
Thanks in advanceSee replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com> wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>|||Thanks John - I posted to the wrong group...
"John Bell" wrote:

> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com> wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>

Parameters and passing values

I am trying to pass two parameters to a stored procedure that works fine when
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas?
Thanks in advance
See replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com > wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>
|||Thanks John - I posted to the wrong group...
"John Bell" wrote:

> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com > wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>

Parameters and MDX

Is there a customer Data Providor for Reporting Services that handles
Parameteized MDX statments
I.E.
For instance the below MDX Statement would have
<%TOPBOUNDS%>,<%BOOKINGS%>,<%DTLVL%> etc replaced by the parameters passed
into the data providor
WITH SET [MAIN] AS '<%TOPBOUNDS%>({[CUSTOMER CLASS].[CUSTOMER ID].MEMBERS},
<%SELCOUNT%>, [MEASURES].[YTD <%BOOKINGS%> <%STD%>])'
MEMBER [CUSTOMER CLASS].[OTHER] AS 'SUM(EXCEPT({[CUSTOMER CLASS].[CUSTOMER
ID].MEMBERS},{[MAIN]}))'
MEMBER [CUSTOMER CLASS].[TOTAL] AS 'SUM({[CUSTOMER CLASS].[ALL CUSTOMER
CLASS]})'
MEMBER [MEASURES].[PRIOR YR_<%DTLVL%> $] AS '[MEASURES].[LAST YEAR
<%BOOKINGS%> <%STD%>]'
MEMBER [MEASURES].[PRIOR YR YTD $] AS '[MEASURES].[LAST YTD <%BOOKINGS%>
<%STD%>]'
MEMBER [MEASURES].[DIFFERENCE $] AS '[MEASURES].[<%DTLVL%> <%BOOKINGS%>
<%BSTD%>]-[MEASURES].[PRIOR YR_<%DTLVL%> $]',FORMAT='$#,0.00'
MEMBER [MEASURES].[YTD DIFFERENCE $] AS '[MEASURES].[YTD <%BOOKINGS%>
<%STD%>]-[MEASURES].[PRIOR YR YTD $]',FORMAT='$#,0.00'
MEMBER [MEASURES].[% OF YTD <%BOOKINGS%>] AS '[MEASURES].[YTD <%BOOKINGS%>
<%STD%>]/SUM({[TOTAL]},[MEASURES].[YTD <%BOOKINGS%>
<%STD%>])',FORMAT='0.00%'
SELECT { [MEASURES].[<%DTLVL%> <%BOOKINGS%> <%BSTD%>],[MEASURES].[PRIOR
YR_<%DTLVL%> $],[MEASURES].[DIFFERENCE $],[MEASURES].[YTD <%BOOKINGS%>
<%STD%>],[MEASURES].[PRIOR YR YTD $],[MEASURES].[YTD DIFFERENCE
$],[MEASURES].[% OF YTD <%BOOKINGS%>] } ON COLUMNS,
{[MAIN],[CUSTOMER CLASS].[OTHER], [CUSTOMER CLASS].[TOTAL] } ON ROWS
FROM [BOOKINGS] WHERE (<%SELDATE%>,<%SRCAPPID%>)The OleDB provider for AS 2000 does not support parameterized MDX queries.
This MSDN article explains how to achieve parameterized MDX in RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
In addition, you may want to download this sample:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
BTW: RS 2005 Beta 2 contains graphical and text-based query designers for
MDX and DMX. They also support single-value parameters at this point.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will Byron" <will.byron@.maxqtech.com> wrote in message
news:e36nP4EjEHA.3988@.tk2msftngp13.phx.gbl...
> Is there a customer Data Providor for Reporting Services that handles
> Parameteized MDX statments
> I.E.
> For instance the below MDX Statement would have
> <%TOPBOUNDS%>,<%BOOKINGS%>,<%DTLVL%> etc replaced by the parameters
passed
> into the data providor
> WITH SET [MAIN] AS '<%TOPBOUNDS%>({[CUSTOMER CLASS].[CUSTOMER
ID].MEMBERS},
> <%SELCOUNT%>, [MEASURES].[YTD <%BOOKINGS%> <%STD%>])'
> MEMBER [CUSTOMER CLASS].[OTHER] AS 'SUM(EXCEPT({[CUSTOMER CLASS].[CUSTOMER
> ID].MEMBERS},{[MAIN]}))'
> MEMBER [CUSTOMER CLASS].[TOTAL] AS 'SUM({[CUSTOMER CLASS].[ALL CUSTOMER
> CLASS]})'
> MEMBER [MEASURES].[PRIOR YR_<%DTLVL%> $] AS '[MEASURES].[LAST YEAR
> <%BOOKINGS%> <%STD%>]'
> MEMBER [MEASURES].[PRIOR YR YTD $] AS '[MEASURES].[LAST YTD <%BOOKINGS%>
> <%STD%>]'
> MEMBER [MEASURES].[DIFFERENCE $] AS '[MEASURES].[<%DTLVL%> <%BOOKINGS%>
> <%BSTD%>]-[MEASURES].[PRIOR YR_<%DTLVL%> $]',FORMAT='$#,0.00'
> MEMBER [MEASURES].[YTD DIFFERENCE $] AS '[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>]-[MEASURES].[PRIOR YR YTD $]',FORMAT='$#,0.00'
> MEMBER [MEASURES].[% OF YTD <%BOOKINGS%>] AS '[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>]/SUM({[TOTAL]},[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>])',FORMAT='0.00%'
> SELECT { [MEASURES].[<%DTLVL%> <%BOOKINGS%> <%BSTD%>],[MEASURES].[PRIOR
> YR_<%DTLVL%> $],[MEASURES].[DIFFERENCE $],[MEASURES].[YTD <%BOOKINGS%>
> <%STD%>],[MEASURES].[PRIOR YR YTD $],[MEASURES].[YTD DIFFERENCE
> $],[MEASURES].[% OF YTD <%BOOKINGS%>] } ON COLUMNS,
> {[MAIN],[CUSTOMER CLASS].[OTHER], [CUSTOMER CLASS].[TOTAL] } ON ROWS
> FROM [BOOKINGS] WHERE (<%SELDATE%>,<%SRCAPPID%>)
>

Parameters and Horizontal Scroll Bar

I am experiencing a problem whereas I have a parameter dropdown that is
dynamically populated. In the case there is only one value, the horizontal
scroll bar is covering up that value and the user cannot see it to select it.
Are there other workaround other than coding for a "dummy" value in the list?On Jan 15, 11:18 am, Chriss G <Chri...@.discussions.microsoft.com>
wrote:
> I am experiencing a problem whereas I have a parameter dropdown that is
> dynamically populated. In the case there is only one value, the horizontal
> scroll bar is covering up that value and the user cannot see it to select it.
> Are there other workaround other than coding for a "dummy" value in the list?
As far as I know, there is not. A work around would be to create a
custom ASP.NET application that gives some flexibility in the sizing
of the web controls and then call SSRS via the RS web service or URL
access. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Parameters and Formulas in a group field

I have a formula in my group footer that I need to pass to a paramter. The formula doesn't come up in my select expert box? When I try to manually add it, it gives me this error
"This formula cannot be used because it must be evaluated later"

my formula is a account balance formula and I have a parameter that I want to choose where the balance is greater than ".01"

How can I get this accomplished?So you want to use the result of the group footer formula in the record selection formula?
What is your formula? You might be able to use the group selection formula instead.
e.g. if you're grouping on account and summing a value field your group selection formula could be something like
sum({value}, {account}) > 0
which will only return accounts whose 'balance' is > 0

Parameters & reporting model

Hi,

I have created a reporting model.

When i use this model to create a report, all goes well.

But now i want to add date parameters (from ... to) to this report.

When i add my parameters in my reporting parameters (i use visual studio to create my reports)

i get the calender to select a from and to date.

but when i generate my report, i get the whole date range available in my database (so the parameters

are not used).

I tried to create a filter in my dataset, but there i cannot specify the parameters i created (error : from is not a date. i also tried @.From but that doesn't work either)

anybody have an idea how i can add parameters ? but it must be based on reporting model.

Vincent

Follow the following steps to achive this:

1. open the report.

2. click on data tab, edit selected dataset

3. click on parameter tab.

4. give the propername of parameter. and select the declared report parameter in value column.

5. then click on filter tab, select the desire field name in Exp. column and then value column will be

=Parameters!Parametername.Value

Now run the report.

Parameters & Sybase DB

I have a report that takes a single date parameter. My DataSource however,
could be either a MSSQL db or a Sybase db. Both of these are included as
shared datasources in my project (This is becase some clients of the report
may point to the MSSQL db while others will point to the Sybase db). However,
so far it seems that the rules for writing parameterized queries differ for
these 2 platforms i.e.
For MSSQL, i have:
where date > @.date,
but for Sybase, I had to say:
where date > ?
How can I write just one report so that when deployed, changing the
datasource from MSSQL to Sybase does not cause a problem?Have you tried doing a stored procedure and passing a the parameter in?...
That might work for both...(Although I am surprised that there is even the
difference you have discovered.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>I have a report that takes a single date parameter. My DataSource however,
> could be either a MSSQL db or a Sybase db. Both of these are included as
> shared datasources in my project (This is becase some clients of the
> report
> may point to the MSSQL db while others will point to the Sybase db).
> However,
> so far it seems that the rules for writing parameterized queries differ
> for
> these 2 platforms i.e.
> For MSSQL, i have:
> where date > @.date,
> but for Sybase, I had to say:
> where date > ?
> How can I write just one report so that when deployed, changing the
> datasource from MSSQL to Sybase does not cause a problem?|||One important principle of Reporting Services 2000 is to not "rewrite" the
dataset query. The statement is essentially sent directly to the data
provider. Some data providers do not support named parameters (like OleDb
data providers). Other data providers support named parameters, but they use
different syntax to mark parameters and may have various flavors for SQL
keywords.
E.g.:
Managed SQL Provider: select * from emp where name = @.Name
Managed Oracle Provider: select * from emp where name = :Name
For the Sybase provider, it looks like Aparna is using the Sybase OleDb
provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> Have you tried doing a stored procedure and passing a the parameter in?...
> That might work for both...(Although I am surprised that there is even the
> difference you have discovered.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>>I have a report that takes a single date parameter. My DataSource however,
>> could be either a MSSQL db or a Sybase db. Both of these are included as
>> shared datasources in my project (This is becase some clients of the
>> report
>> may point to the MSSQL db while others will point to the Sybase db).
>> However,
>> so far it seems that the rules for writing parameterized queries differ
>> for
>> these 2 platforms i.e.
>> For MSSQL, i have:
>> where date > @.date,
>> but for Sybase, I had to say:
>> where date > ?
>> How can I write just one report so that when deployed, changing the
>> datasource from MSSQL to Sybase does not cause a problem?
>|||Hi Robert,
I actually tired using both the Sybase ASE Ole DB provider, as well as the
'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
Problems'. Here are the steps he mentioned:
---
1. Create the ODBC Machine DSN and test that connection works
2. Pick provider Microsoft OLE DB Provider for ODBC drivers
3. Next
4. Use Data Source name and pick the DSN you created in 1
5. Enter username and password to use.
6. Pick initial catalog to use and click on test connection
7. Click OK
You should now be done. If you have a problem with username and password
after you are done double click on the data source and go to the credential
tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
---
In both cases, I tried running the parameterized query (using @.Date) in both
the Generic Query Designer as well the Visual Query Designer. In both cases,
I get the message "The Data Extension ODBC does not support named parameters.
Use unnamed parameters instead".
I did read about Oracle's conventions for using named parameters, and tried
searching for info. on using named parameters in Sybase, but to no avail. I
know Wayne mentioned using stored procedures, but I wanted to know if I could
use a simple query for both Sybase & Sql Server platforms. Any idea how named
parameters are used for Sybase? All in all, it seems to me that because
there are slight variations in syntax, etc between the various providers, it
may not be possible to write a parameterized query and then dynamically
change the data source provider once deployed (i.e. from Sql Server to Oracle
or Sybase)...Is that correct?
--Aparna.
"Robert Bruckner [MSFT]" wrote:
> One important principle of Reporting Services 2000 is to not "rewrite" the
> dataset query. The statement is essentially sent directly to the data
> provider. Some data providers do not support named parameters (like OleDb
> data providers). Other data providers support named parameters, but they use
> different syntax to mark parameters and may have various flavors for SQL
> keywords.
> E.g.:
> Managed SQL Provider: select * from emp where name = @.Name
> Managed Oracle Provider: select * from emp where name = :Name
> For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > Have you tried doing a stored procedure and passing a the parameter in?...
> > That might work for both...(Although I am surprised that there is even the
> > difference you have discovered.)
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> >>I have a report that takes a single date parameter. My DataSource however,
> >> could be either a MSSQL db or a Sybase db. Both of these are included as
> >> shared datasources in my project (This is becase some clients of the
> >> report
> >> may point to the MSSQL db while others will point to the Sybase db).
> >> However,
> >> so far it seems that the rules for writing parameterized queries differ
> >> for
> >> these 2 platforms i.e.
> >> For MSSQL, i have:
> >> where date > @.date,
> >>
> >> but for Sybase, I had to say:
> >> where date > ?
> >>
> >> How can I write just one report so that when deployed, changing the
> >> datasource from MSSQL to Sybase does not cause a problem?
> >
> >
>
>|||For Sybase you cannot use named parameters (regardless of provider used).
For Sybase you put a ?
I have (unfortunately) been living in Sybase with RS so let me know if you
have any other difficulties. There are little quirks from time to time.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
> Hi Robert,
> I actually tired using both the Sybase ASE Ole DB provider, as well as the
> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
> Problems'. Here are the steps he mentioned:
> ---
> 1. Create the ODBC Machine DSN and test that connection works
> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
> 3. Next
> 4. Use Data Source name and pick the DSN you created in 1
> 5. Enter username and password to use.
> 6. Pick initial catalog to use and click on test connection
> 7. Click OK
> You should now be done. If you have a problem with username and password
> after you are done double click on the data source and go to the
credential
> tab.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ---
> In both cases, I tried running the parameterized query (using @.Date) in
both
> the Generic Query Designer as well the Visual Query Designer. In both
cases,
> I get the message "The Data Extension ODBC does not support named
parameters.
> Use unnamed parameters instead".
> I did read about Oracle's conventions for using named parameters, and
tried
> searching for info. on using named parameters in Sybase, but to no avail.
I
> know Wayne mentioned using stored procedures, but I wanted to know if I
could
> use a simple query for both Sybase & Sql Server platforms. Any idea how
named
> parameters are used for Sybase? All in all, it seems to me that because
> there are slight variations in syntax, etc between the various providers,
it
> may not be possible to write a parameterized query and then dynamically
> change the data source provider once deployed (i.e. from Sql Server to
Oracle
> or Sybase)...Is that correct?
> --Aparna.
> "Robert Bruckner [MSFT]" wrote:
> > One important principle of Reporting Services 2000 is to not "rewrite"
the
> > dataset query. The statement is essentially sent directly to the data
> > provider. Some data providers do not support named parameters (like
OleDb
> > data providers). Other data providers support named parameters, but they
use
> > different syntax to mark parameters and may have various flavors for SQL
> > keywords.
> > E.g.:
> > Managed SQL Provider: select * from emp where name = @.Name
> > Managed Oracle Provider: select * from emp where name = :Name
> >
> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> > provider.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > > Have you tried doing a stored procedure and passing a the parameter
in?...
> > > That might work for both...(Although I am surprised that there is even
the
> > > difference you have discovered.)
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> > >>I have a report that takes a single date parameter. My DataSource
however,
> > >> could be either a MSSQL db or a Sybase db. Both of these are included
as
> > >> shared datasources in my project (This is becase some clients of the
> > >> report
> > >> may point to the MSSQL db while others will point to the Sybase db).
> > >> However,
> > >> so far it seems that the rules for writing parameterized queries
differ
> > >> for
> > >> these 2 platforms i.e.
> > >> For MSSQL, i have:
> > >> where date > @.date,
> > >>
> > >> but for Sybase, I had to say:
> > >> where date > ?
> > >>
> > >> How can I write just one report so that when deployed, changing the
> > >> datasource from MSSQL to Sybase does not cause a problem?
> > >
> > >
> >
> >
> >|||I have a Sybase Anywhere db (version 8.x) that I have been using since RS
was released. For some reason my parameter (specified with a ?) is not
linking up with the selection entered in by the user prompt I have
associated with it. Any ideas? =)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> For Sybase you cannot use named parameters (regardless of provider used).
> For Sybase you put a ?
> I have (unfortunately) been living in Sybase with RS so let me know if you
> have any other difficulties. There are little quirks from time to time.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
> both
>> the Generic Query Designer as well the Visual Query Designer. In both
> cases,
>> I get the message "The Data Extension ODBC does not support named
> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
> tried
>> searching for info. on using named parameters in Sybase, but to no avail.
> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various providers,
> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
> as
>> > >> shared datasources in my project (This is becase some clients of the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>|||Sometimes you have to hook them backup up. Go to the dataset, click on the
..., parameters tab. On the left put ? on the right pick the appropriate
report parameter. This should be done in the order that the ? appear in your
query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:11hhcmvr55mpl81@.corp.supernews.com...
>I have a Sybase Anywhere db (version 8.x) that I have been using since RS
>was released. For some reason my parameter (specified with a ?) is not
>linking up with the selection entered in by the user prompt I have
>associated with it. Any ideas? =)
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> For Sybase you cannot use named parameters (regardless of provider used).
>> For Sybase you put a ?
>> I have (unfortunately) been living in Sybase with RS so let me know if
>> you
>> have any other difficulties. There are little quirks from time to time.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
>> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
>> both
>> the Generic Query Designer as well the Visual Query Designer. In both
>> cases,
>> I get the message "The Data Extension ODBC does not support named
>> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
>> tried
>> searching for info. on using named parameters in Sybase, but to no
>> avail.
>> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
>> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
>> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various
>> providers,
>> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
>> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
>> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
>> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
>> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase
>> > OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
>> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
>> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
>> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
>> as
>> > >> shared datasources in my project (This is becase some clients of
>> > >> the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase
>> > >> db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
>> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>>
>

Parameters & ReportingService.Render

My report has 2 parameters. One I would like to set the value of in my vb
code, the other I would like to be queried according to the report design.
In the designer the first parameter is non-queried and will allow blank but
not null. The second is "from query". Obviously everything works in the
designer. ;) The 2nd parameter dropdown list is correctly populated from
the query that's embedded in the report design.
My <DeviceInfo> has toolbar=true, parameters=true.
I have tried several things. In all cases parameters(1).value = <correct
value>.
1) parameters(2).Value = Nothing. Error is "Default value or value provided
for the report parameter 'TDN' is not a valid value."
2) parameters(2).value = "". Error is the same as #1.
3) parameters(2).value = " ". Error is the same as #1.
4) parameters(2).value = "C100". This is a typical value for this field,
but won't be accurate in all situations. same error.
5) leave off parameter(2) in the vb code. Error is "This report requires a
default or user-defined value for the report parameter 'TDN'. To run or
subscribe to this report, you must provide a parameter value."
6) paraamters(2).value = "". check "default" and "null" in Report Manager.
Error is "Default value or value provided for the report parameter 'TDN' is
not a valid value."
Can anyone shed some light on this?
Thanks."Joe" wrote:
> My report has 2 parameters. One I would like to set the value of in my vb
> code, the other I would like to be queried according to the report design.
> In the designer the first parameter is non-queried and will allow blank but
> not null. The second is "from query". Obviously everything works in the
> designer. ;) The 2nd parameter dropdown list is correctly populated from
> the query that's embedded in the report design.
Look inside the YourReport.rdl (View Code) and double check the
ReportParameter names in the xml. You should find your two, case sensitive
parameter names.
If one is called parameter1 instead of your name, or additional entries, try
changing it to what you want, then redeploy the report and retry your code.
Sometimes the Query Designer has a mind of its own with parameters, and has
messed up working rdl files. <g>
I hope this is of some help to you.

Parameters - Selection from a long list

Hello

I want to have a report query parameter which has many thousands of possible values (i.e. Customer Names). I need to provide the ability to allow a user to search for the customer they want and then have that customer's detail appear in the parameter.

Ideally I would like something like the ability to have a "Find" button next to the parameter that would kick off a "Find" report and then pass the value selected back to the original parameter.

The parameter in question is one of a numvber (6) on the actual report so have discounted the option of creating a "Find Customer" report as step 1 and then passing the selection forward to the actual report (Step 2) i.e. drill through.

Any thoughts/suggestions would be much appreciated as I know I will have many of these scenarios in the coming months as I will be using numerous parameters with large selection lists.

Thanks in advance

Eliot

No user is going to want a maseeev ddlist to scroll through.

Has anyone ever tried using code to do an ajax/google suggest type thing?

If so please post!

I think you are going to have to use cascading parameters

So free entry text param 1 allows the user to enter some of the name

param 2 is driven by this and does some sort of fuzzy matching to return a list of possible candidates LIKE 'param1' + '%' (soundex also might help)

|||

Thanks for your reply.

Using Cascading parameters will work in some cases but not in this particular situation as I need to provide multiple ways to search for a customer. i.e. Surname, Customer Number, Postcode etc.

However, some of my scenarios could use cascading parameters.

Any other thoughts out there?

Parameters - Multiple Values -RS SP2

Hi All.
Just wondering if anyone knows if entering multiple values into parameters
will be included as part of SP2 for reporting services 2000' as it is going
to be in RS 2005 Beta 3. Or if I'm going to have to code some stored procs to
get round it?
--
Nick Colebourn (MCDBA)
DBA
United Coop LtdMulti value parameters will ONLY be in RS 2005
http://www.ReportingServicesFAQ.com/ow.asp?RS2005Features
Nick Colebourn wrote:
> Hi All.
> Just wondering if anyone knows if entering multiple values into parameters
> will be included as part of SP2 for reporting services 2000' as it is going
> to be in RS 2005 Beta 3. Or if I'm going to have to code some stored procs to
> get round it?|||Thanks Jerry. Much Appreciated. Roll On RS 2005! :-)
"Jerry" wrote:
> Multi value parameters will ONLY be in RS 2005
> http://www.ReportingServicesFAQ.com/ow.asp?RS2005Features
>
> Nick Colebourn wrote:
> > Hi All.
> > Just wondering if anyone knows if entering multiple values into parameters
> > will be included as part of SP2 for reporting services 2000' as it is going
> > to be in RS 2005 Beta 3. Or if I'm going to have to code some stored procs to
> > get round it?
>

Parameters - Multiple Values for one Label - Possible?

Is it possible to have a parameter with one label but multiple values. For example:

Label Value

Machinist (100,200,300)

Is it possible to set up an expression that when the user selects this label it will look for the job codes 100, 200 and 300

and return all employees in those codes?

Thanks,

A

No, but what you can do is this...

Create the viewable parameter (We'll call it Parm1)

Create a 2 column SQL table that contains your cross references (We'll call that tblXRef)

in this case tblXRef would contain 3 rows:

Machinist, 100

Machinist, 200

Machinist, 300

Create a dataset that uses the following SQL:

Select Type, ID from tblXRef Where Type = @.Parm1

(Call this dataset Lookups)

Create another parameter (We'll call it Parm2), set it to Internal, Multi value, and set the source = Lookups and the fields = ID

Set the Default Values to Lookups, ID.

In your main SQL dataset, use the Parm2 parameter to do your filtering.

HtH

BobP

|||

Not sure how big is the list but can make a dataset like this and set the paramters -

Select 1,'Machinist-100' As Machinist-100
Union
Select 2,'Machinist-200' As Machinist-200
Union
Select 3,'Machinist-300' As Machinist-300

Hope this helps someway.

Parameters - get last minus one

I have a list of parameters, and they are months. This list is dynamic, and
is based on a query to an OLAP cube.
I want my default parameter to be the last month minus one, so that when the
last month is December, I want to have November as default.
How can I do this?
All help appreciated!
Kaisa M. Lindahljust use other query to get max(month),then use the result to parameter's
default value
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> дÈëÏûÏ¢
news:#sYiAa61EHA.4004@.tk2msftngp13.phx.gbl...
> I have a list of parameters, and they are months. This list is dynamic,
and
> is based on a query to an OLAP cube.
> I want my default parameter to be the last month minus one, so that when
the
> last month is December, I want to have November as default.
> How can I do this?
> All help appreciated!
> Kaisa M. Lindahl
>

Parameters - Dropdown list value to lookup values and pass to sp

I have a simple report where I want to have a dropdown list box that is
populated from a sql query (no problems here).
Then I want to take the value from the selected parameter to lookup another
value in a query, then pass the returned value to a stored procedure as a
parameter.
So far, I have not been successful, does anybody have a clue how to do this?
Thanks, MikeMichael,
You will need 2 datasets (2 parameters) and 2 Report Parameters (Report
Menu). First parameter (that you already have) will fetch one set of list. In
the third (main query that will fetch the reporting data) will be queried
like below:
"
Select Count(AccountNumber) as Dis, PrimaryCareProviderId ,
PrimaryCareProviderName,InpatientServiceName,Left(DateName(Month,Dischargedatetime),3) as Mon
From dbo.Portal_PrvVolumes Where Dischargedatetime >= '07/01/2004' and
dischargedatetime < '12/01/2004'
and
primarycareproviderid = @.PrimaryCareProviderId (FIRST PARAMETER)
and InpatientServiceName = @.PrvService (SECOND PARAMETER)
"
Then create the second dataset for second parameter list.
Now create 2 report parameters...
I hope this helps...
"Michael Morisoli" wrote:
> I have a simple report where I want to have a dropdown list box that is
> populated from a sql query (no problems here).
>
> Then I want to take the value from the selected parameter to lookup another
> value in a query, then pass the returned value to a stored procedure as a
> parameter.
>
>
> So far, I have not been successful, does anybody have a clue how to do this?
>
> Thanks, Mike
>
>
>

Parameters - Coalesce!

Hi all,
Just a quick question.
I am calling a stored procedure which has two optional parameters.
Optional meaning they are declared with a default value NULL.
If for some reason and empty string send sent to the procedure will the
internal value of that parameter be NULL or "" ?
Area these the same thing in TSQL ?
Following on from that:
Will the Coalesce Function treat "" & NULL the same?
IE: I need to know if the following will result in 10 if an empty string is
sent to a stored procedure for the 'myParam' parameter..
CREATE PROCEDURE [dbo].[myProcedure]
@.myParam INT = NULL
As
BEGIN
SELECT * FROM myTable
WHERE myTableID = Coalesce(@.myParam,10)
END
Many thanks to those who respond!!!
Adam"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eNcdq2gyFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Just a quick question.
> I am calling a stored procedure which has two optional parameters.
> Optional meaning they are declared with a default value NULL.
> If for some reason and empty string send sent to the procedure will the
> internal value of that parameter be NULL or "" ?
An "Empty String" passed in as a parameter is treated as an empty string in
SQL ('')

> Area these the same thing in TSQL ?
A empty string, or zero-length string (ZLS) is not the same thing as NULL.

> Following on from that:
> Will the Coalesce Function treat "" & NULL the same?
COALESCE(value1, value2, ...) returns the first non-NULL value from the list
of values. A common usage of coalesce is something like this:
COALESCE(column, '')
To return a zero-length string in place of a NULL.

> IE: I need to know if the following will result in 10 if an empty string
> is sent to a stored procedure for the 'myParam' parameter..
> CREATE PROCEDURE [dbo].[myProcedure]
> @.myParam INT = NULL
> As
> BEGIN
> SELECT * FROM myTable
> WHERE myTableID = Coalesce(@.myParam,10)
> END
>
An empty string is not a NULL. You shouldn't be passing a 'string' value in
to this procedure anyway - it's an INT parameter. For your example would it
make sense to set the default for @.myParam INT = 10 and get rid of the
COALESCE() function call?

> Many thanks to those who respond!!!
> Adam
>

Parameters - A OR B

Probably a pretty easy question: How do I implement 2 parameters in a OR situation? I want the users to be able to either choose a value for A OR a value for B, i.e. select the product name OR the product code. I have 3 datasets defined, 1 for each parameter and 1 for the report output. The last one has ...WHERE A=@.A or B = @.B. When testing, it seems to require both. I want them to be mutually exclusive, the user can only pick one.Hi,

you probably would be able to make one parameter disbled (using a dependency) if the one is chosen, but you won′t be able to do this for bither having circular references. What you can do is to implement both parameters in your report and make them selectable in the GUI and implement the logic for differenting the cases in the query code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the information. I did some research and I updated my query's WHERE clause: WHERE (D.name = ISNULL(@.name, D.name)) AND (D.tracking_number = ISNULL(@.tracking_number, D.tracking_number))

I also checked the "allow null value" for each of the 2 parameters. I also have the default value to be null. However, if I only select a name, I'm still getting the prompt: Please select a value for the parameter 'Tracking Number'.

Any ideas what else I need to do? I thought the "allow null value" would do it.

|||

I had somewhat-similiar report

User pick a date range, and pick which date column to use (data is coming from a Stored Proc)

I can't think of an easy way to do it in StorProc unless I copy code twice (once for each date column), or dynamic query

so I used the filter in SSRS, on the table

=IIF(Parameters!UseImportedDateRange.Value = "Imported", Fields!ImportedDate.Value, Fields!MostRecentCallDate.Value)

> @.start_date

parameters

Hello,

i am new to ssrs and am trying to generate a report with 4 parameters. 2 of which are dates. The other 2 are drop downlists. Now the report works fine when i enter all 4 parameters. But in some cases i want to leave one of the parameters unentered . It doesnt all me to do that. Gives a error saying i need to enter the parameter. How do u get aroud this issue ?

I have seperate dataset for this parameters list and am using a where clause in my main query.

Can anyone please help me out with this?

Thx

Ashish

the parameters which are not needed all times can be set as

1. under parameter properties check all ow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

the parameters which are not needed all times can be set as

1. under parameter properties check allow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

hello rakam,

Thx for the reply.

My problem still continues. i tried ur suggestion but it still prompts me to select that paramater when trying to view report.

Any other options with be appreciated.

Thx

Ashish

|||The only thing you should have to do is define a valid Default value in the parameter setup in Design view of the report. If you still have the problem after saving and redeploying the report, you may want to delete the report from the report server and then deploy it again. Sometimes the subtle updates don't post properly unless you delete first.|||Read this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061210&SiteID=1

Parameters

My query is returning phonenumber,Name and Id.

In layout I have textbox.In Edit Expression,the parmeter is like First(phonenumber)

Why it is showing First(phonenumber).

YOu have to prefix your expression with the eual sign to evaluate expressions, e.g. =First(Phonenumer).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

Parameters

SELECT CodiceCliente, RagioneSociale, Articolo, Comma, descrizione, Area, dtDeroga, dtFineDeroga, SuContratto, Parametri
FROM MasterDeroghe
WHERE Articolo = (?CodArt)

I want to pass a parameter at a report of reporting services with this query but this query don't conceid it.

why?

what's wrong?

My database is access

If ?CodArt is supposed to be your parameter, that is your problem. Report server parameter variables start with @., i.e. @.CodArt|||

The parameter syntax really depends on the data source being used. Some data provider (such as SQL Server, Oracle) support named parameters, other data providers only support unnamed parameters (e.g. OleDB provider)

Examples for parameter syntax:
SQL Server select * from table where column = @.ParameterName
Oracle select * from table where column = :ParameterName
OleDB (unnamed parameters) select * from table where column = ?

-- Robert

parameters

Does anyone know where I can find excercise examples on
parameters
Thank you
What parameters are you talking? For stored procedures etc?
Check the SQL Server BOL for a startup:
http://www.microsoft.com/sql/techinf...2000/books.asp
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:18c301c51b24$2fdad3d0$a601280a@.phx.gbl...
> Does anyone know where I can find excercise examples on
> parameters
> Thank you

Parameters

How do I pass or ask for parameters from SQL*PlusIt looks like a question for Oracle, so try to post on Oracle forum in order to get the right answer faster.
Or do you want to pass parameters between SQL Server and Oracle?

Parameters

Hello everybody!
I have a question for you.
In access, you can make a query using parameters:
DELETE *
FROM tableName
WHERE myDate < [DATE]
[DATE] is a question asked to the user.
Can we do the same in SQL Store procedures?
I've tried the ? and %DATE% but it don't work.
Tanks all!
MarianneSQL Server runs on the server machine, so it cannot be made to pop up any
type of dialog on the client machine. I.e., you cannot have any type of user
interaction inside a stored procedure. Do this in the client application
instead and pass the value as a parameter to the stored procedure.
--
Tibor Karaszi
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Try:
CREATE PROCEDURE myproc
int @.myvariable
AS
SELECT @.myvariable
DELETE FROM mytable WHERE mytable.myfield = @.myvariable
GO
Take a look "CREATE PROCEDURE" in BOL.
Regards
---
All information provided above AS IS.
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Hello,
In SQL server you cant provide a parameter during run time. Instead you have
to execute the statement along with parameter value.
Sample:
declare @.date datetime
set @.date= getdate()
DELETE FROM tableName WHERE myDate < @.date
You can stored procedures also to do the similar stuff.
Thanks
Hari
US Technology
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:erTWbu6oDHA.2964@.tk2msftngp13.phx.gbl...
> Try:
> CREATE PROCEDURE myproc
> int @.myvariable
> AS
> SELECT @.myvariable
> DELETE FROM mytable WHERE mytable.myfield = @.myvariable
> GO
> Take a look "CREATE PROCEDURE" in BOL.
> Regards
> ---
> All information provided above AS IS.
> "Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
> news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> > Hello everybody!
> > I have a question for you.
> >
> > In access, you can make a query using parameters:
> >
> > DELETE *
> > FROM tableName
> > WHERE myDate < [DATE]
> >
> > [DATE] is a question asked to the user.
> > Can we do the same in SQL Store procedures?
> > I've tried the ? and %DATE% but it don't work.
> >
> > Tanks all!
> > Marianne
> >
>

Parameters

I am failry new to sql 2005 report services. I was wondering when using parameters to alter the information that is displayed in a report, can you use a request object to go against your query?
ie: Select * from table1 where ID = request("ID")
What I am doing is opening a report from a webpage and printing out what is displayed in a report. Some are straight forward that are just a listing of information where others the results are based on a unique value like an ID that is passed in the URL. Is there anyway to pass that to the report as well and filter the information based on a request or something along those lines?

Thanks in advance!

Hmm, I don't know if I understand you but when designing your report, create dataset with query: "select * from table1 where ID = @.ID" then in report parameters define @.ID parameter (it can be "static" or depend on another dataset).

Next, before you execute report you must assign to your parameter a value. In Rerpoting Services portal you have GUI that allow user to do so. You can also create GUI for your report parameters on your web page and send parameter values in URL.

Maciej

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

parameters

Hello,

i am new to ssrs and am trying to generate a report with 4 parameters. 2 of which are dates. The other 2 are drop downlists. Now the report works fine when i enter all 4 parameters. But in some cases i want to leave one of the parameters unentered . It doesnt all me to do that. Gives a error saying i need to enter the parameter. How do u get aroud this issue ?

I have seperate dataset for this parameters list and am using a where clause in my main query.

Can anyone please help me out with this?

Thx

Ashish

the parameters which are not needed all times can be set as

1. under parameter properties check all ow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

the parameters which are not needed all times can be set as

1. under parameter properties check allow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

hello rakam,

Thx for the reply.

My problem still continues. i tried ur suggestion but it still prompts me to select that paramater when trying to view report.

Any other options with be appreciated.

Thx

Ashish

|||The only thing you should have to do is define a valid Default value in the parameter setup in Design view of the report. If you still have the problem after saving and redeploying the report, you may want to delete the report from the report server and then deploy it again. Sometimes the subtle updates don't post properly unless you delete first.|||Read this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061210&SiteID=1

Parameters

Attempting to use parameters for report pulling from Teradata. Appears TD
does not like parameters. Thinking of custom code for generate dataset query
dynamically.. Is there function/method to generate / replace sql string?
Other ideas?
Thxin place of your query, have a vb-type expresssion like:
="select * from tablename where col1 = " & Parameters!Parm1.value
Let the report create your fields for you by running a hard-coded version of
the query, then change the query string to the vb expression. It will
generate the sql string expression, then execute it. As long as the
expression generates a sql string whose fields match those pre-generated in
the report, it will work.
Steve
"DJC" wrote:
> Attempting to use parameters for report pulling from Teradata. Appears TD
> does not like parameters. Thinking of custom code for generate dataset query
> dynamically.. Is there function/method to generate / replace sql string?
> Other ideas?
> Thx|||What error are you geting with the Parameters you are useing ?
In so cases TD does not let you name your Parameters. so what i did was this
--
(DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanks Steve.. works like a charm.
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanke for the response. Steve's respone resolved...
BTW, Most of the errors were I think were related to OLE DB vs ODBC on the
TD side...
"C.M" wrote:
> What error are you geting with the Parameters you are useing ?
> In so cases TD does not let you name your Parameters. so what i did was this
> --
> (DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
>
>
> "SteveIrwin" wrote:
> > in place of your query, have a vb-type expresssion like:
> >
> > ="select * from tablename where col1 = " & Parameters!Parm1.value
> >
> > Let the report create your fields for you by running a hard-coded version of
> > the query, then change the query string to the vb expression. It will
> > generate the sql string expression, then execute it. As long as the
> > expression generates a sql string whose fields match those pre-generated in
> > the report, it will work.
> >
> > Steve
> >
> > "DJC" wrote:
> >
> > > Attempting to use parameters for report pulling from Teradata. Appears TD
> > > does not like parameters. Thinking of custom code for generate dataset query
> > > dynamically.. Is there function/method to generate / replace sql string?
> > > Other ideas?
> > > Thx

Parameters

Hi, can i receive login name user who run my report as parameter which i can
use in report? if yes, how. We are use integrated security.
Thank you.You can use User!UserID
Check out http://blogs.msdn.com/tudortr/archive/2004/07/20/189398.aspx
--
| Thread-Topic: Parameters
| thread-index: AcT+A7k/V/wT0FbUQ7GfmvUvXhuiJQ==| X-WBNR-Posting-Host: 213.221.37.58
| From: =?Utf-8?B?ZG1pdHJp?= <dmitri@.discussions.microsoft.com>
| Subject: Parameters
| Date: Wed, 19 Jan 2005 00:49:01 -0800
| Lines: 4
| Message-ID: <62791180-F578-4629-BA4F-9F27B607CAC9@.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.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40211
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi, can i receive login name user who run my report as parameter which i
can
| use in report? if yes, how. We are use integrated security.
|
| Thank you.
|

Parameters

Hello,
I have a report with 2 parameters ( 2 combo boxes ). I would like the
content of the second combox box dynamically to change depending on what I
select in the first combobox.
Is it possible to achieve this in SQL reporting services ?
Thank you
PascalYes, look at this posting
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=51ea6a57-7bec-4787-93bc-5899a3ea9d71&mid=491a878a-2a2e-4cda-a96a-2cdc00423519
HTH
Charles Kangi, MCT, MCDBA
"Pascal Simler" wrote:
> Hello,
> I have a report with 2 parameters ( 2 combo boxes ). I would like the
> content of the second combox box dynamically to change depending on what I
> select in the first combobox.
> Is it possible to achieve this in SQL reporting services ?
> Thank you
> Pascal
>
>

Parameters

As a new person to SQL RS, With the report date parameters, can you have a
date control to choose from the calendar?Hi,
With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
Jan Pieter Posthuma
"Mike R" wrote:
> As a new person to SQL RS, With the report date parameters, can you have a
> date control to choose from the calendar?
>
>|||"Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> wrote in message
news:E705C246-22A2-4FC7-B60D-1E7465CB03F6@.microsoft.com...
> Hi,
> With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
> Jan Pieter Posthuma
> "Mike R" wrote:
>> As a new person to SQL RS, With the report date parameters, can you have
>> a
>> date control to choose from the calendar?
>>
Thanks, Whens SRS2005 available?|||November is the promised release date.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike R" <news@.mikeread.freeserve.co.uk> wrote in message
news:d9lvqc$6vj$1$8302bc10@.news.demon.co.uk...
> "Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> wrote in message
> news:E705C246-22A2-4FC7-B60D-1E7465CB03F6@.microsoft.com...
>> Hi,
>> With SRS 2000 it isn't possible. With the new SRS 2005 it's included.
>> Jan Pieter Posthuma
>> "Mike R" wrote:
>> As a new person to SQL RS, With the report date parameters, can you have
>> a
>> date control to choose from the calendar?
>>
> Thanks, Whens SRS2005 available?
>

Parameters

Isn't it possible to have parameters in the top of af statement, I get
syntax error if I try this and the parameter is there.
select top @.top etc.
JackTSQL doesn't support using a variable in a TOP statement, so reporting
services can't use a parameter in this case. The only way I know to do
this in TSQL is build the SQL as a string, and run it with
sp_executesql or EXEC.

Parameters

Hi all,
Is it possible to group parameters fields into logical groups in report
view?
eg.
filters:
start date:
end date:
colors:
table border:
chart background:
...
TIA,
KamelThere would be nice to have separate control to manage parameters
presentation and logic.
Sometimes there is a need to have a lot of parameters (over 20) and
there is a presentation problem in reportviewer.
Kamel
kamel wrote:
> Hi all,
> Is it possible to group parameters fields into logical groups in report
> view?
> eg.
> filters:
> start date:
> end date:
> colors:
> table border:
> chart background:
> ...
> TIA,
> Kamel

Parameters

Can I do formatting like font, color to the parameters. Also can we adjust the position of the parameters on the reportRS2K SP1 allows some modifications to the HTML Viewer toolbar through a
style sheet. Check out
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeethendar" <Jeethendar@.discussions.microsoft.com> wrote in message
news:89E8F35F-8BED-425C-9377-21D3778218AC@.microsoft.com...
> Can I do formatting like font, color to the parameters. Also can we adjust
the position of the parameters on the report

Parameters

Hi !
How come i pass olap parameter to my subreport?
I have two reports and i'd like link these reports by parameters, but when a
put parameter to link the value doen't pass to subreport.
I use SSAS 2005 and my parameters are dimension of my cube.
Help me !!!!
Thanks.
--
MattosCreate a custom member in your "parent" report. Example:
If you had the ReportDate attribute of the [Time] dimension in your report
and it needed to be passed to the sub-report, you'd create a custom member
with the definition
'[Time].[ReportDate].CurrentMember.UniqueName'
This would return something like
'[Time].[ReportDate].&[2006-05-26T00:00:00]'.
When you've added the sub-report, make sure you set up the parameters for it
too. They can be either parameters in the primary report or they can be
field values.
"André Mattos" <AndrMattos@.discussions.microsoft.com> wrote in message
news:3AD0B66F-FF85-4A76-A615-3A4B033B053C@.microsoft.com...
> Hi !
> How come i pass olap parameter to my subreport?
> I have two reports and i'd like link these reports by parameters, but when
> a
> put parameter to link the value doen't pass to subreport.
> I use SSAS 2005 and my parameters are dimension of my cube.
> Help me !!!!
> Thanks.
> --
> Mattos|||Tim,
Thanks, now work well.
André Mattos
--
Mattos
"Tim Dot NoSpam" wrote:
> Create a custom member in your "parent" report. Example:
> If you had the ReportDate attribute of the [Time] dimension in your report
> and it needed to be passed to the sub-report, you'd create a custom member
> with the definition
> '[Time].[ReportDate].CurrentMember.UniqueName'
> This would return something like
> '[Time].[ReportDate].&[2006-05-26T00:00:00]'.
> When you've added the sub-report, make sure you set up the parameters for it
> too. They can be either parameters in the primary report or they can be
> field values.
>
> "André Mattos" <AndrMattos@.discussions.microsoft.com> wrote in message
> news:3AD0B66F-FF85-4A76-A615-3A4B033B053C@.microsoft.com...
> > Hi !
> >
> > How come i pass olap parameter to my subreport?
> >
> > I have two reports and i'd like link these reports by parameters, but when
> > a
> > put parameter to link the value doen't pass to subreport.
> >
> > I use SSAS 2005 and my parameters are dimension of my cube.
> >
> > Help me !!!!
> >
> > Thanks.
> > --
> > Mattos
>
>

parameters

I am trying to make a report that will select the sales people who have
made 10 sales or more during the current month and show what those
sales are with the price..., and then show what those same sales
persons totals were for the previous month. my problem is to get the
totals from the previous month. example of the report is below. I am
trying to use two datasets one to get the first name, last name,
sales_id, product sold, price, and quantity. the other dataset will
get the last month total. I want to pass the sales person id to the
other dataset for each sales person. is there a way to do this?
Thanks, Landon
sales people who sold 10 or more:
first name, last name, sale_id, product sold, price, quantity
last month total: total
< -- this is where my problem is
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: totalWhat works best in this is to have a sub report. Create a normal report that
you pass the sales person id as a parameter. Make sure the report works.
Drag and drop the report onto the first report. Right mouse click on report,
parameters. Set the parameter for the sub report to the field which has the
sales person id.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135788313.801129.98180@.g49g2000cwa.googlegroups.com...
>I am trying to make a report that will select the sales people who have
> made 10 sales or more during the current month and show what those
> sales are with the price..., and then show what those same sales
> persons totals were for the previous month. my problem is to get the
> totals from the previous month. example of the report is below. I am
> trying to use two datasets one to get the first name, last name,
> sales_id, product sold, price, and quantity. the other dataset will
> get the last month total. I want to pass the sales person id to the
> other dataset for each sales person. is there a way to do this?
> Thanks, Landon
> sales people who sold 10 or more:
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> < -- this is where my problem is
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
>|||would i be able to use that data from the sub report in a graph with
the data from the other dataset?|||You can't join two datasets regardless of whether or not it is in a
subreport or not. You need to join the data into a single dataset if you
want to do this. Your subreport can have a graph, that isn't the problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135793391.283032.284030@.z14g2000cwz.googlegroups.com...
> would i be able to use that data from the sub report in a graph with
> the data from the other dataset?
>|||so in one graph i can not have the current month and last month totals?|||Not that I am aware of. A graph is based on a dataset, not on two datasets.
You can't join datasets. So if you want the data from two datasets on the
graph then you need to join the data at the source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135794518.867450.152820@.g49g2000cwa.googlegroups.com...
> so in one graph i can not have the current month and last month totals?
>|||Thanks Bruce for your help!
Landon|||You should fetch your data as a subquery in your data stream. You would then
have access to both current and prior month values. Example:
select first_name, last_name, sale_id, product_sold, price,
quantity,last_month_total
,(SELECT SUM(amount)
FROM sales
WHERE first_name = s.first_name and last_name = s.last_name
and sales_date in <month>
) AS prior_month_total
FROM sales s
where ...|||If they are on different databases you can link them in the query like
so:
Lets say 1 data set db is DB1 and the second is DB2
Place the query in DB1(and the dataset) and then you can say:
SELECT first_name, last_name, sale_id, product_sold, price,
quantity,DB2.tablename.last_month_total|||I would make a stored procedure and use a temp table based on the
months needed by the user. That way you can have both months in the
same dataset.

ParameterMetaData.getParameterType gives errors

Hi,
I'm using SQL Server 2005 and the new JDBC, version 1.0.809.102. I'm trying to get the ParameterMetaData out of a PreparedStatement, and have mixed success.

When getting the parameter type data out of very basic queries, like

SELECT * FROM users WHERE users.user_name = ?

I get the expected results, but when using a more complicated query, with joins like

SELECT * FROM users INNER JOIN customers WHERE customers.customer_name = ?
I get this error when attempting to get the parameter's type:

com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "customers.customer_name" could not be bound.

In fact, I seem to get this error with any prepared statement that contains a join. I don't see what I'm doing wrong, and the error message isn't really helpful to me either. Any ideas what might be wrong? Is this a driver bug?

Here's the code I use to get the parameter data:

PreparedStatement p =
con.prepareStatement("SELECT * FROM users INNER JOIN customers WHERE customers.customer_name = ?");
ParameterMetaData metaData = p.getParameterMetaData();
for (int i = 1; i <= metaData.getParameterCount(); i++) {
System.out.println(i + ": " + metaData.getParameterType(i));
}

Best regards,
Per Liedman

Is your issue resolved?

Thanks

|||

Hi-

Recently deployed our exisiting portal over a sql server 2005 database. The portal uses getParameterMetaData to return the number of expected paramters into prepared statements. We seem to be getting the above mentioned problem when we have joined tables in the query. Any ideas?

Thanks,

Paul

|||

Hi,

To identify what exactly goes to the SQL Server, please collect and review a SQL Profiler trace. That would tell you what's wrong, for instance if the parsing is incorrect.

BTW - you don't have a join predicate, are you intentionally seeking to get a cartesian product?

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

ParameterMetaData.getParameterType gives errors

Hi,
I'm using SQL Server 2005 and the new JDBC, version 1.0.809.102. I'm trying to get the ParameterMetaData out of a PreparedStatement, and have mixed success.

When getting the parameter type data out of very basic queries, like

SELECT * FROM users WHERE users.user_name = ?

I get the expected results, but when using a more complicated query, with joins like

SELECT * FROM users INNER JOIN customers WHERE customers.customer_name = ?
I get this error when attempting to get the parameter's type:

com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "customers.customer_name" could not be bound.

In fact, I seem to get this error with any prepared statement that contains a join. I don't see what I'm doing wrong, and the error message isn't really helpful to me either. Any ideas what might be wrong? Is this a driver bug?

Here's the code I use to get the parameter data:

PreparedStatement p =
con.prepareStatement("SELECT * FROM users INNER JOIN customers WHERE customers.customer_name = ?");
ParameterMetaData metaData = p.getParameterMetaData();
for (int i = 1; i <= metaData.getParameterCount(); i++) {
System.out.println(i + ": " + metaData.getParameterType(i));
}

Best regards,
Per Liedman

Is your issue resolved?

Thanks

|||

Hi-

Recently deployed our exisiting portal over a sql server 2005 database. The portal uses getParameterMetaData to return the number of expected paramters into prepared statements. We seem to be getting the above mentioned problem when we have joined tables in the query. Any ideas?

Thanks,

Paul

|||

Hi,

To identify what exactly goes to the SQL Server, please collect and review a SQL Profiler trace. That would tell you what's wrong, for instance if the parsing is incorrect.

BTW - you don't have a join predicate, are you intentionally seeking to get a cartesian product?

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

ParameterMetaData.getParameterType gives errors

Hi,
I'm using SQL Server 2005 and the new JDBC, version 1.0.809.102. I'm trying to get the ParameterMetaData out of a PreparedStatement, and have mixed success.

When getting the parameter type data out of very basic queries, like

SELECT * FROM users WHERE users.user_name = ?

I get the expected results, but when using a more complicated query, with joins like

SELECT * FROM users INNER JOIN customers WHERE customers.customer_name = ?
I get this error when attempting to get the parameter's type:

com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "customers.customer_name" could not be bound.

In fact, I seem to get this error with any prepared statement that contains a join. I don't see what I'm doing wrong, and the error message isn't really helpful to me either. Any ideas what might be wrong? Is this a driver bug?

Here's the code I use to get the parameter data:

PreparedStatement p =
con.prepareStatement("SELECT * FROM users INNER JOIN customers WHERE customers.customer_name = ?");
ParameterMetaData metaData = p.getParameterMetaData();
for (int i = 1; i <= metaData.getParameterCount(); i++) {
System.out.println(i + ": " + metaData.getParameterType(i));
}

Best regards,
Per Liedman

Is your issue resolved?

Thanks

|||

Hi-

Recently deployed our exisiting portal over a sql server 2005 database. The portal uses getParameterMetaData to return the number of expected paramters into prepared statements. We seem to be getting the above mentioned problem when we have joined tables in the query. Any ideas?

Thanks,

Paul

|||

Hi,

To identify what exactly goes to the SQL Server, please collect and review a SQL Profiler trace. That would tell you what's wrong, for instance if the parsing is incorrect.

BTW - you don't have a join predicate, are you intentionally seeking to get a cartesian product?

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Parameterizing the XPath for a modify()

Hi all,

I'm trying to write a generic stored procedure which will parameterize the XPath in my XML file so that I can update a value by giving just its path and the new value I wish to store:

Here is the code:
CREATE PROCEDURE ModifyLoanXML
@.LoanNumber char(60),
@.XPathQuery varchar(300),
@.Value varchar(300)
AS
UPDATE Loans SET LoanXML.modify('replace value of (sql:variable("@.XPathQuery"))[1] with xs:string(sql:variable("@.Value"))')
WHERE Loans.LoanNumber = @.LoanNumber
GO

Unfortunately I am getting the following error:
Msg 2337, Level 16, State 1, Procedure ModifyLoanXML, Line 6
XQuery [Loans.LoanXML.modify()]: The target of 'replace' must be at most one node, found 'xs:string ?'

I've tried a number of things to try to get this to go through but I'm having no luck. Is it possible to completely parameterize the XPath you wish to change when calling XQuery.modify() ?

Is there any way to specify that my path will always point to an attribute (not a node) so that this proc can be created? Thanks!

-Karthik Hariharan

The path specified in modify() must be a string literal, so the approach you are taking will not work out. The sql:variable("@.XPathQuery") is not interpreted as a path, but as a string value.

You can use dynamic sql to achieve what you are looking for. SInce you will be generating dynamic SQL, you will need to take steps to ensure that you dont end up with SQL injection from accepting untrusted XPaths.

|||Thanks Todd. I was trying to avoid the dynamic SQL method precisely to avoid any SQL injection vulnerabilities. Is there no way to achieve this dynamix XQuery using a stored procedure? If anyone else has a suggestion please let me know. Thanks.
|||I resolved the issue by using an Exec() T-SQL command. Here is my code below:

CREATE PROCEDURE [dbo].[ModifyLoanXML]
@.LoanID UNIQUEIDENTIFIER,
@.XPathQuery varchar(max),
@.Value varchar(max)
AS
DECLARE @.query varchar(max)
DECLARE @.LoanIDstr varchar(max)
SET @.LoanIDStr = CONVERT (varchar(max),@.LoanID)

SET @.query='UPDATE Loans SET LoanXML.modify(''declare namespace MISMO="http://mrgdev.local/mismo/";replace value of ' + @.XPathQuery + ' with "' +@.Value +'"'') WHERE Loans.InternalID = ''' + @.LoanIDstr + ''''
exec(@.query)

To avoid a possible SQL injection, I parameterized the LoanID and convert it to a varchar within the stored procedure. Just wanted to share this with you all.

Regards,
Karthik Hariharan
|||

Hi Karthik,

Your code still appears to be subject to SQL injection attacks with respect to the XPathQuery variable and the Value variable, if either is untrusted. Since you are concatenating them with the SQL string, if an untrusted user was able to specify the XPathQuery or Value variable, then they may be able to embed quotes and comment characters to change the behavior of your query. You can change your query to parameterize both Value (using sql:variable) and LoadIDstr (using a parameter) but accepting untrusted XPathQuery will be difficult without fully validating that it is safe.