Query is returning rows but report is blank

[former_member650363]

Hi,

I have the following query in "Show SQL query"

SELECT "ITT1_1"."Code", "ITT1"."ChildNum", "ITT1"."Code", "OITW"."WhsCode", "OITW"."AvgPrice", "ITM1"."PriceList", "OITM"."ItemName"

FROM ((("SBK_IL_ACC"."dbo"."ITT1" "ITT1_1" INNER JOIN "SBK_IL_ACC"."dbo"."ITT1" "ITT1" ON "ITT1_1"."Father"="ITT1"."Father") INNER JOIN "SBK_IL_ACC"."dbo"."OITM" "OITM" ON "ITT1_1"."Father"="OITM"."ItemCode") INNER JOIN "SBK_IL_ACC"."dbo"."ITM1" "ITM1" ON "ITT1_1"."Father"="ITM1"."ItemCode") INNER JOIN "SBK_IL_ACC"."dbo"."OITW" "OITW" ON "ITT1"."Code"="OITW"."ItemCode"

WHERE "ITT1_1"."Code"=N'500005' AND "ITT1"."Code"<>N'500005' AND "OITW"."AvgPrice"<>0 AND "OITW"."WhsCode" LIKE N'HO%%%%' AND "ITM1"."PriceList"=1 AND "ITT1"."ChildNum"=1

The O/P is :

500005 1 9000073 HOMFG 966.01 ; 1 Synthetic 5LTR

500005 1 9000047 HOMFG 875.51 ; 1 Synthetic 4LTR

500005 1 9000021 HOMFG 361.68 ; 1 Synthetic 1LTR

500005 1 9000024 HOMFG 243.32 ; 1 Synthetic 0.5LTR

500005 1 9000048 HOMFG 158.25 ; 1 Synthetic 0.2LTR

But when I pull any data I am not able to see it, the report is showing blank. Any pointers would help me.

[Former Member · Accepted solution]

Make sure

1. there is not an additional Crustal filter in select expert which is removing data locally

2. There is no suppression formula in section expert hiding data.

3. Group selection filter hidding data locally

Ian

[former_member650363 · Accepted solution]

Hi Ian,

1. My Report -> Selection Expert -> Record has the following data:

{ITT1_1.Code} = '500005' AND {ITT1.Code} <> '500005' AND {OITW.AvgPrice} <> 0 AND {OITW.WhsCode} LIKE 'HO%%%%' AND {ITM1.PriceList} = 1 AND {ITT1.ChildNum} = 1

which is part of the query.

2. The Section Expert, all are set to Normal.

3. I had not grouped on any feild.

Thanks in advance. Let me know whether I got your suggestions correctly.

Vineela.

[Former Member · Accepted solution]

wild card in Crystal is * not %

Try

{ITT1_1.Code} = '500005'

AND {ITT1.Code} '500005'

AND {OITW.AvgPrice} &lt;&gt; 0

AND {OITW.WhsCode} LIKE 'HO*'

AND {ITM1.PriceList} = 1

AND {ITT1.ChildNum} = 1

Ian

[former_member650363 · Accepted solution]

Hi Ian,

Thanks for the reply. It worked. But my report is looking like this:

Pack_Size Packing_Tin_Cost Packing_Box_Cost

5 LTR 1000 0

5 LTR 0 100

4LTR 800 0

4LTR 0 80

1LTR 600 0

1LTR 0 60

The formula I gave for Packing_Tin_Cost : if {ITT1.ChildNum} = 1 then {OITW.AvgPrice}

The formula I gave for Packing_Box_Cost : if {ITT1.ChildNum} = 2 then {OITW.AvgPrice}

Can I do something to remove the rows that have value as zero.

Thanks

Vineela

[Former Member · Accepted solution]

YOu can suppress them from display, in section expert add a condition to the suppression formula.

However, take care when using summaries as data is still in report just suppressed from views.

If you want to do counts or totals you will need to use running totals with evaluate conditions the reverse of your suppress condition.

Ian

[former_member650363 · Accepted solution]

Hi Ian,

Can you help me on what condition I can suppress. Because if I suppress the row saying Packing_Tin_Cost = 0, is true then the row which is displaying Packing_Box_Cost is getting suppressed. Alternatively can I sum them up or something like that.

Thanks,

Vineela.

[Former Member · Accepted solution]

You can do exactly as you suggest

Packing_Tin_Cost + Packing_Box_Cost = 0

Will suppress line only when that sum is true.

Ian

[former_member650363 · Accepted solution]

Hi Ian,

Even if I give the suppress condition as Packing_Tin_Cost + Packing_Box_Cost =0, all the rows will be returned as one of them is zero in alternate rows.

Pack_Size Packing_Tin_Cost Packing_Box_Cost
5 LTR              1000                     0
5 LTR                 0                  100
4LTR               800                     0
4LTR                   0                   80 
1LTR                600                     0
1LTR                   0                    60

Now my required O/P:

Pack_Size Packing_Tin_Cost Packing_Box_Cost
5 LTR              1000                  100
4 LTR                800                    80
1LTR                 600                    60

I had even tried grouping but it was not giving me the correct results.

Thanks,

Vineela.

[Former Member · Accepted solution]

Hi, I saw you only will get the records that with childnum = 1, then why you still need to write sentence 'if childnum = 2'?

Please put only package size and avgprice into the detail section, and show me the result.

Thx, Hao

[former_member650363 · Accepted solution]

Hi Hao,

I had later removed the condition ITT1.Child_Num=1 from the query. I'll try to put it clearly if Child_Num=1 then the AvgPrice will be Packing_Tin_Cost and if it is 2 then it will be Packing_Box_Cost.

I need to display both of them in the report or at max, I can sum them up and show as Packing Material Cost, but by doing that also for each size it is displaying two rows.

eg:

Pack_Size Packing_Material_Cost
5 LTR              1000                  
5LTR                 100
4 LTR                800
4LTR                   80
1LTR                 600  
1LTR                   60

Please suggest what I can do in such case, I tried grouping, suppressing and everything but I failed.

Thanks,

Vineela.

[Former Member · Accepted solution]

Try,

1. Create group based on Pack_Size

2. Create formula Packing_Tin_Cost: if {Sheet1_.ChildNum} = 1 then {Sheet1_.AvgPrice}else 0

3. Create formula Packing_Box_Cost: if {Sheet1_.ChildNum} = 2 then {Sheet1_.AvgPrice} else 0

4. Create formula Sum_Packing_Tin_Cost: sum({@Packing_Tin_Cost},{Sheet1_.ItemName})

5. Create formula Sum_Packing_Box_Cost: sum({@Packing_Box_Cost},{Sheet1_.ItemName})

6. Put Pack_Size, Sum_Packing_Tin_Cost, Sum_Packing_Box_Cost in the section: Group Header #1 section.

7. Suppress detail section or GF1 if needed.

Thx, Hao

[former_member650363 · Accepted solution]

Hi Hao,

Thanks a ton, that worked. My report is working just as I would like to.

Vineela.