Advertisement

10.03.2008 at 12:58PM PDT, ID: 23786276
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.2

Reporting Services "DATA SETS" question...award points FAST....!!

Asked by CRXIuser2005 in MS SQL Server

Experts:

I have the attached SQL Query that I use to pull data into my RS Report. I have a requirement to SLICE this dataset based on the WHERE clause and ONLY show a COUNT for specific CUSTOMER STATUS types.

OK,...so I have a DATASET created using the attached SQL,...so.... what is the BEST way (or CAN I USE) to use this DATASET and simply CHANGE the WHERE clause to read ONE of the following 4 cases.

WHERE status.[CUSTOMER STATUS] = 'ACTIVE'
WHERE status.[CUSTOMER STATUS] = 'DEFAULTED''
WHERE status.[CUSTOMER STATUS] = 'CANCELLED'
WHERE status.[CUSTOMER STATUS] = 'COMPLETED'

There SIMPLE result COUNT for each type of where clause will be used to populate a SINGLE text box on my report....HOW CAN I ACCOMPLISH THIS..??

Currently, I'm having to create SEPARATE DATASETS...containing each different WHERE CLAUSE...but it seems to me like RS should be advanced enough so that I can simply use somekind of EXPRESSION and place the WHERE CLAUSE data there...in order to use a SINGLE DATA SET..and then filter my data based on my need.

i JUST DONT KNOW HOW TO DO IT OR WHERE TO LOOK FOR HELP,....?????
Thanks
M
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

select distinct status.[customer status] as [CUSTOMER STATUS]
 , count(*) as [COUNT]
FROM
(
SELECT 
 distinct pd.orderid as [ORDERID]
 , min(pd.customerid) as [CUSTOMEID]
 , min(pd.transactiondate) as [MAX PAYMENT DATE]
 , min(minorder.[MIN ORDERDATE]) as [MIN ORDERDATE]
 , min(pd.transactionresultid) as [TRANSACTIONRESULTID]
 , min(itemstatusid) as [ITEMSTATUSID]
 , min(pd.itemstatus) as [ITEMSTATUS]
 , min(cast(@EndDate as datetime)) as [STATUSDATE]
 , (select(case when vic.max_installment >= vic.[TOTAL INSTALLMENT COUNT] then 'COMPLETED'
                when(case when MAX(case when pd2.itemstatusid =15 then 'CANCELLED' end) = 'CANCELLED'THEN 'CANCELLED' END ) is null and pd.transactionresultid <>1 then 'DEFAULTED'
                when(case when MAX(case when pd2.itemstatusid =15 then 'CANCELLED' end) = 'CANCELLED' THEN 'CANCELLED' END) = 'CANCELLED' then 'CANCELLED' 
                when(case when MAX(case when pd2.itemstatusid =15 then 'CANCELLED' end) = 'CANCELLED' THEN 'CANCELLED' END) is null
                     and (pd.transactionresultid) = 1 then 'ACTIVE' else 'N/A' end)
           from dbo.view_payment_details_v2 pd2
              where pd2.orderid = pd.orderid
                   and pd2.transactiondate <= @EndDate)    as [CUSTOMER STATUS]
  FROM dbo.view_payment_details_v2 pd
     left outer join bi_reporting.dbo.View_Installment_Counts vic
         on vic.orderid = pd.orderid
      inner join dbo.view_min_orderdate minorder
			  on minorder.customerid = pd.customerid
 WHERE pd.transactiondate <= (SELECT MAX(i.transactiondate) FROM dbo.view_payment_details_v2 i WHERE i.orderID = pd.orderid and i.transactiondate <= @EndDate )
    and minorder.[min orderdate] between @BeginDate and @EndDate
     --or pd.transactiondate is null
group by pd.orderid, pd.customerid, transactiondate,pd.itemstatusid,pd.itemstatus, pd.transactionresultid,vic.max_installment, vic.[total installment count]
) status
group by status.[CUSTOMER STATUS]
order by status.[CUSTOMER STATUS]
[+][-]10.03.2008 at 01:13PM PDT, ID: 22637605

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 01:16PM PDT, ID: 22637639

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.03.2008 at 01:35PM PDT, ID: 22637793

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: MS SQL Server
Sign Up Now!
Solution Provided By: angelIII
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20081112-EE-VQP-42 / EE_QW_2_20070628