FM Model (Relational) Calculation and Filter Options

[gosoccer]

Hi Guys, Good morning :) :)

I'm facing an issue with the Calculation I'm using in my Physical View.
When I insert the following statement in FM Calculation, I'm not getting the right count
but when I insert the same logic in the Filter Option as a filter, the counts are displayed
correctly.

If ( [Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209))THEN
(1)
else
(0)

Thank you in advance for your time. 8) 8)

[Lynn]

Hi Guys, Good morning :) :)

I'm facing an issue with the Calculation I'm using in my Physical View.
When I insert the following statement in FM Calculation, I'm not getting the right count
but when I insert the same logic in the Filter Option as a filter, the counts are displayed
correctly.

If ( [Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209))THEN
(1)
else
(0)

Thank you in advance for your time. 8) 8)

Queries will automatically group and summarize data so you aren't necessarily counting individual rows in a database table with an expression like yours in the framework model. You are more likely getting a count of rows in your result set which could be a much smaller figure.

If you want a count of detail rows in the database table then the best approach is to add a field with a value of 1 on every row and have it set to total. Alternatively, if you have a unique ID on the table you can use that in the "then" portion and null in the else portion, setting the aggregate function for the field to count. Not as elegant as the former approach but possibly workable.

I don't know if my guess here are correct because you didn't explain what you are getting vs. what you are expecting. I'm also not sure what you mean by using the same logic as a filter because that expression doesn't look like a filter expression and I don't know what you are counting?

[gosoccer]

Sorry,
I meant to say the same part of the Calculation I'm using such as

[Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209) in
the filter.

Ok so, if I understand correctly, I can add a field with a value of 1 on every row and have it set to total. So,
create a New Query Item in the Subject Query and based on the above conditions, set to 1 or 0? and then Total that field
in Report?

Thanks so much Lynn.  Could you elaborate a little bit more:)
Sorry, I'm in a huge time crunch and people are going crazy.

Thanks

[Lynn]

Sorry,
I meant to say the same part of the Calculation I'm using such as

[Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209) in
the filter.

Ok so, if I understand correctly, I can add a field with a value of 1 on every row and have it set to total. So,
create a New Query Item in the Subject Query and based on the above conditions, set to 1 or 0? and then Total that field
in Report?

Thanks so much Lynn.  Could you elaborate a little bit more:)
Sorry, I'm in a huge time crunch and people are going crazy.

Thanks

Not exactly. I was saying that the best method to provide a count metric in your package is to have a field in the database table with a value of 1 on every row. The report author could then filter accordingly for the desired count. This approach would easily support any requirement for counting, albeit at the report level.

It isn't clear why you need this particular count calculation in the framework model. If someone else wants to count differently are you going to continue adding those variations in the model? Is this one so broadly used as to warrant a stand alone calculation? If so, then you could try to amend your expression to use the count field created as described above.

Code Select Expand


If ( [Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209))THEN
( [YourNamespace].[YourFactTable].[YourCountField] )
else
(0)


Of course I don't know what your overall model looks like so take that FWIW.

[gosoccer]

I'm getting 4 records less than what I get when I use the Filter compared to the count from Calculation.

Yeah! good point. Unfortunately, there isn't a DB Field available to use so I'm trying to come up with this count
within the Framework Model Physical or Business View. I'm trying even in my Physical View to use something like
below the get the COUNT for each row as a part of the overall SQL.

Select
---
---
---
(case when [Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209))
   then 1 end) as D_COUNT,
---
---
Thanks for your time.

[Lynn]

Compare the generated SQL from both approaches to assess what might be causing the difference. I wouldn't recommend using manual SQL like that as your physical layer. Usually that would just be something like: select * from yourtablename

Why do you need this in the model? Can a count calculation in the report suffice? Did you try using a stand alone calculation in the model?

What I've been trying to explain is that using '1' as you are will not necessarily give you the result you expect when auto grouping and summary happens on the query.

Assume you have these individual rows in a table:

ID Brand
1     A
2     A
3     A
4     B
5     B

You add a count calculation to the model:
case when [Brand] = 'B' then 1 else 0 end

You run a report to include brand and your count calculation and may get:
Brand  Count
A         0
B         1

The '1' that you have specified is coming up at the summary level and not based on the underlying rows in the table.

[gosoccer]

You right, 0 and 1 is not doing it.
Count calculation in the report suffice or using the stand alone calculation in the model?

When you are mentioning the Stand alone, I tried it by Creating a Calculation (Create Calculation) and not within the Subject Q. but after I put in the below without using the 0 and 1 I'm getting a big total rather than 14.

case when ( [Physical View].[BRAND_TYPE]=15 and  [Physical View].REC_BRAND_CD]  in (100,101,209)) ) then count(distinct [Physical View].REC_BRAND_ID  ) END

Is there a way to change the property of the above calculation to not to summarize or I should handle it within the Report?

Thanks a lot for your time.