Limitations in Reporting Services on SQL Server Express Edition

What are the limitations in Reporting Services on SQL Server Express Edition ?

Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition but following restrictions will be applicable to this edition.

·         SQL Server Management Studio Express cannot be used to administer report server
·         Report Models will not be available
·         Other features like Caching, History and Delivery of Report is not available.
·         Report Builder is not available
·         Local SQL Server is a only option, which can be used as Report Data Source.
·         We cannot store the report server database on a remote server, it has to be local only.
·         Reports can be rendered only in Excel, PDF, Image formats only
·         No SQL Server agent is available, so no scheduling is possible.
·         Reporting Services will not be able to use more than 1 GB of RAM
·         No Subscriptions (Standard and Data Driven) can be made
·         Can not be integrated with Share Point
·         Only named instances is supported
·         Scale-out Report Servers will not be available
·         Can not implement Role based security.

Page break in ssrs

The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.

=Int((RowNumber(Nothing)-1)/25)

Steps for adding Page Break in Reporting
Step 1- Remove all the Groupings of the Table.
Step 2- Select the Detail Row(All total Data Row) of the Table -->Right Click.
Step 3- Click on Add Group --> Parent Group.
Step 4- In the Group By Expression
                         Add =Int(RowNumber(Nothing-1)/10) -->OK.
Step 5- Go for Row Group(At the left corner below of the Design View) --> Group Properties.
Step 6- Go for Sorting Properties, Remove the Sort Expression.
Step 7- Go for Page Breaks and check the "Between Each Instance of Group"-->OK.
Step 8- If you dont want the Newly added Column , Select The Total Column -->
             Click on Delete Columns.
Step 9- Select the "Delete only columns" Radio Button from the Dialog Box.

Java script Button onmouseover and onmouseout with in cognos

<input type=button onclick='validateDate()' value='Finish' style=""
class="clsPromptButton" onmouseover="this.className = 'clsPromptButtonOver'"
onmouseout="this.className = 'clsPromptButton'" >

JavaScript for Date Validation in COGNOS

Step 1: Drag 2 Date Prompts into the prompt page

Step 2: Name the Date Prompts as FromDate and ToDate

Step 3: Drag an HTML Item below the prompts and include the below script:

<script type="text/javascript">
function validateDate()
{
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) 
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}

var FromDate = fW.txtDateFromDate.value;
var ToDate = fW.txtDateToDate.value;

if(Date.parse(FromDate)> Date.parse(ToDate))
{alert('From Date is greater than To Date');}
else
{promptAction('finish');}
}
</script>

Step 4: Drag another HTML Item to create the prompt button. The onclick event of the button will trigger the date validation function.

<input type=button onclick='validateDate()' value='Finish'>

Step 5: Drag a Prompt Button - Finish to enable the submission of the prompt page and hide the button by seting the height and width to 0px.

Dynamically Hiding and showing Report columns in cognos report studio by parameter selection with Cognos

In order to hide or show report columns conditionly you have to use a string variable and assign the values to it then dynamically set the value of the variable according to a parameter chosen by the user.

Steps

1.Create a parameter with choices for the user to choose
2.create a string variable and add values to it same as the parameter choices
3.And write the variable expression
as
if(ParamValue('P_year'))='2011'
then
'2011'
else
'2012'
4.Then go to the properties of report column to show or hide
5.Click the render variable property value
6.choose the string variable as render varaible
7. Then select values so when variable value matches to the selected values the column  will be shown
8.Do the same for all columns to show or hide according to the parameter selected by the user

Finding Duplicate Indexes with MYSQL

SELECT
    ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,
    CASE
        WHEN ndx1.COLUMNS = ndx2.COLUMNS
        AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
        THEN GREATEST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
        ELSE ndx1.INDEX_NAME
    END REDUNDANT_INDEX_NAME,
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN ndx1.COLUMNS = ndx2.COLUMNS
            AND (ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
            THEN LEAST(ndx1.INDEX_NAME, ndx2.INDEX_NAME)
            ELSE ndx2.INDEX_NAME
        END
    ) INDEX_NAME
FROM
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF(INDEX_TYPE='BTREE',SEQ_IN_INDEX,0), COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx1 INNER JOIN
(
    SELECT
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,
        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
        GROUP_CONCAT(
        CONCAT('`',COLUMN_NAME,'`')
        ORDER BY IF( INDEX_TYPE = 'BTREE'
        , SEQ_IN_INDEX
        , 0)
        , COLUMN_NAME
        ) COLUMNS
    FROM
        information_schema.STATISTICS
    GROUP BY
        TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE,NON_UNIQUE
) ndx2
ON ndx1.TABLE_SCHEMA = ndx2.TABLE_SCHEMA
AND ndx1.TABLE_NAME = ndx2.TABLE_NAME
AND ndx1.INDEX_NAME != ndx2.INDEX_NAME
AND ndx1.INDEX_TYPE = ndx2.INDEX_TYPE
AND CASE
WHEN ndx1.COLUMNS = ndx2.COLUMNS
AND (ndx1.IS_UNIQUE = 'NO'
OR ndx1.IS_UNIQUE = ndx2.IS_UNIQUE)
THEN TRUE
WHEN ndx1.INDEX_TYPE = 'BTREE' -- when BTREE
AND INSTR(ndx2.COLUMNS, ndx1.COLUMNS) = 1
AND ndx1.IS_UNIQUE = 'NO'
THEN TRUE
ELSE FALSE
END
GROUP BY ndx1.TABLE_SCHEMA,ndx1.TABLE_NAME,REDUNDANT_INDEX_NAME



Reference url: http://dba.stackexchange.com/questions/18975/how-to-improve-mysql-server-performance

split integer

sample data with 1st 2 chars date,next 2 chars month,next 4 chars hour and minute last 8 chars emp code 2901185001014227

   SELECT cast(right([Column 0],8) as int) As empcode,
     cast('20130129' as date) +convert(datetime,SUBSTRING([Column 0],5,2)+':'+SUBSTRING([Column 0],7,2)) as ad,1 as MachId,GETDATE() as cd,convert(datetime,SUBSTRING([Column 0],5,2)+':'+SUBSTRING([Column 0],7,2))
  FROM [btnetdev].[dbo].[bio29missing1]