November 11, 2013 at 1:48 pm
I have the following report I need to generate . I am new to SSRS.
Number of Stores with June July Aug
0 sales 12 34 32
1 to 9 Sales 12 34 45
10+ sales 15 45 54
The tables I have are as follows :
Store :
YEAR MONTH StoreName
2013 10 ABC
2013 10 DEF
2013 09 JKL
2013 06 FGH
Store Sales : (Contains only stores whose sales are > 0)
YEAR MONTH StoreName NumberOfSales
2013 10 ABC 3
2013 09 JKH 14
2013 10 FRH 9
I am not really sure what I need to do to get the report in the above format ? I can write a query for sales in a single month , but how do I write a query to get the report for all 3 months ? Or is there a better way to do these reports using ssrs ?
If you can direct me on what to do it will be great ?
November 11, 2013 at 3:23 pm
First, in the future try including your sample data like this:
DECLARE @Store TABLE (s_yr int, s_mo tinyint, storeName varchar(10))
DECLARE @Store_Sales TABLE (s_yr int, s_mo tinyint, storeName varchar(10), NumberOfSales int)
INSERT INTO @Store VALUES
(2013, 10, 'ABC'),
(2013, 10, 'DEF'),
(2013, 09, 'JKL'),
(2013, 06, 'FGH');
INSERT INTO @Store_Sales VALUES
(2013, 10, 'ABC', 3),
(2013, 09, 'JKH', 14),
(2013, 10, 'FRH', 9)
Using that sample data, you can get the resultset you are looking for like so (I included all months but you could filter as needed):
SELECT s_yr,
SUM(CASE WHEN s_mo=1 THEN NumberOfSales ELSE 0 END) 'Jan',
SUM(CASE WHEN s_mo=2 THEN NumberOfSales ELSE 0 END) 'Feb',
SUM(CASE WHEN s_mo=3 THEN NumberOfSales ELSE 0 END) 'Mar',
SUM(CASE WHEN s_mo=4 THEN NumberOfSales ELSE 0 END) 'Apr',
SUM(CASE WHEN s_mo=5 THEN NumberOfSales ELSE 0 END) 'May',
SUM(CASE WHEN s_mo=6 THEN NumberOfSales ELSE 0 END) 'June',
SUM(CASE WHEN s_mo=7 THEN NumberOfSales ELSE 0 END) 'Jul',
SUM(CASE WHEN s_mo=8 THEN NumberOfSales ELSE 0 END) 'Aug',
SUM(CASE WHEN s_mo=9 THEN NumberOfSales ELSE 0 END) 'Sep',
SUM(CASE WHEN s_mo=10 THEN NumberOfSales ELSE 0 END) 'Oct',
SUM(CASE WHEN s_mo=11 THEN NumberOfSales ELSE 0 END) 'Nov',
SUM(CASE WHEN s_mo=12 THEN NumberOfSales ELSE 0 END) 'Dec'
FROM @Store_Sales
GROUP BY s_yr
You can learn more about the technique I used here: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Let us know if this helps.
-- Itzik Ben-Gan 2001
November 11, 2013 at 7:11 pm
SqlServerNinja (11/11/2013)
The tables I have are as follows :
Store :YEAR MONTH StoreName
2013 10 ABC
2013 10 DEF
2013 09 JKL
2013 06 FGH
Sorry but I need to ask a question here. Why would you have YEAR and MONTH in a table that contains Stores? How are those columns attributes of a Store?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 12, 2013 at 8:00 am
Good question !
These are all the stores that have registered with our company in that month, year.
November 12, 2013 at 5:40 pm
SqlServerNinja (11/12/2013)
Good question !These are all the stores that have registered with our company in that month, year.
Please excuse me on 2 points then:
- Apparently my question wasn't clear. I meant to ask how year/month in the Store table relates to the problem at hand.
- You should never store year/month as separate columns like that. It will make later manipulations awkward to say the least. Better to use a DATE data type and simply set the day part to 1.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply