SSRS Report using letter and detail are grouped two table ,but output taken all Customer letter and all customer detail
Display .Grouping with Payee is not coming.It is taking as a individual. finally i've tried with Sub Report. this is
also same output Display, individual letter and detail will be Display proper I Want one by one letter,detail report for all record ?
I am a complete newbie in SSRS. I've worked for several years with Crystal Reports. I created a Crystal report which lists detail information for each General Ledger Account. I want to group the report by the first 10 characters of the account, list all the accounts in that group, and then page break for a new group. The report should look like this:
2-999-9999
2-999-9999-1234
Transaction Number Date Amount Remarks
1111 10/1/2007 300.00 JE999
2222 10/2/2007 200.00 JE59
2-999-9999-5678
Transaction Number Date Amount Remarks
1212 10/5/2007 600.00 PY92
I think I must be missing something very simple, but I can't seem to do this in SSRS.
Can anyone help?
Thanks very much.
Sue
I think this is a simple question for creating a report with SQL Reporting Services, but I can't seem to find any straight forward answers for this.
I have some detail data I'm pulling in from 1 view into my report dataset:
------------------------------------------------------
Loan NumberLoan AmountPayment DatePayment
1000550000Jan 200815000
1000550000Feb 200815000
1000550000Mar 200815000
1002300000Jan 200850000
1003450000Jan 200820000
1003450000Feb 200820000
------------------------------------------------------
I'm creating a RDL with this data and in my detail row, I'm showing the Loan Pmt and Loan Date with Grouping on the Loan Number & Loan Amount.
My problem is that when I look at my grand total, it is summing up the Loan amount more than 1 time - once for each detail record.
------------------------------------------------------
RDL
Loan Number Loan Amount Payment Date Payment
1000 550000 Jan 2008 15000
Feb 2008 15000
Mar 2008 15000
Subtotal 45000
Loan Balance505000
1002 300000 Jan 2008 50000
Subtotal 50000
Loan Balance250000
1003 450000 Jan 2008 20000
Feb 2008 20000
Subtotal 40000
Loan Balance410000
Grand Totals 2850000 135000
------------------------------------------------------
So my total on the Loan Amount column is incorrect, it should really be 1,300,000, but instead it's calculating 2,850,000.
Can someone explain to me how I should correct my grouping or dataset in a table?
I was able to use a Subreport for my detail records, and keep the Loan details in my Parent report to get the right totals, but then I hit the issue with exporting to Excel - "Subreports within table/matrix cells are ignored"
Any suggestions??
I have an SSRS chart which has 4 to 5 levels of categories (for eg- Country,State,City,Street) levels , Now when i select the street it shows groups starting from Country then State, City, and then street . Because of these grouping sometimes the label is not appearing properly its gets truncated between lines , Is there any way to avoid showing the groups only show the last group for example as per the above example (Instead of showing State, City, and then street ) just show the Street itself so that all grouping lines get avoided and chart looks better without any label truncation
View 8 Replies View RelatedI have a table that will always return 6 records. I would like to group the table, such that the first 3 records, are GROUPED Together first, do the sub-total on these 3 records first. Then the last 3 records are grouped together. and then the sub-total on the last 3 records. Is there a way I can do that in SSRS 2005?
View 3 Replies View RelatedExample: A sales report that details and summarizes each Salesperson’s total sales within a company’s stores that reside in each state.
The report has 3 levels of groupings where the highest 2 levels require summary counts on a separate page and the lowest level produces a summary count in the midst of the detail lines of the report.
The data consumed by the report is ordered by State, Store ID, and Salesperson.
The header of each page lists the State and Store ID. The detail lines of the report list items sold and the associated sales amount for each item sold by each store’s salesperson.
At the end of each Salesperson’s items sold, a summary line is listed on the next line in the report showing the total amount and total count of items sold by that Salesperson.
The very next line on the report lists items sold and the associated sales amount for the next Salesperson within that particular store.
Summary lines will be produced for each Salesperson within each particular store on the next line of detail in the report.
At the end of all sales data for each store, a summary page is produced on a separate page listing the summary of each Salesperson’s totals.
Each line of the summary page contains the same counts for each Salesperson that was embedded in the detail section of the report.
The summary page also contains a grand total line listing the total count and sales for all salesmen within each given store.
At the end of all sales data for each state a summary page is produced on a separate page listing the summary of each store’s totals.
Each line of the summary page contains the grand totals of all sales for each store within a given state.
The summary page also contains a grand total line listing the total count and sales for all stores within each given state.
The simple breakdown is the groupings and totals for each state and store must be listed on a separate page in the report.
The grouping and totals for each Salesperson must be listed on the next line within the detail section of the report.
Current issue: The requirement is for no page break at the end of each salesperson’s sales data because the page breaks produce too many extra pages in the report.
Disabling the page break at the Salesperson level produces format issues such as page headers printing on the next line in the report before each Salesperson’s summary line.
Disabling the page breaks at this level also produces the problem of suppressing the page break for the summary page that comes at the end of each store.
Question:The report has 3 levels of grouping with Salesperson at the lowest level, Store ID being the parent group of Salesperson, and State being the parent group of Store ID. Is it possible to format the Salesperson summary line (lowest level grouping) as just another detail line in the report without impacting the report format that requires page breaks at the parent group levels?
A sample report layout of what we are trying to achieve is on the next page.
Sales Report
State: Alaska
Store ID: 100
Item Sold
Sales Amount
Hammer
$10.00
Saw
$15.00
[Code] .....
I want to display data in following format in ssrs report.
Rank Group Value
1 G1 10
2 G1 20
3 G1 30
4 G1 40
1 G2 25
2 G2 54
3 G2 64
I tried row count and other ranking functions but it did not working.
I want to develop a ssrs report which is grouped by month?
View 4 Replies View RelatedWe are facing problem in doing page break with column grouping. Our column group contains years e.g 2011, 2013 . We want to show a complete page for a year.
Suppose 2011 has 10 records(horizontal) and 2013 has 12 records(horizontal) in column. The output should be 10 records of 2011 in first page, 12 records of 2013 in second page.
We cannot change the report layout to make column to row and vice versa.
We have a report that was created in SSRS 2008 R2 that has 3 tables with different datasets that share a common ID that I want to use to group them.
If we run the report passing only a single value for the grouped parameter then the report works perfectly. What we need is for this report to allow multiple values to be selected for this parameter and for the report to run as if the user had selected each value one at a time and run the report with page breaks in between. Currently, when we pass multiple selected values for the grouping parameter the report displays all values for table 1, then all values for table 2, then all values for table three as below:
Table 1:
Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
....
Table 3:
Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
But we want it to render like this:
Table 1:
Detail rows for Group Param Value 1...
Table 2:
Detail rows for Group Param Value 1...
Table 3:
Detail rows for Group Param Value 1...
[code]....
The page breaks are needed so that when the report is exported to excel each individual report (by group param) will be on its own uniquely named tab.The report must export cleanly to excel and currently does for the single value passed.
I have a report where in I want to show each record on a separate page.
So, to achieve that I took a single cell from table control, expanded it and used all the controls in that single cell. This looks nice so far.
Now, I also have to show a sub grid on each record. So I took a table control and added on the same single cell and tried to add a parent group to the table row.
When I preview, it throws this error.
"The tablix has a detail member with inner members. Detail members can only contain static inner members."
What am I doing wrong? How can I achieve table grouping inside a table cell?
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
Hi,
Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?
Thanks,
Currently we are using Custom Delivery Extension for SSRS 2008R2 We are planning to move it SSRS2012
My Question is: Whether we can use the same Code used for SSRS2008R2 to SSRS2012?
if not what code changes we should do?
Is it possible to write SSRS 2008 reports to run on an existing SSRS 2005 server?
If yes, what do I need to do to be able to write SSRS 2008 reports on my developer PC that will run on the SQL 2005 Server (which also has SSRS 2005 installed on it)?
I installed Visual Studio 2008 beta on the development PC and it appears that it needs SSRS installed on it too. So I installed SQL 2008 SSRS (SQL Server Reporting Services) and SQL 2008 Workstation Components onto the developer PC. Next I run the SQL 2008 "Reporting Services Configuration" tool. When I click on the "Web Service URL" section it hangs indefinitely and I have to force it closed.
The developer PC is Windows Vista Business.
Any suggestions on making this work, or any other information that you can think of that would allow me to use Visual Studio 2008 and the SQL 2008 "Report Designer Preview" tool to develop SSRS reports that will run on the SQL 2005 SSRS server?
Thanks!
-ErikR
Hi,
We are in need of migrating SSRS subscriptions along with the reports. Could you please let me know the process ?
I really appreciate your help on this.
Thanks,
Sam
I did a quick Google for this but found nada...
If I develop a report in SSRS 64 bit, can it be ported to a 32 bit installation? Any issues?
I will do my own testing, but I was curious if anyone else had experience doing this.
Hi there, i have an SQL query which select the number of products and display them per sub_cat_id e.g.
7 french dressers3 spanish dressers1 english dresser
However what i want to do is then group those under the the main category (cat_id)
All descriptions are held in tbl_cat and and main category and sub category can be found by the cat_cat colum, if there is an M in this colum it means it is a main category e.g. beds, tables, if there is the name of a M category then it is a sub cat e.g. double beds, single beds etc.
Can anybody help me with my grouping nightmare?
this is the code i have so far
SELECT COUNT(tbl_stock.Stock_ID) AS TOTALOFSTOCK, tbl_stock.sub_cat_id, tbl_cat.Cat_DescriptionFROM tbl_stock INNER JOIN tbl_cat ON tbl_stock.sub_cat_id = tbl_cat.Category_IDGROUP BY tbl_cat.Cat_Description, tbl_stock.sub_cat_id
I have a table which lists manufacturers' products. Users are allowed to vote for the most popular item belonging to the manufacturer. Moderators assign the products to manufacturers so on occassion they may accidently (or deliberately) get it wrong. For this reason, the table also acts as the ChangeLog so if choose, I could instantly decide whether all changes made by a specific user should be dismissed. Product | Manufacturer | NoOfVotes | ChangedBy | ChangedTime
============ ============== =========== =========== =============
XBOX 360 Microsoft 25* Dic 05/03/2008
XBOX 360 Sony 1 Tom 04/03/2008
Playstation Sony 100* Dic 03/03/2008
XBOX 360 Microsoft 25* Dic 02/03/2008
XBOX 360 Sony 1 Harry 01/03/2008
I'm using ID fields for Product, Manufacturer and ChangedBy which are obtained from different tables but have simplified the table to make it easier to read.
I need to retrieve each product in the table, the last Manufacturer assigned to it, with a total of the votes where the product-manufacturer match. The results should be: Product | Manufacturer | TotalVotes | LastChanged
============ ============== ============ =============
XBOX 360 Microsoft 50 05/03/2008
Playstation Sony 100 03/03/2008
Any help would be greatly appreciated.
Thanks, Pete.
Hello, All,
Here's the query I'm attempting to execute:
Code:
SELECT LEA, Count(SSN)
FROM WorkData2003_04.dbo.Students --Database and Table Name
WHERE Grade_Level='10' AND SSN NOT IN(
SELECT SSN
FROM WorkData2003_04.dbo.Students --Database and Table Name
WHERE Grade_Level='09')
However, SQL returns the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'WorkData2003_04.dbo.Students.lea' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm trying to generate a count of SSNs by LEA (LEA is the same thing as a school) that exist one school year but do not exist in the next school year (essentially students that transfer OUT of a school district).
Could someone provide assistance? I think I need to incorporate the HAVING clause but I'm not for sure...
Thanks!
Cody
I'm trying to write a report and need to group columns that are alike.
For example.
DeptID JobTitle
40344 Sales Clerk
1st Assistant
Store Manager
40666 Sales Clerk
2nd Assistant
Store Manager
Sorry, it will not output correctly, I hope you get what I'm trying to do.
I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.
Hi !
I have a log table like this :
UserID LoginDate
------ --------------
31 2005-11-01
31 2005-11-05
8 2005-11-02
31 2005-11-02
7 2005-11-04
I need to get the LAST login date for each user as the following result :
7 2005-11-04
8 2005-11-02
31 2005-11-05
Can someone help me please ?
Thanks !
I have 2 columns
RecordDate(DateTime)
Quantity(Int)
I would like to group the data quarter wise like,
Quarter 1 500
Quarter 2 340
Quarter 3 450
Quarter 4 400
Note: Say quarters are fixed from Jan-Mar,Apr-Jun etc.,
Prakash.P
The secret to creativity is knowing how to hide your sources!
I am writing a report from an historical database that uses posting_times. The way the posting works is if something has a posting time of 01/01/97 00:00:00 it means that the record covers from 12/31/96 00:00:00 to 01/01/97 00:00:00.
This is where my problem is at. The report i am writing needs to be grouped by Month. Before i figured out how the posting dates worked i was using ... DATEPART(month, posting_time) AS monthDate ... for grouping purposes in the report.
Now that i have figured out how the posting dates work i am at a total loss as how to group this. I am using SQL Reporting Services 2000 to write this report.
Any help would be greatly appreciated.
Thanks
Mitch
Is it possible to return just the top row of each group within a query result?
Par example, if I have a query that returns a list of products, grouped by order date and product name and ordered by date, name desc
How can I just get back the top row within each date grouping so that I can list the latest prod order per prod?
Thank you for any tips!
--PhB
its showing the promotion group not grouped right can you help
--#ttTransaction
SELECT ShipToID, Trans.StoreID, TransactionNumber, BatchNumber, [Time], CustomerID, CashierID,
CASE WHEN Trans.StoreID IN (172,173) THEN Total*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN Trans.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN Total*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE Total END
ELSE Total END END AS Total,
CASE WHEN Trans.StoreID IN (172,173) THEN SalesTax*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN Trans.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN SalesTax*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE SalesTax END
ELSE SalesTax END END AS SalesTax,
Comment, ReferenceNumber,
Status, ExchangeID, ChannelType, RecallID, RecallType,
CASE WHEN Trans.[Time] >= SO.ShiftDate THEN 'Same' ELSE 'New' END AS SalesType
INTO #ttTransaction
FROM QSCHQ.dbo.[Transaction] Trans
LEFT JOIN Reporting.dbo.RetailSalesComparison_StoreOpenings SO ON Trans.StoreID = SO.StoreID
WHERE YEAR([Time]) >= YEAR(GETDATE())-1
--#ttTransactionEntry
SELECT TE.Commission,
CASE WHEN TE.StoreID IN (172,173) THEN TE.Cost*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN TE.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN TE.Cost*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE TE.Cost END
ELSE TE.Cost END END AS Cost,
CASE WHEN TE.StoreID IN (172,173) THEN TE.FullPrice*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN TE.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN TE.FullPrice*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE TE.FullPrice END
ELSE TE.FullPrice END END AS FullPrice,
TE.StoreID, TE.[ID], TE.TransactionNumber, TE.ItemID,
CASE WHEN TE.StoreID IN (172,173) THEN TE.Price*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN TE.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN TE.Price*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE TE.Price END
ELSE TE.Price END END AS Price,
TE.PriceSource, TE.Quantity, TE.SalesRepID, TE.Taxable, TE.DetailID, TE.Comment,
TE.AutoID, TE.DiscountReasonCodeID, TE.ReturnReasonCodeID, TE.TaxChangeReasonCodeID,
TE.SalesTax, TE.QuantityDiscountID
INTO #ttTransactionEntry
FROM QSCHQ.dbo.TransactionEntry TE
INNER JOIN #ttTransaction Trans
ON TE.StoreID = Trans.StoreID
AND TE.TransactionNumber = Trans.TransactionNumber
WHERE YEAR([Time]) >= YEAR(GETDATE())-1
--#ttTransactionWithPesoConversionAndCustomerAccount
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
INTO #ttTransactionWithPesoConversionAndCustomerAccount
FROM #ttTransaction Trans
INNER JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND 1 = Customer.GlobalCustomer
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
UNION ALL
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
FROM #ttTransaction Trans
INNER JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND Trans.StoreID = Customer.StoreID AND 1 <> Customer.GlobalCustomer
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
UNION ALL
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
FROM #ttTransaction Trans
LEFT JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND Trans.StoreID = Customer.StoreID
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
WHERE Customer.[ID] IS NULL
AND Trans.CustomerId < 100000
UNION ALL
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
FROM #ttTransaction Trans
LEFT JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
WHERE Customer.[ID] IS NULL
AND Trans.CustomerId > 100000
--#ttRetailSales_ByStore_ByCustomer_ByDay
SELECT CONVERT(DATETIME, CONVERT(CHAR, [Time], 101)) AS [Date],
Trans.StoreID, Trans.StoreGroupID,
Trans.AccountNumber, Trans.Company,
SUM(TE.Price*TE.Quantity) AS Sales,
COUNT(DISTINCT Trans.TransactionNumber) AS TRXCount,
SUM(TE.Quantity) AS UnitsSold, Trans.SalesType
INTO #ttRetailSales_ByStore_ByCustomer_ByDay
FROM #ttTransactionWithPesoConversionAndCustomerAccount Trans
INNER JOIN #ttTransactionEntry TE
ON Trans.StoreID = TE.StoreID AND Trans.TransactionNumber = TE.TransactionNumber
WHERE (Trans.Total - Trans.SalesTax) <> 0
GROUP BY Trans.AccountNumber, Trans.StoreGroupID, CONVERT(DATETIME, CONVERT(CHAR, Trans.[Time], 101)),
Trans.StoreID, Trans.Company, Trans.SalesType
-- DROP TABLE #ttTransaction
-- DROP TABLE #ttTransactionEntry
-- DROP TABLE #ttTransactionWithPesoConversionAndCustomerAccount
-- ***** #ttTransGroup *****
SELECT [Date], PromotionGroupID,AccountNumber, SUM(Total) AS Total, SUM(TRXCount) AS TRXCount, SalesType
INTO #ttTransGroup
FROM
(SELECT [Date],
QSTStore.PromotionGroupID, Trans.AccountNumber,
Sales AS Total,
TRXCount, SalesType
FROM #ttRetailSales_ByStore_ByCustomer_ByDay Trans
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
WHERE Trans.AccountNumber LIKE 'C-%'
UNION ALL
SELECT TransactionDate AS [Date], QSTStore.PromotionGroupID, AccountNumber, SUM(Amount) AS Total, 0 AS TRXCount,
CASE WHEN QuickSellSalesAdjustment.[TransactionDate] >= SO.ShiftDate THEN 'Same' ELSE 'New' END AS SalesType
FROM DelSol.dbo.QuickSellSalesAdjustment QuickSellSalesAdjustment
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON QuickSellSalesAdjustment.StoreID = QSTStore.StoreID
LEFT JOIN Reporting.dbo.RetailSalesComparison_StoreOpenings SO ON QuickSellSalesAdjustment.StoreID = SO.StoreID
WHERE YEAR(TransactionDate) >= YEAR(GETDATE())-1
GROUP BY AccountNumber, QSTStore.PromotionGroupID, TransactionDate,
CASE WHEN QuickSellSalesAdjustment.[TransactionDate] >= SO.ShiftDate THEN 'Same' ELSE 'New' END) ttTemp
GROUP BY AccountNumber, PromotionGroupID, [Date], SalesType
HAVING SUM(ttTemp.Total) <> 0
-- ***** #ttCSVF *****
SELECT CSVFlag.TransactionDate, CSVFlag.StoreGroupID, CSVFlag.AccountNumber, CSVFlagType.[Description]
INTO #ttCSVF
FROM DelSol.dbo.CruiseShipVisitFlag AS CSVFlag
INNER JOIN DelSol.dbo.CruiseShipVisitFlagType AS CSVFlagType ON CSVFlagType.ID = CSVFlag.FlagTypeID
-- ***** Main *****
SELECT ttTransGroup.[Date] as TransactionDate,
QSTPromoStoreGroup.[ID] AS PromoGroupID, QSTPromoStoreGroup.StoreGroup AS PromoGroup,
ttTransGroup.AccountNumber AS AccountNumber, CruiseShip.ShipName AS ShipName,
CruiseShipGroup.[Description] AS ShipLine, CruiseShipCompany.[Description] AS PortLecturerCompany,
Sum(ttTransGroup.Total) AS GroupSales,
(CSVP.AdjustedProjection) AS Projection,
CruiseShip.Capacity, CASE WHEN CruiseShip.Capacity = 0 THEN 0 ELSE ttTransGroup.Total/CruiseShip.Capacity END AS ASPP,
CASE WHEN CONVERT(DECIMAL, CruiseShip.Capacity) = 0 THEN 0 ELSE CONVERT(DECIMAL, ttTransGroup.TRXCount)/CONVERT(DECIMAL, CruiseShip.Capacity) END AS Yield,
ttTransGroup.TRXCount, CASE WHEN ttTransGroup.TRXCount=0 THEN 0 ELSE ttTransGroup.Total/ttTransGroup.TRXCount END AS ADPT,
CruiseShipPortLecturer.[Name] AS PortLecturer,
CSVF.[Description] AS Flag, ttTransGroup.SalesType
FROM QSCHQ.dbo.QSTPromoStoreGroup QSTPromoStoreGroup
INNER JOIN #ttTransGroup ttTransGroup ON QSTPromoStoreGroup.[ID] = ttTransGroup.PromotionGroupID
INNER JOIN DelSol.dbo.CruiseShip CruiseShip ON ttTransGroup.AccountNumber = CruiseShip.AccountNumber
INNER JOIN DelSol.dbo.CruiseShipCompany CruiseShipCompany ON CruiseShip.PromotionCompany = CruiseShipCompany.[ID]
INNER JOIN DelSol.dbo.CruiseShipGroup CruiseShipGroup ON CruiseShip.GroupID = CruiseShipGroup.[ID]
LEFT JOIN DelSol.dbo.CruiseShipPortLecturer CruiseShipPortLecturer
ON CruiseShip.AccountNumber = CruiseShipPortLecturer.AccountNumber
AND ttTransGroup.[Date] BETWEEN CruiseShipPortLecturer.StartDate
AND CASE WHEN CruiseShipPortLecturer.EndDate IS NULL THEN '12/31/2099' ELSE CruiseShipPortLecturer.EndDate END
LEFT JOIN DelSol.dbo.CruiseShipVisitProjection CSVP
ON CSVP.TransactionDate= ttTransGroup.[Date]
AND CSVP.StoreGroupID = QSTPromoStoreGroup.[ID]
AND CSVP.AccountNumber = ttTransGroup.AccountNumber
LEFT JOIN #ttCSVF CSVF
ON CSVF.TransactionDate = ttTransGroup.[Date]
AND CSVF.StoreGroupID = QSTPromoStoreGroup.[ID]
AND CSVF.AccountNumber = ttTransGroup.AccountNumber
WHERE QSTPromoStoreGroup.ReceivesCruiseShips = 1
AND CSVF.[Description] IS NULL
Group By ttTransGroup.AccountNumber, QSTPromoStoreGroup.[ID],QSTPromoStoreGroup.StoreGroup,
ttTransGroup.[Date],
CruiseShip.ShipName,
CruiseShipGroup.[Description], CruiseShipCompany.[Description],
(CSVP.AdjustedProjection),
CruiseShip.Capacity, CASE WHEN CruiseShip.Capacity = 0 THEN 0 ELSE ttTransGroup.Total/CruiseShip.Capacity END,
CASE WHEN CONVERT(DECIMAL, CruiseShip.Capacity) = 0 THEN 0 ELSE CONVERT(DECIMAL, ttTransGroup.TRXCount)/CONVERT(DECIMAL, CruiseShip.Capacity) END,
ttTransGroup.TRXCount, CASE WHEN ttTransGroup.TRXCount=0 THEN 0 ELSE ttTransGroup.Total/ttTransGroup.TRXCount END,
CruiseShipPortLecturer.[Name],
CSVF.[Description], ttTransGroup.SalesType
Order by QSTPromoStoreGroup.StoreGroup
SQL2000. I have a table as follows. Table shows a log of solutions posted by agents. For example, Ticket#5 was replied once by each agent A,B and C.
==========================
ticket_number || agent || solution
==========================
5 || A || test
5 || B || test
5 || C || test
7 || B || test
7 || C || test
8 || C || test
9 || B || test
I need to find out number of all ticket solutions/counts (how many ticket was touched by all agents) but in format related to the single agent:
AGENT TICKET_NUMBER COUNT
A 5 3
B 5 3
C 5 3
B 7 2
C 7 2
.....
How could I achieve this?
Thank you much!
Julia
Hi
I have created a table and linked it to a dataset and grouped the data based on a field called SYSTEM.
Now in the group footer I have to display summary values for each system from another dataset.
But it always gives me the First Value in that. That is for all the groupings I get the summary value for the first system
only. How do I go about this.
Thanks
Sai
Hi,
I have a stored procedure which is returning results in the following format:
No.of substances Count
1 29
2 89
3 876
.. ..
15 56
16 89
Now i need to display like this:
No.of substances Count %
1 20
2 89
.. ..
>=9 8766
Total
All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9
How to achieve this
Thanks in advance
The report I'm designing has a number of records in it, but two main fields that I'm concerned with Budget, and spent.
for example
Budget Spent
75 5
75 10
75 8________________
Total 225 23
The spent records are all unique, but the budget record is the same field repeating itself over and over. I've used a grouping to eliminate the repetition of the Budget amount, but the total still comes out to be incorrect. It now looks like this
Budget Spent
75
5
10
8________________
Total 225 23
Is there a way which I can add the values that are displayed rather than the underlying values?
Please Help!
Thanks,
-Shahin
I'h having some throughput problems, and so, I decided to create a view with the info I need. The problem now is this: I have to select all the days where there were associations in my clients website in this format: dd/MM/yyyy (xx) where XX is the number of associations on that day. Here is the first code, wich worked but resulted in timeout:SELECT DISTINCT (CONVERT(varchar, buy_date, 103) + ' (' + CONVERT(varchar(10), (SELECT COUNT(*) FROM user_plan up2 WHERE CONVERT(datetime, CONVERT(varchar, up2.buy_date, 101)) = CONVERT(datetime, CONVERT(varchar, up1.buy_date, 101)))) + ')') AS 'text',CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'FROM user_plan up1WHERE CONVERT(varchar, buy_date, 101) <= CONVERT(varchar, getdate(), 101)ORDER BY value DESCThen I tried to create a view in wich I intented to save the number of associations to avoid the n² complexity of my query... This is the create view script: CREATE VIEW quadro_social AS SELECT COUNT(1) AS total, CONVERT(VARCHAR, buy_date, 103) as buy_date, CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value' FROM user_planGROUP BY buy_dateBut what happens is, becaus the "buy_date" column is datetime, they are not beign grouped because they have different times, but the same days... How can I group the registers with the same date (dd/MM/yyyy) ignoring the hour, minutes and seconds??? Thanks a lot!Guilherme Bertini Boettcher
View 2 Replies View RelatedHi,
I have this query...
cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1)
It produces the following output (trunctated by me for this post example).
name
category_id
CNameID
link_id
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
7
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
22
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
24
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
40
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
45
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
89
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
134
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
137
Architecture
5
Architecture5
37
Architecture
5
Architecture5
90
I would like it to display instead (where 8 and 2 are the counts):
Accounting/Bookkeeping 8
Architecture 2
Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp. The data is in SQL Server 2000. I was hoping to do it in one SqlCommand statement if possible. I guess I can drop it into a view and then run my group by query on the view if I had to.
I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far.
Thanks in advance,
Stewart