May 24, 2017 at 8:18 pm
I am new to SSRS Reports. I have a stored procedure to extract all the items that have been invoiced for less than 14.9% GP based on the salesman parameter.
ALTER PROCEDURE [dbo].[sp_Lessthan15GPAlert]
-- Add the parameters for the stored procedure here
@salesrep varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT distinct
oh.CustomerCode
, c.CustomerName
, ohl.ProductCode
, ohl.ProductDescription1
, ohl.ProductDescription2
, oh.InvoiceNumber
, CONVERT(VARCHAR(10), oh.InvoiceDate,126) InvoiceDate
, ((ohl.NetPrice-ohl.AccountingCost)/ohl.NetPrice) * 100 AS GP
, OHL.PriceSourceCode
, OH.SellingWarehouseNumber
, s.description
FROM OrderHistory oh
inner join orderhistoryline ohl on oh.CompanyID = ohl.CompanyID and oh.OrderNumber = ohl.OrderNumber
inner join Customer c on c.CompanyID = oh.companyid and c.customercode = oh.CustomerCode
inner join salesman s on s.companyid = c.CompanyID and s.SalesmanID = c.SalesRepOne
WHERE oh.CompanyID = '002'
and oh.InvoiceDate = CONVERT(varchar(10),dateadd(d,-4,GetDate()),126)
AND ((ohl.NetPrice - ohl.AccountingCost)/ohl.NetPrice) * 100 < 14.9
and ohl.NetPrice <> 0.00
AND OHL.PRICESOURCECODE NOT LIKE 'M%'
and s.description like @salesrep
AND COALESCE(OHL.ShippedQuantity,0) <> 0
END
The description column in salesman table has sample data as
R02 702 SMITH, JOHN TSR
R07 707 FRANKLIN, ROB TSR
R04 704 SCOTT, KEVIN TSR
R04 704 PAUL, KEITH RSM
R02 702 JOSEPH, MARY TSR
R04 704 CRIPPS, ROBERT TSR
Keith Paul manages two locations both 702 and 704.
I have set up available values for parameter @salesrep as follows
| Label | Value
%SMITH%JOHN% | %SMITH%JOHN%
%FRANKLIN%ROB% | %FRANKLIN%ROB%
%SCOTT%KEVIN% | %SCOTT%KEVIN%
%PAUL%KEITH% | %PAUL%KEITH%
% | %
L702 | R02%702%
L707 | R07%707%
L704 | R04%704%
Now, I can run the report for each salesman and each region but I also want to run for each RSM (Regional Sales Manager). Suppose I want to run everything under Keith Paul. How can I add one more in available values for parameter such as
Label | Value
Keith Paul | R02%702%, R04%704%
So I would get results both from 702 and 704. How can I achieve this?
R02 702 SMITH, JOHN TSR
R04 704 SCOTT, KEVIN TSR
R04 704 PAUL, KEITH RSM
R02 702 JOSEPH, MARY TSR
R04 704 CRIPPS, ROBERT TSR
May 25, 2017 at 5:37 am
normalize your salesman table, first. you shouldn't have multiple concepts in one column. Then you'd have to check to see whether you're searching for a manager level or sales rep level in the parameters (separate parameters, I would think) and modify the query appropriately to handle both.
maybe as is, you could do a check first to see if you can find the " RSM" where s.description=@salesrep, and extract the codes you're looking for by parsing the s.description column and using that in a variable to build your search for the actual results?
However, from your sample data it looks like Keith Paul is only listed on location 702 in the data, and the only way you're tying to both is in the parameter you built.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply