14 Replies Latest reply on Jul 3, 2015 2:47 PM by mfontaine

    Crystal Reports - Need help

    Apprentice

      Hello all,

       

      I'm just starting using Crystal Reports, my manager ask me to create a report with few informations he needs. I took a crystal report class few weeks ago, but this class was only to help me to understand the interface.

       

      The first thing I'm trying to get on the report is:

      Total of incident from date x to y

      Total of problem from date x to y

      Total of request from date x to y

       

      Than I'll make a graphic with those numbers to be able to compare the difference between those 3 processes.

      The problem I have right now, I can get the total of incidents, but when I want to get the total of problem, I get no data, loosing the count of incident also.

       

      On DB expert I selected im_incident, pr_problem, rm_request and pm_process. I removed all links except them to the pm_process.

      I created 2 parameters field, one for starting date and another one for end date

      I created 3 formula field :

      - one for the date : Date(ShiftDateTime({pm_process.pm_creation_date}, "UTC,0",""))

      - one for the incident count: Count ({im_incident.im_id})

      - one for the problem count: Count ({pr_problem.pr_id})

       

      I can put either Incident Count or Problem Count, both working individualy, but when I put both of them in my report, I get no data from my formula.

       

      Anyone can help me on this?

       

      Thank you!

        • 1. Re: Crystal Reports - Need help
          Apprentice

          Hi,

           

          It's quite difficult to understand exactly how you are set-up to be honest! If you are trying to get Incidents, Requests and Problems you will obviously need to make sure you have your JOINS setup correctly otherwise pm_process will be looking for records which correspond to all im_incident, pr_problem and rm_request.

           

          You say you have im_incident working, now I'd suggest setting that link to be a Outer Join (depending on your links) to pm_process and then doing the same for pr_problem. Put something in the details section and confirm that you can see data from both Incident and Problem then repeat for Request.

           

          Once you've done this I'll give you some help on how to get the counts of all three types.

          • 2. Re: Crystal Reports - Need help
            Apprentice

            Hi Dr4ke,

             

            First of all, thanks for your help!

             

            Here a screenshot of my link right now. To be honest, I don't know what you meant by Outer Join:

            Crystal-BDLink.jpg

             

            So to simplify what I was saying earlier, I have 2 formula field:

            Count Incident : Count ({im_incident.im_id})

            Count Problem : Count ({pr_problem.pr_id})

             

            Those 2 formula works fine as long as I don't put them 2 on the report:

            Only Incident:

            Crystal-Incident.jpg

            Only Problem:

            Crystal-Prob.jpg

            When I use both:

            Crystal-Both.jpgCrystal-Concept.jpg

             

            I probably do something wrong, hope you can help me!

             

            Other question while I'm here:

            - How can I adjust the number show in my formula to don't show the 2 digits?

            - I have to do the same thing with request, but I have to also add a lifecycle condition (ex: count(rm_request.rm_id) where rm_request.lifecycle.title = YQB request ) Well... something like this, not sure how to proper type it

             

            Thanks for your help

            • 3. Re: Crystal Reports - Need help
              dmshimself ITSMMVPGroup

              I'd create 3 reports and then make them sub-reports into the main one.  It keeps things simple and you don't suddenly get your whole report changing because of a join added in another section

              • 4. Re: Crystal Reports - Need help
                Apprentice

                Morning,

                 

                First off; no disrespect to dmshimself but I would strongly recommend against using subreports for this. There is no need and it would be worth your while to understand how to write reports without the need to use them; it will improve the performance of the reports etc.

                 

                Secondly I would bet money on the fact that your JOINS are wrong. This is where you also need to get some knowledge in databases as well - you need to think about what you are doing :-). You're joining im_incident to pm_process as an INNER JOIN (which means all records must match the pm_guid in both tables), you then do a count and it works. You do the same for pr_problem and the same happens. When you introduce both counts into the report it is looking for records where pm_guid matches all 3 tables; which will never exist (i.e. it's looking for tickets that exist in both your Incident and your Problem modules).

                 

                For now you need to start right at the bottom and work your way up, at the moment you are trying to make the end product work without really having an idea about how it fits together and, worse, why it's breaking. So until it's working, forget your counts. We can work on that later.

                 

                Using your first screenshot - go back to this screen have pr_problem and im_incident linked to pm_process. Once you have done this double click the line and a new window will pop up - you will have options on the left hand side for 'Right Outer Join' and 'Left Outer Join' (as well as some others), at the top of the window it will say something like pm_process.pm_guid => im_incident.pm_guid.

                 

                It it's pm_process.pm_guid => im_incident.pm_guid set it to a LEFT OUTER JOIN, if it's the other way round (im_incident.pm_guid => pm_process.pm_guid) then set it to a RIGHT OUTER JOIN. Alternatively feel free to post a screenshot and I'll tell you which one you need to use. Do this for both tables.

                 

                Next add in a field from both tables in the report details section (it doesn't matter what field you chose, you'll get rid of it later) - if the report is blank you've done something wrong. If it is populated then it's working.

                 

                I can help you through this but it would be extremely valuable for you to understand database schema's, JOINS, SQL Querys etc. This will help you in the future when your reports get more complicated.

                 

                Hope this helps and drop a reply if you get stuck.

                1 of 1 people found this helpful
                • 5. Re: Crystal Reports - Need help
                  karenpeacock SupportEmployee

                  I recorded a video tutorial a while back specifically just about linking tables in Crystal.  I hadn't posted it yet because I felt it needed to be re-recorded to make it not so long and (dare I say it) not so boring Anyway until I get to re-recording I thought it might be helpful to share to further explain the great advice you've been given from Dr4ke and dmshimself.

                   

                  I think the reason that a sub-report was suggested would be if you wanted to include an actual list of incident or problems on your report (for example list all the incident ids and dates they were created) you may end up seeing duplicate rows if you dragged some problem and incident fields into the one report details section.  If you just want to show an overall total you might be ok.  I'd do a distinct count on the id field though rather than the count you've done so that any possible duplicate records won't be counted towards your total.  (It should explain the difference between a distinct count and a count in the Crystal help if you haven't come across this before)

                   

                   

                  If you want to see some better (and more exciting!) video tutorials I can usually find some nice short Crystal tutorial snippets on popular video sharing sites such as YouTube.  I use this a lot when trying to figure out something new in Crystal.

                   

                  Hope this helps

                  Karen

                  1 of 1 people found this helpful
                  • 6. Re: Crystal Reports - Need help
                    Apprentice

                    I just watched a little of your video karenpeacock and you've done yourself a disservice by hinting at it being boring . Good job at making it - I'm sure that's going to help OP more than anything put on this thread so far.

                    • 7. Re: Crystal Reports - Need help
                      karenpeacock SupportEmployee

                      Thanks!  I've posted it as a KB document now, pending approval, as that will make it easier to find in future.  Hopefully I will make it better at some point too

                      • 8. Re: Crystal Reports - Need help
                        Apprentice

                        Wow! Thanks guys! Your advices are really usefull

                         

                        Now that I know how to set my link properlly, I just need to understand how to make my own formula.

                         

                        Like I said earlier, I was using simple formula so far : Count ({im_incident.im_id})

                        Can you explain me the distinct count you were talking about Karen?

                         

                        I'm not really sure how to make more complicated formula... Can I simply use SQL formula? I need to use more complexe count, like counting request which have lifecycle.title == "name" and request which have rm.request.externe == "true". If you guys can give me a good exemple I should be able to work around all my critiera myself.

                         

                        Also, how can I adjust the number show in my formula to don't show the 2 digits?

                         

                        Once again, thanks for your help!

                        • 9. Re: Crystal Reports - Need help
                          Apprentice

                          There is no need to create a custom formula for this. What you should do is insert a 'Summary' (or three) to count a field from im_incident, pr_problem and rm_request.

                          • 10. Re: Crystal Reports - Need help
                            Apprentice

                            I can't use Summary since I have 2 distinct process under request that I need to separate. I tried using Running Total:

                            Field to summarize: rm_request.rm_id

                            Type : distinct count

                            Evaluate : Formula : {lc_lifecycle_description.lc_title} = "YQB Simple Request"

                            Reset: Never

                             

                            I get 0, mean I got something wrond. Maybe my formula is wrong, or it's my link. Here a screenshot of my link:

                            Crystal-Link.jpg

                            So I linked rm_request.pm_guid to pm_process.pm_guid as a Right Outter Join.

                            I linked pm_process.pm_lifecycle_guid to lc_lifecycle_description.lc_guid as a Right Outter Join. I also tried internal join.

                             

                            Can you see what I did wrong?

                             

                            Thank you!

                            • 11. Re: Crystal Reports - Need help
                              Apprentice

                              I don't really understand what you are doing with your links; you said you just want to count the number of Requests/Problems and Incidents in a given time frame? If this is the case you only need these tables:

                               

                              im_incident

                              pr_problem

                              rm_request

                               

                              Did you do what I suggested before? Have you tried to do this one step at a time; i.e:

                               

                              1) attach im_incident to pm_process (delete all other tables)

                              2) add a field from im_incident to Details

                              3) confirm that you can see records.

                               

                              If you can:

                               

                              4) attach rm_request to pm_process (keeping im_incident as it is)

                              5) add a field from rm_request to Details (keeping the field from im_incident where it is)

                              6) confirm that you can see both fields (from im_incident and rm_request) and there is data. If there isn't your joins are wrong.

                               

                              If you can see data from both tables repeat for pr_problem.

                               

                               

                              Honestly; it seems like you're rushing to get this finished without thinking about what you're doing. I don't mean that in a harsh way, but you need to take one step at a time and find out where it's failing. This will be much easier than doing everything then trying to problem solve.

                               

                              I'm happy to help you but we need to go through each stage first; it will be extremely difficult for me to help you while everything is in place. Like I said before; forget your formula's, forget your counts, make sure you actually have the data available first.

                              • 12. Re: Crystal Reports - Need help
                                Apprentice

                                Nevermind, it's working, depending where I put it on my report. I'll do more test and let you know if it's ok! Thank you

                                • 13. Re: Crystal Reports - Need help
                                  Apprentice

                                  Glad it's working for you!

                                   

                                  If you're referring to the counts working depending on where you put them on the report consider this; Crystal has a serious of 'stages' it goes through when producing a report (there will be information about them online and I'm afraid I can't remember all of the stages off the top of my head so I'll not give you a half complete reply) and depending on where you put your counts will determine what information you get back.

                                   

                                  So imagine you're report in this structure:

                                   

                                  Report Header

                                  Page Header

                                  Details

                                  Page Footer

                                  Report Footer

                                   

                                  If you put a count in the Report Header then it is trying to evaluate the counts before it looks at the Details section (you can actually get around that but, for simplicity, I'm not going to get into it). Remembering that the Details section is actually where all of the data resides, whether you have populated it or not, you are effectively asking Crystal to count the number of records before it even knows they exist.

                                   

                                  If you put the count in the Report Footer it will evaluate the counts after it has looked/populated the Details section so it knows how many records you have and will display the correct figure.

                                   

                                  This is very high level and there are a number of caveats involved as this isn't always true (e.g. you can get counts in the Report Header if you really wanted) but hopefully it will help you along your way to understanding why it works in one section and not another.

                                  • 14. Re: Crystal Reports - Need help
                                    Apprentice

                                    Thanks for your help Drake and Karen, I was able to complete my report with your advices! It's really appreciated