Need to be written comment like "Lost"

Question

Post reply

Need to be written comment like "Lost"

vijay.verma2

SSC Enthusiast

Points: 182
More actions
January 19, 2023 at 2:41 pm

#4139577

Dear All,
I am new in SSRS, I created one report like the attached file. (Design, Output)
I want to add a comment on the last those customers who didn't purchase any item continue two months.
if any challenges in understanding pls let me know.

Attachments:
You must be logged in to view attached files.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

Can you share the query used in the dataset of the report?

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 2

I created a view.

select * from p21_sales_history_report_view_lost_customer

where

salesrep_id in (@SalesRep)

and columns are

select

customer.salesrep_id,

COALESCE(salesrep_contact.first_name, '')+' '+

COALESCE(salesrep_contact.last_name,'') as salesrep_info,

customer.customer_name,

customer.customer_id,

p21_view_invoice_line.qty_shipped,

invoice_hdr.invoice_date,

DATEPART(mm,invoice_date) as Month,

DATEPART (Year,invoice_hdr.invoice_date) as Year

Attachments:

You must be logged in to view attached files.

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

Here is a tsql technique where your dataset returns individual sales and a comment that tests whether a sale exists in the prior two months.

You'll need to adapt to your data structure.

DROP TABLE IF EXISTS #Sales

CREATE TABLE #Sales (
salesrep_id INT NOT NULL,
customer_id INT NOT NULL,
invoice_date DATE NOT NULL,
qty_shipped INT NOT NULL
)

INSERT INTO #Sales (salesrep_id,customer_id,invoice_date,qty_shipped) VALUES (1,1,'2023-01-20',20)
INSERT INTO #Sales (salesrep_id,customer_id,invoice_date,qty_shipped) VALUES (1,1,'2022-12-15',40)
INSERT INTO #Sales (salesrep_id,customer_id,invoice_date,qty_shipped) VALUES (1,1,'2022-11-01',40)

INSERT INTO #Sales (salesrep_id,customer_id,invoice_date,qty_shipped) VALUES (1,2,'2022-12-09',40)
INSERT INTO #Sales (salesrep_id,customer_id,invoice_date,qty_shipped) VALUES (1,2,'2022-11-08',40)

INSERT INTO #Sales (salesrep_id,customer_id,invoice_date,qty_shipped) VALUES (1,3,'2022-11-01',40)

SELECT 
*,
DATEDIFF(MONTH,MostRecentInvoiceDate,GETDATE()) AS MonthDifferenceCount,
CASE WHEN DATEDIFF(MONTH,MostRecentInvoiceDate,GETDATE())  >=2 THEN 'Customer has no purchaes in two months' END AS Comment
FROM #Sales S1
INNER JOIN
(
SELECT 
customer_id,
MAX(invoice_date) AS MostRecentInvoiceDate
FROM #Sales
GROUP BY
customer_id
) S2
ON 
S1.customer_id = S2.customer_id

This reply was modified 1 year, 10 months ago by Chrissy321.

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 4

Thanks for your response.

as per your query, do I need to create a temp table or it can be created with a normal table?

Chrissy321 SSCoach Points: 15763 More actions · Answer 5

The create table statements is a technique to generate sample data for the purpose of demonstrating the select query in the dataset. Typically the dataset select query will query directly against your permanent data tables. It is possible to use a temp table in a dataset if you need an intermediate data set to facilitate in a complicated query. I have never seen a permanent table created in a query, that is not advised. Try to adapt the select query to run against your existing permanent tables.

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 6

I wrote a query,

select *,DATEDIFF(MONTH,invoice_date,GETDATE())

,CASE WHEN DATEDIFF(MONTH,invoice_date,GETDATE()) >=2 THEN 'LOST CUSTOMER' END AS Comment

from p21_sales_history_report_view_lost_customer s1

inner join

(select customer_id,max(invoice_date) as MostRecentInvoiceDate from p21_sales_history_report_view_lost_customer

group by customer_id

) S2 on s2.customer_id= s1.customer_id

where salesrep_id='1036'

and DATEDIFF(MONTH,invoice_date,GETDATE()) >=2

it's working in SQL but in SSRS it says "An item with the same key has already been added."

Chrissy321 SSCoach Points: 15763 More actions · Answer 7

I suspect you are returning a duplicate columns. Do not use SELECT * except for testing, ad hoc types of queries. List all your columns explicitly.

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 8

This query works. A data set has been created. tnx

select salesrep_id,salesrep_info,customer_name,s2.customer_id,qty_shipped,invoice_date,Month,Year,datediff(MONTH,invoice_date,GETDATE())