12 Replies Latest reply on Feb 17, 2010 1:56 PM by charpham

    Is it possible to get  a results  from following sql script in Report Writer?

    Gob

      Hi. I want to create a Report, but I cannot get a results in Report Writer.

      It's a very simple script.

      IF OBJECT_ID(N'tempdb..#t_out', N'U') IS NOT NULL 
      DROP TABLE #t_out
      create table #t_out (Cap  Varchar(255), Outage int)

      insert #t_out (Cap,Outage)
      select 'asdasdasd',100

      select *
      from #t_out

      DROP TABLE #t_out

        • Re: Is it possible to get  a results  from following sql script in Report Writer?

          Hi Gob--

          I need a little more information in order to help you.

          --what business need are you trying to fill with this report?

          --from where is the data that you are putting into the db in the report originating?

          Let me know,

          Thanks,

          M

            • Re: Is it possible to get  a results  from following sql script in Report Writer?
              Gob

              Hi MarieB. Thank you for your answer. I want to create a Summary Report  from How long each interface or node was down.

              This report provides data on the outages duration for each node in selected time period.  

              This is a full version of my script. It work perfectly in MS SQL Management Studio.

               

              ---!!!!!!!!!!!!!!!!!!!!!!!!!!

              set dateformat dmy

              ---!!!!!!!!!!!!!!!!!!!!!!!!!!

              declare @period smallint

              declare @periodbegin datetime

              declare @periodend datetime

              declare @Sensitivity smallint

               

              --Add here Sensitivity (min), period(days) or periodbegin/periodend==========

              set @Sensitivity=25

              set @period='31'

              set @periodbegin=''

              set @periodend=''

              --======================================================================

              if @period!=''

              begin

              set @periodbegin=(GetDate()-@period)

              set @periodend=getdate()

              end

              if @periodend='' set @periodend=getdate()

               

               

               

              --============================create temp table==========================

              IF OBJECT_ID(N'tempdb..#t_out', N'U') IS NOT NULL

              DROP TABLE #t_out

              create table #t_out (Caption  Varchar(255), OutageDurationInMinutes int)

              --=======================================================================

              insert #t_out (Caption,OutageDurationInMinutes)

               

               

               

              --===================fill in (begin)===================

              SELECT

               Nodes.Caption,

              ------------------------------Add here another column-------------------

              --address,        

              ------------------------------------------------------------------------                     

              DATEDIFF(Mi, StartTime.EventTime, (SELECT TOP 1 EventTime FROM Events AS Endtime

              WHERE

                       EndTime.EventTime > StartTime.EventTime AND

                       EndTime.EventType = 5 AND EndTime.NetObjectType = 'N' AND

                       EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime))

              AS OutageDurationInMinutes

               

               

              FROM Events StartTime INNER JOIN Nodes ON

              StartTime.NetworkNode = Nodes.NodeID

              WHERE

              (StartTime.EventType = 1) AND

              (StartTime.NetObjectType = 'N') AND (

              ---------------------------------period---------------------

               EventTime > @periodbegin

              AND EventTime < @periodend

              -----------------------------------------------------------------------------------

               )and (DATEDIFF(Mi, StartTime.EventTime, (SELECT TOP 1 EventTime FROM Events AS Endtime

              WHERE

              EndTime.EventTime > StartTime.EventTime AND

              EndTime.EventType = 5 AND EndTime.NetObjectType = 'N' AND

              EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)))

              --------------------------------------Sensitivity (min)-----------------------

                                          > @Sensitivity

              ---------------------------------------Add here another filter---------------------

              --                                             and caption like '%pix%'

              -------------------------------------------------------------------------------------                            

               

              ORDER BY caption asc

               --===================fill in (END)===================

               

               

              select caption, sum(OutageDurationInMinutes) as OutageDurationInMinutes,

              cast(floor (sum(OutageDurationInMinutes)/60) as varchar(3))+':'+cast(sum(OutageDurationInMinutes)-60*floor (sum(OutageDurationInMinutes)/60) as varchar(2)) as OutageDurationInHours

              from #t_out

              group by caption

               

              DROP TABLE #t_out

            • Re: Is it possible to get  a results  from following sql script in Report Writer?
              TXNOGAS

              What would be the format of periodbegin= & periodend=???

              I've tried the following???

              periodbegin='02/14/2010' & periodend='02/15/2010'

              periodbegin='2/14/2010' & periodend='2/15/2010'

              periodbegin='02142010' & periodend='02152010'

              periodbegin='2142010' & periodend='2152010'

                • Re: Is it possible to get  a results  from following sql script in Report Writer?
                  cdh

                  I actually hijacked this report too!  :)  (Thanks Gob!)

                  I created 2 reports - Outage Duration by Device - Last 7 Days and Last 31 Days.  The only field I modified was:

                   

                  --Add here Sensitivity (min), period(days) or periodbegin/periodend==========
                  set @Sensitivity=25
                  set @period='31'
                  set @periodbegin=''
                  set @periodend=''

                  I changed it to '7'.

                  Not a SQL expert by any means, but that appears to have done the trick.

                  • Re: Is it possible to get  a results  from following sql script in Report Writer?
                    Gob

                    Hi All.

                     



                    What would be the format of periodbegin= & periodend=???

                     



                    Date format is dmy . If you want to use a custom period you should  set @period to nothing. For example.

                    set @period=''
                    set @periodbegin='20-02-2009'
                    set @periodend='01-03-2009'

                    charpham , you can use any columns from Nodes table for your filter. Including your custom properties. Which columns do you can to use? Try this simple query

                    select top 10 * from nodes

                    in MS SQL Management studio or in Report Writer.

                    Then find this line in my script and correct it for self.


                    ---------------------------------------Add here another filter---------------------

                    --and (device_type='router' or device_type='switch') and device_type<>'firewall' 

                    -------------------------------------------------------------------------------------

                      • Re: Is it possible to get  a results  from following sql script in Report Writer?

                        thanks.. This worked regarding the filters... The one I would like to do is add two more "columns" to the table "Site_Location" and Device_Type" but I am not sure how this can be done.. so it would look something like this...

                         

                        Caption            Site_Location        Device_Type     OutageDurationInHours   OutageDurationInMinustes

                        router XXXX     las vegas               WAN                  1:4                                   64

                         

                        thanks again for your help

                          • Re: Is it possible to get  a results  from following sql script in Report Writer?
                            Gob

                            It's easy.

                            --============================create temp table==========================
                            IF OBJECT_ID(N'tempdb..#t_out', N'U') IS NOT NULL
                            DROP TABLE #t_out
                            create table #t_out (Caption  Varchar(255),Site_Location  Varchar(255),Device_Type  Varchar(255), OutageDurationInMinutes int)
                            --=======================================================================
                            insert #t_out (Caption,Site_Location,Device_Type,OutageDurationInMinutes)

                             

                             

                             

                            ------------------------------Add here another column-------------------
                            Site_Location,Device_Type,    
                            ------------------------------------------------------------------------

                             

                             

                             

                            select caption,Site_Location,Device_Type, sum(OutageDurationInMinutes) as OutageDurationInMinutes,
                            cast(floor (sum(OutageDurationInMinutes)/60) as varchar(3))+':'+cast(sum(OutageDurationInMinutes)-60*floor (sum(OutageDurationInMinutes)/60) as varchar(2)) as OutageDurationInHours
                            from #t_out
                            group by caption,Site_Location,Device_Type