7 Replies Latest reply on Aug 17, 2011 4:50 PM by hadyn.dawson

    Crystal Reports Parameter

    Rookie

      Im wanting to create crystal report that allows users to select all incidents logged under a particular category.

       

      I originally set the parameter to look at "incident category full name", however due to the number of Categories in the list it only gets down to Application C to D. (See Fullname.gif)

       

      So i next decided i could create seperate parameters for each tier (one for top, middle and bottom), hoping if i selected "Application A-B" in the top tier parameter, then the Middle Tier Parameter would only show Categories between A-B using filtering. However this just shows all categories regardless of the Top Tier Parameter.

       

      I just want to create a simple report that can be used for Trending by selecting all Incidents under a particular Incident Category, which then can be manipulated to show how many incidents have been logged monthly.

       

      Can someone Help??? Or share their experiences how they have got round this issue?

        • 1. Re: Crystal Reports Parameter
          Expert

          You can create a view on the database to return the Parent Categories using the script below-

           

          CREATE VIEW ParentCategory AS
          SELECT im_guid AS "GUID", im_title AS "Title"
          FROM im_incident_category WHERE im_parent_guid is NULL

           

          Then in your report add this view and create a dynamic list parameters to pull through the Title field from the view and the im_title field from the im_incident_category table after that. Then in your record selection formula enter-

           

          {im_incident_category.im_full_name} =
          {?Category - Title} + " - " + {?Category - im_title}

           

          This will then prompt you for a parent first and then a child second (which is filtered based on the Parent selection). This example works for two levels of categories. For 3 or more you can simply create another view to return only the "middle" categories and add that between the parent and child. An example view would be-

           

          CREATE VIEW ParentCategory AS
          SELECT im_guid AS "GUID", im_title AS "Title"
          FROM im_incident_category WHERE im_parent_guid <> NULL AND im_guid IN

          (SELECT im_parent_guid FROM im_incident_category)

           

          When linking in the report link GUID from the view to im_parent_guid on the category table. Hope this info helps.

           

          Cheers,

          Hadyn

          1 of 1 people found this helpful
          • 2. Re: Crystal Reports Parameter
            Rookie

            Thanks Haydn the two tier filtering works great!!

             

            However im still struggling with the 3 tier category lists, im not sure if I'm linking the tables incorrectly but ive tried multiple combinations with no sucess so im unsure if im still doing something wrong?

             

            I've created the following Views

             

            Top Category View -

            CREATE VIEW ParentCategory AS

            SELECT im_guid AS "GUID", im_title AS "Title"

            FROM im_incident_category WHERE im_parent_guid is NULL

             

            Middle Tier Category

            CREATE VIEW ParentCategory AS

            SELECT im_guid AS "GUID", im_title AS "Title"

            FROM im_incident_category WHERE im_parent_guid <> NULL AND im_guid IN

            (SELECT im_parent_guid FROM im_incident_category)

             

            Ive linked them in the following way but im not sure this is correct as then i just get blank fields in my Parameters? Am i doing something wrong?

            See attached Screenshot how i have linked these.

            • 3. Re: Crystal Reports Parameter
              Expert

              Sorry, slight syntax error in the mid-level view. I never tested it as I only did this on a two-tier system but this should work as a mid-tier view-

               

              CREATE VIEW MidCategory AS
              SELECT im_guid AS "GUID", im_title AS "Title"
              FROM im_incident_category WHERE im_parent_guid IS NOT NULL AND im_guid IN
              (SELECT im_parent_guid FROM im_incident_category)

               

              The linking looked correct so with the amended view it should be correct.

               

              Cheers,

              Hadyn

              • 4. Re: Crystal Reports Parameter
                Rookie

                Hi Hadyn,

                 

                Ive amended the view to the above however now im finding the following when i try to use the middle tier view, see attached screenshot1&2 and actual parameter screenshot3?

                 


                Also how do you input the selection from the parameter into the selection expert, as the field only displays the bottom field selected. see screenshot4 to explain further? i need to be able to input something like the following into the selection expert

                im_incident_category = {?Category.Toplevel)+{?Category.Middlelevel} if that makes sense, as from what i can see i only have one field generated by the parameter?

                 

                Hopefully that makes sense, as ive spend all day changing combinations and linking of the category views and dont know what is wrong?

                 

                Cheers

                Adam

                • 5. Re: Crystal Reports Parameter
                  Expert

                  Hi Adam,

                   

                  What might prove easier is if I give you a report with the linking and views/tables added already. So first step, run the attached SQL scripts to create the appropriate views on your database then edit the attached report and point it at your database then it should be right to go. Simply add fields/tables as you require them and redesign as necessary.

                   

                  Hope this helps.

                   

                  Cheers,

                  Hadyn

                  • 6. Re: Crystal Reports Parameter
                    Rookie

                    Hi Hadyn,

                     

                    The report works great thank you for your help its really appreciated, I think the problem exists with our category list being far too large as it only shows half the incident categories within the first drop down for the parent category.

                     

                    See screenshots to explain but im assuming that this is a constraint of Crystal?

                     

                    Thanks

                    Adam

                    • 7. Re: Crystal Reports Parameter
                      Expert

                      Hi Adam,

                       

                      It should display the full list, so something else must be happening here but without looking at your database it is hard to troubleshoot. You could raise this with support and see if they can offer any insights as to why this might be occurring, but they may not support a custom report like this.

                       

                      Sorry I can't be of more help on that, but like I said it displays very large category lists for me when I connect it to a database with large category lists. This is Crystal 11 too, if that makes any difference.

                       

                      Cheers,

                      Hadyn