Need to be written comment like "Lost"

  • 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.
  • Can you share the query used in the dataset of the report?

  • 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.
  • 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 6 days, 18 hours ago by  Chrissy321.
  • 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?

  • 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.

  • 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."

     

     

  • 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.

  • 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())

    ,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

  • Make your life easier.  Create a stored procedure in the database (or send the query to whoever is allowed to do it) , and then create a dataset in SSRS and point to the Stored Procedure.  then your data complexity stays in the database and doesn't spill into SSRS, which is for presentation.

    "I'm [not] Kent Brockman, [but] that's my two cents."

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

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