Bug? Available Values list for boolean parameters

Hi,
I have defined a boolean parameter in my report and I wish to display a list
of possible values instead of the True/False radio button. I have defined
two possible values in the Available Values list (Report Parameters Window)
but it looks as though they are ignored, the parameter toolbar still
displaying the True/False radio buttons...
Hope someone can help.Looks like a bug to me...best chance is to programatically create your
own parameter page.
"webfrog" <webfrog@.discussions.microsoft.com> wrote in message news:<11B5246A-F6E6-4583-8945-A945851F58CD@.microsoft.com>...
> Hi,
> I have defined a boolean parameter in my report and I wish to display a list
> of possible values instead of the True/False radio button. I have defined
> two possible values in the Available Values list (Report Parameters Window)
> but it looks as though they are ignored, the parameter toolbar still
> displaying the True/False radio buttons...
> Hope someone can help.|||If you change your datatype to integer, then put in available values such
that true = 1 and false = 0, you will get a drop-down list with true & false
options.
"webfrog" wrote:
> Hi,
> I have defined a boolean parameter in my report and I wish to display a list
> of possible values instead of the True/False radio button. I have defined
> two possible values in the Available Values list (Report Parameters Window)
> but it looks as though they are ignored, the parameter toolbar still
> displaying the True/False radio buttons...
> Hope someone can help.
>

Bug?

Is there a bug in SQL Server Reporting Services which produces an error like
The value expression for the query parameter â'@.homebaseâ' refers to a
non-existing report parameter â'homebase_1â'
I made some changes in the dataset (1 of them) and it starts giving me this
error for no reason although the same parameter @.homebase is referred in more
than 5 datasets...
Any help is appreciated!
ThanksI got this before and did this...
1) Copy query in dataset
2) Delete dataset
3) re-create dataset
4) run-query in data view and it will re-build columns.
This is what I always do when I do anything to the columns.
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:Asim@.discussions.microsoft.com:
> Is there a bug in SQL Server Reporting Services which produces an error
> like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
> this
> error for no reason although the same parameter @.homebase is referred in
> more
> than 5 datasets...
> Any help is appreciated!
> Thanks|||Click on the ..., go the parameters tab and remap your query parameter to
the report parameter. Sometimes RS gets confused.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:1C6BF53F-97E6-4678-B76F-66D0BB655A3F@.microsoft.com...
> Is there a bug in SQL Server Reporting Services which produces an error
like
> The value expression for the query parameter '@.homebase' refers to a
> non-existing report parameter 'homebase_1'
> I made some changes in the dataset (1 of them) and it starts giving me
this
> error for no reason although the same parameter @.homebase is referred in
more
> than 5 datasets...
> Any help is appreciated!
> Thanks
>|||Yep - bug
Lost over 2 days development time re-creating reports
NICE ONE MICROSOFT !
From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Another option could have been to hand edit the rdl file to correct the
problem (this is an XML file).
We have done this once to use an option that the UI didn't offered in the
first release of Reporting Services...
--
Patrice
"om" <om_optical@.hotmail.com> a écrit dans le message de news:
1df92cf4-df9e-4b38-905c-79df77ba241f@.developmentnow.com...
> Yep - bug
> Lost over 2 days development time re-creating reports
> NICE ONE MICROSOFT !
>
> From http://www.developmentnow.com/g/115_2005_1_0_0_454665/Bug.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

BUG: provider cannot derive parameter information and setparameter

I create reports on an oracle db. I use the correct oracle provider. When i
create a report parameter and run it in the datatab, it says "provider cannot
derive parameter information and setparameterinfo has not been called".
However if I go to generic query designer or the layout tab, the report
parameter works correctly.
This appears to be a bug!!. Could someone from Microsoft please confirm this.
Much appreciated
Kevin WeirYou will need an Oracle Client 8.1.7 or later installed on the server and
the client machines. Please verify you are not using an older Oracle client.
Regardless of the data provider (OleDB or managed), they will need the
Oracle Client (OCI) to actually talk to the Oracle server.
As indicated in several other discussion threads on this newsgroup, the
visual query designer (with 4 panes) is a shared component of the VS
framework (which is unable to use the managed provider and uses the MS OleDB
provider for Oracle instead). Even if you select "Oracle" from the data
source dialog, the visual query designer will actually use the OleDB
provider, while the generic query designer will *always* use the data
provider you actually selected. I.e. "Oracle" is the managed provider of the
.NET framework. Most likely you are using named parameters (e.g. :par1)
which are only supported by the managed Oracle provider (hence the query
execution works successfully in the generic text-based query designer). The
OleDB provider only supports unnamed parameters.
Some information can also be found in this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Weir" <KevinWeir@.discussions.microsoft.com> wrote in message
news:D359816A-16DD-4CDD-959D-C5B066474C15@.microsoft.com...
> I create reports on an oracle db. I use the correct oracle provider. When
i
> create a report parameter and run it in the datatab, it says "provider
cannot
> derive parameter information and setparameterinfo has not been called".
> However if I go to generic query designer or the layout tab, the report
> parameter works correctly.
> This appears to be a bug!!. Could someone from Microsoft please confirm
this.
> Much appreciated
> Kevin Weir
>|||Just adding another comment: The graphical query designer in RS is the VDT
component which supports only a limited list of data providers as outlined
in this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811241
The text-based generic query designer in RS supports almost any data
provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23AOfSgAsEHA.3396@.tk2msftngp13.phx.gbl...
> You will need an Oracle Client 8.1.7 or later installed on the server and
> the client machines. Please verify you are not using an older Oracle
client.
> Regardless of the data provider (OleDB or managed), they will need the
> Oracle Client (OCI) to actually talk to the Oracle server.
> As indicated in several other discussion threads on this newsgroup, the
> visual query designer (with 4 panes) is a shared component of the VS
> framework (which is unable to use the managed provider and uses the MS
OleDB
> provider for Oracle instead). Even if you select "Oracle" from the data
> source dialog, the visual query designer will actually use the OleDB
> provider, while the generic query designer will *always* use the data
> provider you actually selected. I.e. "Oracle" is the managed provider of
the
> .NET framework. Most likely you are using named parameters (e.g. :par1)
> which are only supported by the managed Oracle provider (hence the query
> execution works successfully in the generic text-based query designer).
The
> OleDB provider only supports unnamed parameters.
> Some information can also be found in this KB article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Kevin Weir" <KevinWeir@.discussions.microsoft.com> wrote in message
> news:D359816A-16DD-4CDD-959D-C5B066474C15@.microsoft.com...
> > I create reports on an oracle db. I use the correct oracle provider.
When
> i
> > create a report parameter and run it in the datatab, it says "provider
> cannot
> > derive parameter information and setparameterinfo has not been called".
> >
> > However if I go to generic query designer or the layout tab, the report
> > parameter works correctly.
> >
> > This appears to be a bug!!. Could someone from Microsoft please confirm
> this.
> >
> > Much appreciated
> > Kevin Weir
> >
>|||Hi Robert
Thanks for your response. Your comments have clarrified this issue for me.
I did not realize the OLE DB provider was being used by Visual Designer
which accounts for different behavior in Generic Query designer.
For others that may read this thread we are using Oracle 9.2.0.4 client with
named parameters. For example, one of our queries might look like this:
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :EMPLOYEE_ID
Regards
Kevin
"Robert Bruckner [MSFT]" wrote:
> You will need an Oracle Client 8.1.7 or later installed on the server and
> the client machines. Please verify you are not using an older Oracle client.
> Regardless of the data provider (OleDB or managed), they will need the
> Oracle Client (OCI) to actually talk to the Oracle server.
> As indicated in several other discussion threads on this newsgroup, the
> visual query designer (with 4 panes) is a shared component of the VS
> framework (which is unable to use the managed provider and uses the MS OleDB
> provider for Oracle instead). Even if you select "Oracle" from the data
> source dialog, the visual query designer will actually use the OleDB
> provider, while the generic query designer will *always* use the data
> provider you actually selected. I.e. "Oracle" is the managed provider of the
> ..NET framework. Most likely you are using named parameters (e.g. :par1)
> which are only supported by the managed Oracle provider (hence the query
> execution works successfully in the generic text-based query designer). The
> OleDB provider only supports unnamed parameters.
> Some information can also be found in this KB article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Weir" <KevinWeir@.discussions.microsoft.com> wrote in message
> news:D359816A-16DD-4CDD-959D-C5B066474C15@.microsoft.com...
> > I create reports on an oracle db. I use the correct oracle provider. When
> i
> > create a report parameter and run it in the datatab, it says "provider
> cannot
> > derive parameter information and setparameterinfo has not been called".
> >
> > However if I go to generic query designer or the layout tab, the report
> > parameter works correctly.
> >
> > This appears to be a bug!!. Could someone from Microsoft please confirm
> this.
> >
> > Much appreciated
> > Kevin Weir
> >
>
>

Bug: default value showing as query based when it is not (report m

I have a report which uses a query to populate a parameter.
The default value is not queried, this is just set to "=1".
When the report is deployed, the parameter in report manager is showing as
'query based' which is wrong.
This is a pain in the ass as when I try linking to this report, I am not
able to set the value to something else.If you use a query to specify a range of parameters then it seems to show
default as 'query based' in the report manager.
What I have discovered is that if you change something to do with the
parameters on the report and redeploy, you can pick up the changes in the
linked report by repointing the linked report at the same report, this forces
some kind of refresh to occur and although very annoying, is easier than
recreating the linked report.
<despair>Thanks to MS for providing a UI of such high quality.</despair>
"adolf garlic" wrote:
> I have a report which uses a query to populate a parameter.
> The default value is not queried, this is just set to "=1".
> When the report is deployed, the parameter in report manager is showing as
> 'query based' which is wrong.
> This is a pain in the ass as when I try linking to this report, I am not
> able to set the value to something else.

bug: data-driven subscription generates deadlock in ReportServer D

I've got a report that can have about 50 possible parameter combinations. The
underlying query is fairly expensive, so I want to cache the dataset every
morning after I expire yesterday's cache. So I set up a data-driven
subscription to run every morning and execute all the parameter combinations
and deliver it to the Null delivery extension. The problem is that when the
scheduled time rolls around, it tries to run all 50 parameter combinations at
once. (Actually, it looks like it runs 2 or 3 combinations a second without
waiting for the first one to finish.) The query of the data sources run fine,
but I get a deadlock when Reporting Services tries to write the dataset to
the cache. Somewhere between 10% and 50% of the 50 fail every day. The logs
show a standard deadlock error. The error is below.
Is there any config file setting to tell RS to retry writing to the cache a
few times before giving up because of a deadlock? Is this possibly a SP2 fix?
Or is there any way to tell Reporting Services to run the 50 executions
serially instead of concurrently? Or do I need to investigate creating some
custom code to execute and cache the reports on schedule (so I can make it
execute them serially)?
Please let me know if you need more info... or if you need something from
the SQL Server logs. Thanks in advance.
--
ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
132) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Guid
reportId, ReportSnapshot snapshotData, DateTime executionDateTime, DateTime&
expirationDateTime)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
-- End of inner exception stack trace --Hi,
Try setting MaxQueueThreads to 1 in the RSReportServer.config file. You may
want to escalate this to the RS PSS since it looks like a bug to me.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:516B456B-0E41-46AA-B775-D3BA1B13C1F0@.microsoft.com...
> I've got a report that can have about 50 possible parameter combinations.
The
> underlying query is fairly expensive, so I want to cache the dataset every
> morning after I expire yesterday's cache. So I set up a data-driven
> subscription to run every morning and execute all the parameter
combinations
> and deliver it to the Null delivery extension. The problem is that when
the
> scheduled time rolls around, it tries to run all 50 parameter combinations
at
> once. (Actually, it looks like it runs 2 or 3 combinations a second
without
> waiting for the first one to finish.) The query of the data sources run
fine,
> but I get a deadlock when Reporting Services tries to write the dataset to
> the cache. Somewhere between 10% and 50% of the 50 fail every day. The
logs
> show a standard deadlock error. The error is below.
> Is there any config file setting to tell RS to retry writing to the cache
a
> few times before giving up because of a deadlock? Is this possibly a SP2
fix?
> Or is there any way to tell Reporting Services to run the 50 executions
> serially instead of concurrently? Or do I need to investigate creating
some
> custom code to execute and cache the reports on schedule (so I can make it
> execute them serially)?
> Please let me know if you need more info... or if you need something from
> the SQL Server logs. Thanks in advance.
> --
> ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
> 132) was deadlocked on lock resources with another process and has been
> chosen as the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Gu
id
> reportId, ReportSnapshot snapshotData, DateTime executionDateTime,
DateTime&
> expirationDateTime)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
> -- End of inner exception stack trace --
>|||That did it. Thanks SO much for your response. Now the executions of each
individual parameter combination run serially and don't cause deadlocks.
I did some research on that config file setting you suggested, Teo. Here's
what I found:
MaxQueueThreads
Specifies the maximum number of threads dedicated to polling the event table
in the report server database.
Range: 0 to max integer.
The default is 0.
So if I understand correctly, this setting will ONLY affect the number of
threads used to process events like the fulfilling of subscriptions. Is that
correct? It will not impact performance of multiple users executing and
viewing reports on demand and at the same time, right?|||This is my understanding as well but verify it using the Performance
Monitor.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C9EF39EE-1638-47E9-AE5F-24896700A73D@.microsoft.com...
> That did it. Thanks SO much for your response. Now the executions of each
> individual parameter combination run serially and don't cause deadlocks.
> I did some research on that config file setting you suggested, Teo. Here's
> what I found:
> MaxQueueThreads
> Specifies the maximum number of threads dedicated to polling the event
table
> in the report server database.
> Range: 0 to max integer.
> The default is 0.
> So if I understand correctly, this setting will ONLY affect the number of
> threads used to process events like the fulfilling of subscriptions. Is
that
> correct? It will not impact performance of multiple users executing and
> viewing reports on demand and at the same time, right?

Bug report RS with SP1

Microsoft gang,
I have the following query. if I use @.Shipping instead of @.CustomerShipTo as
a parameter name, at the preview stage, I receive @.StartDate not defined
error.
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
To)
GROUP BY ProductType,Product,UnitofMeasure,DDate
Regards,
CemSome more information about the parameter bug.
When I add the parameter manually (Report > Report Parameters), the query
below blows at run time, works perfect at query designer.
The way I get around this problem was to define the parameter in the result
query first. The moment I add it to the function below, it shows up in
report parameters. After configuration works perfect.
/*Main query*/
SELECT
ProductType,Product,
SUM(TotalCases) as TotalCases,
UnitofMeasure,
DatePart(yyyy, DDate) as YDate,
DateName(mm,DDate) as MDate
FROM
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
,@.BrandName,@.CanSize)
GROUP BY ProductType,Product,UnitofMeasure,DDate
/*Parameter query*/
SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM fn_CreateCanList()
UNION
SELECT Null , '--Select All--'
Regards,
Cem
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>
> Microsoft gang,
> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
as
> a parameter name, at the preview stage, I receive @.StartDate not defined
> error.
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
>
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> To)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> Regards,
> Cem
>|||Thanks. We'll take a look and see if we can repro here.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Could you send me definitions of your functions fn_CreateBlankSalesCases and
fn_CreateCanList?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Some more information about the parameter bug.
> When I add the parameter manually (Report > Report Parameters), the query
> below blows at run time, works perfect at query designer.
> The way I get around this problem was to define the parameter in the
> result
> query first. The moment I add it to the function below, it shows up in
> report parameters. After configuration works perfect.
> /*Main query*/
> SELECT
> ProductType,Product,
> SUM(TotalCases) as TotalCases,
> UnitofMeasure,
> DatePart(yyyy, DDate) as YDate,
> DateName(mm,DDate) as MDate
> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> ,@.BrandName,@.CanSize)
> GROUP BY ProductType,Product,UnitofMeasure,DDate
> /*Parameter query*/
> SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> fn_CreateCanList()
> UNION
> SELECT Null , '--Select All--'
> Regards,
> Cem
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
>>
>> Microsoft gang,
>> I have the following query. if I use @.Shipping instead of @.CustomerShipTo
> as
>> a parameter name, at the preview stage, I receive @.StartDate not defined
>> error.
>> SELECT
>> ProductType,Product,
>> SUM(TotalCases) as TotalCases,
>> UnitofMeasure,
>> DatePart(yyyy, DDate) as YDate,
>> DateName(mm,DDate) as MDate
>> FROM
> fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
>> To)
>> GROUP BY ProductType,Product,UnitofMeasure,DDate
>> Regards,
>> Cem
>>
>|||Lev,
Below you may find the functions you requested. Thanks.
Regards,
Cem
CREATE FUNCTION fn_CreateBlankSalesCases (
@.StartDate as DateTime,
@.EndDate as DateTime,
@.Broker as VarChar(50),
@.Customer as VarChar(50),
@.CustomerShip as VarChar(50),
@.BrandName as VarChar(50),
@.CanSize as VarChar(50))
RETURNS @.DateTable TABLE (
ProductType VarChar(150),
Product VarChar(150),
TotalCases Int,
UnitofMeasure VarChar(10),
DDate DateTime)
/*Table created in memory*/
AS
BEGIN
DECLARE @.StartTempDate as DateTime
DECLARE @.ProductType as VarChar(150)
DECLARE @.Product as VarChar(150)
DECLARE @.TotalCases as Int
DECLARE @.UnitofMeasure as VarChar(10)
DECLARE @.TDate as DateTime
SET @.StartTempDate = @.StartDate
/* String manupilation for the brandname */
If (@.CanSize IS NULL)
SET @.CanSize = '%'
ELSE
SET @.CanSize = '%' + @.CanSize + '%'
If (@.BrandName IS NULL)
SET @.BrandName = '%'
ELSE
SET @.BrandName = @.BrandName + '%'
DECLARE SalesActualProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesActualProducts
FETCH NEXT FROM
SalesActualProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,@.TotalCases,@.UnitofMeasure,@.TDate)
FETCH NEXT FROM SalesActualProducts INTO @.ProductType, @.Product,
@.TotalCases, @.UnitofMeasure, @.TDate
END
CLOSE SalesActualProducts
DECLARE SalesProducts CURSOR FOR
SELECT
can_typ1.description AS ProductType,
plldb1.descl_plldbx AS Product,
sitem1.qty_sd AS TotalCases,
sitem1.um_sd AS UnitofMeasure,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime) AS TDate
FROM can_typ1 INNER JOIN
plldb1 ON can_typ1.type_mstr = plldb1.can_type INNER JOIN
sitem1 ON plldb1.part_mstr = sitem1.part_sd INNER JOIN
shead1 ON sitem1.sorder_sd = shead1.sorder_so
WHERE
(NOT (shead1.sotype_so = 'V')) AND
(NOT (shead1.post_date IS NULL)) AND
(shead1.post_date > @.StartDate) AND
(shead1.post_date < @.EndDate) AND
(shead1.sales_so = @.Broker) AND
(shead1.billto_so = COALESCE(@.Customer, shead1.billto_so)) AND
(shead1.shipto_so = COALESCE(@.CustomerShip, shead1.shipto_so))AND
(sitem1.part_sd LIKE @.BrandName) AND
(can_typ1.can_size LIKE @.CanSize)
GROUP BY
can_typ1.description,
plldb1.descl_plldbx,
sitem1.qty_sd,sitem1.um_sd,
CAST(CAST(MONTH(shead1.post_date) AS varchar(20)) + '/1/' +
CAST(YEAR(shead1.post_date) AS varchar(20)) AS datetime)
/*Start to insert data into the table */
OPEN SalesProducts
FETCH NEXT FROM
SalesProducts INTO @.ProductType, @.Product, @.TotalCases, @.UnitofMeasure,
@.TDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
WHILE (@.StartDate < @.EndDate)
BEGIN
INSERT INTO @.DateTable VALUES (@.ProductType,
@.Product,0,@.UnitofMeasure,@.StartDate)
SET @.StartDate = DATEADD(month, 1, @.StartDate)
END
SET @.StartDate = @.StartTempDate
FETCH NEXT FROM SalesProducts INTO @.ProductType, @.Product, @.TotalCases,
@.UnitofMeasure, @.TDate
END
CLOSE SalesProducts
/*Return Table*/
RETURN
END
CREATE FUNCTION fn_CreateCanList()
RETURNS
@.CanTable TABLE (
CanSize VarChar(150),
CanSizeOld VarChar(150)
)
/*Table created in memory*/
AS
BEGIN
DECLARE @.CanSizeTemp as VarChar(50)
DECLARE CanSizeList CURSOR FOR
SELECT DISTINCT can_size
FROM can_typ1
WHERE can_size IS NOT NULL
/*Start to insert data into the table */
OPEN CanSizeList
FETCH NEXT FROM
CanSizeList INTO @.CanSizeTemp
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF CHARINDEX('/',@.CanSizeTemp) > 0
INSERT INTO @.CanTable
VALUES(RIGHT(RTRIM(@.CanSizeTemp),LEN(@.CanSizeTemp)-CHARINDEX('/',@.CanSizeTem
p)) ,@.CanSizeTemp)
ELSE
INSERT INTO @.CanTable VALUES(@.CanSizeTemp, @.CanSizeTemp)
FETCH NEXT FROM CanSizeList INTO @.CanSizeTemp
END
CLOSE CanSizeList
/*Return Table*/
RETURN
END
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:uQ8Q1hnfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Could you send me definitions of your functions fn_CreateBlankSalesCases
and
> fn_CreateCanList?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> news:uNgnLbhfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> >
> >
> > Some more information about the parameter bug.
> >
> > When I add the parameter manually (Report > Report Parameters), the
query
> > below blows at run time, works perfect at query designer.
> >
> > The way I get around this problem was to define the parameter in the
> > result
> > query first. The moment I add it to the function below, it shows up in
> > report parameters. After configuration works perfect.
> >
> > /*Main query*/
> > SELECT
> > ProductType,Product,
> > SUM(TotalCases) as TotalCases,
> > UnitofMeasure,
> > DatePart(yyyy, DDate) as YDate,
> > DateName(mm,DDate) as MDate
> >
> > FROM
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> > ,@.BrandName,@.CanSize)
> > GROUP BY ProductType,Product,UnitofMeasure,DDate
> >
> > /*Parameter query*/
> > SELECT DISTINCT CanSize, CanSize as CanSizeDisplay FROM
> > fn_CreateCanList()
> > UNION
> > SELECT Null , '--Select All--'
> >
> > Regards,
> > Cem
> >
> > "Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
> > news:%23VP2%23ZmeEHA.2812@.tk2msftngp13.phx.gbl...
> >>
> >>
> >> Microsoft gang,
> >>
> >> I have the following query. if I use @.Shipping instead of
@.CustomerShipTo
> > as
> >> a parameter name, at the preview stage, I receive @.StartDate not
defined
> >> error.
> >>
> >> SELECT
> >> ProductType,Product,
> >> SUM(TotalCases) as TotalCases,
> >> UnitofMeasure,
> >> DatePart(yyyy, DDate) as YDate,
> >> DateName(mm,DDate) as MDate
> >>
> >> FROM
> >>
> >
fn_CreateBlankSalesCases(@.StartDate,@.EndDate,@.Broker,@.Customer,@.CustomerShip
> >> To)
> >> GROUP BY ProductType,Product,UnitofMeasure,DDate
> >>
> >> Regards,
> >> Cem
> >>
> >>
> >
> >
>

bug in focus

so i've got 5 parameters; a couple of booleans
refer to them as
1 2
3 4
5
#2 is the date parameter.. and I keep on getting the date prompt / drop
down...
a) select value for 1, 3, 5
b) enter #2; the value 4/1/06 in the date dropdown
c) mouse focus to #4
d) hit enter when I'm done filling out 4-- since im then DONE filling
in all of my parameters;..
do you know what happens?
i get .dropdown on the datepicker dropdown.
shouldn't they test these things before they start selling it?yeah; i've noticed the same thing.. im surprised MS didn't catch this
-Aaron
On Oct 4, 1:51 pm, "aaron.ke...@.gmail.com" <aaron.ke...@.gmail.com>
wrote:
> so i've got 5 parameters; a couple of booleans
> refer to them as
> 1 2
> 3 4
> 5
> #2 is the date parameter.. and I keep on getting the date prompt / drop
> down...
> a) select value for 1, 3, 5
> b) enter #2; the value 4/1/06 in the date dropdown
> c) mouse focus to #4
> d) hit enter when I'm done filling out 4-- since im then DONE filling
> in all of my parameters;..
> do you know what happens?
> i get .dropdown on the datepicker dropdown.
> shouldn't they test these things before they start selling it?

Break Deadlocks Quicker

Is there a parameter within SQL Server 2000 Enterprise
Edition that will break deadlocks quicker and use less
resources?
Thanks,
Mike
SQL Server usually detects deadlocks pretty quickly and when it does it
wastes no time killing one of the participants. You can however use a hint
called SET DEADLOCK_PRIORITY to potentially affect who gets killed.
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:f55e01c43dcf$fb009320$a301280a@.phx.gbl...
> Is there a parameter within SQL Server 2000 Enterprise
> Edition that will break deadlocks quicker and use less
> resources?
> Thanks,
> Mike
>
|||Mike,
you might want to check out
SET LOCK_TIMEOUT
SET DEADLOCK_PRIORITY
in BOL.
HTH,
Paul Ibison
|||Though don't let that stop you coming to the route causes of the deadlocking. SQL BOL gives a pretty good overview.
Alicia
www.sqlporn.co.uk

Break Deadlocks Quicker

Is there a parameter within SQL Server 2000 Enterprise
Edition that will break deadlocks quicker and use less
resources?
Thanks,
MikeSQL Server usually detects deadlocks pretty quickly and when it does it
wastes no time killing one of the participants. You can however use a hint
called SET DEADLOCK_PRIORITY to potentially affect who gets killed.
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:f55e01c43dcf$fb009320$a301280a@.phx.gbl...
> Is there a parameter within SQL Server 2000 Enterprise
> Edition that will break deadlocks quicker and use less
> resources?
> Thanks,
> Mike
>|||Mike,
you might want to check out
SET LOCK_TIMEOUT
SET DEADLOCK_PRIORITY
in BOL.
HTH,
Paul Ibison|||Though don't let that stop you coming to the route causes of the deadlocking
. SQL BOL gives a pretty good overview.
Alicia
www.sqlporn.co.uk

Break Deadlocks Quicker

Is there a parameter within SQL Server 2000 Enterprise
Edition that will break deadlocks quicker and use less
resources?
Thanks,
MikeSQL Server usually detects deadlocks pretty quickly and when it does it
wastes no time killing one of the participants. You can however use a hint
called SET DEADLOCK_PRIORITY to potentially affect who gets killed.
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:f55e01c43dcf$fb009320$a301280a@.phx.gbl...
> Is there a parameter within SQL Server 2000 Enterprise
> Edition that will break deadlocks quicker and use less
> resources?
> Thanks,
> Mike
>|||Mike,
you might want to check out
SET LOCK_TIMEOUT
SET DEADLOCK_PRIORITY
in BOL.
HTH,
Paul Ibison

brackets in parameter using URL navigation

I am passing my report parameter descriptions from an .aspx page to one
report parameter (concatenated) using url access. It works great except when
the description contains brackets "()" the url get confused on the first "("
and I get an invalid path.
Any ideas?It was Friday when I wrote the original thread, It is actually an "&" that is
causing the problem
"KENWOOD" wrote:
> I am passing my report parameter descriptions from an .aspx page to one
> report parameter (concatenated) using url access. It works great except when
> the description contains brackets "()" the url get confused on the first "("
> and I get an invalid path.
> Any ideas?|||Try uniciding the URL, as an "&" is a special character in a URL, same with %
etc.
"KENWOOD" wrote:
> It was Friday when I wrote the original thread, It is actually an "&" that is
> causing the problem
> "KENWOOD" wrote:
> > I am passing my report parameter descriptions from an .aspx page to one
> > report parameter (concatenated) using url access. It works great except when
> > the description contains brackets "()" the url get confused on the first "("
> > and I get an invalid path.
> >
> > Any ideas?