Archive

Posts Tagged ‘MSDN’

SSRS : Page break after certain number of group

May 29, 2016 Leave a comment

This question I got from MSDN, Click here to get the original source of this question.

In brief :-

page break after certain number of group display

how can I do a page break after displaying 10 group collapsed lines ?

i am showing collapsed groups and want to page break after displaying 10 group lines (in collapsed view)

So user want like page break should happen  after 10 groups. Unfortunately there are no function to get the group counts. So better to bring group count number in the query itself.

Let say you have a sample data query as given below:-

select ‘Fruits’ category, ‘Apple’ product, 50 cost
UNION
select ‘Fruits’ category, ‘Grapes’ product, 40 cost
UNION
select ‘Fruits’ category, ‘Peach’ product, 30 cost
UNION
select ‘Vegetables’ category, ‘Tomato’ product, 50 cost
UNION
select ‘Vegetables’ category, ‘Potato’ product, 20 cost
UNION
select ‘Vegetables’ category, ‘Capsicum’ product, 40 cost
UNION
select ‘Grocery’ category, ‘Rice’ product, 100 cost
UNION
select ‘Grocery’ category, ‘Wheet’ product, 120 cost
UNION
select ‘Grocery’ category, ‘Oil’ product, 180 cost
UNION
select ‘Cleaning’ category, ‘Detol’ product, 80 cost
UNION
select ‘Cleaning’ category, ‘Floor cleaner’ product, 50 cost
UNION
select ‘Cleaning’ category, ‘Dish washer’ product, 40 cost

Dataoutput

add the  Dense_Rank function to add one more column which will give the group number as shown below:-

select *, dense_rank() over( order by category)pagebreakrow from
(
select ‘Fruits’ category, ‘Apple’ product, 50 cost
UNION
select ‘Fruits’ category, ‘Grapes’ product, 40 cost
UNION
select ‘Fruits’ category, ‘Peach’ product, 30 cost
UNION
select ‘Vegetables’ category, ‘Tomato’ product, 50 cost
UNION
select ‘Vegetables’ category, ‘Potato’ product, 20 cost
UNION
select ‘Vegetables’ category, ‘Capsicum’ product, 40 cost
UNION
select ‘Grocery’ category, ‘Rice’ product, 100 cost
UNION
select ‘Grocery’ category, ‘Wheet’ product, 120 cost
UNION
select ‘Grocery’ category, ‘Oil’ product, 180 cost
UNION
select ‘Cleaning’ category, ‘Detol’ product, 80 cost
UNION
select ‘Cleaning’ category, ‘Floor cleaner’ product, 50 cost
UNION
select ‘Cleaning’ category, ‘Dish washer’ product, 40 cost

)T

denseRankoutput

Now go to report design and group properties as shown below:-

GroupProperties

let say I want page break after 2 groups then the expression should be like below:-

=iif(Fields!pagebreakrow.Value mod 2 = 0,False ,True )

run the report, output is like below:-

ReportOutput.png

Let say , I want page break after 3 groups then expression should be as given below:-

=iif(Fields!pagebreakrow.Value mod 3 = 0,False ,True )

run the report

 

ReportOutput

Hope you like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

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

Advertisements
Categories: Home, MSDN, SSRS Tags: ,

SSRS : Dynamic ODBC Excel connection using expressions

May 29, 2016 Leave a comment

ODBC connection using Excel as data-source.

First we check the normal ODBC excel connection,  how to create Shared data source using normal connection, i.e. Static connection is given very nicely in below post.

http://hussain-msbi.blogspot.sg/2012/07/create-ssrs-report-using-excel-data.html

We will only see the connection string of normal connection then we try to modify it to dynamic.

I have created two excel with different data in it as shown below:-

sample.xlsx:-

sample

One more excel with different data.

sample_2016_05.xlsx:-

sample_2016_05

One connection is shared data source with sample.xlsx as shown below :-

`shareddatasource

Above connection string is static one which connect to sample.xlsx.

How to create above data source connection ?

Please refer the blog given below:-

http://hussain-msbi.blogspot.sg/2012/07/create-ssrs-report-using-excel-data.html

Now we will create dynamic ODBC connection with expressions.

Now second file is full path is :-

C:\Users\prasad\Desktop\2016\sample_2016_05.xlsx

To write expression in data source connection, it should be embedded data-source.

Note: In Shared data source connection we cant write expression.

Please follow below screen , how to write expression for dynamic one:-

DynamicDatasource.png

Type: ODBC above is must select option.

Expression given in above screen:-

=”Dsn=ODBC_Connection_Sample;dbq=C:\\Users\\prasad\\Desktop\\” & cstr(year(Today)) & “\\sample_” & cstr(year(Today)) & “_0” & cstr(month(Today)) & “.xlsx;defaultdir=C:\\Users\\prasad\\Desktop;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5”

Now create two dataset using each type of datasource, report design will look like below:-

ReportDesign

Run the report, check the report output.

ReportOutput

 

Hope you people like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

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

 

SSRS: Custom Start of week (Friday to Thursday )

May 15, 2016 Leave a comment

Generally we have start of week Sunday to Saturday or Monday to Sunday depends upon the system settings.

So before setting our expression to custom week as Friday to Thursday , we should know what is the default start of the week.

Can get more information on MSDN

As I am checking weekday Starting from Sunday or Monday.

CheckingWeekStart

So In my SSRS report week start is Sunday and end at Saturday.

Now when we want to modify week as Friday to Thursday then we need to identify the pattern.

Below  column F and H are the pattern :-

week PatternAnalysis

Now write expressions based on the pattern:-

Start of week Expression:-

=Format(iif(WeekDay(Fields!Date.Value)>=6,dateadd(DateInterval.Day, 6-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, -1-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

End Of week Expression:-

=Format(iif(WeekDay(Fields!Date.Value)>=6,dateadd(DateInterval.Day, 12-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, 5-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

Run the report , output as given below:-

SSRS Ouput

Now we will check how to get the previous week using again pattern analysis

Previous week  Pattern Analysis

Now write expressions based on the pattern:-

Start of Previous Week :-

=Format(iif(WeekDay(Fields!Date.Value)<=5,dateadd(DateInterval.Day, -8-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, -1-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

End Of Previous Week:-

=Format(iif(WeekDay(Fields!Date.Value)<=5,dateadd(DateInterval.Day, -2-WeekDay(Fields!Date.Value), Fields!Date.Value),dateadd(DateInterval.Day, 5-WeekDay(Fields!Date.Value), Fields!Date.Value)), “MM/dd/yyyy”)

 

Run the report, output looks like below:-

ReportPreviousWeekOutput

So we can set previous week or current week based on custom requirement, only we need to identify the pattern. Pattern help to write case or IF statements. Without pattern we need to minimum 7 cases for each week day.

Hope you people like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

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

 

Categories: Home, MSDN, SSRS Tags: ,

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: ,