Turning SSRS Report Into Form

The difference between report and form is report pulls data out from database and the form is used to feed data into database. SQL Server Reporting Services is of the report. However, sometimes the two-way interactions are really needed. Here is an example. A parameter need to be input by user; however, if the user had input previously, it should be stored in database and no longer require user to input again.

The logic of the solution is:

1. Get value A of the subject from database according to other parameters.
2. If (Null!=A) then show report based on A, and exit.
3. Else if user input parameter B, then insert B into database, then show report based on B, and exit.
4. Else show the blank report with notice that user needs to key-in the B.

However, this logic does not allow front end to alter the what was already in database. Any change will be manully operated by back end. Alternatively, following logic can be applied:

1. If user input parameter B, then insert or alter B in database, then show report based on B, and exit.
2. Else get value A of the subject from database according to other parameters.
3. If (Null!=A) then show report based on A, and exit.
4. Else show the blank report with notice that user needs to key-in the B.

As a standard procedure, the user therefore is not required to key-in the parameter unless is notified.

INSERT and SET query can be as text or as stored procedure. However, INSERT query should not be stand along as text, or an error message will be generated. At the end, SSRS is of report.

A New Sorting Mechanism in Database

If one wants to add a field to record sorting order, following mechanism is recommended.

In HTML’s Select List or Option List, the positioning indication can be written as:

Before Item 1 – Order_Value is 0
Before Item 2 – Order_Value is 1

Then, the Sort_Order can be written into Database as Order_Value*2-1, which can be applied in both cases as new record or move existing record. By doing this, one can easily insert or move item. This is because in database this field is recorded as 0, 2, 4, 6... Of course, inserting or moving can be done once a time, followed by sorting.

Following is the example in PHP.

public function saveCateNew($order)
{ $orderNew = $order*2-1;
$query = sprintf( ' INSERT INTO CATES_SELF '
. ' (TITLE_1 '
. ' , SORT_ORDER '
. ' ) VALUES '
. ' ("%s" '
. ' , %d '
. ' ) '
, mysql_real_escape_string($this->title_1, $GLOBALS['DB'])
, $orderNew
);
mysql_query($query, $GLOBALS['DB']) or die("An error has ocured: ".mysql_error().":".mysql_errno());
$this->sortCate();
}

public function saveCateOld($order)
{ $orderNew = $order*2-1;
$query = sprintf( ' UPDATE CATES_SELF '
. ' SET TITLE_1 = "%s" '
. ' , SORT_ORDER = %d '
. ' WHERE ID_CATESELF = %d '
, mysql_real_escape_string($this->title_1, $GLOBALS['DB'])
, $orderNew
, $this->id_cateself
);
mysql_query($query, $GLOBALS['DB']) or die("An error has ocured: ".mysql_error().":".mysql_errno());
$this->sortCate();
}

function sortCate()
{ $query = sprintf( ' SELECT ID_CATESELF '
. ' FROM CATES_SELF '
. ' ORDER BY SORT_ORDER '
);
$result = mysql_query($query, $GLOBALS['DB']);
$index = 0;
while($row = mysql_fetch_assoc($result))
{ $id = $row['ID_CATESELF'];
$queryInner = sprintf( ' UPDATE CATES_SELF '
. ' SET SORT_ORDER = %d '
. ' WHERE ID_CATESELF = %d '
, $index
, $id
);
mysql_query($queryInner, $GLOBALS['DB']) or die("An error has ocured: ".mysql_error().":".mysql_errno());
$index = $index+2;
};
mysql_free_result($result);
}