Archive | SQL Server Reporting Services RSS feed for this section

SQL Nested Case Statement Example

2 Jul

As a workaround to the limitation of MS Reporting Services 2005 to do something like this

The value of Reportitems!txt_sales_p.Value >= 100.0
use this expression =code.sales_A(sum(fields!P_SALES_CURR.Value))
ELSE
=code.sales_B(sum(fields!P_SALES_CURR.Value))

Workaround in MS SQL Model

SELECT
STAFF_ACT_P1,
STAFF_ACT_P2,
STAFF_ACT_P3,
CASE WHEN STAFF_ACT_P1 >= ’24’ THEN
CASE WHEN STAFF_ACT_P1 <= ’25’THEN ‘A’
WHEN STAFF_ACT_P1 <= ’28’ THEN ‘B’
WHEN STAFF_ACT_P1 <= ’30’ THEN ‘C’
WHEN STAFF_ACT_P1 >= ’31’ THEN ‘HIGH’ END
ELSE
CASE WHEN STAFF_ACT_P1 <= ’23’ THEN
CASE WHEN STAFF_ACT_P1 >= ’22’ THEN ‘E’
WHEN STAFF_ACT_P1 >= ’21’ THEN ‘F’
WHEN STAFF_ACT_P1 >= ’20’ THEN ‘G’
WHEN STAFF_ACT_P1 >= ’19’ THEN ‘H’
WHEN STAFF_ACT_P1 <= ’18’ THEN ‘LOW’ END
END END
FROM bsc_rpt_model

I hope save some time to others creating reports in MS Reporting Services

–evick

Advertisements

MS SQL Reporting Services 2005 – Not so Good

30 Jun

The report development in SQLRS is a tool that works nice until you hit the land of complex scenarios.

After the report is 90% ready I hit this and after 2 hr I’m not get a solution – I need to make a basic condition based on a value from a dataset field based on that use a function to get the ranges values.

In the code is not allowing me to reference to a value of the field to make the decision.

Ref1:http://odetocode.com/articles/130.aspx

Ref2:http://blogs.msdn.com/b/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-1-4-the-basics-report-expressions-custom-code.aspx

I’m going to evaluate the possible solution to the problem 2hr more if that not work, I think the report it’s going to be done in ASP.NET where I get a lot more flexibility.

–evick

REFERENCE:SELECT INTO, INSERT INTO

23 Jun

English

Ref:http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Spanish

Ref:http://www.guillesql.es/Articulos/SELECT_INTO_INSERT_INTO.aspx

This sometimes can be very handy.

MS SQL Reporting Services 2005 – Time Out Execution Error – Solution

21 Jun

During the last 2 weeks I Been working in a Report that have a great amount of calculations of different accounting GL accounts, the thing is that during the query and view creation with SQL Management Studio everything works nice with response from large query’s in 80 Sec (acceptable).

The Problem arrives when I began to use that query’s in the report construction in Visual Studio Business Intelligence Development Studio , I get error Time Execution Error – Timeout after 30 Sec of running.

This reference help me to identify a 4hr problem Ref:http://www.windows-tech.info/15/b4e1f506fe9c6f41.php

The actual solution to the problem was:

In Visual Studio under Tools – Options.

That resolve the TimeOut problem in the Report development with Visual Studio – Business Intelligence Development Studio.

I hope save some time to others

“Unable to load client print control” in Reporting Services 2005

21 May

Note:This was validated myself

Ref:http://vettekerry.wordpress.com/2009/05/04/unable-to-load-client-print-control-in-reporting-services-2005/

Copy the red text below into a file with a REG file extension and double-click it

Windows Registry Editor Version 5.00

[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{FA91DF8D-53AB-455D-AB20-F2F023E498D3}]

SQL Server Reporting Services Good Tutorial

6 May

Everything that was covered in this tutorial was tested with good results

http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-2/

This one helps me trying to identify was going wrong with pass Parameters.

http://msdn.microsoft.com/en-us/magazine/cc188691.aspx

–evick