Creating custom Column in IDT 4.1

[Former Member]

Hi All,

I am using SAP IDT 4.1 SP03 to build a business layer over a SQL Server database using relational connection.

I having a single table named CUSTOMER and it contains Customer ID, Customer Name,  City, SubscribeForEmail and date column. I want to filter the data on specific date range to view the data on below format:

City	Total Customers	Customers subscribed for email only

I have created the measure in business layer and filter customer count based on subscribeforemail flag. But IDT is showing invalid data. For ex:

Database Table:

Customer ID	Customer Name	City	SubscribeForEmail	Date
1	Customer 1	City1	Y	1/1/2014
2	Customer 2	City2	Y	1/1/2014
3	Customer 3	City1	Y	1/1/2014
4	Customer 4	City1	N	1/1/20014

Data returned By IDT:

City	Total Customers	Email Only
City1	2	2

Kindly guide.

[Former Member]

Dear All,

Kindly address the above problem in IDT.

[former_member4998]

Hi

Can you please share the SQL for report.

Find the below sample conditions.

Measure: Total Customers = Count(DISTINCT Customer_ID)

Where  Subscribeforemail flag =Y or N or ALL

[Former Member]

Hi Sreenivasulu,

I have created two measures in business layer based on below format:

Measure1: Total Customers = Count(DISTINCT Customer_ID)

Measure2:  Email Customers = Count(DISTINCT Customer_ID)

                                                   Where SubscribeForEmail = 'Y'

The query generated is:

SELECT

     Table_3."City",

     count( Distinct (Table_3."Customer_ID")),

     count( Distinct ( Table_3."Customer_ID"))

FROM "Customer" Table_3

WHERE

(Table_3."SubscribeForEmail" = 'Y')

GROUP BY 1

ORDER BY 1

Here, the issue is WHERE clause is applied on BOTH columns whereas I want to apply it on one only.

Regards,

Hasan Arif Khan.