SSRS can't modify dataset

Epicor ERP 10
1

We recently did our last conversion test pass with Epicor. All went well and I installed my solutions for reports and everything else. My big issue is now I can’t fix any forms that had issues. I am missing a field on my SO Form and I went in to SSRS to modify the dataset to add it in the query expression. I can’t modify it at all. I could do it before. I can modify other portions of the form just not the expression…anyone know why??

here are some more details…

I have a strange issue going on with my SO form. So I am missing some data and need to add it, no big deal. But when I go into the SSRS report and open the data set to edit the query I can’t edit it. I have done this plenty of times before. I am at a total loss.

This is where I can’t edit the report….

I just want to add a field to the query… T2.Orderdtl_Character01 and I can’t type anywhere in the expression.

image
image.png1160×781 74.6 KB

2

The expression field is disabled?

Or after making an edit and clicking OK it gives an error/warning?

Or does it just appear to make the change ok, and when you go back in, the expression reverts back to pre-edit state?

3

I guess it seems like it is disabled. I can’t type anything in there. I thought maybe when I installed my solution some permissions got messed up. I even tried another form and same issue.

4

Are you editing the RDL directly from the server? Or did you make a local copy?
(FWIW - I always edit directly from the server.)

Can you save the opened RDL as an other name, and then see if that one is editable. If it’s not, it sounds like a permission thing.

And cany you make a new blank RDL, and edit its query expression?

5

I work directly from the server.

I was able to save it as a new name; but still can’t edit it.

I just tried to make a new Copy of the RDL to create new one and I can’t edit that one either.

I was thinking this morning it was permissions and it sounds like it may be.

1 Like
6

now I just have to figure the permissions part out…LOL

7

Is you conversion from E9 to E10? Or an earlier version of E10 to a later one?

8

It was from E9 to E 10.2.300.2

I didn’t have this issue with my previous conversion pass.

Not sure if the version upgrade from 200 to 300 did it or my solution screwed everything up.

9

So when you downloaded RDL, did you check (using Windows File Explorer) that the file itself is not set as read-only?
Not ideal but for testing you can open the file in text editor and make changes.

10

Just out of curiosity, are you using Report Builder or Visual Studio? Whichever one you are using, can you try the other to see if it works for you there? Report Builder has done some bizarre things to me in the past so I’ve just permanently moved to Visual Studio to work with my SSRS reports. Would right-clicking on the program and choosing “Run as Administrator” make any difference? Seems very strange but just throwing some ideas out there to try…:slight_smile:

11

I found the issue Epicor has maxed out the allowed Characters for SSRS. I will be posting a new post on this find to see what others have found.

I will need to create stored procedure to call this dataset.

12

If that were true, I’d think you’d be able to delete some of the existing text. Can you do that? (Obviously don’t save after deleteing)

Edit:

Just tested it and pasting a query expresion bigger than 32,767 stops at that char 32,767

image

13

Yes I can delete text. I just don’t know what I REALLY need to keep or not. I know there are some fields that I don’t need but worried I will remove something needed or a subreport will need.

14

I think deleting any referenced field would give you an error upon saving.

Did you use a custom RDD, and then use Sync Report?

When a table is added to a RDD, I think it makes every field included. And Synching it may update the query phrase to include all of those unneeded fields. Go into the RDD and make any unneeded filed in added tabels as excluded.

Note, even if you don’t need it in the data set, don’t mark a field from an added table as excluded, if it is used in the table relationship

15

I had started to go back to RDD and try to remove what I may not need…haven’t finished yet. I do have a customized RDD but I have not added any tables…just added Fields.

16

This might be a real hack …

  1. Copy the existing query phrase expression, to a text editor*
  2. Remove any fields from a SELECT’s filed list that don’t have an alias.
  3. Add in the * for the select list.

*(preferably one that has syntax highlighting for SQL)

17

That is what I have origianlly started but the query is so huge it was over whelming.

18 
SELECT T1.RptLanguageID,T1.Company,T1.CreditMemo,T1.CustNum,T1.DocDepositCredit,T1.DocInvoiceAmt,T1.DocRounding,T1.DocumentPrinted,T1.InvoiceComment,T1.InvoiceDate,
T1.InvoiceNum,T1.InvoiceType,T1.LegalNumber,T1.PONum,T1.SoldToInvoiceAddress,T1.Calc_BillToAddressList,T1.Calc_BottomAddress,T1.Calc_CompanyAddressList,
T1.Calc_CurrDocDesc,T1.Calc_CurSymbol,T1.Calc_CustContactName,T1.Calc_CustPartOpts,T1.Calc_DteOrdrd,T1.Calc_fFOB,T1.Calc_MulPackNum,T1.Calc_mulponum,
T1.Calc_MulShipDate,T1.Calc_MulShipTo,T1.Calc_MulShipVia,T1.Calc_MultSoldTo,T1.Calc_MultTaxID,T1.Calc_NumRecordPerPage,T1.Calc_NumTotalParts,T1.Calc_PackNum,
T1.Calc_PrintBottomAddress,T1.Calc_SalesPerson,T1.Calc_SalesTerms,T1.Calc_ShipDate,T1.Calc_ShipToAddressList,T1.Calc_ShipToContactName,T1.Calc_ShipVia,
T1.[Calc_Voucher-String] as Calc_Voucher_String,T1.CurrencyCode_CurrencyID,T1.CurrencyCode_DecimalsGeneral,T1.Calc_SEBankRef,T1.Calc_CustResaleID,T1.BranchID,
T1.CustAgentTaxRegNo, T1.CHISRCodeLine,T1.Calc_CHBankAcctIBANCode,T1.Calc_CHBankAcctISRPartyID, T1.InvoiceRef, T1.CMReason, T1.THIsImmatAdjustment, 
T1.RevisionNum AS InvcHead_RevisionNum, T1.RevisionDate AS InvcHead_RevisionDate, 
T2.AdvanceBillCredit,T2.Company as InvcDtl_Company,T2.DocAdvanceBillCredit,T2.DocDiscount,T2.DocUnitPrice,T2.InvoiceComment as InvcDtl_InvoiceComment,
T2.InvoiceLine,T2.InvoiceNum as InvcDtl_InvoiceNu...

becomes

SELECT T1.*, 
T1.RevisionNum AS InvcHead_RevisionNum, 
T1.RevisionDate AS InvcHead_RevisionDate, 
T1.[Calc_Voucher-String] as Calc_Voucher_String,
T2.AdvanceBillCredit,T2.Company as InvcDtl_Company,T2.DocAdvanceBillCredit,T2.DocDiscount,T2.DocUnitPrice,T2.InvoiceComment as InvcDtl_InvoiceComment,
T2.InvoiceLine,T2.InvoiceNum as InvcDtl_InvoiceNu...

That’s a big assumption that T1.* is a valid SQL reference

1 Like
19

aghh…the light bulb goes on…LOL

20

The table RptLabels_ isjust for labels correct? Could I change that to T4.*?

LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4