sir,i create SP but it show only 1 table in CR

Question

i create these sp but when i load it in cryatl report for link at that time it shows only first table object ,but i want to access whole object for invoice link ,,,what should i do

 

 

ALTER proc [dbo].[Acc_spVoucher_By_Voucher_Id_Get]

(

@Voucher_ID int = null

)

as

Begin

-- get voucher details

select v.Voucher_No, v.Voucher_Date, v.Effective_Date, v.Reference_No, v.Narration, v.Voucher_Amount,

v.Voucher_Status, v.Approved_or_Rejected_By, v.Voucher_Type_ID, v.Voucher_ID,v.Is_Cancelled,VT.Voucher_Type

,(select * from formatcurrency(v.Voucher_Amount )) as Voucher_Amount1,ISNULL(v.GST_Branch_State_ID,0)GST_Branch_State_ID

from Acc_Voucher v

Inner Join Acc_Voucher_Type VT on V.Voucher_Type_ID=VT.Voucher_Type_ID

where v.Voucher_ID = @Voucher_ID

-- get voucher ledger details

select vl.Voucher_Ledger_Id, vl.Ledger_Id , vl.Ref_Dr_Cr, ABS(vl.Ledger_Amount)Ledger_Amount, vl.Currency, vl.Conversion_Rate, vl.Working_Period_Id,

l.Ledger_Name, l.Maintain_Bill, l.Inventory_Applicable, l.Cost_Center_Applicable

---,LG.Isbank

, cast(Case When LGT.Ledger_Group_Type='Bank' then 1 else 0 end as bit) as Isbank,C.Currency_Id

,abs(round(Ledger_Amount*Conversion_Rate,2,1)) as FinalTotalConvAmt

,(select * from formatcurrency(abs(round(Ledger_Amount*Conversion_Rate,2,1)))) as FinalTotalConvAmt1

,(select * from formatcurrency( ABS(vl.Ledger_Amount)) )as Ledger_Amount1

,vl.Comments,ISNULL(VL.Is_GST_Ledger_Row,0)Is_GST_Ledger_Row,ISNULL(VL.Is_Tds_Ledger_Row,0)Is_Tds_Ledger_Row,

ISNULL(LGT.Ledger_Group_Type_ID,0)Ledger_Group_Type_ID

from Acc_Voucher_Ledger vl

inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID Left join Acc_Ledger_Group LG on L.Ledger_Group_ID=LG.Ledger_Group_ID

Inner Join Acc_Currency C on C.Currency_Code =vl.Currency

left Join Acc_Ledger_Group_Type LGT on LG.Ledger_Group_Type_ID =LGT.Ledger_Group_Type_ID

where vl.Voucher_Id = @Voucher_ID --and Currency_Id <>177

order by SUBSTRING(Ledger_Name, 1, CHARINDEX('Service', Ledger_Name))

-- get voucher ledger billwise details

select vlr.Voucher_Ledger_Ref_Id, vlr.Voucher_Ledger_Id, vlr.Method_of_Adjustment, vlr.particulars,vlr.ParticularsId,

vlr.Credit_Days, vlr.Ref_Dr_Cr, ABS(vlr.Ref_Amount)Ref_Amount, vlr.Working_Period_Id, l.Ledger_Name, l.Ledger_ID,Ref_Date

,(select * from formatcurrency(ABS(vlr.Ref_Amount)))as Ref_Amount1

from Acc_Voucher_Ledger_Ref vlr

inner join Acc_Voucher_Ledger vl on vlr.Voucher_Ledger_Id = vl.Voucher_Ledger_Id

inner join Acc_Voucher v on v.Voucher_ID = vl.Voucher_Id

inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID

where v.Voucher_ID = @Voucher_ID

-- get voucher ledger cost center details

select vlcc.Voucher_Ledger_CC_Id, vlcc.Voucher_Ledger_Id, vlcc.Cost_Center_Id, vlcc.Ref_Dr_Cr,ABS( vlcc.Cost_Center_Amount)Cost_Center_Amount,

vlcc.Working_Period_Id, cc.Cost_Center_Name, ccc.Cost_Center_Category_Name,

cc.Cost_Center_Category_ID, l.Ledger_Name, l.Ledger_ID,

--,ROW_NUMBER()over( order by vlcc.Cost_Center_Id )as MainRowIndex,

cast(DENSE_RANK() OVER (partition by vlcc.Voucher_Ledger_Id order By cc.Cost_Center_Category_ID asc) as int)MainRowIndex

,cast(ROW_NUMBER() over(PARTITION BY vlcc.Voucher_Ledger_Id, cc.Cost_Center_Category_ID order by vlcc.Cost_Center_Id) as int)as

SubRowIndex,(select * from formatcurrency(ABS( vlcc.Cost_Center_Amount)))as Cost_Center_Amount1

from dbo.Acc_Voucher_Ledger_Cost_Center vlcc

inner join Acc_Voucher_Ledger vl on vlcc.Voucher_Ledger_Id = vl.Voucher_Ledger_Id

inner join Acc_Cost_Center cc on vlcc.Cost_Center_Id = cc.Cost_Center_ID

inner join Acc_Cost_Center_Category ccc on cc.Cost_Center_Category_ID = ccc.Cost_Center_Category_ID

inner join Acc_Voucher v on v.Voucher_ID = vl.Voucher_Id

inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID

where v.Voucher_ID = @Voucher_ID order by Voucher_Ledger_Id,cc.Cost_Center_Category_ID,vlcc.Cost_Center_Id

---Get Voucher Instrument Details

select AI.Instrument_Id,AI.Voucher_ID,AI.Ledger_Id ,AI.Instrument_No ,AI.Instrument_Bank_Name

,AI.Instrument_Date,AI.Instrument_Amount ,Ai.Clearence_Date ,AI.Instrument_ModeOfPayment ,AL.Ledger_Name

,(select * from formatcurrency(Instrument_Amount))as Instrument_Amount1

from Acc_Instrument_Info AI

Inner Join Acc_Ledger AL on AI.Ledger_Id =AL.Ledger_ID

Where AI.Voucher_ID =@Voucher_ID

select (select * from formatcurrency(PP.Debit )) as Debit,(select * from formatcurrency(PP.credit)) as credit from(

select sum(abs(P.Debit)) as Debit,Sum(P.Credit) as Credit from

(

select case When VL.Ledger_Amount*Conversion_Rate <0 then round(Vl.Ledger_Amount*Conversion_Rate,2,1) else 0 end as Debit

,case When VL.Ledger_Amount*Conversion_Rate >0 then round(Vl.Ledger_Amount*Conversion_Rate,2,1) else 0 end as Credit

from Acc_Voucher_Ledger vl

inner join Acc_Ledger l on vl.Ledger_Id = l.Ledger_ID Left join Acc_Ledger_Group LG on L.Ledger_Group_ID=LG.Ledger_Group_ID

Inner Join Acc_Currency C on C.Currency_Code =vl.Currency

left Join Acc_Ledger_Group_Type LGT on LG.Ledger_Group_Type_ID =LGT.Ledger_Group_Type_ID

where vl.Voucher_Id = @Voucher_ID --and Currency_Id <>177

)p

)PP

Select VLI.Stock_Item_Id,VLI.Voucher_Ledger_Id,VLI.Voucher_Ledger_Inventory_Id

,VLI.Quantity,VLI.Rate,VLI.Inventory_Total,SI.Stock_Name + ' - ' + gcc.SAC_HSN_Code as Stock_Name

,L.Ledger_ID,L.Ledger_Name,VLI.Inventory_Total as Final_Inventory_Total,VLI.PCS,VLI.Discount,VLI.UOM,VLI.Comments

from Acc_Voucher_Ledger_Inventory VLI

Inner Join Acc_Stock_Item SI on VLI.Stock_Item_Id=SI.Stock_Item_Id

Inner join Acc_GST_Charge_Category gcc on gcc.GST_Charge_Category_Id=SI.GST_Charge_Category_Id

--Inner Join Acc_Stock_Category C on SI.Stock_Category_Id=C.Stock_Category_Id

Inner Join Acc_Voucher_Ledger VL on VLI.Voucher_Ledger_Id=VL.Voucher_Ledger_Id

Inner Join Acc_Ledger L on L.Ledger_ID=VL.Ledger_Id

Where VL.Voucher_Id=@Voucher_ID

Select Schedule_Name,Scheduled_Date,Email_Id,Notification_To,Occurrence_Count,Occurrence_Type from Acc_Voucher_Scheduler where Voucher_Id=141

End