How to implement subquery in the designer

building semantic layer
1

Hello All,

How to create a sub query in the Designer.

This is the query, Could any of you tell me how to implement this query in the designer or report

select * from dw_rx_basket_ndc_dm where DW_RX_OWNER_ID in(
select DW_RX_OWNER_ID from dw_rx_basket_ndc_dm where NDC_FLAG = ‘OWNER’)
and NDC_FLAG <> ‘OWNER’

Thank
Grace

Grace555 (BOB member since 2004-06-09)

2

Just create a predefined condition along the lines of:

DW_RX_OWNER_ID in
( 
  select alias1.DW_RX_OWNER_ID 
  from dw_rx_basket_ndc_dm alias1 
  where alias1.NDC_FLAG = 'OWNER'
)

Andreas :de: (BOB member since 2002-06-20)

3

Andreas,

Really, I don’t understand what you are trying to say. Could you please, tell me clearly.

Actually I have created 2 object , one is COmpetitor and another one is OWNER. In both , I have selected same column from the database. The result is repeating. Then in the Where clause , I gave NDC_FLAG=‘OWNER’ in owner and NDC_FLAG=‘COMP’ in COmpetitor object. The result is no data is fetching. Then I decided to put Group by in the clause, in order no to repeat the data.

Could you pls, help me in that. How to do this in the bo (step by Step), If you don’t mind.

Thanks,
Kavitha

Grace555 (BOB member since 2004-06-09)

4

You should NEVER (almost) use the WHERE portion of an object. You have discovered exactly why. You could have accomplished the same thing with a CASE statement:

Owner Object:
Case
When NDC_FLAG=‘OWNER’ then (table.column) else 0
end

Competitor Object:
Case
When NDC_FLAG=‘COMP’ then (table.column) else 0
end

My syntax on CASE may not be exact, but you get the idea.

Steve Krandel :us: (BOB member since 2002-06-25)

5

Could it be that you are storing the information for two separate entites such as “Owner” and “Competitor” in the same table? If so you might have to alias your table and apply a stub-join to the two aliases, see also this post of yours… :?

Andreas :de: (BOB member since 2002-06-20)

6

Hello Andreas/Steve,

I did the way Steve told me. I have created 2 objects and did the case statement for each one.

Now It’s working, but problem is , the corresponding data diaplaying each value in one line. It is not showing all the measures in one line (across Edge display). I did sum in the designer also. Even though ,it is showing irrelavent.

Should I do any calculation in the designer?

Could you pls help,

Thanks,
Grace

Grace555 (BOB member since 2004-06-09)

7

Grace – I’m having trouble figuring out what you’re doing, and what you’re seeing.

Perhaps if you give us more information, someone can give a suggestion.

Try to display, for the tables/objects in question:
1.) how your tables are joined
2.) what SQL you’re using for the objects you have
3.) show us what you are requesting in the query panel
4.) what your report output looks like, and
5.) what you want your report output to look like.

Anita Craig :us: (BOB member since 2002-06-17)

8

Anita,

It is very big universe, It is hard to explain. Anyway, I explain how it works.

Could tell me how should I attach my report layout (it is in EXCEL) through this reply. If I send the report layout to you, that will be helpful for me as well as for all of you.

1.) How your tables are joined
Basket Table has

NDC_ID -HAS different id which is link with another NDC_TABLE(joins throu this id)
BASKET_NAME - GRACE
OWNER_ID-Has same id for GNN and NDC_FLAG(4724) for each BASKET_NAME
GNN(SOME DATA) which is related to NDC_FLAG
NDC_FLAG(OWNER,COMP)

2.) what SQL you’re using for the objects you have

I have created 2 object , one for OWNER and one for COMPETATOR, each has case statement

For Owner object
case when DW.DW_RX_BASKET_NDC_DM.NDC_FLAG = ‘OWNER’ then DW.DW_RX_BASKET_NDC_DM.GNN end

For Competitor object
case when DW.DW_RX_BASKET_NDC_DM.NDC_FLAG = ‘COMP’ then DW.DW_RX_BASKET_NDC_DM.GNN end

3.) show us what you are requesting in the query panel

In the query panel, OWner,Competitor,Units (is in the Fact table,already linked with BASKET TABLE throu NDC_ID and also it is in the measure class). That means , since owner and competitor have same owner_id, I want to display both which is belong to same id
4.) what your report output looks like, and

Could you tell me how to attach the layout , I could send to you all

Thanks in Advance,
Grace

Grace555 (BOB member since 2004-06-09)

9

I’m sorry, the forum doesn’t support attachments.

Can you not use the BBCode “Code” button to layout a small portion of the report as you see it, and then as you’d like to see it, so we can get the idea of the problem? We don’t need the entire report, just a synopsis of what’s happening, and what the desired behavior would be. In fact, the entire report might make it difficult to pinpoint what you want us to see.

For example:

What I’m getting:

Month   Year   Amount
-----   ----   ------
11      2003   $10.75
12      2003   $15.65

What I want:

Year   Month   Amount
----   -----   ------
2003   11      $10.75
       12      $15.65

In your sample, perhaps you can show what you want displayed when the owner and competitor are different, as well.

Anita Craig :us: (BOB member since 2002-06-17)

10

Andreas gave you the answer:

To create a condition, in the bottom left of Designer click on the circle next to the filter symbol (it is really a condition symbol - BusinessObjects uses the same symbol for both, causing unending confusion). Now right-click on your class and select “Insert Condition…” Slap the SQL above into the SQL box. In Reporter, click on the filter object below the Classes and Objects window to get access to your new condition. Double-click on it and it will be added to your query. Click Run and you should be in business.

Dennis W. Disney :us: (BOB member since 2003-09-17)