8 Replies Latest reply on May 27, 2009 8:04 AM by Raghavendra_Mudugal

    Custom SQL script for populating the data and using it in CR

    Apprentice

      Hy;

       

      Say we create one SQL script (stored procedure) for getting the data from different tables under incident module and use this script in crystal report (CR) and create a new one. Is this possible?

       

      * as for now, the query and report creation do not help in the creating in the custom format and it doesn't make any sense on how the data is displayed. So it would be great, by getting the data in stored procedure and use in CR and create custom format and place all the data in the way we required. In our org.. we need so many complex reports which "query and report creation" will not help us in any way, They are very light version of data display. If the above said is possible them, we have lots of complex reports to create.

       

      Let me know.

        • 1. Re: Custom SQL script for populating the data and using it in CR
          masterpetz ITSMMVPGroup

          Hi,

           

          there is no need to create stored procedures for collecting data! Crystal Reports can access all tables in the database and is very flexible. The only thing for you to do is find the matching primary keys to link the tables. Maybe i missunderstand you completely but how should a stored procedure help you, collecting data for a report. Normaly this is handled by views and views can be build in CR or on the SQL server.

          If you tell us a little bit more detailed what your goal is, its easier to help you.

           

          Best regrads

          Christian

          • 2. Re: Custom SQL script for populating the data and using it in CR
            Apprentice

            Hello Christian;

             

            Thank you for the reply.

             

            I completely agree with tables way of doing in CR. These are good for some tables, say 4 or 5. When the table  count reaches more than 15 (and coming)... table way is really a high maintenance, and fetching the data from gigis of rows from the  db, gives very poor performance. This will create a dynamic "view" for ferching the data. and when more than 146+ users access this report to view, you know what I mean....!

             

            Using views are good in general where you need the output without input parameters to filter the data, If you build a SP with all proper joines and fetching only those columns needed to display... gives better performance and can also have the input parameters to pass the value and fetch the data accordingly. Views are parsed at runtime and dynamic views where the parameter are set at the runtime and then parse them its a time killer. Here SP is parsed once in the db and usage is many.

             

            So, SP is like a best answer when used with CR. Chosing the SP over VIEW is more reliable.

             

            (This is completely my theory, but others way of seeing ofcourse change.)

             

            Actually. What I am trying to do is... for example...

             

            incident table... conneted to other 10 user tables (where we gather some additional info depending on the action seleted and which goes with privilliges) Now when you want to see a single incident and its entire communication happened betweew users which are stored in 10 respective tables, I can gather all these infos put into a single temp table sort them on date wise and populate the data as we need.

             

            Here what is happening is consider two actions

             

            MANAGER

            - "Send to Team Member" - ACTION-1 (A1)

             

            TEAM MEMBER

            - "Return to Manager" - ACTION-2 (A2)

             

            Now say the communication is happening

             

            A1

             

            then

             

            A2

             

            then

             

            may be A3

             

            then

             

            A1

             

            then A2

             

            See, these datas are stored in three diffrerent user tables, when I create a report using "query and report design", and when you place the attribute (which is collection type) it shows A1- and its list. in anohter tab A2- and its list and A3.... A4 and so on.

             

            When a user looks in to the report it does not gives the communication a proper link...  and  we do not like to see the A1 collections A2 and A3 in different tabs all need to be display as under one, sort them by date and it can be easily traced.

             

            As we know this cannot be done in 7.2.6 and not ever in 7.3 (I presume). So  I need to go with SP collect all the infor in single temp table, organize the data the way we want, and then just arrange them in CR, which should make sense.

             

            Well;

             

            I guess i tried to put here as i am seeing it... hope this might give you a some-what clear idea then before.

             

            Let me know.

            • 3. Re: Custom SQL script for populating the data and using it in CR
              masterpetz ITSMMVPGroup

              Hi Raghavendra

               

              i don't think, the build in Report designer will do such things in the future. For this reason, use Crystal Reports.

               

              To you statement:

              "...incident table... conneted to other 10 user tables (where we gather some additional info depending on the action seleted and which goes with privilliges) Now when you want to see a single incident and its entire communication happened betweew users which are stored in 10 respective tables, I can gather all these infos put into a single temp table sort them on date wise and populate the data as we need...."

               

              thats exactly what a view is for. You can build your view with joins and selects and whatever you need and the view is always an actual reflection of the involved tables. A SP must be started manually or by a scheduled plan but you cann't execute it by generating a report. So the SP is never that accurate as a view.

              And i can't believe, that the performance is going down by using a view.

               

              Everything else is "just" report designing. But with CR, you can do these things if you spend a bit time with it.

               

              Best regards

              Christian

              • 4. Re: Custom SQL script for populating the data and using it in CR
                Apprentice

                i don't think, the build in Report designer will do such things in the future. For this reason, use Crystal Reports.

                 

                Thank you.  I know.

                 

                 

                To you statement:

                "...incident table... conneted to other 10 user tables (where we gather some additional info depending on the action seleted and which goes with privilliges) Now when you want to see a single incident and its entire communication happened betweew users which are stored in 10 respective tables, I can gather all these infos put into a single temp table sort them on date wise and populate the data as we need...."

                 

                thats exactly what a view is for. You can build your view with joins and selects and whatever you need and the view is always an actual reflection of the involved tables. A SP must be started manually or by a scheduled plan but you cann't execute it by generating a report. So the SP is never that accurate as a view.

                And i can't believe, that the performance is going down by using a view.

                 

                I guess you missed one thing in my comment, I never said that views cannot serve this purpose, all I was referring to the parsing the view and executing it.  Views cannot be parameterized. as my report is based on single incident where i need incident number as a user choice. Say if you mention in "select * from im_incident where im_id = 1" or replace the "1" with "?" is fine with the view, but considering of joining 15+ tables and wtih multiple criteria with different "where" condition... I never go with VIEW. The CR has the ability of creating the report from output of SP. Its all the matter of creating it. Most of all I am not creating a JOB in sql server, which needs to be executed on a scheduled time. SP is a SP, it executes when you want to execute it, depends up on when you call it. And we all know its SP is much faster execution than a raw sql statement (view).

                 

                Note: When a paramater value in the view is changed, its new view for SQL, but for SP, its same but different value, so it not need to be parsed before execution.

                 

                "So the SP is never that accurate as a view."

                 

                I did not understood the word "accurate" here?

                 

                Everything else is "just" report designing. But with CR, you can do these things if you spend a bit time with it.

                 

                Yes, this is the whole plan, if the CR serves my purpose by this SP and custom reporing stuff, then I am on it for creating the complex reports as needed. Now I am doing RnD in CR.

                 

                Thank You.

                Raghavendra Mudugal,

                • 5. Re: Custom SQL script for populating the data and using it in CR
                  oku Apprentice

                  does the report you want to produce is something similiar like the audit trail reports ?

                  • 7. Re: Custom SQL script for populating the data and using it in CR
                    aparker Employee

                    What is being talked about here is the audit tables that are automaitcally populated each time an action is applied to a process. From what I can gather you are looking to report, as a single report, each action in date order? If this is the case, I would suggest starting with the audit trail table and see where you get to from there. I suspect that it might remove a large amount of the complexity of joining many tables. Having siad that, there are other users who are doing what you're doing as pure Crystal and they seem to be ok with it.

                     

                    There is an alternate approach that can be achieved using process design. This entails creating an audit collection that will log in a single table the data from other actions. You do need to put some thought into how you would design that object, but it also has worked pretty well on some installations.

                     

                    Andy

                    • 8. Re: Custom SQL script for populating the data and using it in CR
                      Apprentice

                      Hello Andy! Thanks for the reply.

                       

                      Okay;

                       

                      So how do you eliminate the older and one pick the current one?

                       

                      Audit Trail contains the data of data changing. Say one object and its window... you add the data and save it, then you open that window and modify the data, then save it. so the audit on this object is two rows. How you will come to know which is the current one? by max of the date? or any flagging mechanism?

                       

                       

                      Okay;

                       

                      From the table "tps_audit_trail" you are saying that i can pick tha data related to one incident number where the other 13 objects are mapped and related to this incident? So rather than conneting 13 tables in a join, I can direclty pull the data from single table? Then thats really great!

                       

                      So how do i get the value of a control say "Details" in one of the window, which is the part of the incident process? I am not sure on this table data methodolgy.

                       

                      You have the: tps_object_ref, tps_object_guid, tps_value_object_ref, tps_value_object_guid - so playing with these column values?

                       

                      Need more help here.

                       

                      Thanks a lot.