1. 

   spyhunter88, Member

   In Store Procedure, i use prepare statement to make a "pivot" table with Daily in a Week (Sun, Mon ....). And it show perfectly all col with data. But I can use sum, avg or set style for those columns.
   If I set all (Sun -> Sat) it will show error while not all columns show.
   If I use the same Alias name (Col_name[Alias_name]) it only take the first column in both day.
   So, is it possible to check if column exist like : if 'Sun' exists select 'dbr....';
2. 

   myDBR Team, Key Master

   Hi,
   there usually is no reason for prepared statements in myDBR reports and we tend to discourage using them. While prepared statements are quite handly, they are usually quite hard to maintain as the logic may split into pieces. Usually there is a better way to write the report than using prepares statement.

   If you share the code we can see if there is a better way of doing it.

   As for the optional columns, you can select all the columns needed and hide the ones you do not want to show. If you want just to select some of them, you should use formatting accordingly.

   --
   myDBR Team
3. 

   spyhunter88, Member

   Thanks,

   My purpose: I want to report SUM(Qty) each day in week, and not all days like future days, week-end ... and see one day in column (LIKE SUN | MON | TUE .....).
   And, After that I want to set style, or set Total Row, Avg Row ...

   CREATE TEMPORARY TABLE IF NOT EXISTS data01 AS (SELECT SalemanCode, SUM(Qty) AS Daily, DATE_FORMAT(Date, '%a') ) AS DayIn FROM Sales_report WHERE Qty <> 0 GROUP BY SalemanCode, DATE_FORMAT(Date, '%a') );

   SET @sql1 = CONCAT('(SELECT s.SalemanCode,', @sql, 'FROM data01 s GROUP BY s.SalemanCode' );

   PREPARE stm FROM @sql1; EXECUTE stm;

   Sorry if there some mistake cause of copy text-by-text from other computer.

   I've tried to set style for all day of week from SUN to SAT, but if column not exists, it show Column reference error.

   After that, if I want to show Weekly?
4. 

   myDBR Team, Key Master

   Hi,
   sounds like you are trying to do a cross table by yourself. As the code example you gave does not show the full report (what is the @sql?) it is bit difficult to guess. Again, there should be no reason for using prepared statements.

   Take a look at documentatiom about the Cross-tabulation report or please post the full report / image of the desired output.

   --
   myDBR Team
5. 

   spyhunter88, Member

   I know about CrossTab, but I wonder what happen when I want to put daily between some columns. I can't remember why I didn't try put this Daily column to be first column so I only see all crosstab in the most right side of table result.

   I'll try later.

   Thanks to take me back.
6. 

   myDBR Team, Key Master

   Please note that if you wish to show cross table columns that do not exist in the data (like all weekdays if your data does not have them all), you can use the dbr.crosstab.col-command to predefine the cross tabulation columns to be shown.

   --
   myDBR Team

Reply

You must log in to post.