Reconciling results of one report to match to another.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

Crystal reports
SQL SERVER
I need help in changing Report 2.rpt. Such that Report2.rpt. should be reconciling and make it clear what is being reported and provide an easy way of reconciling to Report1.rpt.

In the case of Reprot2 it is slightly different to Report1.rpt in that it excludes items as follows:

1)Vouchers (ITEM.is_voucher_yn = 0)
2)Non Commission Items (ITEM.non_commission = 0)

So that this is clear Report2 should include another section AFTER to grand total called Excluded (The whole section Blank when Zero):

Vouchers (Blank when zero)
item details here
Non Commision (Blank when zero)
item details here
Sub Total and another Grand Total for Report2

The final Grand Total would reconcile to Report1.rpt Grand Total.

The SQL for both reports are same except for the WHERE clause in the REport 2 has got the above conditions.

I am looking to create one single SQL that gives me separate set of results for both conditions(preferably using CASE statements), that I can use to display results separately in my CR from the two queries below

SQL IS AS FOLLOWS:

Report 1

SQL

SELECT "sales_tx_lines"."item_qty", 
       "sales_tx_lines"."item_price", 
FROM   "sales_tx_hdr" "SALES_TX_HDR" 
           INNER JOIN "sales_tx_lines"                    
               "sales_tx_hdr"."sale_tx_no" = "sales_tx_lines"."sale_tx_no"
          LEFT OUTER JOIN "branch" 
                       ON "sales_tx_hdr"."branch_no" = "branch"."branch_code" 
         LEFT OUTER JOIN "address" 
                      ON "branch"."address_code" = "address"."address_code" 
        LEFT OUTER JOIN "item" 
                     ON "sales_tx_lines"."item_code" = "item"."item_code"
       LEFT OUTER JOIN "item_category" 
                    ON "item"."item_code" = "item_category"."item_code"

Report 2

SQL

SELECT "sales_tx_lines"."item_qty", 
       "sales_tx_lines"."item_price", 
FROM   "sales_tx_hdr" "SALES_TX_HDR" 
           INNER JOIN "sales_tx_lines"                    
               "sales_tx_hdr"."sale_tx_no" = "sales_tx_lines"."sale_tx_no"
          LEFT OUTER JOIN "branch" 
                       ON "sales_tx_hdr"."branch_no" = "branch"."branch_code" 
         LEFT OUTER JOIN "address" 
                      ON "branch"."address_code" = "address"."address_code" 
        LEFT OUTER JOIN "item" 
                     ON "sales_tx_lines"."item_code" = "item"."item_code"
       LEFT OUTER JOIN "item_category" 
                    ON "item"."item_code" = "item_category"."item_code" 
WHERE      (item.is_voucher_yn = 0 or item.non_commission = 0)

Any help is appreciated.

Posted 4-Jul-12 17:48pm

ShaikhM

Updated 4-Jul-12 18:25pm

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 5-Jul-12 0:08am

Help with what? What's your question?
--SA

ShaikhM 5-Jul-12 0:25am

There you go. The bold part.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Swaminathan_K · Answer 1 · 2012-07-04T21:02:00

It is better to use a stored procedure with two parameters.
@ReportType - Report1 or Report2
@Excluded - (This is used only for report2, to show original Report2 data or to show excluded data)

SQL

SELECT
sales_tx_lines.item_qty,
sales_tx_lines.item_price,
FROM
sales_tx_hdr SALES_TX_HDR
INNER JOIN sales_tx_lines ON sales_tx_hdr.sale_tx_no = sales_tx_lines.sale_tx_no
LEFT OUTER JOIN branch ON sales_tx_hdr.branch_no = branch.branch_code
LEFT OUTER JOIN address ON branch.address_code = address.address_code
LEFT OUTER JOIN item ON sales_tx_lines.item_code = item.item_code
LEFT OUTER JOIN item_category ON item.item_code = item_category.item_code
WHERE 1 = 1 
AND
(@ReportType = 1) --To check if Report1 or Report2
OR
(@Excluded = 0 AND (item.is_voucher_yn = 0 OR item.non_commission = 0)) --If Report2, Report2 data
OR
(@Excluded = 1 AND (item.is_voucher_yn <> 0 OR item.non_commission <> 0)--If Report2, Excluded data

Please correct me if i am wrong.

Regards,
Swami

MaulikDusara · Answer 2 · 2012-07-04T18:56:00

Your Solution:

1) Use Stored Procedure[^] instead of inline SQL Query.

2) Pass one parameter[^] to Sotred Procedure[^] which need to differentiate the query i.e. for 1st case or 2nd case.

3)Your stored procedure will look like below

SQL

SELECT
    sales_tx_lines.item_qty,
    sales_tx_lines.item_price,
FROM
    sales_tx_hdr SALES_TX_HDR
    INNER JOIN sales_tx_lines ON sales_tx_hdr.sale_tx_no = sales_tx_lines.sale_tx_no
    LEFT OUTER JOIN branch ON sales_tx_hdr.branch_no = branch.branch_code
    LEFT OUTER JOIN address ON branch.address_code = address.address_code
    LEFT OUTER JOIN item ON sales_tx_lines.item_code = item.item_code
    LEFT OUTER JOIN item_category ON item.item_code = item_category.item_code
WHERE 1 = 1 AND (item.is_voucher_yn = CASE WHEN @intType = 1 THEN item.is_voucher_yn ELSE 0 END
                    or item.non_commission = CASE WHEN @intType = 1 THEN item.non_commission ELSE 0 END)

Reconciling results of one report to match to another.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0