Grouping data based on expression using CountDistinct aggregate function

Question

I am newbie to Stack overflow and also SQL server reporting services. So please excuse me for the format of the question.

So here is the situation:

I am developing a SSRS report which needs to be grouped by an Count of Distinct product names as shown below.

I created a text box called ProdCount with an expression

COUNTDISTNCT(Fields!Product.value,"DataSet1") 

which gives me the count 63 within the scope of DataSet1.

Now i need to group the data by taking product names where the above formula is >1 .

=IIF(ProdCount>1,Fields!Product.value,Nothing)

My Problem:

1. I tried to call the ProdCount from the calculated field since i cant use the aggregate functions in Calculated Fields and use the second expression by using

   = ReportItems!ProdCount.value

   which gives me an error FieldValue Denying ReportItems

2. I tried to combine the above two expressions by creating a calculated field by

   IIF(CountDistinct(Fields!Product.Value,"DataSet1")>1,Fields!Product.Value,Nothing)
   which gives me an error Calculated fields cannot have expressions

3. I tried to use Report Variables in the same way as above(1) which was not working either.

4. I also tried to use CROSS JOIN in the query

Select Count(Distinct(Product Name)
from Query1
Cross join
My Main Query which give me the data

which is taking more time to execute.

So Can anyone help me with solution where i can group the data by combining the above two expressions.

Please excuse me for the format. I was confused with framing question. I accept all your edits , so that i can learn in future.

Here is my code:

    SELECT * FROM 

--Query1 which counts the number of distinct products) 
(SELECT DISTINCT COUNT(gproduct.ProductName) AS ProdCount
 FROM Table1 
 LEFT JOIN Table4
  ON Table1.column=Table1.column
 LEFT JOIN Table2
  ON Table3.Column = TTable1.Column
 LEFT JOIN
 (
    SELECT Distinct Table6.Name AS ProductName,Table9.ColumnId
    FROM Table6
    INNER JOIN Table7
     ON Table6.Column=Table7.Column
    INNER JOIN Table8
     ON Table7.Column=Table8.Column
    INNER JOIN Table9 
     ON Table9.Column=Table8.Column
 )gproduct
  ON Table1.ColumnId=gproduct.ColumnId
 GROUP BY gproduct.ColumnId,
)qProduct
CROSS JOIN
--My main Query which get data from different table including Product name
(SELECT

       Upper(CASE WHEN (CASE WHEN Table4.Column =1 THEN 'Yes' ELSE 'NO' END)='YES' 
           THEN gab.ProductName
      ELSE
           Table2.productName
      END) AS Product, 
FROM Table1 AS ec
LEFT JOIN Table2 AS ep 
  ON --
LEFT JOIN Table3 AS ebrd
 ON --
Left JOIN Table4 AS etpc
  ON --
LEFT  JOIN Table5 AS gst 
  ON --
LEFT JOIN
( 
 SELECT Distinct Table6.Name AS ProductName,Table9.ColumnId
    FROM Table6
    INNER JOIN Table7
     ON Table6.Column=Table7.Column
    INNER JOIN Table8
     ON Table7.Column=Table8.Column
    INNER JOIN Table9 
     ON Table9.Column=Table8.Column
) gab
ON Table1.ColumnId=gab.ColumnId
)QMain

Answer 1

Personally I would try to solve the problem in query itself instead of SSRS report. According the data you provided it would be something like:

SELECT
ProductName,
count(distinct Product)
from
YourTable
group by 
ProductName
having count(distinct product) > 1

Later on creating SSRS report should be quite easy.