7 Replies Latest reply on Oct 13, 2010 7:07 AM by Edwin Lindeman

    How to get Count Summary

    Edwin Lindeman
      Share This:

      Hi,

       

      I'm sure this is something simple but i can't seem to figure it out.

       

      I'm reporting on Object Name and Object Types from Object Authorization. I'm filtering by BLPackage

       

      I put both of those items in a List and if i do a count on Object Name I get summary count at the bottom of the list which shows how many rows got pulled back.

       

      However I want to actaully just get the total count of how many BLPackages exist on one row. I don't want to scroll through a list to the bottom to find out how many total items were there.  Whats the best way to do this?

       

      I hope that made sense.

       

      Thank you

        • 1. Re: How to get Count Summary

          Could you post a screenshot of this scenario for me to get a better idea of how your report looks like?

          • 2. Re: How to get Count Summary
            Edwin Lindeman

            Hi Naveen here is an example. I have Object Type where filter = BLPackage then i have Object Name in a 2nd column. I did a Count on Object Name which at bottoms shows total count. I want to show just the summary of the total account in one row. Like my columns would be Object Type and Object Type Total Count.

             

             

             

             

            liberty01 Oct. 12 17.09.gif

            • 3. Re: How to get Count Summary

              Select the Object Type column and use "Group By" function. This will group your data by BL Package.

              • 4. Re: How to get Count Summary
                Edwin Lindeman

                that groups by BL Package, but all the object names still appear on the list so the same number of rows appear. My goal is to have total count of BLPackages in 1 row. like below.

                 

                Package TypePackage Count
                BLPackage2995
                • 5. Re: How to get Count Summary
                  Edwin Lindeman

                  So I kind of did it by by Hiding the List Column Body under Page Structure. So the only see i see is the Column Title and the Summary which i was able to rename. The results look like this.

                  liberty01 Oct. 12 20.52.gif

                  Not sure if this is a good way to do it but it works. If anyone has a better way please let me know.

                   

                  Thank you

                  • 6. Re: How to get Count Summary
                    Jon Mikula

                    the way you did this will provide you with the details that your looking for.  But is is not the most convenient way of doing so.

                     

                    There is a much simpler way of doing this.  Remember how powerful Cognos can be.

                     

                    You added the Data Item 'Object Name' to your report.  So think of it in Database terms.  If you wanted a count on a table you would run a SQL statement like:

                     

                    select count(*) from table_name;

                     

                    Well this can be done the same way.

                     

                    1.  Go into query explorer and click on your query that is used for this report.

                    2.  Now open up your data item for Object Name.  Should look something like:

                    [Report Layer].[Object Authorization].[Object Name]

                     

                    Now adjust that Data Item to look like:

                     

                    count([Report Layer].[Object Authorization].[Object Name])

                     

                    Then run your report.  You will now see just the count if you do not want to see all of the BLPackage Names.

                    • 7. Re: How to get Count Summary
                      Edwin Lindeman

                      Thanks Jon that was a lot simpler and exactly what i was looking for. I did have to make an adjustment. If I put down just COUNT([Report Layer].[Object Authorization].[Object Name]) I get a large number returned. 265,069  However when I do this COUNT(distinct([Report Layer].[Object Authorization].[Object Name])) I get the correct value back of 2936.   Thanks again