Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I create multiple output on the same columns based on filter?

Posted on 2011-03-09
12
Medium Priority
?
284 Views
Last Modified: 2012-05-11
Current Query - See Attached - Using the 'Where' statement I created a query parameter which will filter on Report parameter item, 1-6.  When I run the query it prompts for input and runs the query fine.

Desired Query - I need to maintain the 1-6 separate Report filters  and Instead of using the Where for a parameter I need my output to provide all six filters at once with their individual outputs:
Count Inv_  AS inv # 1 (Filter 1)
Count Inv_  AS inv # 2 (Filter 2)
Count Inv_  AS inv # 3 (Filter 3)
etc...thru 6

Let me know if I can provide any additional clarity.
EE1.txt
0
Comment
Question by:rhservan
  • 7
  • 3
  • 2
12 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35086480
Are you looking for something like this.
SELECT SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage <= 0.05)) THEN 1 
             ELSE 0 
           END) Filter1_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage >= 0.4)) THEN 1 
             ELSE 0 
           END) Filter2_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (DimInvoiceDetail.NetPrice = 0.01)) THEN 1 
             ELSE 0 
           END) Filter3_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI')) 
                  AND ((DimInvoiceDetail.CostPrice = .01) 
                        OR (DimInvoiceDetail.CostPrice = 888.88) 
                        OR (DimInvoiceDetail.CostPrice = 999.99)) THEN 1 
             ELSE 0 
           END) Filter4_Count, 
       SUM(CASE 
             WHEN ((DimInvoiceDetail.OrderType = 'WA') 
                    OR (DimInvoiceDetail.OrderType = 'WS')) THEN 1 
             ELSE 0 
           END) Filter5_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI')) THEN 1 
             ELSE 0 
           END) Filter6_Count 
  FROM FadRoc.DimEmployee AS DimEmployee 
       INNER JOIN FadRoc.DimProduct AS DimProduct 
                  INNER JOIN FadRoc.DimInvoiceDetail AS DimInvoiceDetail 
                    ON DimProduct.Product_SK = DimInvoiceDetail.Product_SK 
                  INNER JOIN FadRoc.DimCustomer AS DimCustomer 
                    ON DimInvoiceDetail.Customer_SK = DimCustomer.Customer_SK 
                  INNER JOIN FadRoc.DimCompany AS DimCompany 
                    ON DimInvoiceDetail.Company_SK = DimCompany.Company_SK 
         ON DimEmployee.Employee_SK = DimCustomer.Salesman_SK 
       INNER JOIN FadRoc.SalesData AS SalesData 
         ON DimInvoiceDetail.InvoiceDetail_SK = SalesData.InvoiceDetail_SK 
       INNER JOIN FadRoc.DimDate 
         ON DimInvoiceDetail.InvoiceDate_SK = FadRoc.DimDate.Date_SK 
 WHERE FadRoc.DimDate.Date_AK BETWEEN @StartDate AND @EnRRate

Open in new window

0
 

Author Comment

by:rhservan
ID: 35087578
Sharath - Thank you for our efforts.
Please read below to incorporate the count for the invoice number.
I need the total count on this column, DimInvoiceDetail.Invoice_AK per filter.  Doing so will give me the count of invoices.  Is there some way to incorporate this by each filter?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35087607
Can you post the output of the query suggested and the expected result you are looking for?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:rhservan
ID: 35087797
Sharath,
 
I have attached the requested query output as discussed for your review.


Graphic1.png
0
 

Author Comment

by:rhservan
ID: 35087849
I am not sure what the actual counts are from your query.  What do the counts represent?
The filters are correct and I will later make some minor changes to those.

I need the total count on this column, DimInvoiceDetail.Invoice_AK per filter.

Filter1 count = Count of DimInvoiceDetail.Invoice_AK based on filter1
Filter2 count = Count of DimInvoiceDetail.Invoice_AK  based on filter2
etc.....

Let me know if you need additionally clarity.  Thanks again for your efforts.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35088023
and your expected result is?


do you want to sum DimInvoiceDetail.Invoice_AK   or group by it  or ?
SELECT DimInvoiceDetail.Invoice_AK
       ,SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage <= 0.05)) THEN 1 
             ELSE 0 
           END) Filter1_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (SalesData.GPPercentage >= 0.4)) THEN 1 
             ELSE 0 
           END) Filter2_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
                   AND (DimInvoiceDetail.NetPrice = 0.01)) THEN 1 
             ELSE 0 
           END) Filter3_Count, 
       SUM(CASE 
             WHEN DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI') 
              AND DimInvoiceDetail.CostPrice in ( .01, 888.88, 999.99) THEN 1 
             ELSE 0 
           END) Filter4_Count, 
       SUM(CASE 
             WHEN DimInvoiceDetail.OrderType in ( 'WA', 'WS') THEN 1 
             ELSE 0 
           END) Filter5_Count, 
       SUM(CASE 
             WHEN (DimInvoiceDetail.OrderType IN ('RR','RF','DG','DV', 
                                                  'E1','S1','W1','W3', 
                                                  'Q1','Q2','DI')) THEN 1 
             ELSE 0 
           END) Filter6_Count 
  FROM  FadRoc.DimProduct AS DimProduct 
  INNER JOIN FadRoc.DimInvoiceDetail AS DimInvoiceDetail 
     ON DimProduct.Product_SK = DimInvoiceDetail.Product_SK 
  INNER JOIN FadRoc.DimCustomer AS DimCustomer 
     ON DimInvoiceDetail.Customer_SK = DimCustomer.Customer_SK 
  INNER JOIN FadRoc.DimCompany AS DimCompany 
     ON DimInvoiceDetail.Company_SK = DimCompany.Company_SK 
  Inner Join FadRoc.DimEmployee AS DimEmployee    
     ON DimEmployee.Employee_SK = DimCustomer.Salesman_SK 
  INNER JOIN FadRoc.SalesData AS SalesData 
     ON DimInvoiceDetail.InvoiceDetail_SK = SalesData.InvoiceDetail_SK 
  INNER JOIN FadRoc.DimDate 
     ON DimInvoiceDetail.InvoiceDate_SK = FadRoc.DimDate.Date_SK 
 WHERE FadRoc.DimDate.Date_AK BETWEEN @StartDate AND @EnRRate
 group by DimInvoiceDetail.Invoice_AK
 order by 1

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35088360
>> I am not sure what the actual counts are from your query.  What do the counts represent?

Those are the counts based on each filter condition.  Run your original query by passing filter 1 to 6 and compare the counts to the counts from my query. Is that what you are looking for?
0
 

Author Comment

by:rhservan
ID: 35089813
@Sharath - The query filters are on the OrderType and it is returning the query is retruning the total number of orders.
Although the numbers match up correctly based on your query,  they are not the numbers equal to  count of the DimInvoiceDetail.Invoice_AK. column, which would be a smaller number since there are more orders than invoices.
@All
Example:
Invoice #1
Order #1
Order #2
Order #3
Invoice #2
Order #1
Order #2
Order #3
Total Invoice Count is 2 & Total Order Count is 6.
I need the Invoice Count


0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35089889
doesn't 35088023 give you that?
0
 

Author Comment

by:rhservan
ID: 35094566
@ Lowfatspread - I am not sure what that number is but It is not what I am looking for.  Also, in your query it is retruning all invoice rows not the sum or count of all invoice rows.

@All - Everything Sarath has done is correct accept the sum is on orders not on invoice.. Be sure to view the origional query in my first comment where you will find DimInvoiceDetail.Invoice_AK which is the  invoice column  which is the column count or sum I need based on the six filters.. This is what that query output looks like on the 2  columns looks like:

Example:
Invoice                          Order
Invoice #1                   Order #1
                                    Order #2
                                    Order #3

Invoice #2
                                    Order #1
                                    Order #2
                                     Order #3
I need Total Invoice SUM is 2
I dont need  Total Order SUM is 6.

In this format:.
Filter1            Filter2                Filter3              Filter4               Filter5               Filter6
InvSum          InvSum              InvSum            InvSum             InvSum              InvSum


The original query producess all invoice rows plus all order(type) rows based on the filters in the where command. I need the sum  on
0
 

Author Comment

by:rhservan
ID: 35095334
Another way of looking at it is:
1. I need count of distinct DimInvoiceDetail.Invoice_AK based on each of the six filters.
0
 

Author Comment

by:rhservan
ID: 35098673
Comment by Question owner - Rhservan - Please cancel delete.  
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question