February 9, 2018 at 7:32 am
I wrote this, and it works fine in SSMS. But i would prefer it in SSRS so the user can just run it and then open in Excel. I am wondering if i can port this to SSRS. I tried, it didn't work for me.
Thoughts?
USE GTM_ODS
SELECT
*
From
(SELECT
ISNULL(Line_item_Duty,0) as [DUTY],
[Supplier] as [SUPPLIER],
Datepart(MM,Entry_Summary_Date) as [MONTH]
from [ADHOC].[ATS_ESH] H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.trans_Sk
WHERE
H.Importer = 'FORD'
AND Entry_Summary_Date > '1/1/2018')
as Data
PIVOT
(
Sum([DUTY])
FOR
[Month]
IN ("01","02","03","04","05","06","07","08","09","10","11","12")
) AS JEFF
order by [01] Desc
February 9, 2018 at 9:00 am
"it didn't work for me"? What do you mean? Did you get an error, unexpected results? You didn't like what you saw?
Rather than using a SQL query to PIVOT your data and using that data as your dataset in SSRS, consider doing the pivoting in SSRS using a Matrix.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 9, 2018 at 9:01 am
jeffshelix - Friday, February 9, 2018 7:32 AMI wrote this, and it works fine in SSMS. But i would prefer it in SSRS so the user can just run it and then open in Excel. I am wondering if i can port this to SSRS. I tried, it didn't work for me.
Thoughts?
USE GTM_ODS
SELECT
*
From
(SELECT
ISNULL(Line_item_Duty,0) as [DUTY],
[Supplier] as [SUPPLIER],
Datepart(MM,Entry_Summary_Date) as [MONTH]
from [ADHOC].[ATS_ESH] H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.trans_Sk
WHERE
H.Importer = 'FORD'
AND Entry_Summary_Date > '1/1/2018')
as Data
PIVOT
(
Sum([DUTY])
FOR
[Month]
IN ("01","02","03","04","05","06","07","08","09","10","11","12")
) AS JEFF
order by [01] Desc
You could always put the SQL into a stored proc and use that as your SSRS source.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 9, 2018 at 9:17 am
Thom A - Friday, February 9, 2018 9:00 AM"it didn't work for me"? What do you mean? Did you get an error, unexpected results? You didn't like what you saw?Rather than using a SQL query to PIVOT your data and using that data as your dataset in SSRS, consider doing the pivoting in SSRS using a Matrix.
Without the IN() clause in the pivot, how do you force the existence of columns in the pivot? Use an outer join to a Tally table? Otherwise, if there are no results for a month, then the column will drop out of the pivot instead of showing zeroes.
February 9, 2018 at 9:29 am
pietlinden - Friday, February 9, 2018 9:17 AMThom A - Friday, February 9, 2018 9:00 AM"it didn't work for me"? What do you mean? Did you get an error, unexpected results? You didn't like what you saw?Rather than using a SQL query to PIVOT your data and using that data as your dataset in SSRS, consider doing the pivoting in SSRS using a Matrix.
Without the IN() clause in the pivot, how do you force the existence of columns in the pivot? Use an outer join to a Tally table? Otherwise, if there are no results for a month, then the column will drop out of the pivot instead of showing zeroes.
Yes, you'll need something to determine your groupings if there are no results, certainly. Using a Tally table, or Calendar Table (considering the OP's Pivot is on Month), would certainly solve that issue.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 9, 2018 at 9:58 am
jeffshelix - Friday, February 9, 2018 7:32 AMI wrote this, and it works fine in SSMS. But i would prefer it in SSRS so the user can just run it and then open in Excel. I am wondering if i can port this to SSRS. I tried, it didn't work for me.
Thoughts?
Yes, I'm wondering why people think that "it didn't work for me" is a suitable description of their problem. You're going to have to be a LOT more specific if you want help troubleshooting this problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 9, 2018 at 1:55 pm
If the end result is going to be used in an Excel spreadsheet anyway, would it make sense to not pivot the data in SQL, not use SSRS, and just have a query and pivot table in Excel that they can refresh the data on?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply