QTD Actual in the third row (2011-Q1-March) is the sum of values in the 1st, 2nd and 3rd rows of the Monthly Actual Column, which is the Actual Total Sales at the end of the 1st Quarter of 2011. YTD Actual in the second row (2011-Q1-February) is the sum of values in 1st and 2nd rows of Monthly Actual Column, which is the Actual Total Sales at the end of 2nd month (February) for the current year (2011). In the above output, QTD Actual in the second row (2011-Q1-February) is the sum of values in 1st and 2nd rows of the Monthly Actual column, which is the Actual Total Sales at the end of 2nd month (February) for the current quarter (2011-Q1). Now preview the report and the report should look something like below. Now the table should look like the below screenshot.Go to Year group properties and set the name to rgrpYear.Set Group by to Year in the Tablix group window and click OK. Right click on the rgrpQuarter group, Select Add Group and then Parent Group.Go to QuarterNumber group properties and set the name to rgrpQuarter.Set Group by to QuarterNumber in the Tablix group window and click OK. Right click on the rgrpMonth group, Select Add Group and then Parent Group.Go to MonthNumber group properties and set the name to rgrpMonth.Set Group by to MonthNumber in the Tablix group window and click OK. Right click on the Details group, Select Add Group and then Parent Group.In the Group Properties window, change the name to rgrpDetails, set the Group expression to MonthNumber and click OK. If the Grouping tray is not visible, go to Report menu - View - Select Grouping. Go to Grouping tray at the bottom of the Report Design surface.Go to the Tablix Properties window and set the Dataset name to dstSSRSDemo. In the below screenshot, I have assigned a name to each cell (H1, H2, D1, D2 etc.) so that we can refer them easily in the subsequent steps. Add three more columns to the Table towards the right as shown below. By default, a Table control has 3 columns.Add a new Table control to the Report Design surface.Go to the Report Data window, Add a Dataset pointing to the Data Source dsSSRSDemo created in previous step and name it as dstSSRSDemo. If the Report Data window is not visible, you can open it by going to View menu and select Report Data from the drop down, alternatively you can press Ctrl+Alt+D. Go to the Report Data window, Add a new Data Source pointing to any available Dev/Test SQL Server/Database and name it as dsSSRSDemo. Go to the Solution Explorer, Add a new Report and name it SSRSCumulativeAggregates. Open SQL Server Business Intelligence Development Studio ( BIDS), Create a new Report Server project and name it as SSRSDemo. Step 1 - Create a new Report Server Project Now, let us create a report to address the requirement/problem statement. Note that, in the above query, the last column "TargetSales" is set to "120000/12" which gives us the TargetSales at the month level when the TargetSales for the year is set to $120,000.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |