March 24, 2017 at 9:53 am
Hi,
I have a report set up with filters on it so that it produces figures between different months and years as parameters
I have a slight problem now in that the person running the report wants to be able to see it over 2 years. for instance October 2016 to March 2017 but the way I have it set up at the moment it's it filters QuartzReport1.MONTH_NUMBER >= @StartMonth andf QuartzReport1.MONTH_NUMBER <= @EndMonth. The problem with that is if I do April 2016 to July 2017 I only get the months April May June and July back I don;t get any months afgter July back.
Anybody have any idea how I can get around this?
The report is querying a view which is as follows
CREATE view [dbo].[QuartzReport1]
as
Select SALESDESCRIPTION, GROUPDESCRIPTION,
ACCOUNTNUMBER,
ACCOUNTNAME,
DATENAME (MONTH,ORDERDATEANDTIME) as MONTH,
(datepart(mm,ORDERDATEANDTIME)) AS MONTH_NUMBER,
concat ((datepart(mm,ORDERDATEANDTIME)),' ',(DATEPART(yyyy,ORDERDATEANDTIME))) as MONTH_YEAR,
ORDERDATEANDTIME AS ORDERDATE,TYPE as TYPE,
(DATEPART(yyyy,ORDERDATEANDTIME)) as YEAR,
SUM(NP8) AS NP8, SUM(SO) AS SO, SUM(SP) AS SP, SUM(Total) AS Total
FROM (
SELECT dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNUMBER,
dbo.Account.ACCOUNTNAME,
dbo.Orders.ORDERDATEANDTIME,DBO.oRDERS.TYPE,
Case When Type = 0 THEN 1 ELSE 0 END AS SP,
Case When Type = 3 THEN 1 ELSE 0 END AS NP8,
Case When Type = 4 THEN 1 ELSE 0 END AS SO,
CASE WHEN Type IN (0,3,4) THEN 1 ELSE 0 END AS Total
FROM dbo.Products INNER JOIN
dbo.Orders ON dbo.Products.PRODUCTID = dbo.Orders.PRODUCTID INNER JOIN
dbo.Account ON dbo.Orders.ACCOUNTID = dbo.Account.ACCOUNTID INNER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID INNER JOIN
dbo.Sales ON dbo.account.salesid = dbo.sales.salesid
Where Type IN (0,3,4)
) AS Source
Group By SALESDESCRIPTION, GROUPDESCRIPTION, ACCOUNTNUMBER, ACCOUNTNAME,ORDERDATEANDTIME,TYPE
Thanks in advance.
Paul.
March 24, 2017 at 10:33 am
You need to include month AND year as parameters. Otherwise you don't logically have anyway of knowing which year(s) you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 24, 2017 at 10:43 am
I do have the month and year as parameters. I have start month, end month, start year and end year
March 24, 2017 at 10:44 am
paul 69259 - Friday, March 24, 2017 9:53 AMHi,I have a report set up with filters on it so that it produces figures between different months and years as parameters
I have a slight problem now in that the person running the report wants to be able to see it over 2 years. for instance October 2016 to March 2017 but the way I have it set up at the moment it's it filters QuartzReport1.MONTH_NUMBER >= @StartMonth andf QuartzReport1.MONTH_NUMBER <= @EndMonth. The problem with that is if I do April 2016 to July 2017 I only get the months April May June and July back I don;t get any months afgter July back.
Anybody have any idea how I can get around this?
The report is querying a view which is as follows
CREATE view [dbo].[QuartzReport1]
as
Select SALESDESCRIPTION, GROUPDESCRIPTION,
ACCOUNTNUMBER,
ACCOUNTNAME,
DATENAME (MONTH,ORDERDATEANDTIME) as MONTH,
(datepart(mm,ORDERDATEANDTIME)) AS MONTH_NUMBER,
concat ((datepart(mm,ORDERDATEANDTIME)),' ',(DATEPART(yyyy,ORDERDATEANDTIME))) as MONTH_YEAR,
ORDERDATEANDTIME AS ORDERDATE,TYPE as TYPE,
(DATEPART(yyyy,ORDERDATEANDTIME)) as YEAR,
SUM(NP8) AS NP8, SUM(SO) AS SO, SUM(SP) AS SP, SUM(Total) AS Total
FROM (
SELECT dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNUMBER,
dbo.Account.ACCOUNTNAME,
dbo.Orders.ORDERDATEANDTIME,DBO.oRDERS.TYPE,
Case When Type = 0 THEN 1 ELSE 0 END AS SP,
Case When Type = 3 THEN 1 ELSE 0 END AS NP8,
Case When Type = 4 THEN 1 ELSE 0 END AS SO,
CASE WHEN Type IN (0,3,4) THEN 1 ELSE 0 END AS Total
FROM dbo.Products INNER JOIN
dbo.Orders ON dbo.Products.PRODUCTID = dbo.Orders.PRODUCTID INNER JOIN
dbo.Account ON dbo.Orders.ACCOUNTID = dbo.Account.ACCOUNTID INNER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID INNER JOIN
dbo.Sales ON dbo.account.salesid = dbo.sales.salesid
Where Type IN (0,3,4)
) AS Source
Group By SALESDESCRIPTION, GROUPDESCRIPTION, ACCOUNTNUMBER, ACCOUNTNAME,ORDERDATEANDTIME,TYPEThanks in advance.
Paul.
Change your MONTH_YEAR output column to YEAR_MONTH and filter on it.
A little reformatting changes the readability of your view:
CREATE view [dbo].[QuartzReport1]
as
Select
SALESDESCRIPTION,
GROUPDESCRIPTION,
ACCOUNTNUMBER,
ACCOUNTNAME,
DATENAME (MONTH,ORDERDATEANDTIME) as MONTH,
datepart(mm,ORDERDATEANDTIME) AS MONTH_NUMBER,
concat ((datepart(mm,ORDERDATEANDTIME)),' ',(DATEPART(yyyy,ORDERDATEANDTIME))) as MONTH_YEAR,
ORDERDATEANDTIME AS ORDERDATE,
TYPE as TYPE,
DATEPART(yyyy,ORDERDATEANDTIME) as YEAR,
SUM(NP8) AS NP8,
SUM(SO) AS SO,
SUM(SP) AS SP,
SUM(Total) AS Total
FROM (
SELECT
s.SALESDESCRIPTION,
g.GROUPDESCRIPTION,
a.ACCOUNTNUMBER,
a.ACCOUNTNAME,
o.ORDERDATEANDTIME,
o.TYPE,
Case When Type = 0 THEN 1 ELSE 0 END AS SP,
Case When Type = 3 THEN 1 ELSE 0 END AS NP8,
Case When Type = 4 THEN 1 ELSE 0 END AS SO,
CASE WHEN Type IN (0,3,4) THEN 1 ELSE 0 END AS Total
FROM dbo.Products p
INNER JOIN dbo.Orders o
ON p.PRODUCTID = o.PRODUCTID
INNER JOIN dbo.Account a
ON o.ACCOUNTID = a.ACCOUNTID
INNER JOIN dbo.Groups g
ON a.GROUPID = g.GROUPID
INNER JOIN dbo.Sales s
ON a.salesid = s.salesid
Where o.Type IN (0,3,4)
) AS Source
Group By SALESDESCRIPTION, GROUPDESCRIPTION, ACCOUNTNUMBER, ACCOUNTNAME,ORDERDATEANDTIME,TYPE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2017 at 2:52 am
Thank you for your help Chris.
It's nearly there but it's still giving me months I didn't ask for.
If I choose October 2016 to March 2017 it gives all the months of the year across the top of the report, giving both 2017 and 2016 figures for Feb and March and 2016 figures for the rest of the year.
Thanks
Paul.
March 27, 2017 at 3:37 am
paul 69259 - Monday, March 27, 2017 2:52 AMThank you for your help Chris.It's nearly there but it's still giving me months I didn't ask for.
If I choose October 2016 to March 2017 it gives all the months of the year across the top of the report, giving both 2017 and 2016 figures for Feb and March and 2016 figures for the rest of the year.
Thanks
Paul.
No problem Paul.
Here's a little test harness to show how filtration on YEARMONTH should work - assuming that it's the filtration which isn't working:
-- Create a sample date range on the fly
-- 2014-10-09 ... to 2017-07-04 ...
;WITH SampleDateRange AS (
SELECTMyDate = DATEADD(DAY,100-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())FROM
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d3 (n)
)
-- check if YEAR+MONTH accurately filters the date range
SELECT *
FROM SampleDateRange
CROSS APPLY (SELECT YearMonth =CONVERT(CHAR(6),MyDate,112)) x
WHERE YearMonth BETWEEN '201610' AND '201703'
ORDER BY MyDate
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2017 at 4:00 am
Your's works fine, I think the problem with mine is when I am getting year month it's not putting a zero between months 1 to 9.
For example March 2016 is getting stored as 20163 instead of 201603 and so on.....
I am putting mm in the code so it should be putting the zero in shouldn't it?
datepart(mm,ORDERDATEANDTIME))
((
March 27, 2017 at 4:11 am
It's ok, I got it working now. I used your code to get the yearmonth (Year_Month = CONVERT(CHAR(6),ORDERDATEANDTIME,112), instead of concatenating the year and month
Thanks again Chris. I owe you some beers .
March 27, 2017 at 4:16 am
No, it returns an INT and there's nothing in the expression to left-pad with a '0'.
That's easy enough to fix. More important is this - if you filter on an expression as you are now, your query performance will likely suffer.
Your best bet is to convert the range start and range end from your report control into an actual start and end date, and use them to filter ORDERDATEANDTIME. I'm no SSRS expert. If you get stuck, someone will help you out.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2017 at 4:35 am
Here's another little test harness to show you what I mean:
IF OBJECT_ID('TempDB..#SampleDateRange') IS NOT NULL DROPTABLE #SampleDateRange;
SELECT MyDate = DATEADD(DAY,100-ROW_NUMBER() OVER (ORDERBY (SELECT NULL)),GETDATE())
INTO #SampleDateRange
FROM
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))d1 (n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))d2 (n),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))d3 (n)
CREATE UNIQUE CLUSTERED INDEX ucx_Stuff ON#SampleDateRange (MyDate)
-- two report variables
DECLARE @YearMonthStart CHAR(6) = '201611', @YearMonthEndCHAR(6) = '201701'
-- Scans through the whole table reading 1000 rows and
-- converting MyDate into YearMonth to compare withreport variables.
-- Throws away all except 92 rows.
SELECT *
FROM #SampleDateRange
CROSS APPLY (SELECT YearMonth =CONVERT(CHAR(6),MyDate,112)) x
WHERE YearMonth BETWEEN @YearMonthStart AND @YearMonthEnd
ORDER BY MyDate
-- 92 seeks on MyDate.
SELECT *
FROM #SampleDateRange
CROSS APPLY (SELECT YearMonth =CONVERT(CHAR(6),MyDate,112)) x
WHERE MyDate >= CONVERT(DATE,@YearMonthStart+'01',112)AND MyDate < DATEADD(MONTH,1,CONVERT(DATE,@YearMonthEnd+'01',112))
ORDER BY MyDate
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2017 at 5:26 am
I am confused now.
March 27, 2017 at 7:05 am
paul 69259 - Monday, March 27, 2017 5:26 AMI am confused now.
We can't have that, Paul. Which part is confusing?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2017 at 8:31 am
The whole second script you sent. I am unsure what is happening in that.
I have altered the view to contain Year_Month with this line
Year_Month = CONVERT(CHAR(6),ORDERDATEANDTIME,112),
Then in the report, to make it a bit more user friendly I have set parameters of startmonth, startyear, endmonth and endyear
Then in the query I concatenate the two together to filter the report like so
FROM
QuartzReport1 where
QuartzReport1.Year_Month >= concat (@StartYear,@StartMonth)and
QuartzReport1.Year_Month <= concat (@EndYear,@EndMonth)
everything seems to work ok. Are you saying it will miss some dates?
March 27, 2017 at 8:43 am
paul 69259 - Monday, March 27, 2017 8:31 AMThe whole second script you sent. I am unsure what is happening in that.I have altered the view to contain Year_Month with this line
Year_Month = CONVERT(CHAR(6),ORDERDATEANDTIME,112),Then in the report, to make it a bit more user friendly I have set parameters of startmonth, startyear, endmonth and endyear
Then in the query I concatenate the two together to filter the report like so
FROM
QuartzReport1 where
QuartzReport1.Year_Month >= concat (@StartYear,@StartMonth)and
QuartzReport1.Year_Month <= concat (@EndYear,@EndMonth)everything seems to work ok. Are you saying it will miss some dates?
That looks fine to me Paul, in terms of accuracy.
However, Year_Month isn't a column in the table, it's calculated from date. There's no direct link to the date, which means the report cannot benefit from a filter on date. The consequence could be less than optimal performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2017 at 8:49 am
I see, you mean the performance may be compromised.
There is not a whole lot of data in the database and it works fine as it is. i'll leave it at that for the time being.
Thank you very much for your help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply