SSRS dataset expression size

Epicor ERP 10
, , ,
1

Hi mates,

I have duplicated a Sales Order Report RDD and added few tables as per my requirement,
Now when I see the expression in dataset of the RDL system has already generated a huge dataset including labels and literrals etc,

Now here I am trying to manually add some fields into the expression but the epression editor does not type anything only backspace works there.

also if I try to edit in notepad and paste it again the query is pasted half into the expression.

I believe there is no ample amount of space in the editor /

if someone has encountered similar issue or overcome this please let me know.

Thanks in Advance.

2

Did you click the dreaded “sync dataset” button? If so, you might want to start over with a fresh copy of the SO form and add your new tables and fields manually.

Joe

3

Hi Joe,

Thank you for the reply.

yes I did Sync the dataset, But before syncing I Excluded all the labels from the RDD and included only the fields and still there were labels in the RDL.

Anyways I will try to add manually the tables which I need.

Thanks

4

Unless you are working with a BAQ Report, NEVER (and we mean it, NEVER!) use “Sync DataSet”.
You have to edit the SQL in your SSRS Report manually.

1 Like
5

Your basic steps should have been:

  1. Duplicate the OrderAck RDD (say to OrderAck_001)
  2. Modify RDD OrderAck_001 as needed.
  3. Duplicate the OrderAck style closest to what the new one will be (call it “Order Ack plus Tables”)
  4. Change the data source of the new report style to use OrderAck_001 and save.
  5. Run the report using the new style (“Order Ack plus Tables”). Set the archive period to 1 week.
  6. In Report Style Maintenance, download the new report style
  7. Open the downloaded report in Report Builder.
  8. Test run the report using the GUID from the report ran in step 5. You can find it in the Sys Monitor. It should appear identical to to the original style you copied in step 3
  9. Edit the Query Definition, but only adding the joined tables.
  10. Retest like in step 8. This will confirm the new RDD generated the expected tables.
  11. Add the fields to the SELECT clause of the query expression.
  12. Retest
  13. Add the fields (Query type) to the dataset. Now you can reference them in the layout area of the Report builder.

If the query expression has gotten too big, look for fields you can remove from the SELECT clause.

Other things to reduce the total char count would be:

  1. Shorten any aliases in the select clause. Like T1.OrderDt_OrderLine as OrderDtl_OrderLine to T1.OrderDt_OrderLine as OD_OrderLine. Then make sure to update the Query fields in the dataset to match the new name.
  2. Change the table names from T1, T2, T3, etc… to A, B, C, D, etc… While a change from “T1. CustID” to “A.CustID” might not seem like much savings, if there’s 200 fields in the SELECT clause, you reduced the length by 200 bytes.

If you really cant seem to get the query short enough, then use sub-reports. The main reports query won’t need to have the extra fields added. And the subreport will only need the extra fields (plus a few for houskeeping).

6

Thanks Guys for quickly Jumping in ! So the result is unless working with BAQ report never touch the sync Dataset Button

Thank You guys

1 Like