7 Replies Latest reply on Aug 20, 2009 11:46 AM by ricklhamilton_1

    Reporting and Grouping Data

    ricklhamilton_1

      Title

      Reporting and Grouping Data

      Post

      Newbie Question

      Trying FM Pro 10.0. I am curiuos how deep I can get with creating reports. Already hit my first wall.  :)

       

      I have a very simple data set and I want to Group the detail under the Category "SubSummary" The dataset looks like:

       

      categoryID(Number) 

      subCategoryID(Number)

      Category (Text)

      SubCat(Text)

      DetailItem1 (Text)

      DetailItem2 (Text)

       

      I have used the report wizzard -Selected these field - told it to use "Group", sort by CategoryID and then by subCatID blah blah

       

      In the layout I have

       

      Header

       

      subSummary  [categoryID]

       

      Body  [subCat] [DetailItem1]   [DetailItem2]

       

      The issue is 2 fold.

      1) The report view is not breaking at the Category

      2) Becasue of this I cannot put a subSummary description on the report

       

      I'm trying to get the report to look like:

       

      MY REPORT

       

      CATEGORY 1

      ____________________________________________

          SubCAT1    Item 1        Item1more detail

          SubCAT1    Item 2        Item2more detail

          SubCAT2    Item 1        Item1more detail

       

      CATEGORY 2

      ____________________________________________

          SubCAT1    Item 1        Item1more detail

          SubCAT1    Item 2        Item2more detail

          SubCAT2    Item 1        Item1more detail

       

       

      I cannot get it to break and when I place the field   [Category]  (text) in the SubSummary it does not appear... at all.

      I imagine there is something hacky I need to do becasue it feels like (and all the documentation covers) is using the Subsummary to calculate sub totals. I don't have that requirement.....

       

       

        • 1. Re: Reporting and Grouping Data
          mrvodka
             Seems like it should be working for you. Did you make sure that it was indeed sroted by: CategoryID first? Try manually sorting the records by the CategoryID field and nothing else.
          • 2. Re: Reporting and Grouping Data
            mrvodka
               Wait I just realized that you put the Category field in that part. Does every record have the same value for Category that corresponds to the CategoryID? Why isnt this in a separate related table? If the first record when sorted doesnt have a value in this Category field, then it could attribute to your issue.
            • 3. Re: Reporting and Grouping Data
              ricklhamilton_1
                

              Yes - I was trying to keep the thread simple. I actually have a fairly normalized table structure

               

              Table Category

              CatID(number)

              CatDescription (Text)

               

              Table SubCategory

              subtCatID(Number)

              subCatDescription(Text)

               

              table Detail

              detailID(Number)

              CatID(Number)

              subCatID(Number)

              detailSeQorder(Number)

              detailItemShort(Text)

              detailItemfull (text)

               

              Relationships are defined. I started by just running the report over the Detail table to get it to break in the correct spot.  It has not worked. I figured once I got the details to break properly - I could bring the descriptions in via the relationships that exist.

               

              It may be that I have a data issue in any of the sort fields CATID, SubCatID  - if I were missing one I need to dig and find it. I have not learned how to really query the tables yet. (I'm and old SQL guy) and this does not seem to expose that too well. Kind of QBE stuff going on.. :)

               

              Rick

               

              • 4. Re: Reporting and Grouping Data
                mrvodka
                   Does it look atny different if you go into Preview mode?
                • 5. Re: Reporting and Grouping Data
                  ricklhamilton_1
                     Nope - looks the same. Everything is there - just no break in the Category...
                  • 6. Re: Reporting and Grouping Data
                    mrvodka
                      

                    Why dont you try and create a competely new one. Do not use the wizard. Just start with a blank new layout and then add a subsummary part ( break CategoryID ) in layout mode. Put the CategoryID field in the subsummary part and maybe oen or two fields in the body.

                     

                    Make sure it ends up being a list view when you go back into browse mode and sort by the

                    • 7. Re: Reporting and Grouping Data
                      ricklhamilton_1
                        

                      I think I figured it out. I had a completely blank record in the data set - this was killing it I think. Once I removed it all seemed to work. Thanks for the help here.

                       

                      Now I can get on with trying to see how sophisticated I can get with reports. I'm trying to actually manage my airplane check-list in this small application. It's to much to do in word processing when you want to make flow changes. I need to get the checklist in a Db with a small app so I can re- order and re word specific tasks and then have it "print" the checklist for me in a particular format that is useful in the cockpit.. Now I off to figure out how to create some good Layouts that allow me to show all the Check list items associated with a particular AC and Checklist- make it easy to reorder them and I will have created a HUGE time savor. Then I will see what it will take to push the entire application to the web  :) 

                       

                      Thank you again for your help - I'm sure I will be back again