Skip to Content

Report with a Graph.. An Approach!

We often fear when we have to develop complex reports. How much time will it take, how much effort will I have to put in. Isn’t there a way, which could reduce the time I take to code and still give me the desired output..  I would say yes, I have found one way, which as an approach can be applied, in a number of situations. I am sure many of you would be using it already, but I am reiterating this so that it becomes a habit for us.  I was given this requirement for picking up data from a number of tables and then process that data in a way to segregate it into four categories. At the end I must have these four categories and the totals for each category. Based on these totals plot a graph.  Not that complex a requirement but it would require loads of efforts to get the graph part going. I know it can be done using a simple function module. But what about the determination of coordinates, colour and of course the OLE part. All this would definitely require a lot of effort. At the end of it you would come up with a particular type of graph, which only makes 1 user happy. What about the other 5, who want the graph in a different way?  Why dont we use a simple approach here and still get the result as I did.  I developed a simple ABAP report using ALV and just dumped all my data on it. After this I downloaded the Standard Excel template available in the ALV. Defined my own worksheets in this template, wrote some macros to pick up the data from the “RawHeader” sheet, which is available by default and will contain the ALV data. I inserted 1 chart in this Excel template. In this chart I used the same chart type as was being used by the user for his graph. Just right clicked on the Graph area and made the changes in the source data and made it point to the sheet containing the final data. That’s it my job is almost done. After this uploaded this template back into the report output through  the layout settings->Change Layout Tab. Save it as a variant and made it a default. (Do not default it if you have more than 1 user and more than 1 template…. Select the appropriate variant for the appropriate user and then display) Well, this also was not that easy as I had thought. I landed up into 1 trouble. In my report the number of columns displayed was not constant and kept changing based on the input. This fact was taken care by designing a variable field catalogue. But now I had gone past the simple ALV display and was giving the output in an Excel sheet using a pre-defined template. Well, I immediately found a solution to this with the set_frontend_fieldcatalogue method of CL_GUI_ALV_GRID class and fixed the field catalogue every time after calling the set_table_for_first_display method. This solved most of my problems, which were not many though. Lets have a look at the Excel output image     It has this Refresh button, which calls the underlying macros. As can be seen my macros will pick up data from the RawData sheet and make three reports out of that.  Report 1 in Sheet named: Consumption Report 2 in Sheet named: Contracted Qa Report3 in Sheet named: Report The Graph tab has the graph generated using the data in sheet named: Report And it looks like this  image    The user doesnt like the colour… Give him the graph and ask him to colour it the way he wants it. Add this graph as a new chart type in your excel. Make a new template having the same macros but this new chart type. That is it; your new graph is for the new user is ready.. No changes in ABAP absolutely. Make as many reports as the user wants in Excel itself.  The user just needs to click on the Refresh button to trigger the macros and just save this file. That’s it!!! Our report is done and that too with minimal effort.  Well, it is just about not re-inventing the wheel and using the right product for the right job. In our case it is the usage of MS Excel to help us solve this complex looking problem.
You must be Logged on to comment or reply to a post.
  • Hi,

    Your approach is certainly very nice.
    I too have the same requirement.
    I would like to know in detail, how have you done this.
    Can I please know, where to download the standard excel template for excel??
    Kindly explain to me your apptroach, in detail, as I too have the same kind of requirement.
    I would be really grateful.

    Thans and regards,

  • Hi varun,
      I got a small requirement in which i have to display the values of the table in the form of a pie chart. So can you please guide how can i do this. If possible can you send me the sample code or program name so that i can go through it.

    Thanks in advance

  • Hi Varun,
      There is a requirement to download the graphical display from ABAP program output into an excel. Please suggest any method to download  the graph.
    Also please let us know how we can writ macros required in excel through ABAP program.

    Kasiraman r