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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy