Showing posts with label below. Show all posts
Showing posts with label below. Show all posts

Wednesday, March 28, 2012

Report Design Question two horizontal column headers one below other

I have a report requirement, i am new to reports.

I want to have two lines of column headers.( with 7 columns in parent columns header)

and 7 columns in child column header.

The first column header will show a parent record.

And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.

its a one to many: 1 parent record and below all child records for that parent record.

can i use subreports controls to show all child records of the parent.

Thank you very much for the information.

I would recommend that you a table report

Assume that you have a structure

Country A

Region A1

Region A2

Country B

Region B1

Region B2

Region B3

In this scenario create a table that groups records by country. In the group header/footer (depending on your need) add a new table that contains regions associated with the country.

HTH.

Report design issue

Hi,
i need to design a report similiar as the one below...
Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
Regular Hours
Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
Engineer General 88.00 8.00 20.00 20.00 20.00 Project
Manager Validation 110.00 20.00 15.00 15.00
Project Manager General Head Office 24.00 8.00
Totals: 318.00 36.00 39.00 51.00 43.00 51.00
Overtime Hours
Engineer Validation San Francisco 48.00 8.00 8.00 8.00
Project Manager General San Francisco 32.00 8.00 8.00 8.00
Totals: 80.00 16.00 16.00 16.00 16.00 8.00
Expenses
Engineer Airfare $600.00 $300.00
Engineer Lodging $560.00 $80.00 $80.00 $80.00
Engineer Misc $25.00 $20.00 $5.00
Project Manager Airfare $1,800.00 $300.00 $400.00 Project
Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
$90.00
Equipment Equipment $999.00 $999.00
Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
$300.00 $170.00 $170.00
The 3 sections regular hours,overtimehours and expense are in 3
different tables in the database...
Please let me know the design for this kind of report..
ThanksLet me explain the report in detail...
----
HEADER1 HEADER2 tOTAL 1/2 1/3 1/4 1/
----
SECTION1
VALUE1 VALUE1 4.00 2.00 2.00
VALUE2 vALUE2 5.00 2.00
3.00
TOTAL 9.00 4.00
2.00 3.0
----
SECTION2
VALUE1 VALUE1 4.00 2.00 2.00
VALUE2 vALUE2 5.00 2.00
3.00
TOTAL 9.00 4.00
2.00 3.0
----
SECTION3
VALUE1 VALUE1 4.00 2.00 2.00
VALUE2 vALUE2 5.00 2.00
3.00
TOTAL 9.00 4.00
2.00 3.0
----
AS I MENTIONED THE 3 SECTION NEEDS TO BE REPETIVE (THESE 3 SECTIONS WILL BE
REPEATED FOR EACH DIFFERENT PROJECT) AND THEY ARE COMING FROM THE 3
DIFFERENT STORED PROCUDEURES....
IF A PARTICULAR SECTION HAS NO VALUES THEN THAT SECTION NEED NOT BE
SHOWN......
"CCP" wrote:
> Hi,
> i need to design a report similiar as the one below...
>
> Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
> Regular Hours
> Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
> Engineer General 88.00 8.00 20.00 20.00 20.00 Project
> Manager Validation 110.00 20.00 15.00 15.00
> Project Manager General Head Office 24.00 8.00
> Totals: 318.00 36.00 39.00 51.00 43.00 51.00
> Overtime Hours
> Engineer Validation San Francisco 48.00 8.00 8.00 8.00
> Project Manager General San Francisco 32.00 8.00 8.00 8.00
> Totals: 80.00 16.00 16.00 16.00 16.00 8.00
> Expenses
> Engineer Airfare $600.00 $300.00
> Engineer Lodging $560.00 $80.00 $80.00 $80.00
> Engineer Misc $25.00 $20.00 $5.00
> Project Manager Airfare $1,800.00 $300.00 $400.00 Project
> Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
> $90.00
> Equipment Equipment $999.00 $999.00
> Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
> $300.00 $170.00 $170.00
> The 3 sections regular hours,overtimehours and expense are in 3
> different tables in the database...
> Please let me know the design for this kind of report..
> Thanks
>|||Don't want much, do you? I'm sure there are consultants out there somewhere
that would be happy to design your report for you....for a fee.
"CCP" wrote:
> Let me explain the report in detail...
> ----
> HEADER1 HEADER2 tOTAL 1/2 1/3 1/4 1/5
> ----
> SECTION1
> VALUE1 VALUE1 4.00 2.00 2.00
> VALUE2 vALUE2 5.00 2.00
> 3.00
> TOTAL 9.00 4.00
> 2.00 3.00
> ----
> SECTION2
> VALUE1 VALUE1 4.00 2.00 2.00
> VALUE2 vALUE2 5.00 2.00
> 3.00
> TOTAL 9.00 4.00
> 2.00 3.00
> ----
> SECTION3
> VALUE1 VALUE1 4.00 2.00 2.00
> VALUE2 vALUE2 5.00 2.00
> 3.00
> TOTAL 9.00 4.00
> 2.00 3.00
> ----
> AS I MENTIONED THE 3 SECTION NEEDS TO BE REPETIVE (THESE 3 SECTIONS WILL BE
> REPEATED FOR EACH DIFFERENT PROJECT) AND THEY ARE COMING FROM THE 3
> DIFFERENT STORED PROCUDEURES....
> IF A PARTICULAR SECTION HAS NO VALUES THEN THAT SECTION NEED NOT BE
> SHOWN......
> "CCP" wrote:
> > Hi,
> > i need to design a report similiar as the one below...
> >
> >
> >
> > Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
> > Regular Hours
> > Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
> > Engineer General 88.00 8.00 20.00 20.00 20.00 Project
> > Manager Validation 110.00 20.00 15.00 15.00
> > Project Manager General Head Office 24.00 8.00
> > Totals: 318.00 36.00 39.00 51.00 43.00 51.00
> > Overtime Hours
> > Engineer Validation San Francisco 48.00 8.00 8.00 8.00
> > Project Manager General San Francisco 32.00 8.00 8.00 8.00
> > Totals: 80.00 16.00 16.00 16.00 16.00 8.00
> > Expenses
> > Engineer Airfare $600.00 $300.00
> > Engineer Lodging $560.00 $80.00 $80.00 $80.00
> > Engineer Misc $25.00 $20.00 $5.00
> > Project Manager Airfare $1,800.00 $300.00 $400.00 Project
> > Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
> > $90.00
> > Equipment Equipment $999.00 $999.00
> > Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
> > $300.00 $170.00 $170.00
> >
> > The 3 sections regular hours,overtimehours and expense are in 3
> > different tables in the database...
> > Please let me know the design for this kind of report..
> >
> > Thanks
> >|||No......
Im very new to reporting services and to programming too....
thought i would get some valuable here which could help me learn sql
reporting services......
"Ronathospice" wrote:
> Don't want much, do you? I'm sure there are consultants out there somewhere
> that would be happy to design your report for you....for a fee.
>
> "CCP" wrote:
> >
> > Let me explain the report in detail...
> > ----
> > HEADER1 HEADER2 tOTAL 1/2 1/3 1/4 1/5
> > ----
> > SECTION1
> > VALUE1 VALUE1 4.00 2.00 2.00
> > VALUE2 vALUE2 5.00 2.00
> > 3.00
> > TOTAL 9.00 4.00
> > 2.00 3.00
> > ----
> > SECTION2
> > VALUE1 VALUE1 4.00 2.00 2.00
> > VALUE2 vALUE2 5.00 2.00
> > 3.00
> > TOTAL 9.00 4.00
> > 2.00 3.00
> > ----
> > SECTION3
> > VALUE1 VALUE1 4.00 2.00 2.00
> > VALUE2 vALUE2 5.00 2.00
> > 3.00
> > TOTAL 9.00 4.00
> > 2.00 3.00
> > ----
> >
> > AS I MENTIONED THE 3 SECTION NEEDS TO BE REPETIVE (THESE 3 SECTIONS WILL BE
> > REPEATED FOR EACH DIFFERENT PROJECT) AND THEY ARE COMING FROM THE 3
> > DIFFERENT STORED PROCUDEURES....
> > IF A PARTICULAR SECTION HAS NO VALUES THEN THAT SECTION NEED NOT BE
> > SHOWN......
> >
> > "CCP" wrote:
> >
> > > Hi,
> > > i need to design a report similiar as the one below...
> > >
> > >
> > >
> > > Category Time / Expense Type Totals 1/5 1/6 1/7 1/8 1/9
> > > Regular Hours
> > > Engineer PLC Programming 96.00 8.00 16.00 16.00 8.00
> > > Engineer General 88.00 8.00 20.00 20.00 20.00 Project
> > > Manager Validation 110.00 20.00 15.00 15.00
> > > Project Manager General Head Office 24.00 8.00
> > > Totals: 318.00 36.00 39.00 51.00 43.00 51.00
> > > Overtime Hours
> > > Engineer Validation San Francisco 48.00 8.00 8.00 8.00
> > > Project Manager General San Francisco 32.00 8.00 8.00 8.00
> > > Totals: 80.00 16.00 16.00 16.00 16.00 8.00
> > > Expenses
> > > Engineer Airfare $600.00 $300.00
> > > Engineer Lodging $560.00 $80.00 $80.00 $80.00
> > > Engineer Misc $25.00 $20.00 $5.00
> > > Project Manager Airfare $1,800.00 $300.00 $400.00 Project
> > > Manager Lodging $630.00 $90.00 $90.00 $90.00 $90.00 $90.00 $90.00
> > > $90.00
> > > Equipment Equipment $999.00 $999.00
> > > Totals: $4,614.00 $1,789.00 $575.00 $570.00 $570.00 $470.00 $0.00
> > > $300.00 $170.00 $170.00
> > >
> > > The 3 sections regular hours,overtimehours and expense are in 3
> > > different tables in the database...
> > > Please let me know the design for this kind of report..
> > >
> > > Thanks
> > >|||This is a simple LEFT join assuming there is a key that ties all three tables
together.
SELECT section, a.header1, a.header2
, SUM(ISNULL(a.hours ,0) + ISNULL(b.hours ,0) + ISNULL(c.hours ,0))
,ISNULL(a.hours ,0)
,ISNULL(b.hours ,0)
ISNULL(c.hours ,0)
FROM table1 a
LEFT JOIN table 2 on a.id = b.id
LEFT JOIN table 3 on a.id = c.id
GROUP BY section, a.header1, a.header2
The grouping would also need to be declared in the report output
with a sum function going in the group footer.
Good luck|||Thanks William,
ur method was very helpful for me.....
One problem though is the date field is dynamic, so can i use a matrix
instead of a table and get that design
Thanks for ur time and understanding....
"William" wrote:
> This is a simple LEFT join assuming there is a key that ties all three tables
> together.
> SELECT section, a.header1, a.header2
> , SUM(ISNULL(a.hours ,0) + ISNULL(b.hours ,0) + ISNULL(c.hours ,0))
> ,ISNULL(a.hours ,0)
> ,ISNULL(b.hours ,0)
> ISNULL(c.hours ,0)
> FROM table1 a
> LEFT JOIN table 2 on a.id = b.id
> LEFT JOIN table 3 on a.id = c.id
> GROUP BY section, a.header1, a.header2
> The grouping would also need to be declared in the report output
> with a sum function going in the group footer.
> Good luck

Monday, March 26, 2012

Report Deployment Error on --http://localhost/reportserver

I am getting the following ERROR!! below when I try to DEPLOY my report. The output generated from - Debug --> Start
-- Deploy started: Project: Report Project1, Configuration: DebugLocal --
Deploying to http://localhost/reportserver/?%2fReport+Project1
Error rsReportServerDatabaseUnavailable : The report server cannot opena connection to the report server database. A connection to thedatabase is required for all requests and processing.
Deploy complete -- 1 errors, 0 warnings
=====================================================

When I try to go tohttp://<server>/reportserver a PROMT window pop up "Enter username and password for "" at http://localhost. Atthis point I'm pretty desperate to get it to work., I have been tryingfor couple of days. I reinstalled reporting services couple of timeswith no success. Any help would be greatly appreciated. Thanks!

-lalon

Hi it sounds to me the AspNet user or the user used in your dataset don't have access to your SRS database( "ReportServer" DB) . Check the permissions of the SQL Reporting Services database. Also you can't deploy to a server not running the SRS Server, unless you are on a SQL reporting server as localhost . I assume you clicked 'play' in VS.Net ? You need to deploy to a SRS Server.

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
>>
>>
>

Friday, March 23, 2012

Report Builder: Custom Aggregate

Hi All,
I have used Report Builder to enable user to do Ad-Hoc Report that has
been great so far, but I have unique requirement for below table/view:
Month Headcount
================== January-06 45
February-06 41
March-06 43
April-06 42
May-06 38
June-06 30
July-06 50
(etc...)
Dec-06 42
Above table represents how many employees, one company/department has
during Month column. In January-06, there is 45 employees,
February-06, there is 44 employees, and so on.
If user choose to show Headcount attribute, Report Builder should
display each month headcount. The current problem is on the aggregate.
for example, report should show Headcount for Quarter 1 as 43, which
is headcount for March-06 (how many employees I have at the end of
Quarter 1), Quarter 2 should display 30, which is headcount for
June-06, and for Year of 2006 should be 42 (at Dec-06). There will be
more complexity: if now is still in the month of November 06 and no
data for December yet, report should show headcount of November 06 as
headcount for Year of 2006.
I don't think default available aggregates which are: Total, Average,
Max, Min, able to do this.
Q1, correct value should be 40 (March-06)
If I use Total Aggregate, it will be 99.
If I use Average, it will be 24.75
If I use Max, it will be 45.
If I use Min, it will be 41.
I need an aggregate that show number from last record in the dataset.
Is there any possible way how to do this? Any input is appreciated.
Thank you.On Jun 19, 4:59 pm, Gunady <Gun...@.gmail.com> wrote:
> Hi All,
> I have used Report Builder to enable user to do Ad-Hoc Report that has
> been great so far, but I have unique requirement for below table/view:
> Month Headcount
> ==================> January-06 45
> February-06 41
> March-06 43
> April-06 42
> May-06 38
> June-06 30
> July-06 50
> (etc...)
> Dec-06 42
> Above table represents how many employees, one company/department has
> during Month column. In January-06, there is 45 employees,
> February-06, there is 44 employees, and so on.
> If user choose to show Headcount attribute, Report Builder should
> display each month headcount. The current problem is on the aggregate.
> for example, report should show Headcount for Quarter 1 as 43, which
> is headcount for March-06 (how many employees I have at the end of
> Quarter 1), Quarter 2 should display 30, which is headcount for
> June-06, and for Year of 2006 should be 42 (at Dec-06). There will be
> more complexity: if now is still in the month of November 06 and no
> data for December yet, report should show headcount of November 06 as
> headcount for Year of 2006.
> I don't think default available aggregates which are: Total, Average,
> Max, Min, able to do this.
> Q1, correct value should be 40 (March-06)
> If I use Total Aggregate, it will be 99.
> If I use Average, it will be 24.75
> If I use Max, it will be 45.
> If I use Min, it will be 41.
> I need an aggregate that show number from last record in the dataset.
> Is there any possible way how to do this? Any input is appreciated.
> Thank you.
Reporting Services actually supports "Last" Aggregate (using regular
RDL file, e.g. developed using Visual Studio), which I think can solve
the problem. I notice that we can use
Count,CountDistinct,StDev,StDevP,Var, or VarP, in the Report Builder
editor itself, but there is no First or Last available. Does anyone
knows how to use this "Last" aggregate in report builder?

Friday, March 9, 2012

Report Builder 401

When attempting to run Report Builder I get error below. After reading
earlier posts I have tried several combinations on the File Security
properties of the 6 Report Builder files and seem to get the furthest (I get
the Application Run - Security Warning dialog) while enabling Anonymous
access using the Report Server service account and also using Authenticated
access (Integrated Windows Authentication). I also tried changing the
RSWebApplication.Config from FullTrust to Partial Trust but that did not work.
Thanks in advance for any help
Please note that unlike earlier posts I have a Win32Exception at the end of
the error detail.
PLATFORM VERSION INFO
Windows : 5.1.2600.131072 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url :
http://reporting.voyagerlearning.com/ReportServer/ReportBuilder/ReportBuilder.application
Server : Microsoft-IIS/6.0
X-Powered-By : ASP.NET
X-AspNet-Version: 2.0.50727
Application url :
http://reporting.voyagerlearning.com/ReportServer/ReportBuilder/ReportBuilder.exe.manifest
Server : Microsoft-IIS/6.0
X-Powered-By : ASP.NET
X-AspNet-Version: 2.0.50727
IDENTITIES
Deployment Identity : ReportBuilder.application, Version=9.0.2047.0,
Culture=neutral, PublicKeyToken=c3bce3770c238a49, processorArchitecture=msil
Application Identity : ReportBuilder.exe, Version=9.0.2047.0,
Culture=neutral, PublicKeyToken=c3bce3770c238a49, processorArchitecture=msil,
type=win32
APPLICATION SUMMARY
* Online only application.
* Trust url parameter is set.
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later
in the log.
* Activation of
http://reporting.voyagerlearning.com/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected:
+ Downloading
http://reporting.voyagerlearning.com/ReportServer/ReportBuilder/reportbuilder.chm.deploy did not succeed.
+ The remote server returned an error: (401) Unauthorized.
+ The logon attempt failed
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [8/9/2006 11:26:32 PM] : Activation of
http://reporting.voyagerlearning.com/ReportServer/ReportBuilder/ReportBuilder.application has started.
* [8/9/2006 11:26:32 PM] : Processing of deployment manifest has
successfully completed.
* [8/9/2006 11:26:32 PM] : Installation of the application has started.
* [8/9/2006 11:26:32 PM] : Processing of application manifest has
successfully completed.
* [8/9/2006 11:26:35 PM] : Request of trust and detection of platform is
complete.
ERROR DETAILS
Following errors were detected during this operation.
* [8/9/2006 11:26:35 PM]
System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading
http://reporting.voyagerlearning.com/ReportServer/ReportBuilder/reportbuilder.chm.deploy did not succeed.
- Source: System.Deployment
- Stack trace:
at
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at
System.Deployment.Application.FileDownloader.Download(SubscriptionState
subState)
at
System.Deployment.Application.DownloadManager.DownloadDependencies(SubscriptionState
subState, AssemblyManifest deployManifest, AssemblyManifest appManifest, Uri
sourceUriBase, String targetDirectory, String group, IDownloadNotification
notification, DownloadOptions options)
at
System.Deployment.Application.ApplicationActivator.DownloadApplication(SubscriptionState
subState, ActivationDescription actDesc, Int64 transactionId, TempDirectory&
downloadTemp)
at
System.Deployment.Application.ApplicationActivator.InstallApplication(SubscriptionState subState, ActivationDescription actDesc)
at
System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at
System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
-- Inner Exception --
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
-- Inner Exception --
System.ComponentModel.Win32Exception
- The logon attempt failed
- Source: System
- Stack trace:
at System.Net.NTAuthentication.GetOutgoingBlob(Byte[] incomingBlob,
Boolean throwOnError, SecurityStatus& statusCode)
at System.Net.NTAuthentication.GetOutgoingBlob(String incomingBlob)
at System.Net.NegotiateClient.DoAuthenticate(String challenge, WebRequest
webRequest, ICredentials credentials, Boolean preAuthenticate)
at System.Net.NegotiateClient.Authenticate(String challenge, WebRequest
webRequest, ICredentials credentials)
at System.Net.AuthenticationManager.Authenticate(String challenge,
WebRequest request, ICredentials credentials)
at System.Net.AuthenticationState.AttemptAuthenticate(HttpWebRequest
httpWebRequest, ICredentials authInfo)
at System.Net.HttpWebRequest.CheckResubmitForAuth()
at System.Net.HttpWebRequest.CheckResubmit(Exception& e)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.Never mind. Just use How to Enable Anonymous Access on the ReportBuilder
Folder in Configure a Report Server for Report Builder Access

Tuesday, February 21, 2012

Replication: Synchronization Status

In SQL 2005 when i expand the local publication , Under replication when i try to view Synchronization Status i see the below message.

The initial snapshot for publication '%Database' is not yet available.

can any one help me on this issue.

Thanks

You can open the replication monitor, go under this publication, go to the other agents tab, and start snapshot.|||This is because initial snapshot for the publication has not been generated. So generate snapshot first.