0

There is a table

+----+----------+-----+-----------+----------+

| ID | Date     | ADDRESS   | Expensis  |

+----+----------+-----+-----------+----------+

|  1 | 10 Dec   | Ahmedabad |  2000.00 |

|  2 | 10 Dec   | Delhi     |  1500.00 |

|  3 | 11 DEC   | Delhi     |  2000.00 |

|  4 | 11 DEC   | Mumbai    |  6500.00 |

|  5 | 13 DEC    | Mumbai    |  8500.00 |

|  7 | 15 Dec    | Delhi     | 10000.00 |

+----+----------+-----+-----------+----------+

Supposition: There are many more rows in similar format in the above table

Using this table I want to create a report which should have output something similar to below

+----+----------+-----+-----------+----------+

| Date     | Ahmedabad     | Mumbai | Delhi  |

+----+----------+-----+-----------+----------+

|  10 Dec  |  5            |  3      | 0     |

|  11 Dec  |  2            |  8      | 3     |

|  12 Dec  |  6            |  1      | 4     |

|  13 Dec  |  0            |  7      | 6     |

|  14 Dec  |  4            |  2      | 7     |

+----+----------+-----+-----------+----------+

Where the numbers under Mumbai and Delhi are the count which is calculated form this table.

Each count value in each cell can only be calculated using individual SQL query on the same table for each cell

e.g. select count(city) from abc where city='Delhi' and date='11 dec.'

Madhur
  • 1
  • 2
  • what is the issue with this requirement in crystal reports? – Siva Dec 11 '14 at 06:09
  • I want to do it with crystal reports. But I don't know how to create such a summarized report/ drilled down report. – Madhur Dec 11 '14 at 06:16
  • without date in the source table how are you planning to get that in report are you missing something? provide complete data – Siva Dec 11 '14 at 17:11
  • Date and city are there in some other table. The problem is to get the count of each city on each date. As each count requires a separate query. I will modify the table for your convenience. – Madhur Dec 11 '14 at 17:26
  • You are trying to do a pivot table on crystal, check out this post http://stackoverflow.com/questions/17667913/how-do-i-achieve-a-pivot-report-in-crystal-reports-for-visual-studio – Raphael Dec 12 '14 at 13:43
  • Thanks @Raphael for your response. Yea I am trying to do Pivot on crystal. Do you have any idea about doing it dynamically. I am able to do it with SQL currently. Though I have to search currently on Pivot for crystal report dynamically. Thanks for response. – Madhur Dec 12 '14 at 16:26
  • You can use cross tabs in crystal. But creating a view using SQL pivot and directly using the view on the report is a lot easier (personally I think). Do you know how to create a sql pivot? – aMazing Dec 15 '14 at 00:37
  • Yes I know how to create sql pivot. Thanks for your reply... Though I would like to understand would it be more costly to develop crosstab in crystal or pivot on sql – Madhur Dec 17 '14 at 05:20

0 Answers0