none
How can i make an adjacent right column row, only show one row value?

    Question


  • Product

    Total

    Adjacent 1

    Adjacent 2

    A

    40

     

    40

    B

    420

    65

     

    B

    420

     

    355


    Hi All...

    Please, can someone help me with this report. I'm stuck about one month.

    The question is:

    How can the 65 & 355 value can be showed in only one row? As you can see, the value 420 is the total of 65 and 355 value.

    The client want this only in one row, so grouping and hide duplicates is not the solution i'm looking for.

    Kind Regards.


    Wednesday, October 03, 2012 8:55 AM

Answers

  • Hi Gelarossi,

    Thank you for posting the detailed information about the report.

    According to the screenshots, the "shift" field in placed in a details row. In this case, it is impossible to put the "65" value and the "355" value in the same row because they are corresponding to differe "shift" values. To achieve your goal, we need to make the tablix group on the "shift" field. To do this, we can use a matrix instead and place the "shift" field in the second row of the first column. After that, please right click the first column and insert two "Inside Group - Left" columns to place the "DIES" and "PRODUCT" field respectively.

    If you have any questions, please feel free to ask.

    Regards,
    Mike Yin


    Mike Yin

    TechNet Community Support

    Wednesday, October 10, 2012 9:13 AM
    Moderator

All replies

  • Hello gellorasi,

     Could you please provide more inputs on your underlying table structure and data set query you are using in the report for further analysis ?

    Best Regards

    Sorna

    Wednesday, October 03, 2012 4:34 PM
  • Hi Sorna,

    Sorry for my late replies.

    Here is the Query SQL :

    SELECT TOP 10
    	A.process,
    	A.area,
    	A.line,
    	A.product,
    	A.dies_id,
    	A.shift,
    	A.planned_downtime,
    	B.downtime_duration,
    	B.downtime_description
    FROM
    	(SELECT
    		a.process,
    		a.area,
    		a.line,
    		a.product,
    		a.dies_id,
    		a.shift,
    		a.working_time,
    		CONVERT(FLOAT, a.man_power) AS man_power,
    		a.planned_downtime,	
    		a.loading_time,
    		a.linestop_time,
    		a.ls_brkdwn_machine,
    		a.ls_dies_problem,
    		a.ls_process_problem,
    		a.ls_supply_material,
    		a.ls_others,
    		a.actual_oper_time,
    		a.std_oper_time,
    		a.actual_production,
    		a.std_time_cycle,
    		ISNULL(a.material_yield, 0) AS material_yield,
    		a.ok_qty,
    		a.product_spec,
    		ISNULL(a.material_usage, 0) AS material_usage,
    		a.okqty_spec,
    		a.loss_time,
    		ISNULL(CONVERT(FLOAT, a.planned_dt_rate), 0) AS planned_dt_rate,
    		ISNULL(CONVERT(FLOAT, a.ls_time_rate), 0) AS ls_time_rate,
    		ISNULL(CONVERT(FLOAT, a.man_hour_idx), 0) AS man_hour_idx,
    		ISNULL(CONVERT(FLOAT, a.efficiency_rate_load), 0) AS efficiency_rate_load,
    		ISNULL(CONVERT(FLOAT, a.efficiency_rate_work), 0) AS efficiency_rate_work,
    		ISNULL(CONVERT(FLOAT, a.quality_problem), 0) AS quality_problem,
    		a.rejection,
    		a.rework,
    		a.prod_plan,
    		ISNULL(a.total_shot, 0) AS total_shot
    	FROM
    		OPERATIONAL_REPORT AS a
    	WHERE
    		a.period = 'd'
    		AND a.bu_id = 'nm'
    		--AND a.process IN (@process)
    		AND (a.date BETWEEN ('6/1/2012') AND ('6/1/2012'))
    	) AS A
    	INNER JOIN
    		(
    			SELECT 
    				i.process,
    				i.line_id,
    				h.dies_id,
    				h.shift,
    				l.product_sap_id,
    				SUM(FLOOR(ISNULL(j.downtime_duration*ratio,0))) AS downtime_duration,
    				k.downtime_description
    			FROM
    				operational AS h,
    				line AS i,
    				downtime_evt AS j,
    				downtime AS k,
    				oper_has_product AS l
    			WHERE
    				h.line_id = i.line_id
    				and h.prod_row_id = l.prod_row_id
    				and h.bu_id = l.bu_id
    				and h.prod_row_id = j.prod_row_id
    				and j.downtime_id = k.downtime_id
    				and k.planned = 'Y' and k.downtime_category = 'Planned Downtime'
    				and h.division = 'production'
    				and h.production_date BETWEEN ('6/1/2012') AND ('6/1/2012')
    			GROUP BY
    				h.production_date,
    				i.process,
    				i.area,
    				i.line_id,
    				l.product_sap_id,
    				k.downtime_description,
    				h.bu_id,
    				h.dies_id,
    				h.shift
    			) B
    		ON A.product = B.product_sap_id
    		AND A.process = B.process
    		AND A.line = B.line_id
    		AND A.dies_id = B.dies_id
    		AND A.shift = B.shift

    The capture result of that query is:

    Query Result

    The capture result of Report is:

    Report Result

    The problem which i asked is, The value 420 of the Planned Downtime column, i want to be displaed as one row, and the adjacent right column to...


    • Edited by Gelarossi Friday, October 05, 2012 4:33 AM Forgot
    Friday, October 05, 2012 4:31 AM
  • Hello Gelarossi

    In the first post as you have mentioned.

    there are two rows of data for 'B' value.

    To achieve this scenario, you need to first group on the Product value on table control used in the report design with which you can avoid the mutliple rows of blank data.

    let me know if any other issues.

    Cheers,

    Arun Gangumalla

    Tuesday, October 09, 2012 7:00 AM
  • Hi Gelarossi,

    Thank you for posting the detailed information about the report.

    According to the screenshots, the "shift" field in placed in a details row. In this case, it is impossible to put the "65" value and the "355" value in the same row because they are corresponding to differe "shift" values. To achieve your goal, we need to make the tablix group on the "shift" field. To do this, we can use a matrix instead and place the "shift" field in the second row of the first column. After that, please right click the first column and insert two "Inside Group - Left" columns to place the "DIES" and "PRODUCT" field respectively.

    If you have any questions, please feel free to ask.

    Regards,
    Mike Yin


    Mike Yin

    TechNet Community Support

    Wednesday, October 10, 2012 9:13 AM
    Moderator