1 2 Previous Next 17 Replies Latest reply on Nov 19, 2019 2:29 PM by Marek Ceizel

    SmartReporting Report - like Calendar

    Marek Ceizel
      Share This:

      Dear experts

       

      I need help with a report which should actually substitute the Calendar functionality (the Calendar 9.1.04 is no useable for us - Change Calendar 9.1.04 slow startup )

      We have now this output in Calendar:

       

      It is all about Scheduled Start Date and Scheduled End Date/.

      - On the X Axis are the seven days of this week and we can see in the upper part Changes which runs Though more Days.

      - On the bottom are Tickets which run in the same day.

       

      Actually this is general description of Calendar view.

       

      Anybody has idea how to show a timeline and there tickets so, that they fill the timerange from Scheduled Start to Scheduled End date ?

       

      thanks a lot

       

      best regards

      Marek

        • 1. Re: SmartReporting Report - like Calendar
          Marek Ceizel

          to make it less complicated. I have one main requirement. Is it possible to get something like this in SmartReporting ?

          example.jpg

          I need to have Xaxis Timeline and Yaxis the tickets. The timerange between scheduled start date and scheduled end date should be filled in the ticket line.

           

          An ideas ?

           

          thanks a lot for help

          • 2. Re: SmartReporting Report - like Calendar
            Marek Ceizel

            Hello,

             

            There is no easy way to replicate at least the basic calendar functionality / displaying in SmartReporting. What I mean is a report with TimeRange in Xaxis and Tickets in Yaxis. Where the lines in Xaxis will be filled from Scheduled Start till Scheduled End date.

             

            I asked directly on Yellowfin forum . This Chart is named Gantt Chart and can be achieved only using JavaScript:

            https://community.yellowfinbi.com/topic/chart-with-timerange

             

            I'm not sure if this is possible to implement into SmartReporting but I'll maybe check later.

             

            regards

            Marek

            1 of 1 people found this helpful
            • 3. Re: SmartReporting Report - like Calendar
              Marek Ceizel

              Maybe one more question. My requirement is really easy.

              How to make Xaxis a timeline. For example if I want show

              x-timeline

              y-number of tickets (bar)

               

              So for example 6 days and so that if there was no ticket there will be the day but without bar.

               

              what should be the the data mapped to Xaxis ?

               

              thanks a lot

              Marek

              • 4. Re: SmartReporting Report - like Calendar
                Arlene Paraiso

                Hi Marek,

                 

                We have the same requirement. Did you get the chance to make it work from Smart Reporting?

                • 5. Re: SmartReporting Report - like Calendar
                  Marek Ceizel

                  Hi Arlene,

                   

                  not with smartreporting. i used birt and quite crazy qualification which at the end generates the reports i need

                   

                  regards

                  marek

                  • 6. Re: SmartReporting Report - like Calendar
                    Sinisa Mikor

                    Hi Arlene,

                     

                    would report structured similarly to what I described in Last 12 Months Report in between years - In Smart Reporting help?  Basic idea on how to create is there, but it would have to be modified to fit these requirements.

                    • 7. Re: SmartReporting Report - like Calendar
                      Sinisa Mikor

                      Here's quick illustration of what I meant earlier:

                       

                       

                      Start hour and end hour are "extracted" from Scheduled Start Date and Scheduled End Date, respectively, though not the way they should be done in actual report -- this is just an illustration of displaying Gantt chart without eponymous chart.  They are not really necessary for it to paint intervals correctly, and may not save any time when declaring hourly columns unless you're forced to use Simple Formula Type; I'd recommend Freehand SQL because it allows pasting SQL fragments.

                       

                      Basic idea is to fill, in this example hourly, columns with some value if they cover time within scheduled interval -- that means that Scheduled Start Date must precede or fall within displayed interval (thus Scheduled Start Date <= {hour start}) and Scheduled End Date must succeed or fall within the same time interval (thus Scheduled End Date >= {hour end}).

                       

                      Painting is done by simple conditional formatting -- if Hour n isn't empty, paint it green.  It could be made more colorful (e. g. different colors could be used for different risk levels), but then this example might look too complicated.

                       

                      SELECT DISTINCT
                          `CHG:Infrastructure Change`.`Infrastructure Change ID`,
                          HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1,
                          HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 1 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 1
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 2 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 2
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 3 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 3
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 4 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 4
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 5 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 5
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 6 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 6
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 7 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 7
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 8 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 8
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 9 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 9
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 10 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 10
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 11 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 11
                                THEN
                                    '+'
                          END,
                          CASE
                                WHEN
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled Start Date`) % 12 + 1 <= 12 AND
                                    HOUR(`CHG:Infrastructure Change`.`Scheduled End Date`) % 24 + 1 >= 12
                                THEN
                                    '+'
                          END
                      FROM
                          `AR System Schema`.`CHG:Infrastructure Change`
                      3 of 3 people found this helpful
                      • 8. Re: SmartReporting Report - like Calendar
                        Arlene Paraiso

                        Hi Sinisa,

                        This is what i'm looking for, but instead of hours can it be displayed by say weekly/monthly based on the scheduled start and end dates? Is it ok to share the xml to me so that I can import it on my environment and play with it? Thanks so much for your inputs, much appreciated.

                        • 9. Re: SmartReporting Report - like Calendar
                          Sinisa Mikor

                          In general, time scale need not be limited to hours -- intervals can be adjusted to display any type of time units from seconds to years (one hour per column just happened to be time scale used in my example).  For example, you could use one day per column scale to display monthly calendar -- it might display thirty-seven columns (first seven columns to start at any week day, twenty-eight more columns to cover next four weeks, and two more columns in case first day of month is last or next to last week day and month has thirty or thirty-one days) per row, with individual cells filled in where necessary.  What I envision as possibly limiting factor is lack of fixed points in time, so displaying something like every change stretching over last three months may be difficult if each month must be shown in separate "row"/section; I also haven't tested if it is feasible yet, but if calculated fields were defined at view level, it might even be possible to drill down.  My Smart Reporting is currently unavailable, so I can't export xml right now; once it's back, I'll finish report and post it.

                          2 of 2 people found this helpful
                          • 10. Re: SmartReporting Report - like Calendar
                            Arlene Paraiso

                            Thanks Sinisa. Looking forward to the xml

                            • 11. Re: SmartReporting Report - like Calendar
                              Jason Miller

                              That is  a creative solution.

                               

                              gold-star.jpg

                              • 12. Re: SmartReporting Report - like Calendar
                                Sinisa Mikor

                                There are a few tidbits left to clean up, but that should be done by next week.  In the meantime, here's a preview of draft version which actually uses entire dates from Scheduled Start Date and Scheduled End Date.

                                 

                                 

                                Most of it is monochrome for the moment, except column Tue 2 -- colors there correspond to Risk Level.

                                2 of 2 people found this helpful
                                • 13. Re: SmartReporting Report - like Calendar
                                  Sinisa Mikor

                                  Column Mon 1 offered a flimsy excuse for not appearing in that illustration, so it's being put back into report.

                                  • 14. Re: SmartReporting Report - like Calendar
                                    Sinisa Mikor

                                    Thank you, I'd hang it in my profile's gallery, but (perhaps contrary to popular opinion) it's already full.

                                    1 2 Previous Next