SSRS- Journal Entry

Dear Experts, How to display Journal Voucher Header “Description” column value in “Print Edit List” Report (Refer to below screen)

report.

We did below below mentioned activities but no result

  1. Copy the Report Style (JournalEditList)
  2. Copy Report Data Definition
  3. Added table “GLJrnHed”
  4. Created the Relation Ship between RvJrnTr and GLJrnHed (Refer to below screen)

  1. Clicked on Sync DataSet
  2. SSRS report is synchronized with report data definition successfully.

@Hari_Dutt that merely gives you a base to start with in your RDL it will not magically add the column to your Report you have to go to http://yourReportServer/Reports and then reports/CustomReports find your Copied Report and open it - it should download Report Builder and then you have to make changes to the report, save it and so on…

Thats the usual way, in 10.2 they added more SSRS Design Features within Epicor, but either way works.

I think you need to find a guide or help file or even check Epicor University on SSRS Reports, prob even YouTube otherwise you will struggle without the basic essentials being understood.

Dear hasokeric,

We have already added the description column in custom report using Report Builder. But data is not displaying in the report. We have also added the “Description” column and tables relationship as in RDD maintenance into SQL query phrase in the RDL dataset (Refer to below code)

“SELECT T5.Description, T2.Company, T2.BookID, T2.RvJrnUID, T2.RvJrnTrUID, T1.CurrencyCode, T2.FiscalYear, T2.FiscalYearSuffix, T2.FiscalPeriod, T2.JEDate, T2.Calc_GroupID, T2.Calc_JournalNum, T1.[Description], CAST( T1.CurrencyCode_DecimalsGeneral as nvarchar ) as CurrencyCode_DecimalsGeneral, T1.CurrencyCode_CurrDesc, T2.BookCreditAmount, T2.BookDebitAmount, T2.Calc_LegalNum, T4.BookCreditAmount T4BookCreditAmount,T4.BookDebitAmount T4BookDebitAmount,T4.CreditAmount T4CreditAmount, T4.CurrencyCode T4CurrencyCode,T4.DebitAmount T4DebitAmount,T4.[Description] T4Description, T4.GLAccount T4GLAccount,T4.JournalLine T4JournalLine,T4.Reverse T4Reverse, T4.Calc_AccDescription T4Calc_AccDescription, T4.CurrencyCode_DecimalsGeneral T4CurrencyCode_DecimalsGeneral, T4.HasReverseLine T4HasReverseLine, T4.DebitStatAmt T4DebitStatAmt, T4.CreditStatAmt T4CreditStatAmt, (T4.DebitStatAmt - T4.CreditStatAmt) T4StatAmtBalance, T4.StatUOMCode T4StatUOMCode, T4.Calc_StatDecimals T4StatDecimals, T4.Statistical T4Statistical, T4.Calc_AmortCodeDescription, T4.JournalNum FROM GLBook_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN RvJrnTr_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.BookID = T2.BookID left join (SELECT T3.DebitStatAmt, T3.CreditStatAmt, T3.StatUOMCode, T3.BookCreditAmount,T3.BookDebitAmount,T3.Company,T3.CreditAmount,T3.CurrencyCode,T3.DebitAmount,T3.[Description],T3.GLAccount,T3.JournalLine,T3.Reverse,T3.RvJrnTrUID,T3.RvJrnUID,T3.Calc_AccDescription,T3.CurrencyCode_DecimalsGeneral, T3.HasReverseLine, T3.Calc_StatDecimals, T3.Statistical, T3.Calc_AmortCodeDescription, T3.JournalNum FROM RvJrnTrDtl_" + Parameters!TableGuid.Value + " T3) T4 ON T2.RvJrnUID = T4.RvJrnUID AND T2.RvJrnTrUID = T4.RvJrnTrUID AND T2.Company = T4.Company LEFT OUTER JOIN GLJrnHed_" + Parameters!TableGuid.Value + " T5 ON T2.Company = T5.Company AND T2.Cal_GroupID = T5.GroupID"

Column : T5.Description

Tables Join: LEFT OUTER JOIN GLJrnHed_" + Parameters!TableGuid.Value + " T5 ON T2.Company = T5.Company AND T2.Cal_GroupID = T5.GroupID"

Not sure what direction to pursue next.

In the RDD you shouldnt be able to select a Calc_ field from the dropdown in your RDD Screenshot howd you get that Calc_ in there, if you use the dropdown select the GroupID not Calc_GroupID. However do not Sync Dataset again. Just save and try again.

Also in your SQL Query Cal_GroupID looks like its missing a c or perhaps it shouldnt be Calc_GroupID there either but just GroupID

One or the other is off :slight_smile:

In the RDD, there is no GroupID exist in the drpdown (Refer to below screen). So thats why, I have select the Calc_GroupID for joining.

GroupID

1 Like

Change Cal_GroupID to Calc_GroupID

Tables Join: LEFT OUTER JOIN GLJrnHed_" + Parameters!TableGuid.Value + " T5 ON T2.Company = T5.Company AND T2.Calc_GroupID = T5.GroupID"

No Impact.

Odd so you get no errors, your report generates, it is a left join so the only thing I can think of that is doesnt find a row… The only thing I can suggest next is to go to your SSRS Database via SQL Studio… when you generate a report in Epicor with Archive 1 day it will give you a GUID in the Task Agent somewhere to the right, then you can find those tables in the SSRS Database and query them to see what Epicor generated in those tmp tables which the RDL reads from.

2019-05-22_0516

This is the Reports Database that you created via Admin Console under the SSRS Tab
2019-05-22_0520
Paste GUID In
2019-05-22_0522

Then inspect the Rows in the tables returned. You should see all the tables from you Report Data Definition… So if your GLJrnHead table is empty then its a problem in RDD or simply Epicor didnt find anything. If it has data then we can narrow down that its a problem in the RDL (report style)

Can’t find table in SSRS database using above information.

Tables

When you printed your Report did you put Archive Period 1 Day?

Where we have to set Archive Period 1 Day?

Whenever you Print a Report usually there is an Archive option:
If you dont change it to Day or more it will only exist for a split second before being deleted.

2019-05-22_0709

Thanks @hasokeric for your support. Actually, We have set Archive Period “0 Days”.

Ok change to 1 day or week then you can go to the SSRS Database with the new GUID and it should show the tables then. Then you can inspect them by Querying the Rows to see what each one returned.

@Hari_Dutt howd you make out on the Report?