Open Forum

Expand all | Collapse all

update SSRS report to show all fields

  • 1.  update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 26, 2020 12:39 PM
    Edited by Vanessa Halacsy May 26, 2020 12:57 PM
    We are trying to use the SSRS settlement reports in TMW, and found one of the standard ones that's almost exactly what we want, with one issue. There are two types of pay: taxable earnings, and non-taxable expense reimbursements.

    In a week where a settlement sheet has both types of pay, the SSRS report lists the taxable earnings in detail with a total, and the expense reimbursements with a total. In a week where a settlement sheet has only one type of pay (always taxable earnings), the SSRS report lists the taxable earnings in detail with a total, and a second total labeled only as "Grand Total" with no reason in evidence of why it would be different from the YTD total listed (that is in fact only the YTD of the taxable earnings).

    All I need to do is change the report such that the expense reimbursements line is still visible, doesn't hide, and shows its YTD total so that the Grand total which is a sum of the two YTD amounts actually makes sense to a casual observer.

    I am not yet proficient at SSRS; I tried looking at the Visibility section of the Text Box Properties, and none of the fields in question are marked as Hide or as Show or Hide based on an Expression. I have been trying to look through the formulas in the Expression section for each field, and have not been able to tell if one of them is hiding the fields or not.

    Any suggestions of how I might figure out which piece is hiding the fields?

    Thanks!

    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: update SSRS report to show all fields

    GPUG ALL STAR
    Posted May 27, 2020 07:14 AM
    @Vanessa Halacsy,

    ​I don't know what TMW is, but in any SSRS report there are a couple reasons for the behavior you're seeing:

    1. The CanShrink property is set to True. Visibility isn't the only contributor to items not appearing on a report. If the CanShrink property for a design element on the report is set to True, then the height of the element can shrink to 0 if there's no data to display.
    2. To me it sounds like the dataset containing the expense reimbursements for your report only includes the current period amounts. Coupled with CanShrink=True, nothing is visible for that period with respect to expense reimbursements.

    What's odd to me is that your report doesn't seem to have a YTD section on it. Is that the case? I would expect that the report design would show YTD amounts for not only taxable earnings in a separate column or as a block of YTD fields in a section footer, but it should show the YTD amounts for expense reimbursements, too.

    Does this help? Not being able to see the design you're talking about means that I'm using Braille to make a guess.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 08:14 AM
    @Steve Erbach ​-What's odd to me is that your report doesn't seem to have a YTD section on it. Is that the case? --

    Yes, that's exactly the case, there is no YTD amount for the Expense Reimbursements, and that's what I'm trying to fix. I've attached two examples - the red outlined section shows what I'm talking about.

    That's good to know about the CanShrink, I'll look for that.

    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: update SSRS report to show all fields

    GPUG ALL STAR
    Posted May 27, 2020 08:21 AM
    @Vanessa Halacsy,

    It would be more illuminating if you could also post screen shots of the SSRS report design from Report Builder or Visual Studio.

    Is TMW a GP add-on?

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 11:20 AM
    @Steve Erbach GP was bundled with TMW (trucking software) for the accounting part. But the only place I've been able to learn about SSRS reports has been GPUG, so here I am.

    I'm not sure which screenshots would be helpful - when I first open to edit in Microsoft SQL Server Report Builder, I see this:



    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: update SSRS report to show all fields

    GPUG ALL STAR
    Posted May 27, 2020 11:25 AM
    @Vanessa Halacsy,

    OK, that's exactly where you need to be to alter the report... but have you done that sort of thing before? I'm not saying that I've figured out what the problem is... it's just that you need a more hands-on approach. Is this something that the creators of the TMW software could help with?​ They came up with the report layout; I'd say that they'd be the best ones to go to for any modifications.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 11:37 AM
    @Steve Erbach I have done other edits to SSRS reports before, and taken a class at Summit last year, so I'm not -completely- in the dark; TMW charges thousands of dollars for SSRS reports, so I was hoping this would be a small enough change to run down with the aid of the very helpful GPUG people, but you may be right, it might be above my skill level. ​

    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: update SSRS report to show all fields

    GPUG ALL STAR
    Posted May 27, 2020 12:50 PM
    @Vanessa Halacsy,

    There certainly is the budget issue! I sympathize. In this situation, you'd have to offer up so many details to us... and then there's the testing.

    You know, I'd at least ask the TMW folks about it. That looks like a straightforward fix. The Grand Total is correct; you just don't see the additional numbers that make up the Grand Total. Might you kinda turn it back on them to say, hey! This report's incomplete.

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 01:20 PM
    @Steve Erbach Okay, thanks for looking. I'll see what we can get from TMW.

    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: update SSRS report to show all fields

    TOP CONTRIBUTOR
    Posted May 27, 2020 09:23 AM
    Hi Vanessa,

    It can be quite difficult to find out if the text box is hidden by a formula, a table row, or some other item.  From you SSRS editor, you should be able to save the report a RDL file to your local hard drive.  The RDL file is xml formatted text file.  You can open it in notepad and search for stuff - show, hide, visible, hidden.  This may help you find out what object is being hidden.

    Good Luck!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 11:33 AM
    @John Arnold I'll try that, thanks!​

    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: update SSRS report to show all fields

    GPUG ALL STAR
    Posted May 27, 2020 11:37 AM
    Definitely "inside baseball," @John Arnold!​

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: update SSRS report to show all fields

    TOP CONTRIBUTOR
    Posted May 27, 2020 11:49 AM
    @Steve Erbach,  SSRS has thrown me many curve balls, so please don't be foul and balk at the methods I've used after striking out.  ​:)

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 12:28 PM
    @John Arnold, this kind of tip is EXACTLY the reason I read every GPUG forum thread.  You never know what gems are hidden in the outfield!  That's a baseball thing, right?

    ------------------------------
    Sarah Brewster
    Implementation and Support
    ActivReporter by AccountingWare
    Lubbock TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: update SSRS report to show all fields

    GPUG ALL STAR
    Posted May 27, 2020 12:37 PM
    @John Arnold,

    That was good, John. 4, count 'em, four baseball references in a single sentence!​ Next you'll be doing Firehouse Sub ads with all their firefighter references!

    But tell me one thing: do you actually use Notepad [shudder!] for forensics? At least get Notepad++, man!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 16.  RE: update SSRS report to show all fields

    TOP CONTRIBUTOR
    Posted May 27, 2020 01:04 PM
    My goal is to not spend two minutes in the penalty box by leaving Vanessa short handed by mentioning a program she may not have.  Of course I use Notepad++!  You can "check" out my Notepad++ Language xml file for GP macros at http://rnoldz.com/replicator.  Ok... my shift is over... I'm done  :)

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 17.  RE: update SSRS report to show all fields

    SILVER CONTRIBUTOR
    Posted May 27, 2020 01:22 PM
    Haha. I do have and love Notepad++ for many years, so when @John Arnold said "notepad" I mentally adjusted to "notepad++" and opened the file in that program accordingly. (See, I said I wasn't completely in the dark!) ​

    ------------------------------
    Vanessa Halacsy
    Controller
    Wooster Motor Ways, Inc.
    Wooster OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role