To SUB or not to SUB 1

[Toshilap]

I am building Report of 1 table but ran into dilemma.
Few columns should be having a filter that the rest of the Report shouldn't have to.

I want products and orders total and next column would be
Total_SUBS = DUC.

So I basically have to formulate
Select Product from Table1 where Total_SUBS=DUC just for this column.

Should I think of subreport or I can do it within this one?

 

[Turkbear]

Hi,
There appears to be a misunderstanding here ( either of your question or of basic database concepts):

A Filter is row-based in any database system but it is applied to one of more fields in that row..

So, if you say where Column_Name = 'Y' then all records ( with any of their fields you choose to include) with the value of 'Y' in that particular field will be returned to your report.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Toshilap]

Ok, SQL of the my CR is Select * from MyTable
Then there is a Column4 which have to use

Select Prod from MyTable
where Total_SUBS = DUC

So THIS particular field needs
to be = 'where Total_SUBS = DUC'

The rest of the Report data uses whole data without criteria.

Thanks

 

[Toshilap]

I wrote :
IIF({ORDER_DETAIL.SUBSYSTEM}='SUB',Count({ORDER_DETAIL.PRODUCT_ID}),0)

Got to execute...just wanted to show you that I am thinking.LOL

 

[Toshilap]

Here is what I need to have.
Query:

SELECT dbRDER_DETAIL.SUBSYS,
Count(dbRDER_DETAIL.PRODUCT_ID),
dbRDER_DETAIL.PRODUCT
FROM dbRDER_DETAIL
GROUP BY dbRDER_DETAIL.SUBSYS,
dbRDER_DETAIL.PRODUCT
HAVING (((dbRDER_DETAIL.SUBSYSTEM)="SUB"));

To be wrote as a CR formula where if dbRDER_DETAIL.SUBSYSTEM<>'SUB'then 0

 

[Toshilap]

OK, got it in CR
SELECT "ORDER_DETAIL"."SUBSYSTEM",
Count("ORDER_DETAIL"."PRODUCT_ID")
from "ORDER_DETAIL"
where "ORDER_DETAIL"."SUBSYSTEM"='SUB' or "ORDER_DETAIL"."SUBSYSTEM"='MBR'
group by "ORDER_DETAIL"."PARENT_PRODUCT"


but will not go through...'incorrect syntax near word Select'.'.

 

[Turkbear]

Hi,
Are you using a command object to produce that Sql code?

Why not let CR do it:

Create a group on the "ORDER_DETAIL"."PARENT_PRODUCT"
field..In its details place:
"ORDER_DETAIL"."SUBSYSTEM" and ORDER_DETAIL"."PRODUCT_ID"

Right-click on ORDER_DETAIL"."PRODUCT_ID" and Insert Summary Count at the Group level..

In your Record Selection formula use
(ORDER_DETAIL"."SUBSYSTEM"='SUB' or "ORDER_DETAIL"."SUBSYSTEM"='MBR')

Won't this give you what you want?











To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Toshilap]

No, it will not.
I can't have SUBSYSTEM on my Report.
I have to have records without ='SUB' and 'MBR'.
Those are done and over with.
Now I am adding few coulumns that are ='SUB' and 'MBR'.
My Select would give me exactly what I want.
What is wrong with my Select statement? Can you see? THANKS

 

[Turkbear]

Hi,
OK, how did you create that Sql code?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Toshilap]

I wrote a query in Acess and executed it. Got result I wanted.
Then I went to CR and used SQL Expression field. Wrong?

 

[GJParker]

a SQL expression field can only return 1 value and can not return a recordset.

It looks to me like you need a sub report here, what you are trying to do is show the same information in 2 different ways.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

A solution that uses the SQL query to return the count is dependent upon your version of CR. So what version are you using?

-LB

 

[Toshilap]

GJ, I was suspecting it but tried to avoid.
lbass, I am using 9.2 version.

GJ, If I used formula instead of a SQL exp would that help?
IIF({ORDER_DETAIL.SUBSYSTEM}='SUB' or {ORDER_DETAIL.SUBSYSTEM}='MBR',Count({ORDER_DETAIL.PRODUCT_ID}),0)

This formula I wrote gets me what I want but somehow does not distribute by product even placed into group where product is. Thanks

 

[GJParker]

I think it would be easier for everyone to understand your situation if you show an example of your report layout and how your expected outcome.

I for one am still a little confused on exactly what it is you are trying to do here.



Gary Parker
MIS Data Analyst
Manchester, England

 

[Toshilap]

Allright, here I try.

Products Orders Cancel SUBS
--------------------------------
A 3 1
B 10 5
where
Products Orders Cancel are filled already with names of the Products and Count for how many were ordered and cancelled.

SUBS are suppose to be a count of only those products that have SUBSYS field = SUB and MBR.
So first 3 fields are count for all Products and SUB is a count for some products (that are = SUB and MBR)

I hope I had not add to a confusion. Thanks

 

[Toshilap]

Guys...I think I got it. Talking to you made me realize that I had to use this formula without Count

IIF({ORDER_DETAIL.SUBSYSTEM}='SUB' or {ORDER_DETAIL.SUBSYSTEM}='MBR',({ORDER_DETAIL.PRODUCT_ID}),0)

and then Insert Summary (count) when placed on the Report. THANKS so much for being here for me!

 

[lbass]

I don't think you'll get the correct results with this approach, since the count of a formula will always be the number of times the formula executes. If you used a distinctcount, it would still be incorrect some of the time, since it would count zero as one of the distinct outcomes. You are better off with a formula like:

If {ORDER_DETAIL.SUBSYSTEM} in ['SUB','MBR'] then 1

Then right click on this formula and insert a summary (SUM, not count).

-LB

 

[Toshilap]

But how do I count Product_IDs? I need Product_IDs count.
Thanks

 

[lbass]

Isn't the product ID in every record? Counting by 1 for each record that meets the criteria will then be a count of the product IDs. If you are saying you want a count for each product ID, then insert a goup on product Id and insert a summary(sum) at the Product ID group level.

-LB

 

[Toshilap]

It's like you saved me!!! THANKS
(I had inserted Sum into other group by frustration so it didn't work. Worked in Access but not in CR...LOL now)

THANKS