Once you take care of the ordering, it is now time to show those uncomfortable “Gross Profit” which is simply total sales – total direct costs – total operating expenses. This part was bit tricky although not that challenging. This is evidently wrong as we need to first show what’s the total sales of the company is for a particular month, for the operating expenses and direct costs to be deducted from it. This means, Direct Costs will be displayed first, followed by Operating Expenses and Sales. Now if you simply run the report without ordering, it will display heads sorted by alphabetical order. For eg, the Sales have to appear first in the drill down, followed by Direct Costs followed by Operating Expenses. I think, if I had used them, that would have increased the speed of the query by several times.Īnyways once I have ported the data, I wrote a dataset query in SQL Server that represents the order as required in the report. I didn’t use any Oracle’s “connect by” and “prior” duo to get the hierarchy. The structure had the following columns Parent Node, Middle Node, Child Node and the accounts under the child node along with the posted transaction amount in PS_LEDGER table. I wrote a query that ported the relevant data with the necessary structure from Oracle(PeopleSoft Database) to local report server database(SQL Server). I first created a tree that represents the four level drill downs at the rowlevel.
But as far as Reporting Services is concerned, this is how I proceeded. I have no clue how easy or difficult it is to do in nVision. The second drill down level will consist of different business units under each month. The column drill down will have all months at the first level. The fourth level drill down under the head Salaries will consist of all the GL account codes. The third drill down under Human Resource Costs (for eg.) will consist of heads like Salaries, Bonus, Relocation, Business Travel etc. The second drill down will consists of following heads – Human Resource Costs, Employee Costs, Facility Costs etc.
in our case, it will be Operating Expenses. The row level drill down consists of a primart reporting head. The Report will have a drilldown both at the row level as well as the column level. all in a single page with drill down across months for different business units. This is a report that has to give the details like Sales, Direct Costs, Operating Expenses, Gross Profit, Income Tax, Net profit etc. It took me 4 working days to finish the entire thing. I should say that I am throughly delighted.
I have completed my first complex multi level drill down report in Reporting Services.
0 kommentar(er)
