3 Replies Latest reply on Feb 6, 2018 8:42 AM by SSmith14


    chris.machnicki Apprentice

      What's everyone's experience of using SSRS to build reports for HEAT?


      I've been writing a report and after getting it to look and work how I wanted it to in SSRS I proceeded to upload it to HEAT and then spent litterally days going between HEAT and SSRS trying to get something that HEAT will accept without throwing errors. The report mostly works now with the exception of a nested iif expression that - took me almost 2 hours to create, works in SSRS but errors in HEAT. In getting to this point I had to restructure my query several times to account for the fact HEAT only allows datetime not date ranges meaning the user sees a time but the sql ignores it, was unable to filter without setting offset values and had to embed a default set of parameters into the query because =DateAdd("d", -5, Now()) wouldn't work as a default expression - all the time these worked fine in SSRS.


      I've essentially got to the point where I can't create reports in HEAT unless I'm willing to give up days flipping between two systems and want a simple table/matrix, with limited filters, no default values (using expressions), the query is structured in such a way that it only brings back static set of results and even then it probably won't work how I want it to.

        • 1. Re: Reporting?
          SusanJS Specialist

          Hi Chris,

          I'm not sure I totally understand what you are trying to do, but I typically use either the built-in date range options of day/week/month, etc. or I force the user to select a specific date.  I've found that unless the parameters are set as internal, the defaults are often not passed to the application.  At that point, I just set the default parameter to something simple like Now() and make the report runner choose the specific dates.

          • 2. Re: Reporting?
            chris.machnicki Apprentice


            What I was trying to achieve was to run one dataset which brings back all the results and then filters basically organise the data.

            So I built the query on this basis passing no parameters through instead passing the parameters through to the filters.

            The report was basically bringing back a matrix with collapsible fields on all completed tasks with a parameterised filter by team and within a date range i.e. the previous 5 days

            There was also a table which just had ALL outstanding tasks and a parameterised filter on team.

            The rest were niceties i.e. adding nd, th, rd and st tot heend of dates etc.

            The problem I had was that the date comparison I was looking at used the resolveddatetime which included an offset but the value I wanted to pass through was date. All the time this worked in BIDs so I didn't expect any problems but found it didn't work in HEAT so I had to rewrite the query so it included a default offset. Once I got this working I wanted to pass through just a date, got this working in BIDs and then found it only allowed a datetime. I've finally got to the point where it works (sort of) but only by hardcoding a default date into the query but then overiding this if the user enters a datetime, the user still sees a time but this is stripped off. As for the date formatting I started by adding custom code - worked in BIDs but not in HEAT so I rewrote a query nesting about 30 if statements and again works in BIDs but not in HEAT.

            Our account managers going to setup a meeting with Randee so that we can discuss the issues I've hit and go from there.



            • 3. Re: Reporting?
              SSmith14 Rookie

              I'm longing for the days of Crystal Reports. It's *very* clear they didn't want to own the reporting aspects of their software, and integration with SSRS is the result.