November 11, 2007 at 1:35 pm
I have a sql problem that I am trying to resolve. It is for a report, and an analogy can most closely be made to sales/invoices, so I will use that as a starting point. The real data comes from multiple databases in different systems, and I know how to get to it, but much to complicated for a post.
So the end result is a SSRS Report that they "want" grouped by Region,District,Store. This is what I came up with as a demo..
create table #District ( #DistrictId int)
insert into #District values (1)
insert into #District values (2)
insert into #District values (3)
create table #store ( storeId int, #District int )
insert into #store values (1,1)
insert into #store values (2,1)
insert into #store values (3,2)
create table #Invoices ( storeId int, invoiceDate datetime, invoiceAmount money, itemsSold int)
insert into #Invoices values( 1, '1/1/2007',123.45, 12 )
insert into #Invoices values( 2, '1/1/2007',123.45 ,10)
insert into #Invoices values( 3, '1/1/2007',123.45 ,29)
insert into #Invoices values( 1, '4/1/2007',123.45 ,1)
insert into #Invoices values( 2, '4/1/2007',123.45 ,22)
insert into #Invoices values( 2, '4/1/2007',123.45 ,3)
insert into #Invoices values( 3, '4/1/2007',123.45 ,45)
--
--Pass in 2 parameters, start & stop dates, 12 month max
--
--#District#storeTotal#InvoicesTotalItemsMonthYear
--11 123.4512Jan 2007
--12 123.4510Jan 2007
--23 123.4529Jan 2007
--11 123.451Apr 2007
--12 246.9025Apr 2007
--23 123.4545Apr 2007
I went down a couple ( ok couple of dozen 😉 ) different ways to express this. I think the closest was using a query that places each month into a column (or null if no value) and push all that into a temp table. Then figure out the years/months from the parameter and make the Mon YYYY look pretty.
After about 12 hours trying to get this, and a growing bald spot, I thought I'd ask for guidence.
Larry
November 12, 2007 at 2:48 am
Just a slightly different approach, but could you not create a union view from the three data sources and then use that view in the data set for SSRS, so that your SSRS data set contains all the required info via a view.
Then within SSRS you could simply group by region, district and store and supply the filter parameters of a date.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
November 15, 2007 at 3:57 am
SELECT s.#District, s.StoreId #Store, SUM(i.InvoiceAmount) Total#Invoices, SUM(i.ItemsSold) TotalItems,
CONVERT(char(7),i.InvoiceDate,120) YearMonth --, CONVERT(char(8),STUFF(InvoiceDate,4,3,''),101) MonthYear
FROM #Store s JOIN #Invoices i ON s.StoreId = i.StoreId
GROUP BY CONVERT(char(7),i.InvoiceDate,120), s.#District, s.StoreId--, CONVERT(char(8),STUFF(InvoiceDate,4,3,''),101)
Problem with your MonthYear format is sorting.
November 15, 2007 at 4:13 am
Don't forget that you don't have to do all your sorting & grouping in SQL Server itself. You can do some of it with the Groups in SSRS. So if you can't quite get all of it doing T-SQL, get as much of it as you can in a way that makes sense and then do the final bit in SSRS.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply