September 1, 2015 at 10:35 am
Hi,
I use SAP Crystal Report V.14 and SS2012, my task is to build a report/pivot with CR for the last 12 months. As I read in several forums it seems an efficient way to first pivot in SS2012 and then create the CR-report on this SP or view.
The columns are like this:
artnrcustomer custno status LastResponse
10007 cust1 2544 St1 20150824
33139 cust2 5173 St1 20150801
33139 cust3 667 St3 20150717
11764 cust4 1746 St4 20150901
1. Pivot like this
[font="Arial"]
09 10 11 12 01 02 03 04 05 06 07 08
St1 2
St3 1
[/font]
rows: status
columns: the last 12 months
pivot: count
2. The second step would be to
* parameter the CR for current date
* get the last 12 months
As I'm new to CR has anyone a hint for me?
--
candide
________Panta rhei
September 2, 2015 at 9:03 pm
candide (9/1/2015)
Hi,I use SAP Crystal Report V.14 and SS2012, my task is to build a report/pivot with CR for the last 12 months. As I read in several forums it seems an efficient way to first pivot in SS2012 and then create the CR-report on this SP or view.
The columns are like this:
artnrcustomer custno status LastResponse
10007 cust1 2544 St1 20150824
33139 cust2 5173 St1 20150801
33139 cust3 667 St3 20150717
11764 cust4 1746 St4 20150901
1. Pivot like this
[font="Arial"]
09 10 11 12 01 02 03 04 05 06 07 08
St1 2
St3 1
[/font]
rows: status
columns: the last 12 months
pivot: count
2. The second step would be to
* parameter the CR for current date
* get the last 12 months
As I'm new to CR has anyone a hint for me?
Pivoting in SQL is easy enough IF you have a fixed number of pivot columns. If you need this to be a dynamic pivot, you'll need to do that in CR.
It's been a few years since I touched CR (the current version was CRXI the last time I touched it), but I don't recall pivots being a challenge. Just do a Goolge search on "cross tabs in crystal reports".
September 4, 2015 at 8:36 am
candide (9/1/2015)
Hi,I use SAP Crystal Report V.14 and SS2012, my task is to build a report/pivot with CR for the last 12 months. As I read in several forums it seems an efficient way to first pivot in SS2012 and then create the CR-report on this SP or view.
The columns are like this:
artnrcustomer custno status LastResponse
10007 cust1 2544 St1 20150824
33139 cust2 5173 St1 20150801
33139 cust3 667 St3 20150717
11764 cust4 1746 St4 20150901
1. Pivot like this
[font="Arial"]
09 10 11 12 01 02 03 04 05 06 07 08
St1 2
St3 1
[/font]
rows: status
columns: the last 12 months
pivot: count
2. The second step would be to
* parameter the CR for current date
* get the last 12 months
As I'm new to CR has anyone a hint for me?
Here's a dynamic PIVOT for you:
DECLARE @TD AS date = GETDATE();
DECLARE @sql AS varchar(max);
DECLARE @PIVOT_LIST AS varchar(200) = '';
CREATE TABLE #DATA (
artnr int,
customer varchar(20),
custno int,
[status] char(3),
LastResponse date
);
INSERT INTO #DATA (artnr, customer, custno, [status], LastResponse)
VALUES (10007, 'cust1', 2544, 'St1', '20150824'),
(33139, 'cust2', 5173, 'St1', '20150801'),
(33139, 'cust3', 667, 'St3', '20150717'),
(11764, 'cust4', 1746, 'St4', '20150901');
CREATE TABLE #Tally (
GROUPING_DATE date
);
WITH Tally AS (
SELECT DATEADD(day, 1 - DATEPART(DAY, @TD), @TD) AS GROUPING_DATE
UNION ALL
SELECT DATEADD(month, -1, GROUPING_DATE)
FROM Tally
WHERE DATEADD(month, -1, GROUPING_DATE) >= DATEADD(month, -11, DATEADD(day, 1 - DATEPART(DAY, @TD), @TD))
)
INSERT INTO #Tally (GROUPING_DATE)
SELECT *
FROM Tally;
SELECT @PIVOT_LIST = @PIVOT_LIST + '[' + LEFT(REPLACE(CONVERT(char(10), GROUPING_DATE, 112), '-', ''), 6) + '],'
FROM #Tally
ORDER BY GROUPING_DATE;
--PRINT @PIVOT_LIST;
--DROP TABLE #Tally
SET @sql =
'WITH ALL_GROUPS AS (
SELECT LEFT(REPLACE(CONVERT(char(10), T.GROUPING_DATE, 112), ''-'', ''''), 6) AS GROUPING_DATE, D.[status]
FROM #Tally AS T,
(SELECT DISTINCT [status] FROM #DATA) AS D
),
SELECTED_DATA AS (
SELECT X.[status], X.GROUPING_DATE, COUNT(*) AS ROW_COUNT
FROM (
SELECT D.[status], D.LastResponse, LEFT(REPLACE(CONVERT(char(8), DATEADD(day, 1 - DATEPART(day, D.LastResponse), D.LastResponse), 112), ''-'', ''''), 6) AS GROUPING_DATE
FROM #DATA AS D
) AS X
GROUP BY X.[status], X.GROUPING_DATE
),
GROUPED_DATA AS (
SELECT DISTINCT SD.[status], T.GROUPING_DATE, ISNULL(SD.ROW_COUNT, 0) AS ROW_COUNT
FROM ALL_GROUPS AS T
LEFT OUTER JOIN SELECTED_DATA AS SD
ON T.GROUPING_DATE = SD.GROUPING_DATE
)
SELECT [status], ' + LEFT(@PIVOT_LIST, LEN(@PIVOT_LIST) - 1) + '
FROM GROUPED_DATA
PIVOT (SUM(ROW_COUNT) FOR GROUPING_DATE IN (' + LEFT(@PIVOT_LIST, LEN(@PIVOT_LIST) - 1) + ')) AS PVT
WHERE [status] IS NOT NULL;';
PRINT @sql;
EXEC (@SQL);
DROP TABLE #Tally;
DROP TABLE #DATA;
Let me know if that works for you.
EDIT: fixed the number of months of data this generates down to 12 from 13, which I discovered after doing the other fix. This edit just fixes the original post to at least do 12 months of data instead of 13.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 4, 2015 at 9:03 am
Unless CR has changed drastically since the last time I used it, it's not going to be able to do anything with the ever changing set of input columns provided by a dynamic pivot.
September 4, 2015 at 9:21 am
Jason A. Long (9/4/2015)
Unless CR has changed drastically since the last time I used it, it's not going to be able to do anything with the ever changing set of input columns provided by a dynamic pivot.
You're absolutely right. You can create custom column headers easily enough, but the columns themselves need a consistent name, so here's the fixed query:
DECLARE @TD AS date = GETDATE();
DECLARE @sql AS varchar(max);
DECLARE @PIVOT_LIST AS varchar(200) = '';
DECLARE @SELECT_LIST AS varchar(400) = '';
CREATE TABLE #DATA (
artnr int,
customer varchar(20),
custno int,
[status] char(3),
LastResponse date
);
INSERT INTO #DATA (artnr, customer, custno, [status], LastResponse)
VALUES (10007, 'cust1', 2544, 'St1', '20150824'),
(33139, 'cust2', 5173, 'St1', '20150801'),
(33139, 'cust3', 667, 'St3', '20150717'),
(11764, 'cust4', 1746, 'St4', '20150901');
CREATE TABLE #Tally (
GROUPING_DATE date
);
WITH Tally AS (
SELECT DATEADD(day, 1 - DATEPART(DAY, @TD), @TD) AS GROUPING_DATE
UNION ALL
SELECT DATEADD(month, -1, GROUPING_DATE)
FROM Tally
WHERE DATEADD(month, -1, GROUPING_DATE) >= DATEADD(month, -11, DATEADD(day, 1 - DATEPART(DAY, @TD), @TD))
)
INSERT INTO #Tally (GROUPING_DATE)
SELECT *
FROM Tally;
SELECT @PIVOT_LIST = @PIVOT_LIST + '[' + LEFT(REPLACE(CONVERT(char(10), GROUPING_DATE, 112), '-', ''), 6) + '],'
FROM #Tally
ORDER BY GROUPING_DATE;
SELECT @SELECT_LIST = @SELECT_LIST + '[' + LEFT(REPLACE(CONVERT(char(10), GROUPING_DATE, 112), '-', ''), 6) + '] AS DT' +
CAST(ROW_NUMBER() OVER(ORDER BY GROUPING_DATE) AS varchar(2)) + ','
FROM #Tally
ORDER BY GROUPING_DATE;
SET @sql =
'WITH ALL_GROUPS AS (
SELECT LEFT(REPLACE(CONVERT(char(10), T.GROUPING_DATE, 112), ''-'', ''''), 6) AS GROUPING_DATE, D.[status]
FROM #Tally AS T,
(SELECT DISTINCT [status] FROM #DATA) AS D
),
SELECTED_DATA AS (
SELECT X.[status], X.GROUPING_DATE, COUNT(*) AS ROW_COUNT
FROM (
SELECT D.[status], D.LastResponse, LEFT(REPLACE(CONVERT(char(8), DATEADD(day, 1 - DATEPART(day, D.LastResponse), D.LastResponse), 112), ''-'', ''''), 6) AS GROUPING_DATE
FROM #DATA AS D
) AS X
GROUP BY X.[status], X.GROUPING_DATE
),
GROUPED_DATA AS (
SELECT DISTINCT SD.[status], T.GROUPING_DATE, ISNULL(SD.ROW_COUNT, 0) AS ROW_COUNT
FROM ALL_GROUPS AS T
LEFT OUTER JOIN SELECTED_DATA AS SD
ON T.GROUPING_DATE = SD.GROUPING_DATE
)
SELECT [status], ' + LEFT(@SELECT_LIST, LEN(@SELECT_LIST) - 1) + '
FROM GROUPED_DATA
PIVOT (SUM(ROW_COUNT) FOR GROUPING_DATE IN (' + LEFT(@PIVOT_LIST, LEN(@PIVOT_LIST) - 1) + ')) AS PVT
WHERE [status] IS NOT NULL;';
PRINT @sql;
EXEC (@SQL);
DROP TABLE #Tally;
DROP TABLE #DATA;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 4, 2015 at 10:04 am
Hey Steve,
I ran your code and it looks fine:-)
Just one thing for script#2: the number of 12 columns is now correct, but the right and last column (DT12) should be for the date/month 201508 not 201509.
--
candide
________Panta rhei
September 4, 2015 at 10:20 am
Hi,
I posted also in a CR-forum and there's another idea which I think maybe more flexible as I can use the CR-crosstab feature with no restriction (?).
As posted here and here I could create a temp-table with all the 12 months I need as the crosstab-columns, make a JOIN to the database-table and return the result from a StoredProc to CR. So even if my database table has no rows for one month in the CR-crosstab I would see a column with the pivot-value of 0.
That's what I was trying to code before Steve posted his script. Just thinking about...
--
Tried to change the output column names from DT1, DT2 to DT01, DT02 etc. Only the ROW_NUMBER() is replaced
CAST( ROW_NUMBER() OVER(ORDER BY GROUPING_DATE) AS varchar(2)) + ','
to
CAST( Right('0' + convert(varchar(2), ROW_NUMBER() ),2) OVER(ORDER BY GROUPING_DATE) AS varchar(2)) + ','
but there's an issue.
I need the column names with leading 0 for correct sort order in the CR report.
Am I right that the output table cannot be used for CR crosstable but just display simple fields?
--
candide
________Panta rhei
September 5, 2015 at 1:42 pm
candide (9/4/2015)
Hi,I posted also in a CR-forum and there's another idea which I think maybe more flexible as I can use the CR-crosstab feature with no restriction (?).
As posted here and here I could create a temp-table with all the 12 months I need as the crosstab-columns, make a JOIN to the database-table and return the result from a StoredProc to CR. So even if my database table has no rows for one month in the CR-crosstab I would see a column with the pivot-value of 0.
That's what I was trying to code before Steve posted his script. Just thinking about...
--
Tried to change the output column names from DT1, DT2 to DT01, DT02 etc. Only the ROW_NUMBER() is replaced
CAST( ROW_NUMBER() OVER(ORDER BY GROUPING_DATE) AS varchar(2)) + ','to
CAST( Right('0' + convert(varchar(2), ROW_NUMBER() ),2) OVER(ORDER BY GROUPING_DATE) AS varchar(2)) + ','but there's an issue.
I need the column names with leading 0 for correct sort order in the CR report.
Am I right that the output table cannot be used for CR crosstable but just display simple fields?
Your code to fix the field names isn't quite right, as the ROW_NUMBER() function needs its OVER clause directly following it, and you interrupted it with last part of the RIGHT() function. As to using a cross-tab within Crystal, my report makes that unnecessary, and no, you wouldn't want to use it with the data from my query. However, you WILL have to do some programming in Crystal to create your column headers, as those will need to be dynamic, regardless of what kind of Crystal Report you have. Here's an updated query that also fixes the selected months to be the 12 PREVIOUS months, NOT including the current month:
DECLARE @TD AS date = DATEADD(month, -1, GETDATE());
DECLARE @sql AS varchar(max);
DECLARE @PIVOT_LIST AS varchar(200) = '';
DECLARE @SELECT_LIST AS varchar(400) = '';
CREATE TABLE #DATA (
artnr int,
customer varchar(20),
custno int,
[status] char(3),
LastResponse date
);
INSERT INTO #DATA (artnr, customer, custno, [status], LastResponse)
VALUES (10007, 'cust1', 2544, 'St1', '20150824'),
(33139, 'cust2', 5173, 'St1', '20150801'),
(33139, 'cust3', 667, 'St3', '20150717'),
(11764, 'cust4', 1746, 'St4', '20150901');
CREATE TABLE #Tally (
GROUPING_DATE date
);
WITH Tally AS (
SELECT DATEADD(day, 1 - DATEPART(DAY, @TD), @TD) AS GROUPING_DATE
UNION ALL
SELECT DATEADD(month, -1, GROUPING_DATE)
FROM Tally
WHERE DATEADD(month, -1, GROUPING_DATE) >= DATEADD(month, -11, DATEADD(day, 1 - DATEPART(DAY, @TD), @TD))
)
INSERT INTO #Tally (GROUPING_DATE)
SELECT *
FROM Tally;
SELECT @PIVOT_LIST = @PIVOT_LIST + '[' + LEFT(REPLACE(CONVERT(char(10), GROUPING_DATE, 112), '-', ''), 6) + '],'
FROM #Tally
ORDER BY GROUPING_DATE;
SELECT @SELECT_LIST = @SELECT_LIST + '[' + LEFT(REPLACE(CONVERT(char(10), GROUPING_DATE, 112), '-', ''), 6) + '] AS DT' +
RIGHT('0' + CAST(ROW_NUMBER() OVER(ORDER BY GROUPING_DATE) AS varchar(2)), 2) + ','
FROM #Tally
ORDER BY GROUPING_DATE;
SET @sql =
'WITH ALL_GROUPS AS (
SELECT LEFT(REPLACE(CONVERT(char(10), T.GROUPING_DATE, 112), ''-'', ''''), 6) AS GROUPING_DATE, D.[status]
FROM #Tally AS T,
(SELECT DISTINCT [status] FROM #DATA) AS D
),
SELECTED_DATA AS (
SELECT X.[status], X.GROUPING_DATE, COUNT(*) AS ROW_COUNT
FROM (
SELECT D.[status], D.LastResponse, LEFT(REPLACE(CONVERT(char(8), DATEADD(day, 1 - DATEPART(day, D.LastResponse), D.LastResponse), 112), ''-'', ''''), 6) AS GROUPING_DATE
FROM #DATA AS D
) AS X
GROUP BY X.[status], X.GROUPING_DATE
),
GROUPED_DATA AS (
SELECT DISTINCT SD.[status], T.GROUPING_DATE, ISNULL(SD.ROW_COUNT, 0) AS ROW_COUNT
FROM ALL_GROUPS AS T
LEFT OUTER JOIN SELECTED_DATA AS SD
ON T.GROUPING_DATE = SD.GROUPING_DATE
)
SELECT [status], ' + LEFT(@SELECT_LIST, LEN(@SELECT_LIST) - 1) + '
FROM GROUPED_DATA
PIVOT (SUM(ROW_COUNT) FOR GROUPING_DATE IN (' + LEFT(@PIVOT_LIST, LEN(@PIVOT_LIST) - 1) + ')) AS PVT
WHERE [status] IS NOT NULL;';
PRINT @sql;
EXEC (@SQL);
DROP TABLE #Tally;
DROP TABLE #DATA;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 9, 2015 at 7:34 am
Hey Steve,
let me say your solution works - and this code is: artwork:-)
thx
--
candide
________Panta rhei
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply