strategy for profit and loss like reports


  • Hi.  We run 2012 enterprise and I'm reading the general prep steps for a new report at .

    My report is similar to a profit and loss in that any line might need to be presented in a format (eg % vs $ etc) different from the line before it.  Basically its not a traditional repeating pattern of info from line to line like you might see in something like an inventory report.

    There is a time dimension across the top with the budget value in col 1 and then a break down of actuals (eg by week) to the right of that column.

    The report placement of most rows can be predicted (ie fixed) because they are summaries only.  But a few are variable when an unknown number of product line details are shown. 

    The business sections (eg sales, performance etc) need to run together and give the user a scrolling feel (minimally in the browser) rather than a page break after each section.

    Because there is at least one matrix required due to the variable portion I mentioned, I'm already nervous about that thing rs does sometimes when two controls (one on top of the other) dont take on a seamless look.

    I'm also wondering if each row (eg sales, costs, etc etc) should be sourced by a separate dataset or if I should come up with a generic query with union alls supporting all data formats with an indicator on the row specifying what line (hdg) name of the report it should be shown on along with perhaps some sort order value?  By "all data formats" I mean a numeric data type that could hold anything (%s, dollars, counts etc) picked up by the query and worry about the specific reformatting of that data type in the report itself on a line by line basis.

    Any advice would be appreciated.  I'm a big fan of keeping my future maint costs low on something like this.

    • Edited by DB042189 Thursday, January 09, 2014 8:14 PM one more goal
    Thursday, January 09, 2014 5:56 PM

All replies

  • Hello,

    I am not very clearly about you requirement. In SQL Server Reporting Services (SSRS), we can configure the items’ Number property to set a specify format for the item. If we are use table to display data, the columns count is constant. The number of rows and columns for groups of matrix is determined by the number of unique values for each row and column groups. We can according to our requirement to choose which tablix to display our data.

    In SSRS, we can use generic query to get the corresponding fields in one dataset. Union all data in one dataset is more convenient for us to use this fields in one tablix. If you want to use two datasets fields in one tablix, we may need to use Lookup() function or subreport to work around it.

    These articles are for your references.

    If you need more help, could you please post more detail information or screenshots about your requirement? It is benefit for us to do further analysis.

    Alisa Tang

    Alisa Tang
    TechNet Community Support

    Friday, January 10, 2014 12:01 PM
  • Thx Alisa.  The most important part of my strategy question has to do with breaking things up into multiple controls (tablix etc) and datasets or delivering all via one query into one control.

    When a report follows a very nice pattern I've never had a doubt.  For instance, an inventory report that repeatedly lists item, qty on hand, plant location etc etc would fit the "pattern"category.

    But when a report looks like this i start asking questions...

                        Budget                  Jan Actual             Feb Actual              Mar Actual            Apr Actual

    Sales             $x,xxx,xxx.xx        $x,xxx,xxx.xx        $x,xxx,xxx.xx        $x,xxx,xxx.xx        $x,xxx,xxx.xx  

    Cost               $x,xxx,xxx.xx        $x,xxx,xxx.xx        $x,xxx,xxx.xx        $x,xxx,xxx.xx        $x,xxx,xxx.xx 

    % to Sales                 x.xx%                   x.xx%                   x.xx%                  x.xx%                   x.xx%

    Quality Issues          xxxxxxx                  xxxxxxx               xxxxxxx               xxxxxxx                 xxxxxxx

    ...because as you can see different numeric data types are involved.   This example is overly simplified.  Would you source this from one dataset?  And deal with the varying data type formatting in the rs rendering rules, or perhaps use 3 or 4 datasets with 3 or 4 controls?   My report actually has close to 50 rows and the numeric data types vary.  The only pattern I have is that a row hdg would come in and all 5 columns would be used from row to row.  I can easily include a sort order in one dataset.   No particular row or group of rows is reusable in other reports.


    Friday, January 10, 2014 2:34 PM
  • Hello,

    Thank you for your reply.

    If I understand correctly, you have following fields in your dataset: Budget, EventType, MonthAcctual, QualityIssues, Amount. Please refer to the following steps:

    1. Add a matrix in the report body. Add MonthActual field in the Columns text box, EventType in the Rows text box, Amount field in the Data text box.
    2. Right click the second column, select Insert Column -> Outside Group – Left. Add Budget field in the text box.
    3. Right click EventType in the Row Groups, click Add Total. Then, use following expression to replace Sum() expression:
      (Note: Suppose you want to calculate this values of “% to Sales”.)
    4. Right click the text box, select Text Box Properties. Click Number in the left pane, select Percentage option in this dialog box. We can use the same method change the other text box with Currency data type.
    5. Right click the third row, select Insert Row -> Below.
    6. Add QualityIssues field below MonthActual text box.

    Please refer to the following screenshots:
    If there are any misunderstanding, could you please post you dataset with sample data? It is benefit for us to do further analysis.

    Alisa Tang

    Alisa Tang
    TechNet Community Support

    Monday, January 13, 2014 2:21 AM
  • thx Alisa.  I think you are saying not to break this up into multiple datasets.   That is my primary question.

    Quality issues are just counts.

    So if I limit this to one dataset, essentially I have these cols:

    event type

    budget figure (dollars or % or count)

    jan,feb,mar,apr actuals

    ...on every row.

    Monday, January 13, 2014 3:25 PM
  • Hello,

    Thank you for your reply.

    Could you please post your dataset with sample data? It is benefit for us to do further analysis.

    Alisa Tang

    Alisa Tang
    TechNet Community Support

    Thursday, January 16, 2014 1:55 AM
  • thx Alisa.  Just replace your N,G,B,J with 100,50,75,150 and you'll see what I would have posted as sample data.  I have posted a separate question regarding the first obstacle encountered, namely making the numeric format a functional decision if tablix is chosen.

    Here is a link to a sample .  Imagine that percentages and counts are also mixed in to help readers of the statement see summarical things like performance and employee counts.
    • Edited by DB042189 Thursday, January 16, 2014 4:12 PM more info
    Thursday, January 16, 2014 4:01 PM
  • i did most of the work in the database engine by including a format column (eg N2, C0 etc) in my query/resultset.  The format is used in ssrs expressions to control how each number shows.  Primary, secondary and tertiary sort orders are also included in the resultset to control order but are hidden from the user.   At this time I've combined all into the same report, ie I havent broken this up based on display characteristics.  I've also included a "border" column in my resultset.   I'm using it with expressions to display shaded rows with just section headers (no numbers) to help the user visually.  It seems to be working.   My control is tablix. 
    Monday, February 10, 2014 6:28 PM