Running total with if statement – Reporting services

 

=Count(iif(Trim(Fields!FoodPurch.Value)=”Dog Food”,Fields!FoodPurch.Value, Nothing))

SQL – delete duplicate (similar) records

delete

updated_date

(

from cstd_company_seg where inselect min(updated_date) from cstd_company_seg group

by company_id having count(*)>1)

SQL Display duplicate (similar) records

select(

 

* from cstd_company_seg where company_id inselect company_id from cstd_company_seg group

by company_id having count(*)>1)order

by 2

SQL – Merge data from 2 tables

SELECT
    company,
    account,
    year,
    month,
    sum(actual) as actual,
    sum(budget) as budget
FROM
(
    SELECT
      company, account, year, month, amount as actual, 0 as budget
    FROM
      Actuals
    UNION ALL
    SELECT
      company, account, year, month, 0 as actual, amount as budget
    FROM
      Budgets
) x
GROUP BY
    company, account, year, month

SQL Strip time off of timedate field

The following will return the timedate field with the time set back to 00:00 

SELECT DATEADD(DAY,DATEDIFF(DAY,0,[DateTime]),0)

SQL Display all foreign keys

Use this system stored procedure to find all foreign keys linked to a table

EXEC sp_fkeys Products

Microsoft Reporting services RTF data

One of the most irritating shortcomings of Microsoft Reporting services 2005 is the inability to display RTF fields.

I found a solution here.  http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx

To get around this you’ll need to strip out all of the formatting.

Create a function and add it to the Report Properties –> Code

Function RtfToText(ByVal value As String) As String

       If value.Contains(“rtf1″) Then

           Return System.Text.RegularExpressions.Regex.Replace(System.Text.RegularExpressions.Regex.Replace(System.Text.RegularExpressions.Regex.Replace(System.Text.RegularExpressions.Regex.Replace(value,”[\n\r\f]“, “”), “({\\)(.+?)(})|(\\)(.+?)(\b)”, “”), “{“, “”), “}”, “”).Trim()

       End If

       Return value

   End Function

This method is then called by the text box as below:

=Code.RtfToText(Fields!PrintName.Value)

Crystal reports – Format date in formula

Try creating a formula using:

 DateValue({datefieldname}).

 

This will just return the date from the datetime field.

 

SQL – find records by field length >x

SELECT *
  FROM tablename
WHERE len(fieldname) > 20

Report Builder – users unable to save reports

I ran into a problem that admins could save reports, but limited users could not.  It ended up that I had to give the users at least view access to the model directory.  I did verify that they could not change anything in the directory, but they had to at least be able to browse.