2013-09-21

SSRS - Create batch executing function for the reports which cannot use Data-driven Subscription functionality - V2 (...reviewing, V3 is coming soon.)

Please note that there is a more powerful method I have developed, the new function can meet concurrent requirement while the existing one only allows single-user to execute it. I will update this article as soon as possible.

Here is the scenario:
Users manually run the specific report by using a set of predefined parameters to export reports with different purposes/results. Instead of manually running the report one by one, users want to automatically run it by maintaining those parameters in the table.

The first thought came to my mind is Data-driven Subscription functionality in Reporting Services, unfortunately, the specific report uses too complex dynamic design, which means it use many user-defined parameters to dynamically control report's interface. When I was trying to use the function, I received the following error message:

2013-09-19

SSRS - Line Chart - Create stacked line and start from 0

Stakeholders want a Stacked Line Chart to illustrate the difference of accumulative value between Actual and Forecast, unfortunately, SSRS doesn't provide this type of chart. There are 5 columns in original dataset: ProjectID, Project_Name, Category(Actual or Forecast), Transaction_Period, and Amount.

First of all, I need to check initial result of graph from original dataset, so I drag a Line Chart in the report, in Values section, add field Amount; in Category Groups, add field Transaction_Period; in Series Groups, add field Category.

2013-09-16

SQL Server - Retrieve projects' monthly cost and work information from Project Server Reporting database

Microsoft Project Server is a project management server solution, it stores project information in central SQL Server databases. One of them is Reporting database (RDB), it includes tables and views that are designed to help make queries for reports of Project Server data easier than querying the Published database.

Stakeholders want to know projects' monthly distribution data related to cost and work fields from Project Server, for accomplishing the requirement, and I have to find relevant columns from the following views in RDB, and cooperate with the stored procedure dbo.usp_Distribution_By_Periodic_Proportion:

2013-09-15

SQL Server - Calculate input amount by using periodic proportion of occurred period - Part 2

In the Part 1, I explained how to calculate "normal" periodic distribution result of occurred period, what if stakeholders want "financial" period types result. Let's take a look at the sample table:

SQL Server - Calculate input amount by using periodic proportion of occurred period - Part 1

Sometimes stakeholders want to know periodic(annual, quarterly, monthly, weekly, or daily, etc.) distribution result from amount of cost or working hours during occurred period. It is very easy to calculate periodic result if dataset only uses 1 timestamp field(e.g. TransDate) to identify when the record happened by using GROUP BY clause. When dataset uses 2 timestamp fields(e.g. StartDate and FinishDate) to identify occurred period of the record, for retrieving periodic distribution result, the most important part of the logic is to identify proportion of each type of periodic, please see the following examples:

2013-09-14

SSRS - Column Chart - Provide enough space for parent category group

There is a Column chart, in the Values section: Sales, and there are two Category Groups: Project and Quarter. Here is the situation, the chart doesn't provide enough space for some Quarter groups which they don't have enough projects displayed in the chart, see the following initial graph: