Report Design help for Newb - Regarding Date Paramaters

I have been trying very hard for days now to build a report using VS 2003.net
I just need to query an order table by a date range. ie) from date a to
date b. The order table contains PO's and have recorded dates. I just need
an example of how to report based upon date range. Seems simple enough,
just cant figure it out.I assume you mean you want the user to be able to specify the date range?
If you currently have a sql statement that returns all orders and have a
report that displays that data, simply modify the sql statement and specify:
where datefield between @.startdate and @.enddate
That will automatically create report parameters. When you preview the
report, it will prompt you to type in start and end dates.
For more info, just do searches for query parameters and report parameters.
Mike G.
"tdodd" <tdodd@.discussions.microsoft.com> wrote in message
news:6A7F2EE5-FAFA-4852-B771-35FBC9431D71@.microsoft.com...
>I have been trying very hard for days now to build a report using VS
>2003.net
> I just need to query an order table by a date range. ie) from date a to
> date b. The order table contains PO's and have recorded dates. I just
> need
> an example of how to report based upon date range. Seems simple enough,
> just cant figure it out.

Report DateTime Parameters in UK date format

I have a report that I've developed that accepts several datetime parameters.
In the Report properties, I've set the report Language to English (United
Kingdom).
I am calling my report via the Web Service from an ASP.Net application. The
ASP.Net application Culture and UICulture are set for en-GB.
What is happening is that when a date is passed to one of the report
parameters (gEndDate) as "14/12/2004" (December 14), I get the following
error:
The value provided for the report parameter 'gEndDate' is not valid for its
typeI should also point out that my Visual Studio .Net 2003 environment is in
en-US and my computer is in the United States English setting.
The development environment I am testing (where I got the error) is a
Virtual PC environment set up as English UK in the Control Panel.
"AdamB" wrote:
> I have a report that I've developed that accepts several datetime parameters.
> In the Report properties, I've set the report Language to English (United
> Kingdom).
> I am calling my report via the Web Service from an ASP.Net application. The
> ASP.Net application Culture and UICulture are set for en-GB.
> What is happening is that when a date is passed to one of the report
> parameters (gEndDate) as "14/12/2004" (December 14), I get the following
> error:
> The value provided for the report parameter 'gEndDate' is not valid for its
> type
>

Report Date Help

I created the below report and it has worked fine all year, however it will
not pull data for Dec 2004. Can anyone tell me what may be causing this?
Thanks!
Glen
SELECT
Customers.CustomerName, DNRM_ItemMaster.ItemCategory4,
SalesDetail.ExtendedPrice,
ISNULL((SELECT
SalesDetailWH.ExtendedPrice
FROM
SalesDetail SalesDetailWH
WHERE
SalesDetailWH.UserDefined5 = 'Warehouse' AND
SalesDetailWH.RowID = SalesDetail.RowID),0) AS WHDollars,
ISNULL((SELECT
SalesDetailFD.ExtendedPrice
FROM
SalesDetail SalesDetailFD
WHERE
SalesDetailFD.UserDefined5 <> 'Warehouse' AND
SalesDetailFD.RowID = SalesDetail.RowID),0) AS FDDollars,
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount AS Price,
SalesDetail.TradeDiscountAmount,
SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
SalesDetail.DocumentType,
SalesDetail.CustomerNumber, DNRM_ItemMaster.ItemClassDescription,
SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
DNRM_Stockholder.StockholderNumber, DNRM_Stockholder.StockholderName,
ISNULL((SELECT
1
FROM
DNRM_ItemMaster DNRM_ItemMasterOB
WHERE
(DNRM_ItemMasterOB.ClassCode='DPOS' OR
DNRM_ItemMasterOB.ClassCode='MTYKFD' OR DNRM_ItemMasterOB.ClassCode='NPOS'
OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
DNRM_ItemMasterOB.ClassCode='POS' OR
(DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
DNRM_ItemMasterOB.ItemNumber = DNRM_ItemMaster.ItemNumber),0) AS
OverBill
FROM
(DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
INNER JOIN DNTIRWH.dbo.Customers Customers ON
Customers.CustomerNumber = SalesDetail.CustomerNumber
INNER JOIN DNTIRWH.dbo.DNRM_Stockholder DNRM_Stockholder ON
Left(DNRM_Stockholder.StockholderNumber,
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
WHERE
-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC', 'AT', 'BA', 'BT', 'FM',
'LG', 'MT', 'OR', 'SP', 'TU') AND
-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
DNRM_ItemMaster.ItemType='Sales Inventory' AND
-- Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00' AND
Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
ORDER BY
SalesDetail.CustomerNumber,
DNRM_ItemMaster.ItemCategory4Hi Glen,
Are you using a SQL Server database as the Data Source?
To isolate this issue you may want to run the query via
Query Analyzer or OSQL against your database to see if
there is any data returned. If there is any problem at
this point, the SQL Server database may have something
wrong.
If SQL Server returns the data correctly, run the query
in Report Designer->Data tab to verify the result. You
may also want to create a new report and create a new
dataset to test the problem.
Sincerely,
William Wang
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>From: "Glen Tosco" <glen@.delnat.com>
>Subject: Report Date Help
>Date: Thu, 6 Jan 2005 14:49:19 -0600
>Lines: 68
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:38876
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I created the below report and it has worked fine all
year, however it will
>not pull data for Dec 2004. Can anyone tell me what may
be causing this?
>Thanks!
>Glen
>SELECT
> Customers.CustomerName,
DNRM_ItemMaster.ItemCategory4,
>SalesDetail.ExtendedPrice,
> ISNULL((SELECT
> SalesDetailWH.ExtendedPrice
> FROM
> SalesDetail SalesDetailWH
> WHERE
> SalesDetailWH.UserDefined5 = 'Warehouse' AND
> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
WHDollars,
> ISNULL((SELECT
> SalesDetailFD.ExtendedPrice
> FROM
> SalesDetail SalesDetailFD
> WHERE
> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
FDDollars,
>
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
AS Price,
>SalesDetail.TradeDiscountAmount,
> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>SalesDetail.DocumentType,
> SalesDetail.CustomerNumber,
DNRM_ItemMaster.ItemClassDescription,
> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>DNRM_Stockholder.StockholderNumber,
DNRM_Stockholder.StockholderName,
> ISNULL((SELECT
> 1
> FROM
> DNRM_ItemMaster DNRM_ItemMasterOB
> WHERE
> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
DNRM_ItemMasterOB.ClassCode='NPOS'
> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>DNRM_ItemMasterOB.ClassCode='POS' OR
> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> DNRM_ItemMasterOB.ItemNumber =DNRM_ItemMaster.ItemNumber),0) AS
>OverBill
>FROM
> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> INNER JOIN DNTIRWH.dbo.Customers Customers ON
> Customers.CustomerNumber =SalesDetail.CustomerNumber
> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
DNRM_Stockholder ON
> Left(DNRM_Stockholder.StockholderNumber,
>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>WHERE
>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
'AT', 'BA', 'BT', 'FM',
>'LG', 'MT', 'OR', 'SP', 'TU') AND
>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>-- Month(SalesDetail.GLPostingDate) <
Month(Getdate()) and
> Year(SalesDetail.GLPostingDate) >=(Year(Getdate())-1) and
> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
AND
> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>ORDER BY
> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemCategory4
>
>|||I created these User-Defined Fuctions to generate the previous month date
range.
For example your syntax would be:
SalesDetail.GLPostingDate >= dbo.fn_ISTCurrentMonth1(GETDATE())
AND
SalesDetail.GLPostingDate <= dbo.fn_ISTCurrentMonth2(GETDATE())
---
CREATE FUNCTION fn_ISTCurrentMonth1 (@.DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @.ISTCurrentMonth1 VarChar(20)
SET @.ISTCurrentMonth1 = convert(varchar,
convert(datetime,(convert(varchar, CASE WHEN MONTH(@.DATE)-1 = 0 THEN 12 ELSE
MONTH(@.DATE)-1 END) + '/' + '1' + '/' + convert(varchar, CASE WHEN
MONTH(@.DATE)-1 = 0 THEN YEAR(@.DATE)-1 ELSE YEAR(@.DATE) END)), 101), 101)
RETURN(@.ISTCurrentMonth1)
END
---
CREATE FUNCTION fn_ISTCurrentMonth2 (@.DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @.ISTCurrentMonth2 VarChar(20)
SET @.ISTCurrentMonth2 = convert(varchar, DATEADD(day, -1,
convert(datetime,(convert(varchar, MONTH(@.DATE)) + '/' + '1' + '/' +
convert(varchar, YEAR(@.DATE))), 101)), 101)
RETURN(@.ISTCurrentMonth2)
END
---
I hope these help.
I also have user-defined functions to calculate Current Year, Last Year,
Last Year Month date ranges. The years are based on a 10/1 fiscal year, but
can be modified to work for any. Let me know if these would be of any
benefit to you.
Thanks.
"William Wang[MSFT]" wrote:
> Hi Glen,
> Are you using a SQL Server database as the Data Source?
> To isolate this issue you may want to run the query via
> Query Analyzer or OSQL against your database to see if
> there is any data returned. If there is any problem at
> this point, the SQL Server database may have something
> wrong.
> If SQL Server returns the data correctly, run the query
> in Report Designer->Data tab to verify the result. You
> may also want to create a new report and create a new
> dataset to test the problem.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> >From: "Glen Tosco" <glen@.delnat.com>
> >Subject: Report Date Help
> >Date: Thu, 6 Jan 2005 14:49:19 -0600
> >Lines: 68
> >X-Priority: 3
> >X-MSMail-Priority: Normal
> >X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> >X-RFC2646: Format=Flowed; Original
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> >Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >NNTP-Posting-Host: mail.delnat.com 65.196.130.98
> >Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
> >Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38876
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >I created the below report and it has worked fine all
> year, however it will
> >not pull data for Dec 2004. Can anyone tell me what may
> be causing this?
> >
> >Thanks!
> >Glen
> >
> >SELECT
> > Customers.CustomerName,
> DNRM_ItemMaster.ItemCategory4,
> >SalesDetail.ExtendedPrice,
> > ISNULL((SELECT
> > SalesDetailWH.ExtendedPrice
> > FROM
> > SalesDetail SalesDetailWH
> > WHERE
> > SalesDetailWH.UserDefined5 = 'Warehouse' AND
> > SalesDetailWH.RowID = SalesDetail.RowID),0) AS
> WHDollars,
> > ISNULL((SELECT
> > SalesDetailFD.ExtendedPrice
> > FROM
> > SalesDetail SalesDetailFD
> > WHERE
> > SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> > SalesDetailFD.RowID = SalesDetail.RowID),0) AS
> FDDollars,
> >
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
> AS Price,
> >SalesDetail.TradeDiscountAmount,
> > SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
> >SalesDetail.DocumentType,
> > SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemClassDescription,
> > SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
> >DNRM_Stockholder.StockholderNumber,
> DNRM_Stockholder.StockholderName,
> > ISNULL((SELECT
> > 1
> > FROM
> > DNRM_ItemMaster DNRM_ItemMasterOB
> > WHERE
> > (DNRM_ItemMasterOB.ClassCode='DPOS' OR
> >DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='NPOS'
> > OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
> >DNRM_ItemMasterOB.ClassCode='POS' OR
> > (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
> >(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
> >
> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
> > 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> > DNRM_ItemMasterOB.ItemNumber => DNRM_ItemMaster.ItemNumber),0) AS
> >OverBill
> >FROM
> > (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
> >DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> > SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> > INNER JOIN DNTIRWH.dbo.Customers Customers ON
> > Customers.CustomerNumber => SalesDetail.CustomerNumber
> > INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
> DNRM_Stockholder ON
> > Left(DNRM_Stockholder.StockholderNumber,
> >Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
> >WHERE
> >-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
> 'AT', 'BA', 'BT', 'FM',
> >'LG', 'MT', 'OR', 'SP', 'TU') AND
> >-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> > DNRM_ItemMaster.ItemType='Sales Inventory' AND
> >-- Month(SalesDetail.GLPostingDate) <
> Month(Getdate()) and
> > Year(SalesDetail.GLPostingDate) >=> (Year(Getdate())-1) and
> > Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
> AND
> > Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
> >ORDER BY
> > SalesDetail.CustomerNumber,
> > DNRM_ItemMaster.ItemCategory4
> >
> >
> >
>|||The problem is right here in your code
Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
The previous month will not always be < current month...when the current
month is January (1), the previous month December will be (12)..
So you need to change your code to take that into account... Try testing
something like
IF you are looking for the previous month
SalesDetail.GLPostingDate between
dateadd(mm,-1,dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate),
getdate()) ) -- first day of prev month..
and
(dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate)) - 1,
getdate()) --lastday of prev month
This is just some on the fly code, with thought and testing you can make
this better I am sure...
--
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
"Glen Tosco" <glen@.delnat.com> wrote in message
news:u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl...
> I created the below report and it has worked fine all year, however it
will
> not pull data for Dec 2004. Can anyone tell me what may be causing this?
> Thanks!
> Glen
> SELECT
> Customers.CustomerName, DNRM_ItemMaster.ItemCategory4,
> SalesDetail.ExtendedPrice,
> ISNULL((SELECT
> SalesDetailWH.ExtendedPrice
> FROM
> SalesDetail SalesDetailWH
> WHERE
> SalesDetailWH.UserDefined5 = 'Warehouse' AND
> SalesDetailWH.RowID = SalesDetail.RowID),0) AS WHDollars,
> ISNULL((SELECT
> SalesDetailFD.ExtendedPrice
> FROM
> SalesDetail SalesDetailFD
> WHERE
> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> SalesDetailFD.RowID = SalesDetail.RowID),0) AS FDDollars,
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount AS Price,
> SalesDetail.TradeDiscountAmount,
> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
> SalesDetail.DocumentType,
> SalesDetail.CustomerNumber, DNRM_ItemMaster.ItemClassDescription,
> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
> DNRM_Stockholder.StockholderNumber, DNRM_Stockholder.StockholderName,
> ISNULL((SELECT
> 1
> FROM
> DNRM_ItemMaster DNRM_ItemMasterOB
> WHERE
> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
> DNRM_ItemMasterOB.ClassCode='MTYKFD' OR DNRM_ItemMasterOB.ClassCode='NPOS'
> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='POS' OR
> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
> (DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM
','PSYKOVATON','PSYKPR3000',
> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> DNRM_ItemMasterOB.ItemNumber = DNRM_ItemMaster.ItemNumber),0) AS
> OverBill
> FROM
> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
> DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> INNER JOIN DNTIRWH.dbo.Customers Customers ON
> Customers.CustomerNumber = SalesDetail.CustomerNumber
> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder DNRM_Stockholder ON
> Left(DNRM_Stockholder.StockholderNumber,
>
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,L
en(SalesDetail.CustomerNumber)-2)
> WHERE
> -- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC', 'AT', 'BA', 'BT', 'FM',
> 'LG', 'MT', 'OR', 'SP', 'TU') AND
> -- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> DNRM_ItemMaster.ItemType='Sales Inventory' AND
> -- Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
> Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00' AND
> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
> ORDER BY
> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemCategory4
>|||If works fine for all months except December...I am sure it is in the coding
but I cannot find a way to get it to work!
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:Bh4U2CG9EHA.764@.cpmsftngxa10.phx.gbl...
> Hi Glen,
> Are you using a SQL Server database as the Data Source?
> To isolate this issue you may want to run the query via
> Query Analyzer or OSQL against your database to see if
> there is any data returned. If there is any problem at
> this point, the SQL Server database may have something
> wrong.
> If SQL Server returns the data correctly, run the query
> in Report Designer->Data tab to verify the result. You
> may also want to create a new report and create a new
> dataset to test the problem.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
>>From: "Glen Tosco" <glen@.delnat.com>
>>Subject: Report Date Help
>>Date: Thu, 6 Jan 2005 14:49:19 -0600
>>Lines: 68
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>>Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38876
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>I created the below report and it has worked fine all
> year, however it will
>>not pull data for Dec 2004. Can anyone tell me what may
> be causing this?
>>Thanks!
>>Glen
>>SELECT
>> Customers.CustomerName,
> DNRM_ItemMaster.ItemCategory4,
>>SalesDetail.ExtendedPrice,
>> ISNULL((SELECT
>> SalesDetailWH.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailWH
>> WHERE
>> SalesDetailWH.UserDefined5 = 'Warehouse' AND
>> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
> WHDollars,
>> ISNULL((SELECT
>> SalesDetailFD.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailFD
>> WHERE
>> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
>> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
> FDDollars,
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
> AS Price,
>>SalesDetail.TradeDiscountAmount,
>> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>>SalesDetail.DocumentType,
>> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemClassDescription,
>> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>>DNRM_Stockholder.StockholderNumber,
> DNRM_Stockholder.StockholderName,
>> ISNULL((SELECT
>> 1
>> FROM
>> DNRM_ItemMaster DNRM_ItemMasterOB
>> WHERE
>> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='NPOS'
>> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>>DNRM_ItemMasterOB.ClassCode='POS' OR
>> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
>> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
>> DNRM_ItemMasterOB.ItemNumber => DNRM_ItemMaster.ItemNumber),0) AS
>>OverBill
>>FROM
>> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
>> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
>> INNER JOIN DNTIRWH.dbo.Customers Customers ON
>> Customers.CustomerNumber => SalesDetail.CustomerNumber
>> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
> DNRM_Stockholder ON
>> Left(DNRM_Stockholder.StockholderNumber,
>>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>>WHERE
>>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
> 'AT', 'BA', 'BT', 'FM',
>>'LG', 'MT', 'OR', 'SP', 'TU') AND
>>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
>> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>>-- Month(SalesDetail.GLPostingDate) <
> Month(Getdate()) and
>> Year(SalesDetail.GLPostingDate) >=> (Year(Getdate())-1) and
>> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
> AND
>> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>>ORDER BY
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemCategory4
>>
>|||Kind of a mini-Y2K every year, eh? Unless you switch around your SQL to use
date arithmetic, you're going to have to add some fancy stuff to solve the
wraparound logic. You're treating months and years like separate integers.
Put them together in a single date and let SQL do the work for you: you've
already been given the answer: DateAdd
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Glen Tosco" <glen@.delnat.com> wrote in message
news:OJJsmxT9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> If works fine for all months except December...I am sure it is in the
> coding but I cannot find a way to get it to work!
> "William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
> news:Bh4U2CG9EHA.764@.cpmsftngxa10.phx.gbl...
>> Hi Glen,
>> Are you using a SQL Server database as the Data Source?
>> To isolate this issue you may want to run the query via
>> Query Analyzer or OSQL against your database to see if
>> there is any data returned. If there is any problem at
>> this point, the SQL Server database may have something
>> wrong.
>> If SQL Server returns the data correctly, run the query
>> in Report Designer->Data tab to verify the result. You
>> may also want to create a new report and create a new
>> dataset to test the problem.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> Get Secure! - <www.microsoft.com/security>
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and
>> confers no rights.
>> --
>>From: "Glen Tosco" <glen@.delnat.com>
>>Subject: Report Date Help
>>Date: Thu, 6 Jan 2005 14:49:19 -0600
>>Lines: 68
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>>Path:
>> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
>> hx.gbl!TK2MSFTNGP12.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:38876
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>I created the below report and it has worked fine all
>> year, however it will
>>not pull data for Dec 2004. Can anyone tell me what may
>> be causing this?
>>Thanks!
>>Glen
>>SELECT
>> Customers.CustomerName,
>> DNRM_ItemMaster.ItemCategory4,
>>SalesDetail.ExtendedPrice,
>> ISNULL((SELECT
>> SalesDetailWH.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailWH
>> WHERE
>> SalesDetailWH.UserDefined5 = 'Warehouse' AND
>> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
>> WHDollars,
>> ISNULL((SELECT
>> SalesDetailFD.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailFD
>> WHERE
>> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
>> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
>> FDDollars,
>> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
>> AS Price,
>>SalesDetail.TradeDiscountAmount,
>> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>>SalesDetail.DocumentType,
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemClassDescription,
>> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>>DNRM_Stockholder.StockholderNumber,
>> DNRM_Stockholder.StockholderName,
>> ISNULL((SELECT
>> 1
>> FROM
>> DNRM_ItemMaster DNRM_ItemMasterOB
>> WHERE
>> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
>> DNRM_ItemMasterOB.ClassCode='NPOS'
>> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>>DNRM_ItemMasterOB.ClassCode='POS' OR
>> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
>> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
>> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
>> DNRM_ItemMasterOB.ItemNumber =>> DNRM_ItemMaster.ItemNumber),0) AS
>>OverBill
>>FROM
>> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
>> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
>> INNER JOIN DNTIRWH.dbo.Customers Customers ON
>> Customers.CustomerNumber =>> SalesDetail.CustomerNumber
>> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
>> DNRM_Stockholder ON
>> Left(DNRM_Stockholder.StockholderNumber,
>>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
>> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>>WHERE
>>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
>> 'AT', 'BA', 'BT', 'FM',
>>'LG', 'MT', 'OR', 'SP', 'TU') AND
>>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
>> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>>-- Month(SalesDetail.GLPostingDate) <
>> Month(Getdate()) and
>> Year(SalesDetail.GLPostingDate) >=>> (Year(Getdate())-1) and
>> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
>> AND
>> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>>ORDER BY
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemCategory4
>>
>>
>

Report Date Error

Hi,
Back in 2004, 'Lisa' posted this question. I'm having exactly the same
issue. No resolution was proposed at that time. If anyone can help me out,
I'd appreciate it greatly!
-- begin quoted message --
Hi,
I have a report set up that is used by multiple users who have subscriptions
set up on it. One user gets the following error when she tries to run it and
the subscription she set up fails:
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot read the next data row for the data set CustomerDB. -->
System.Data.SqlClient.SqlException: The conversion of char data type to
smalldatetime data type resulted in an out-of-range smalldatetime value.
She is the only user that gets this error and she is set up the same as the
other ones on the reporting server. There is nothing in the report that is
user specific and no formulas that involve a date so I'm really not sure
where this message is coming from. When she logs onto the report from a
different computer it works fine, but if anyone tries logging in and running
the report from her computer it doesn't work. I checked her browser settings
and they are the same as everyone elses.
I'm out of ideas, so any help would be greatly appreciated!
--
Thanks!
ChrisCheck to see what her regional & language options are.
You will find these defined in the control panel.
Be sure they match what they are supposed to be - in other words - check the
machine where it is running fine vs. the values on this machine for the
regional & langage options.
If this is english and you are in the U.S. then click the button "Customize"
(On the regional options tab in the dialog for Regional and Language Options
box) and verify that those settings also match on the machines that are
running fine.
=-Chris
"ChrisE" <ChrisE@.discussions.microsoft.com> wrote in message
news:0FACE68F-B113-40D9-9C43-6D950FAB4334@.microsoft.com...
> Hi,
> Back in 2004, 'Lisa' posted this question. I'm having exactly the same
> issue. No resolution was proposed at that time. If anyone can help me
> out,
> I'd appreciate it greatly!
> -- begin quoted message --
> Hi,
> I have a report set up that is used by multiple users who have
> subscriptions
> set up on it. One user gets the following error when she tries to run it
> and
> the subscription she set up fails:
> Info:
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> Cannot read the next data row for the data set CustomerDB. -->
> System.Data.SqlClient.SqlException: The conversion of char data type to
> smalldatetime data type resulted in an out-of-range smalldatetime value.
> She is the only user that gets this error and she is set up the same as
> the
> other ones on the reporting server. There is nothing in the report that is
> user specific and no formulas that involve a date so I'm really not sure
> where this message is coming from. When she logs onto the report from a
> different computer it works fine, but if anyone tries logging in and
> running
> the report from her computer it doesn't work. I checked her browser
> settings
> and they are the same as everyone elses.
> I'm out of ideas, so any help would be greatly appreciated!
> --
>
> Thanks!
> Chris|||Hi,
Thanks - this solved the problem. Her computer was configured to English -
Canadian instead of English - US. Changing it to English - US fixed the
problem.
Thanks again!
"Christopher Conner" wrote:
> Check to see what her regional & language options are.
> You will find these defined in the control panel.
> Be sure they match what they are supposed to be - in other words - check the
> machine where it is running fine vs. the values on this machine for the
> regional & langage options.
> If this is english and you are in the U.S. then click the button "Customize"
> (On the regional options tab in the dialog for Regional and Language Options
> box) and verify that those settings also match on the machines that are
> running fine.
> =-Chris
>
> "ChrisE" <ChrisE@.discussions.microsoft.com> wrote in message
> news:0FACE68F-B113-40D9-9C43-6D950FAB4334@.microsoft.com...
> > Hi,
> >
> > Back in 2004, 'Lisa' posted this question. I'm having exactly the same
> > issue. No resolution was proposed at that time. If anyone can help me
> > out,
> > I'd appreciate it greatly!
> >
> > -- begin quoted message --
> >
> > Hi,
> > I have a report set up that is used by multiple users who have
> > subscriptions
> > set up on it. One user gets the following error when she tries to run it
> > and
> > the subscription she set up fails:
> >
> > Info:
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > Cannot read the next data row for the data set CustomerDB. -->
> > System.Data.SqlClient.SqlException: The conversion of char data type to
> > smalldatetime data type resulted in an out-of-range smalldatetime value.
> >
> > She is the only user that gets this error and she is set up the same as
> > the
> > other ones on the reporting server. There is nothing in the report that is
> > user specific and no formulas that involve a date so I'm really not sure
> > where this message is coming from. When she logs onto the report from a
> > different computer it works fine, but if anyone tries logging in and
> > running
> > the report from her computer it doesn't work. I checked her browser
> > settings
> > and they are the same as everyone elses.
> >
> > I'm out of ideas, so any help would be greatly appreciated!
> >
> > --
> >
> >
> > Thanks!
> >
> > Chris
>
>sql

Report caching

Hi all,
I have a report that takes about 10 minutes to run for a given date. How can
I cache it some how so that it would run lot faster for the same data input
(date) later on?
Thanks in advance.You can have the report run from the cache... ON the reporting Server,
select the report-Properties... You can set the report to run from cache and
choose the cache refresh schedule... The first person who runs the report
after the cache has expired, must wait... Others during the period get fast
response.
If you do not want to wait the first time, you may run a snapshot for the
report on a schedule and have the report rendered from the snapshot...
However The only reports which will be rendered must be snapshotted with the
appropriate report parameters...
--
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
"Trisha" <trisha@.nospam.nospam> wrote in message
news:CB6AB88B-40D6-4E26-A40C-9F086B171142@.microsoft.com...
> Hi all,
> I have a report that takes about 10 minutes to run for a given date. How
> can
> I cache it some how so that it would run lot faster for the same data
> input
> (date) later on?
> Thanks in advance.|||Wayne,
What if I want to cache the report for several different input parameters.
What if I have several different reports w/several different parameters.
Would all those reports be cached on the server for each of the executed
input parameters? If thats the case, then it would put lots of load on the
server and I might have to look into a different strategy.
Thanks again..
"Wayne Snyder" wrote:
> You can have the report run from the cache... ON the reporting Server,
> select the report-Properties... You can set the report to run from cache and
> choose the cache refresh schedule... The first person who runs the report
> after the cache has expired, must wait... Others during the period get fast
> response.
> If you do not want to wait the first time, you may run a snapshot for the
> report on a schedule and have the report rendered from the snapshot...
> However The only reports which will be rendered must be snapshotted with the
> appropriate report parameters...
> --
> 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
> "Trisha" <trisha@.nospam.nospam> wrote in message
> news:CB6AB88B-40D6-4E26-A40C-9F086B171142@.microsoft.com...
> > Hi all,
> >
> > I have a report that takes about 10 minutes to run for a given date. How
> > can
> > I cache it some how so that it would run lot faster for the same data
> > input
> > (date) later on?
> >
> > Thanks in advance.
>
>

report by multiple parameter with OR relationship

I am trying to create a report with three parameters (date, license number and application number). I want report by License Number or Application number in the date that I put in. I trying following in Record Selection:
1.
{Command.LICENSE_NO} = {?License Number} or
{Command.LICENSE_ID} = {?Application Number}

2.
if isnull({?License Number}) = false then
{Command.LICENSE_NO} = {?License Number};
if isnull({?Application Number})=false then
{Command.LICENSE_ID}={?Application Number};

** license_id is the application number(number). license number(string)

but both of them do not work. somehow I can not leave Application number empty.

Please Help!!!As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.|||As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.

I have tried using 0 as default, but for some reason. I still can not leave application number empty. I used exactly same as your code

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

but search will not start if I leave application parameter empty.

any thought?

Thanks for your help|||As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.

Got it working with your code!!!

Thanks your so much!!

Report Builder: Date filter, Relative Date

I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error:

The Value expression for the report parameter ‘FromDate’ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression)

Can anyone fix this problem?

But it works for Today or (n)months ago.

Thanks.

Post your expressions and indicate what the data types are for the referenced field(s).|||

OK, I narrow down what cause this, my date Fields are standard date type. I have 2 dates From Date and To Date, and I put those in filter with default values:

From Date : first day this month, To Date: last day this month

I also have a Textbox with value: =parameters!FromDate.Value

Because I need to display what is being filter on the output, but that is what cause the error, if I use Today or (n) months ago as default, it works.

|||

So are you saying this gives the error?

=parameters!FromDate.Value

I doubt that is causing the problem unless you maybe have a number format string for the textbox?

|||

If I remove the Textbox with value: =parameters!FromDate.Value, then it works fine with the From Date : first day this month, To Date: last day this month. But if I add the Textbox back with those value it gives me the above error?

|||And then if you put a new text box on the report and set the Expression to =Parameters!FromDate.Value and leave all other settings for the text box alone, what happens?

Report Builder Date Range Filter

When selecting From To date range filter you can no longer choose
prompt for input. Why is this? Is there a fix for it.
I can replicate the required functionality by using On or Before and On
or After but it seems a bit messy.
Regards
TobiThis is a known issue and is under consideration to be fixed in a future
release.
"Tobi" wrote:
> When selecting From To date range filter you can no longer choose
> prompt for input. Why is this? Is there a fix for it.
> I can replicate the required functionality by using On or Before and On
> or After but it seems a bit messy.
> Regards
> Tobi
>

Report Builder and Custom Value in TextBox

How can I add a custom value in a textbox. For example, I would like to add
a footer with a page number, date and the name of the user who's running the
report.Right click on the textbox and choose Expression. Everything you need is
under Globals. Well basic VB script knowledge is needed to put all together
though.
"SYoung" wrote:
> How can I add a custom value in a textbox. For example, I would like to add
> a footer with a page number, date and the name of the user who's running the
> report.|||When I right click on the textbox all I get is Format... option.
"Bing Bing Yu" wrote:
> Right click on the textbox and choose Expression. Everything you need is
> under Globals. Well basic VB script knowledge is needed to put all together
> though.
>
> "SYoung" wrote:
> > How can I add a custom value in a textbox. For example, I would like to add
> > a footer with a page number, date and the name of the user who's running the
> > report.|||Are you actually building your report in SQL Server BI Dev Studio?
"SYoung" wrote:
> When I right click on the textbox all I get is Format... option.
> "Bing Bing Yu" wrote:
> > Right click on the textbox and choose Expression. Everything you need is
> > under Globals. Well basic VB script knowledge is needed to put all together
> > though.
> >
> >
> > "SYoung" wrote:
> >
> > > How can I add a custom value in a textbox. For example, I would like to add
> > > a footer with a page number, date and the name of the user who's running the
> > > report.|||I'm actually using Report Builder. If you go to
"http://YourReportServer/Reports/", you'll be able to see "New Folder", "New
Data Source", "Upload File", And "Report Builder" below the two tabs
"Contents" & "Properties".
You may want to go to the links below to get more info about Report Builder.
http://www.microsoft.com/sql/technologies/reporting/default.mspx
http://www.developer.com/db/article.php/3520116
"Bing Bing Yu" wrote:
> Are you actually building your report in SQL Server BI Dev Studio?
> "SYoung" wrote:
> > When I right click on the textbox all I get is Format... option.
> >
> > "Bing Bing Yu" wrote:
> >
> > > Right click on the textbox and choose Expression. Everything you need is
> > > under Globals. Well basic VB script knowledge is needed to put all together
> > > though.
> > >
> > >
> > > "SYoung" wrote:
> > >
> > > > How can I add a custom value in a textbox. For example, I would like to add
> > > > a footer with a page number, date and the name of the user who's running the
> > > > report.|||You can actually enter RDL expressions in a Report Builder textbox -- just
start the text with "=". However, you are basically working blind, because
there is no direct visibility into report field names, textbox names, etc.
Also, page headers/footers are not supported in this release. Best bet is to
put the page count, report date, username, etc. at the top and bottom of the
report, so it will be on the first and last pages.
"SYoung" wrote:
> How can I add a custom value in a textbox. For example, I would like to add
> a footer with a page number, date and the name of the user who's running the
> report.

Report Builder - Add a report date

any way using report builder ad hoc tool for users to add an "as of" date to
a report using the TODAY function?
Rich BelangerI figured it out, you just need to type an expression into a text box in
design view.
"Rich Belanger" <rich.belanger@.verizon.net> wrote in message
news:udq0ZW9vHHA.3508@.TK2MSFTNGP03.phx.gbl...
> any way using report builder ad hoc tool for users to add an "as of" date
> to a report using the TODAY function?
> Rich Belanger
>

report based on day

I have a report that runs daily.
I need to set the criteria for a date field to be today.
if i use the now() command then i get the value to the minute, whereas i wan
to cover the entire day.
today() doen't work in reporting services with visul studio.
what other date command can i use?This will give you the date with no time portion.
Now.ToShortDateString
Note that for a date/time field this really turns out to be today at 12 AM.
This would convert it back to a date instead of text.
CDate(Now.ToShortDateString)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anon" <anon@.anon.com> wrote in message
news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>I have a report that runs daily.
> I need to set the criteria for a date field to be today.
> if i use the now() command then i get the value to the minute, whereas i
> wan to cover the entire day.
> today() doen't work in reporting services with visul studio.
> what other date command can i use?
>
>|||How would i enter this into the criteria part of the query?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23bxuGQD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
> This will give you the date with no time portion.
> Now.ToShortDateString
> Note that for a date/time field this really turns out to be today at 12
> AM.
> This would convert it back to a date instead of text.
> CDate(Now.ToShortDateString)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "anon" <anon@.anon.com> wrote in message
> news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>>I have a report that runs daily.
>> I need to set the criteria for a date field to be today.
>> if i use the now() command then i get the value to the minute, whereas i
>> wan to cover the entire day.
>> today() doen't work in reporting services with visul studio.
>> what other date command can i use?
>>
>|||If this is the default for the report parameter then just use this for the
default.
If you haven't yet done any queries that are parameter based then backup and
do some simple reports that use query parameters like this:
select * from sometable where somevalue = @.MyParam
Note that if this is hard coded, the report should only do the current day
then use SQL syntax. You don't even need a query parameter. I am at home and
don't have my SQL reference but I am sure there is some normal SQL that can
be used (like DatePart, DateAdd etc).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anon" <anon@.anon.com> wrote in message
news:%23Y0u5KP8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> How would i enter this into the criteria part of the query?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23bxuGQD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
>> This will give you the date with no time portion.
>> Now.ToShortDateString
>> Note that for a date/time field this really turns out to be today at 12
>> AM.
>> This would convert it back to a date instead of text.
>> CDate(Now.ToShortDateString)
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "anon" <anon@.anon.com> wrote in message
>> news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>>I have a report that runs daily.
>> I need to set the criteria for a date field to be today.
>> if i use the now() command then i get the value to the minute, whereas i
>> wan to cover the entire day.
>> today() doen't work in reporting services with visul studio.
>> what other date command can i use?
>>
>>
>

Report based on a Date

Hi,
I have to create a report for all the business days in a current month. (no matter the records are available or not in the database for the day).
If you have any idea, pls tell me
ThanksCan you be a little more specific? Do you need a separate report for each day, or do you need one report that will show all the days in the month regardless if there's a record?|||Thanks Malleyo!

The second condition in your post is my report.

My report should display all the business days in the current month and the corresponding sum of amounts from a table which contains a date field. The sum is grouped by the date for each day.

The problem here is the table doesn't contain records for all the days upto end of the month. But I have to display all the days. Now I'm using a dummy table which contains all the business days and linking this with my main table using left outer join so that I'm able to display all the dates.

But I think this is not the right way to do this?
Ex report is

Date Amount
6/1 100
6/2 50
6/3 90
6/4 150
6/7
6/8
...
...
...
6/30

Ex. The problem is table contains data upto 6/4. So while generating the report it shows the report upto 6/4. But I need upto 6/30 even though the amount field is empty.

Thanks|||I'm using a dummy table which contains all the business days and linking this with my main table using left outer join so that I'm able to display all the dates.
Joining the 2 tables sounds good to me.

Try using a Group for Date. Make sure that you don't have 'SuppressIfBlank' turned on. Make sure that the query you're running is really returning all the info you need.|||Thanks Malleyo,

One more things, I'm using MySQL as my database. While linking the tables using left outer join, it is not working properly. It returns the result of inner join.

Actually my first table contains all the business days and the second table contains records for some of the days. I linked the table on date field using left outer join. It has supposed to return all the days in the first table regardless of second table. But it 's not. It's returning only the matching records.

Any idea please,|||You may be doing the wrong kind of Join. Unfortunately I don't know a whole lot about Joins, and the knowledge that I do have is for SQL Server 7 (which I'm not sure if uses different syntax than MySQL.

Here's what my Joins typically look like:

SELECT * FROM Table1 AS T1
LEFT JOIN Table2 AS T2 ON T2.Column1 = T1.Column1

That will return all the fields in Table1 with any that match in Table2 (or NULL if there isn't a match in Table2).|||I'm also new to mysql.

I'm using the same conventions as given in mysql documentation.

My query is

select table1.date,table2.date,table2.amount from table1 left join table2 on table1.date = table2.date where table2.code = '111'

This is not working

But

select table1.date,table2.date,table2.amount from table1 left join table2 on table1.date = table2.date and table2.code = '111'

This works but it takes so much time to read the records. sometime just hung up. My table1 contains just 22 records.

While adding as tables, it's not working.|||Do you have indexes on table1.date, table2.date, and table2.code? If not, add the indexes and it should speed it up. If you already have indexes, then I'm out of ideas. You can try posting that question on another forum or search www.google.com or something.