Hello All,
Hit a bit of a curly one this week. Hoping one of you clever people can help me.
I have created a crosstab query to display company sales data and managed to find a tip on the net about how to fill in blanks with Zero's (where we sold nothing that month). This works great however my report user now wants to change the report slightly so that when we launch new products, the months prior to launch don't display 0's but are left blank. Subsequent to 'launch' any months that have zero sales need to then display zero's.
So it would look like the below for a product introduced in April.
Month| Jan| Feb| Mar| Apr| May| June| July|
Product A| | |10| 15| 0| 18|
The current SQL of the query producing the report where all blanks are currently filled in with zero's is below.
Any ideas on how to make this work?
Thanks
TRANSFORM nz(Sum([X: Data Export DP Sales Qry].[Total Case Qty]))+0 AS [SumOfTotal Case Qty]
SELECT [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product, [X: Data Export DP Sales Qry].[SKU Description] AS description, [X: Data Export DP Sales Qry].[First Month of Sale]
FROM [X: Data Export DP Sales Qry]
GROUP BY [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product, [X: Data Export DP Sales Qry].[SKU Description], [X: Data Export DP Sales Qry].[First Month of Sale]
ORDER BY [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product
PIVOT [X: Data Export DP Sales Qry].[MM-YYYY (Calendar)];
Hit a bit of a curly one this week. Hoping one of you clever people can help me.
I have created a crosstab query to display company sales data and managed to find a tip on the net about how to fill in blanks with Zero's (where we sold nothing that month). This works great however my report user now wants to change the report slightly so that when we launch new products, the months prior to launch don't display 0's but are left blank. Subsequent to 'launch' any months that have zero sales need to then display zero's.
So it would look like the below for a product introduced in April.
Month| Jan| Feb| Mar| Apr| May| June| July|
Product A| | |10| 15| 0| 18|
The current SQL of the query producing the report where all blanks are currently filled in with zero's is below.
Any ideas on how to make this work?
Thanks
TRANSFORM nz(Sum([X: Data Export DP Sales Qry].[Total Case Qty]))+0 AS [SumOfTotal Case Qty]
SELECT [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product, [X: Data Export DP Sales Qry].[SKU Description] AS description, [X: Data Export DP Sales Qry].[First Month of Sale]
FROM [X: Data Export DP Sales Qry]
GROUP BY [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product, [X: Data Export DP Sales Qry].[SKU Description], [X: Data Export DP Sales Qry].[First Month of Sale]
ORDER BY [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product
PIVOT [X: Data Export DP Sales Qry].[MM-YYYY (Calendar)];