Maybe a better tool at this point is a BAQ. You have all the same text functions and you can export from there.
(Implied decimals…hmm…I smell a Cobol backend nearby…) 
Mark W.
2 Likes
Actually ended up getting it by modding the Check Register report in SSRS! Phew! What a doozey, had to stretch the ol’ SSRS muscles way too much.
Thanks so much Mark and everyone else for the help.
Really appreciate ite!
1 Like
911 request for me to do the same as Mach is doing in this thread for PNC bank. These threads are wonderful resources check before jumping in. I can see my first move will be to take a look at the SSRS Check Register Report. I would love to see your end result Mach. I think I am not the only one wishing for the Crystal days to come back…
Paul
You and I both Paul. Just had to make another change in SSRS and it took me 45 mins, where it would’ve taken me less than 5 in Crystal.
Either way, not sure how to show you my end result as it include bank numbers. Or did you want to look at the SSRS report and not the export?
Actually Mach, I am at a pretty workable format now; probably where you are at as well. I just started deleting as many fields as I could from my Check Register Report.
What has currently got me puzzled is that my Excel output looks usable but preceding each line of data there are a couple of blank lines with empty fields showing up as such:
,
,
1234567890,98357,8/2/2018,“3,300.00”,Trans World Airlines
,
,
1234567890,98358,8/2/2018,“14,960.00”,Pan Am
I have deleted any empty lines in tables I could find and I am down to the two lines you see.
In addition it looks like I have empty fields between each of the data fields. Not sure where this is coming from because I don’t see any extra fields in between.
I also still need to figure out how to suppress the thousand’s separating comma… I am not sure where this formatting is done…
Thanks!
Paul
The list changes the commas in my email. Where it shows a single comma on a blank line I actually have 7 commas…
When you run the report, do you have the Output Format set to Excel? or CSV? I find Excel adds a bunch of formatting that can easily be dealt with if you choose CSV instead.
In terms of changing the format to remove the comma, I had to use this expression. Which would multiply the amount by 100, then it would find where the decimal is, then truncate everything before the decimal. I also had to add in some leading zeros and my field had to be 10 digits:
Left(Fields!CheckAmt.Value100,InStr(Fields!CheckAmt.Value100,".")-1),
LEN(Cstr(Left(Fields!CheckAmt.Value100,InStr(Fields!CheckAmt.Value100,".")-1)))=2,“00000000”
I set the output format to CSV which got rid of the Excel padding; thanks! I don’t have to bother with back-filling 0’s due to my output file being CSV. One thing less to worry about.
I see what you did to get the thousands separator out; but I am not really sure how to change my current line of
=FormatNumber(Fields!CheckAmt.Value, First(Fields!DecimalsGeneral.Value, “RptParameter”))
to incorporate yours.
-I wish it were as easy as setting the formatting on the field directly. Do I miss Crystal…
Oddly, as I don’t have the bank accoun tnumber stored in Epicor, I had to add this as a text box. I lose the value in the box (plain text) as I export to CSV. It is still there if I export as Excel… Any ideas?
Figured it out, and I am leaving it here for the next one to face this issue:
In the field’s Properties / Data Only section, set the value of “DataElementOutput” to “Output”.
Like a charm.