1. 

   hpcalaf, Member

   Hi,

   I'm having trouble with implementation these two functions together. I have a column in my result set that is used in a calculation, but that I don't wish to display. It is the total for a percentage calculation. If the column is unhidden, the dbr.summary.calc function works fine. When I hide the column, I get garbage in the summary calc column.

   Here is the effective code for my calculation

   select 'dbr.hidecolumns',1; select 'dbr.summary.calc',4,'C3/C5*100' select 'dbr.crosstab', 2; select 'dbr.summarytxt',1,'Total'; select 'dbr.colstyle', 4, '%.2f%%'; select 'dbr.sum',3,4;

   select d.PROGRAM, d.YEAR as 'Year', count(d.ID) as 'Count', null as '%', '15000' AS 'Total for Percentage' from <DATABASE> d group by d.PROGRAM, d.YEAR ORDER BY d.YEAR

   and the rendering
   http://hpcalaf.com/mydbr/report.php?r=35&m=5&h=108205be6d0dcca48756cbee0edd141abdcecf3a

   commenting out the top line gives you the second result where the precentage and totals are correct, but unhides the column.

   The Total is actually dynamic in each year and the reason I need to implement the solution this way. I have found a way to generate that Total column, but by inserting a constant in this example I've isolated that the calculation of the dynamic total is not a part of the problem.

   The version is myDBR 2.7.2 (build 1058)

   Any ideas about what might be causing this?

   Thanks
2. 

   myDBR Team, Key Master

   You could simplify the report a bit:

   select 'dbr.crosstab', 2;
   
   select 'dbr.summarytxt',1,'Total';
   
   select 'dbr.colstyle', 4, '%.2f%%';
   
   select 'dbr.sum',3,4;
   
   select d.PROGRAM, d.YEAR as 'Year', count(d.ID), count(d.ID)/15000*100 as '%'
   
   from <DATABASE> d
   
   group by d.PROGRAM, d.YEAR
   
   order by d.YEAR;
   
   

   
   --
   myDBR Team
3. 

   hpcalaf, Member

   Thanks for quick response.

   I have posted at the same link in the second position your suggestion. I get a 0% in all % positions except the for total position where the listed value is incorrect.

   My understanding is that you can't have computed column with an aggregate function in native MS SQL. It doesn't error, but it clearly doesn't work either.

   Shouldn't what I'm attempting to do with the dbr.summary.calc and and the dbr.hidecolumns work. It is a very similar example to what is described in the documenation for dbr.summary.calc. With the exception of the use of dbr.hidecolumns it is actually identical.
4. 

   hpcalaf, Member

   Here is the example link

   http://hpcalaf.com/mydbr/report.php?r=35&m=5&h=108205be6d0dcca48756cbee0edd141abdcecf3a
5. 

   myDBR Team, Key Master

   Hi,
   you can have calculations with aggregate functions also in MS SQL. It should work just fine.

   Mabye we did not understand what you where out after. What is the thing that you are trying to count? The count(d.id) is the same as count(*) and should give you number of rows in that set i.e how many rows there are in each program/year pair. You should probably use either count(*) or count( distinct d.id).

   If you can send us the actual query, we can take a look at it. There is a simpler way of doing this.

   The way you are trying to do it should propably work also, apart from the fact that you would have two values in summary column (one from the 'dbr.sum' and one from the 'dbr.summary.calc'). Looks like dbr.summary.calc does not know how to behave with hidden columns (it was never intended to used that way).

   --
   myDBR Team
6. 

   hpcalaf, Member

   Thanks for your help. I must admit I was about to send out a sarcasticgram, but the combination of writing it and researching some of your thoughts lead me to the following solution. http://stackoverflow.com/questions/1666407/sql-server-division-returns-zero

   I've implemented using a combination of what you suggested in previous answer and from this link above. Also validates no problem with aggregate function in a computed column.

   Thanks!
7. 

   myDBR Team, Key Master

   Yes,
   in SQL Server 2/5 != 2/5.0. The first evaluates as integer the second evaluates as float.

   --
   myDBR Team

Reply

You must log in to post.