SSRS : Hide Data Point on SSRS Chart

May 6, 2016 Leave a comment

This Question is originally posted on MSDN, link click here.

In Brief:-

I am using SSRS 2008 R2. I have a chart where the values are gallons of fuel, category groups are Months, and Series Groups are Years. I want to hide the current month of the current year only when the chart is generated. So when I generate the chart in May 2016, I want the data point hidden, not the category group since there will be data points for May 2013, 2014, and 2015. Then when June 2016 roles around, same thing, etc. Below is a screen shot of my chart. If someone needs to see the SQL Query, I can provide that as well.

Lets create sample report using sample data , Sample data query as given below:-

 

select ‘Jan’ Monthsname, 1 MonthNumber, 2014 year, 2000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2014 year, 3000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2014 year, 5000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2014 year, 7000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2014 year, 8000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2014 year, 9000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2014 year, 10000 gallons
union
select ‘Jan’ Monthsname, 1 MonthNumber, 2015 year, 3000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2015 year, 5000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2015 year, 8000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2015 year, 2000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2015 year, 6000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2015 year, 1000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2015 year, 7000 gallons
UNION
select ‘Jan’ Monthsname, 1 MonthNumber, 2016 year, 9000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2016 year, 8000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2016 year, 7000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2016 year, 3000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2016 year, 2000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2016 year, 10000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2016 year, 3000 gallons

order by year,MonthNumber

Design the report dataset with above query and drag the  line char control on to the report body and set the Values, Category Groups and series groups as shown below:-

1

Run the report to check what it is showing then we modify what needed .

1

As already shown above that , what we needed we need to hide the current month current year data point.

First we show data points with circle as shown below:-

1

Lets Filter the data from dataset or chart as per your need. Better we filter at chart level , I mean filter current Month and current year data.

Go to Chart Properties , Filters Pane and set the expression as shown below:-

1

Expression :-

=cstr(Fields!year.Value) + cstr(Fields!MonthNumber.Value)

Value:-

=CSTR(year(now()))+cstr(month(now()))

Run the report, report will not have the data point but line was breaked as shown below:-

1

Now We need to set the color of the current year series, Why we need to set the color of the series will show in later stage of development.

1

=iif(Fields!year.Value=year(now()),”RED”,”Automatic”)

We will create a line for empty data values so we set color as RED as we set the same series with RED color in above screen

Follow above click on line and go to the properties and then go to EmptyPoint and set Color RED.

1

 

Now run the report

1

let say i didn’t set the series color and for empty rows  I set color green then how it look like:-

1

So better set the color for filtered series and same color used for empty data line

 

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Categories: Home, MSDN, SSRS Tags: ,

SSRS : Replace string with an image

April 30, 2016 Leave a comment

Requirement is like  write an expression in textbox such that based on the value image is replaced.

Let say I have sample data query similar to you data as given below:-

select 'P1' GroupName,'A' Value
union
select 'P1' GroupName,'B' Value
union
select 'P1' GroupName,'C' Value
union
select 'P2' GroupName,'A' Value
union
select 'P2' GroupName,'B' Value
union
select 'P2' GroupName,'C' Value

And I have three images in my desktop as shown below:-

3

Now create a sample report as shown below:-

4

Now right Click on the textbox below the Value column and insert image as shown in below screen:-

111

Once you insert image, its open the image properties as shown below, and click on the expression icon which is also shown in this image:-

222

Expression editor open, where you enter the below expression:-

=IIF(Fields!Value.Value=“A”,“image1”,iif(Fields!Value.Value=“B”,“image2”,“image3”))

333

Images should be added in the report before as shown below:-

444

Run the report, report output looks like below:-

555

So it working, if you like it please like the post below..

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Categories: Home, MSDN, SSRS Tags: ,

SSRS : Disable CSV header while SSRS report export to CSV using URL Reporting

April 28, 2016 Leave a comment

Let say we have the report with only table and its deployed on server as shown below:-

CSVReport

Now export the report as CSV and open it ,  you will see the header columns as shown in below screen:-

CSVExported

Now create report-server URL as shown below:-

http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fSAMPLE%2fTest%2fCSVexportReport&rs:Command=Render&rc:NoHeader=True&rs:Format=CSV

Actually , you need to add the exiting reportserver URL with below line

&rc:NoHeader=True&rs:Format=CSV

Copy the above URL you just created and run on browser, it will ask open or save CSV file, once you open the file, there is no header.

NoHeader

 

 

So it working, if you like it please like the post below..

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Categories: Home, MSDN, SSRS Tags: , , ,

SSRS: Live Exchange Rate Conversion

April 22, 2016 2 comments

Let Say we have Report with USD Amount and Local Currency and user want to have a report with Local currency amount as per the live exchange rate.

Live exchange rate can get on web using XML or RSS feed.

XML file : needs XML query to retrieve the data

RSS feed : no need to write the query only execute it in the dataset without query

Lets use RSS feed , on web I got one click which provide the RSS feed live data for exchange rate.:-

RSS Feed:- http://www.sloomedia.com/currency/feeds/USD.xml

Lets go to the report Shared Data Source Connection:-

Create XML DataSource as shown below:-

1

Go to next tab, Credentials:-

1

Set to Windows Credentials.

Now create Shared Dataset as shown below:-

ExchangeDataset

 

Now go to the query designer and execute without writing query as shown below:-

QueryDesigner

Note this data-set fields will not come automatically. As shown in below screen:-

SharedDataSetFields

Need to add manually whatever fields name showing in the query designer after executing it. I have added manually as shown below:-

ManualAddedFields

After this lets create the Report with two datasets:-

One dataset having database table data which is the actual data to show.

Second dataset having above created shared dataset.

First Data Set sample query:-

select 50000 SalesAmount, ‘USD’ currencycode ,’INR’ localCurrency
UNION

select 2000 SalesAmount, ‘USD’ currencycode,’AUD’localCurrency

Fields coming for dataset 1 are shown below:-

1

Now Create another dataset as dataset2  from shared dataset created above using XML data as shown below:-

ExchangeDataSetinReport

Now come to report design and drag tablix control from toolbox and assign dataset name as dataset1 .

Table

Now run the report as output look like below:-

Reportoutput

Now We need to create a column which will show SalesAmount in local currency.

To understand better , i created 3 columns

1st column will show what is  the exchange rate returned from dataset2

2nd column will show what is the amount per 1 USD in local currency  derived from first column

3rd column is final output of the salesamount calculated in localcurrency.  multiplication of Salesamount * 2nd column.

 

Expressions

Above Expressions:-

“Exchange Rate” column expression:-

=Lookup(Fields!localCurrency.Value,MID(Fields!title.Value,instr(Fields!title.Value,”=”)+2,3),Fields!title.Value, “DataSet2”)

“Exchange Amount With 1 Dollar” column expression:-

=mid(ReportItems!Textbox6.Value,14,8)

“SalesAmount in Local” Column expression:-

=Fields!SalesAmount.Value  * ReportItems!Textbox12.Value

 

Now in below report, we should show what was the last updated currency rate we got from web:-

Last

Expression is :- =”Currency Rate Last Updated: “+First(Fields!pubDate.Value, “DataSet2”)

 

Now run the report:-

output

 

Every time refreshed data will show in the “salesamount in local” column.

 

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Categories: Home, MSDN, SSRS Tags: ,

SSRS : How do I show all columns in a matrix(ssrs) even if there are no data rows

April 14, 2016 2 comments

I got this question from MSDN, original question is here.

in brief:-

below is my query for the report, and i want all columns label to display in table report even if there is no data populated for that column. so am selecting Status, daysinservicingstatus and UPB. so now i have to put the daysinservicingstatus in a range with it UPB. 

SELECT  SG_SRStatus AS SG_SRStatus, 
LS.LS_DaysInServicingStatus,
SUM(ls.SG_UPB) ‘SUM UPB’,
CASE WHEN LS.LS_DaysInServicingStatus BETWEEN ‘0’ AND ‘179’
THEN ‘0-179 Days’
 WHEN LS.LS_DaysInServicingStatus BETWEEN ‘180’ AND ‘209’
THEN ‘180-209 Days’
WHEN LS.LS_DaysInServicingStatus BETWEEN ‘210’ AND ‘239’
THEN ‘210-239 Days’
WHEN LS.LS_DaysInServicingStatus >= ‘240’
THEN ‘240+ Days’
END AS days
FROM dbo.LoanSummary(nolock) ls
WHERE SG_SRStatus IN (
‘Matured-Death’,
‘Matured-In Foreclosure’,
‘Matured-Tax Default’,
‘Matured-Move Out’)
GROUP BY SG_SRStatus, LS.LS_DaysInServicingStatus
ORDER BY SG_SRStatus

below is my result set in ssrs: ( because there is no days between the rang of 210 -239days and 240+ days, . this is not showing in the table but i want to show those two columns even if there is no data. 

Basically if someone know there are fixed columns then better to write sql is the way that one can use table instead of Matrix control, as Matrix is used when columns can dynamically increase.

Anyway if user want in Matrix only we can try to create a report on Matrix control for this case.

Let say my sample table dbo.LoanSummary.

Based on above query my result is like

1

So i dont have two other columns in the query result 210 -239days and 240+ days

So we will add one extra row for each status and each day period column.

Basically this below rows needs to be added in the SQL query:-

select ‘Matured-Death’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Tax Default’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Move Out’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

 

Final query will be like below:-

SELECT SG_SRStatus AS SG_SRStatus,
LS.LS_DaysInServicingStatus,
SUM(ls.SG_UPB) ‘SUM UPB’,
CASE WHEN LS.LS_DaysInServicingStatus BETWEEN ‘0’ AND ‘179’
THEN ‘0-179 Days’
WHEN LS.LS_DaysInServicingStatus BETWEEN ‘180’ AND ‘209’
THEN ‘180-209 Days’
WHEN LS.LS_DaysInServicingStatus BETWEEN ‘210’ AND ‘239’
THEN ‘210-239 Days’
WHEN LS.LS_DaysInServicingStatus >= ‘240’
THEN ‘240+ Days’
END AS days
FROM (
select * from dbo.LoanSummary
UNION
select ‘Matured-Death’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Death’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-In Foreclosure’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Tax Default’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Tax Default’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB

UNION
select ‘Matured-Move Out’ SG_SRStatus, 150 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 200 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 220 LS_DaysInServicingStatus,0 SG_UPB
UNION
select ‘Matured-Move Out’ SG_SRStatus, 250 LS_DaysInServicingStatus,0 SG_UPB
)ls
WHERE SG_SRStatus IN (
‘Matured-Death’,
‘Matured-In Foreclosure’,
‘Matured-Tax Default’,
‘Matured-Move Out’)

GROUP BY SG_SRStatus, LS.LS_DaysInServicingStatus
ORDER BY SG_SRStatus

Then come to report side.

Add matrix control as shown below:-

1

Why count is subtract -1 because we added one extra row in the query.

 

Run the report, report output looks like:-

1

 

So it working, if you like it or you need further help  please add comment.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Categories: Home, MSDN, SSRS Tags: ,

SSRS Multi parameter with boolean

April 12, 2016 Leave a comment

This question originally posted on MSDN. Click here to get the original question link.

In brief:-

Multi parameters with boolean value

I have parameter ‘Position’ with values : Driver , Administration ,Employee

MyTable

ID  Driver    Administration Employee

1        true       false                       false

2         false      false                     true

3        true         false                    false

4        false         true                    false

these are three columns with boolean values
How can I create a query to be able to pass the parameter Position 

and if the position is driver the result should be – table with  two records id: 1 and 3

            if administration or employee – table with one record

        select * from MyTable where Driver = ‘True’

We need to write dynamic query to solve this, as value from report parameter will come as column name .

Create a report parameter  ‘Position’  with specify values as Driver, Administration employee as shown in below screen:-

1

Now write the SQL query as Dynamic query as given below:-

Declare @sqlstr nvarchar(max)
set @sqlstr=’
select * from
(select 1 ID ,”true” Driver, ”false” Administration, ”false” Employee
UNION
Select 2 , ”false”, ”false”,”true”
UNION
Select 3,”true”,”false”,”false”
UNION
select 4 ,”false”,”true”,”false”

)T
where ‘+ @Position+’=”True”’
EXECUTE sp_executesql @sqlstr

@Postition value is passed through report parameter and  which become as column of the table and we check “True” condition.

 

Then run the report , select Driver is the value

1

 

run the report with different parameter value :-

1

 

So it working, if you like it or you need further help  please add comment.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Categories: Home, MSDN, SSRS Tags: ,

SSRS : how to divide the report horizontally and vertically

April 5, 2016 Leave a comment

This Question i got from MSDN, to go to original question click here In Brief:-  i need a report where i need to divide the report columns  horizontally and vertically as below image.. 3*3  need to…

Source: SSRS : how to divide the report horizontally and vertically

Categories: Home