Tuesday, July 21, 2015

SSRS – Chart based Drill-down Report

A picture is worth a thousand words – especially when business is trying to find relationships and understand in their data, which could include thousands or even millions of variables and representation of data visualization through the chart reports become very urgent to take the right action for the business. A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will provide the additional details of that particular category. 

One of the biggest challenges for business users is deciding which visual should be used to best represent the information. Better understanding of the business requirements, chart reports play a very important role to create dynamic Dashboard reports. 
For example; sales admin wants to see the performance of their products in the different regions such as France, England and USA etc. and he is also interested to see the performance of the products in the states of any specific country.  

To meet the requirement, Chart Graph reports are the best option to represent the sales data to state or cities level. We are going to assume that we have Sales Summary data table in our database having the following information –

StateCode
StateName
CountryCode
CountryName
SalesYear
TotalSales
TotalTax
BY
Bayern
DE
Germany
2008
1981811.89
172758.34
HE
Hessen
DE
Germany
2008
2981811.89
272758.34
HH
Hamburg
DE
Germany
2008
2581811.89
342758.34
FR
France
FR
France
2008
3298789.76
306620.72
1
Ain
FR
France
2008
4298789.76
346620.72
2
Aisne
FR
France
2008
1298789.76
106620.72
3
Allier
FR
France
2008
1598789.76
166620.72
ENG
England
GB
United Kingdom
2008
2487391.50
220058.34
AL
Alabama
US
United States
2008
1181754.12
113381.69
AR
Arkansas
US
United States
2008
1323769.09
128255.76
DE
Delaware
US
United States
2008
1059022.59
101832.09
VI
Virgin Islands
VI
Virgin Islands, U.S.
2008
1181754.12
113381.69


We need to display the above information in our SSRS report and Report header should be like as “Regional - Sales Summary Report”. Whenever user clicks on the country category column then it will drill the chart graph and show the state sales summary of that particular country category as shown below:


After click on the country France column then Report will show the following states sales information in the France country- 


We will apply the following things to the graph report–
  • Add country parameter to the report and set ALL as default value for the graph report means to display all countries on the graph. This value will be changed automatically whenever drill down apply to the report.
  • We need to set the dynamic X- axis title for various drill-down based on the report parameter.
  • We need to set the axis title dynamically to set countries name and states name based on the report parameter.
  • The same report will call again whenever drill request will send to server.  

To create Graph Drill Down report, we will follow the following steps as shown below:
1) In our existing report server project, we will add a new report as “Regional - Sales Summary Report” as shown below:



2) Now, we need to create a dataset for the report as shown below:



3) Add Dataset will launch Dataset properties window. We need to set the name as SalesSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:
1    
SELECT [StateProvinceCode]
      ,[StateProvinceName]
      ,[CountryRegionCode]
      ,[CountryRegionName]
      ,[SalesYear]
      ,[TotalSales]
      ,[TotalTax]
FROM [dbo].[SalesSummary]
Where (@Country = 'ALL' OR [CountryRegionCode]=@Country )
  

  


4) After click on the OK button, a dataset has been added in the report data with parameters also as shown below:


5) Click on the Country parameter and set the default value “ALL” as shown below:



6) Now right click on the report, click on Chart from Insert as shown below:



7) From Chart Type window, choose 3-D Clustered Column Chart and click OK button as shown below:
 

8) Now change the Chart Title as “Regional – Sales Summary” and set the dataset to the column chart in the Chart properties window as shown below:



9) Now, we need to set the Tax sales amount and Total sales amount to the column chart. For this, click on the chart legend as shown below:



10) We need to set values in chart data which are work for Y-axis data representation. Choose Total Tax and Totals Sales as shown below: 



11) To set the values in the X-axis, we need to fill the values in Chart Data in Category groups as shown below:



12) We need to set the dynamic X-axis category labels. Right click in Chart Data’s Category Groups as shown below:



13) In Category Group Properties window, In the label, we need to set the conditional expression based on the report parameter. 
  
=IIF(Parameters!Country.Value="ALL",
Fields!CountryRegionName.Value, Fields!StateProvinceName.Value)

  





14) Now click on OK button and view the preview of the report as shown below-



15) Now, we need to set the dynamic X - Axis Title as shown below which is also based on the country report parameter:


16) In the Axis Title Properties, we need to set the Title Text such as following condition as shown below: 

=IIF(Parameters!Country.Value="ALL", "Sales in countries"
, "Sales in "+Fields!CountryRegionName.Value + "'s cities")
+ " in year " + Convert.ToString(Fields!SalesYear.Value)




17) Click on the OK button and need to set the Y-axis title also as shown below-
  


18) Now click on OK button and view the preview of the report as shown below-



19) In the chart report, we need to set the column data labels now. For this, right click on the columns and click on the Show Data Labels as shown below: (do this for both columns)
  




20) Now, we need to set the drill down option on the column. For this, right click on the column (do this for both columns) and click on the Series Properties as shown below:



21) In the Series Properties window, we need to do the following things-
a. Choose the page Action,
b. Select Go to Report radio button,
c. Specify the current report name,
d. Add parameter country and fill the value as [CountryRegionCode] from the values list 
as shown below:


22) Click on the OK button and preview the report as shown below - 



After click on the country France column then Report will show the following states sales information in the France country- 



This completes the tutorial section of the simple chart based drill report.

Other Drill Reports in SSRS
1) To learn the Row Drill Down Report, Click here.
2) To learn the Row Drill Through Report, Click here.
3) To learn the Column based Drill Down Report, Click here.

How can you create your first sub report. Click here.

50 comments:

  1. Top of the RDBMS... we can but Top of the Cube, we have generated chart report and whenever click the chart will display the drill down report.... Is this possible top of the CUBE...

    ReplyDelete
  2. What is being passed to the Stored Procedure are the argument values of the parameters. This said if you use a linked server to your OLAP server and an OpenQuery, you can use a generalized MDX query to extract the necessary data AND then filter the data via a standard T-sql predicate.
    Select ..... from OpenQuery("myLinkedServer", 'MDX query goes here') a
    where field1 = argument1 and field2 = argument2
    Do have a look at some of the BI articles on SQLShack dot com

    ReplyDelete
  3. These are probably the most common types of everyday use cordless drills you will find on the market today. The big advantage with the low rpm/high torque setting is that now you can drive screws and lag bolts at the low rpm needed without having to try to hold the trigger in a certain position. cordless screwdriver

    ReplyDelete
  4. Thanks for this very useful Information. Drill-drown reports are one way of linking two or more reports, so that one report shows summary information and a second report is linked from it to show finer grained detail.
    SSRS Tutorial

    ReplyDelete
  5. There are even more variations and innovations in the works as I write this. While the scope of this guide will not be able to cover every type of power drill in existence, we are going to try to cover most of the bases.https://simplehandyman.co.uk/best-table-saw-uk-reviews/

    ReplyDelete
  6. It's really a nice experience to read your post. Thank you for sharing this useful information.
    Data Modeling Training

    Mule ESB Training

    ReplyDelete
  7. The most ideal approach to limit the sort of drill you ought to get is to make sense of how you plan to utilize your drill.Best cordless drills

    ReplyDelete
  8. you are able to take the help of experts making a call at QuickBooks Payroll Support Phone Number Well! If you’re not in a position to customize employee payroll in.

    ReplyDelete
  9. You are always able to relate with us at our QuickBooks Support contact number to extract the very best support services from our highly dedicated and supportive QuickBooks Customer Support Number executives at any point of the time as most of us is oftentimes prepared to work with you.

    ReplyDelete
  10. Regardless of most of the well-known QuickBooks Premier features you may find difficulty at some steps. QuickBooks Customer Support Number is the foremost destination to call in the period of such crisis.

    ReplyDelete
  11. QuickBooks Enterprise Tech Support Number will be the first point of contact to report the matter where you get your entire worries taken care off and assured solutions straight away offers you complete peace of mind.

    ReplyDelete
  12. If this doesn’t help you, go on and connect with us at QuickBooks Support Phone Number. A lot of us works 24*7 and serve its customers with excellent service each time they contact us.

    ReplyDelete
  13. For such type of information, be always in contact with us through our blogs. To find the reliable supply of help to create customer checklist in QB desktop, QuickBooks online and intuit online payroll? Our Quickbooks Support Number might help you better.

    ReplyDelete
  14. You merely need certainly to avail the assistance through the technical experts by dialing the Quickbooks Support Phone Number. You could have a word of discussion together with them sharing your entire doubts, and getting the essential productive solutions.

    ReplyDelete
  15. Your tech support team team is just an individual tap away, dial our QuickBooks Support Phone Number and experience our best hassle-free tech support team.

    ReplyDelete
  16. In conclusion, don’t hesitate to call us on our QuickBooks Support Phone Number. We have been surely here for you personally. In conclusion, any error, any problem, any bug or whatever else pertaining to QuickBooks related problem, just call our QuickBooks Tech Support Number. Surely, call our QuickBooks Support contact number.

    ReplyDelete
  17. Quickbooks Support Phone Number service number accords assistance to the QuickBooks users’ worldwide. The support team can be reached through various modes such as: phone support, email support, live chat, FAQ, QuickBooks community etc. Solving the Quickbooks related problems and issue Remotely . QuickBooks in current time is Number #1 accounting software popular in the USA , Canada , Europe and Australian market for business management.

    ReplyDelete
  18. When it's found and retrieved, get rid of the program such that QuickBooks Customer Support Number will not cause any longer inconvenience.If all these method still does not provide you with results, make an attempt to improve the name associated with download < nn> folder to downloadqb< nn >.old. That will have the specified effect.

    ReplyDelete
  19. QuickBooks encounter an amount of undesirable and annoying errors which keep persisting as time passes if you don't resolved instantly. Considered one of such QuickBooks issue is Printer issue which mainly arises as a result of a number of hardware and software problems in QuickBooks, printer or drivers. You can actually resolve this error by using the below troubleshooting steps you can simply contact our Support For QuickBooks available at.

    ReplyDelete
  20. You're able to resolve this error by using the below troubleshooting steps you may also simply contact our QuickBooks Support Phone Number available at.You should run QuickBooks Phone Support Number print and pdf repair tool to determine and fix the errors in printer settings prior to starting the troubleshooting.

    ReplyDelete
  21. Different styles of queries or QuickBooks Payroll Tech Support Number related issue, then you're way in the right direction. You simply give single ring at our toll-free intuit QuickBooks Payroll support phone number .

    ReplyDelete
  22. The process to install and set up QuickBooks Support Phone Number on Windows is same as that of Mac. The system requirements for the installation process may vary slightly for both the operating system. You just need to take care of that and just ensure that you check and verify them first, before starting with the procedure. Getting instant and effective help for any matter of concern is what the user’s desire for. With QuickBooks, you can rest assured about getting the most desirable and efficacious help on every issue that you might encounter yourself with. You just need to avail the help from the technical experts by dialing the QuickBooks Support. You can have a word of discussion with them sharing all your doubts, and getting the most productive solutions.

    ReplyDelete
  23. Repairing does not resolve the matter. The QuickBooks Error 3371 usually occurs when a person carries out a system restore or relocates the program files to a new PC or disk drive. The reason for the error code is said to be the Entitlement file.

    ReplyDelete
  24. It’s another fabulous feature of QuickBooks Payroll Support Phone Number service, it really is a site where your entire valuable employees will get the data of your own paychecks. It saves even more time consumed by doing printing and mailing paystubs each day or replacing lost or damaged paystubs.

    ReplyDelete
  25. QuickBooks Enterprise Support Phone Number has almost eliminated the typical accounting process. Along with a wide range of tools and automations, it provides a wide range of industry verticals with specialized reporting formats and tools.

    ReplyDelete
  26. Quickbooks Payroll Support could be the toll-free quantity of where our skilled, experienced and responsible team are available 24*7 at your service. There are a selection of errors that pop up in QuickBooks Payroll which are taken care of by our highly knowledgeable and dedicated customer support executives.

    ReplyDelete
  27. With time number of users and number of companies that can be chosen by some body or even the other, QuickBooks Enterprise Tech Support Number has got lots of choices for the majority of us. Significant amount of features through the end are there any to guide both you and contribute towards enhancing your web business.

    ReplyDelete
  28. Are you currently scratching your head and stuck along with your QuickBooks related issues, you may be just one click away from our expert technical support for your QuickBooks related issues. We QuickBooks Support Phone Number, are leading technical support provider for all your QuickBooks related issues. Either it is day or night, we provide hassle-free tech support team for QuickBooks and its own associated software in minimum possible time. Our dedicated technical team is present for you really to 24X7, 365 days a year to ensure comprehensive support and services round the clock. We assure you the quickest solution of all your QuickBooks software related issues.

    ReplyDelete
  29. Search towards the chart of accounts is simple to manage with added search bar right in the chart of accounts. For better information, you could call at QuickBooks Enterprise Help Number.

    ReplyDelete
  30. Today, payroll running is currently complex. You may need advanced software. There must be a premier mix solution. Quickbooks Support often helps. Proper outsource is crucial. You'll discover updates in connection with tax table. This saves huge cost. All experts can take place. A team operates 24/7. You receive stress free. Traders become free. No one will blame you. The outsourced team will see all.

    ReplyDelete
  31. You comprehend the root cause associated with problem. As company file plays a really crucial role in account management, so that it becomes a little tough to spot. QuickBooks Support Number File corruption issue is a bit tricky, however, you certainly will overcome it for very long as per the second instances:

    ReplyDelete
  32. You just need certainly to build a straightforward charge less call on our QuickBooks Pro Tech Support Phone Number variety and rest leave on united states country. No doubt, here you'll find the unmatchable services by our supportive technical workers.

    ReplyDelete
  33. Well! If you’re not in a position to customize employee payroll in Quickbooks which makes the list optimally, in QB and QB desktop, then read the description ahead. Here, you will get the determination of numerous type of information that which you’ve close at hand for assisting the setup QuickBooks Payroll Support Phone Number

    ReplyDelete
  34. QuickBooks Enterprise Technical Support Number series, QuickBooks PS series square measure many of the foremost common and therefore the most dangerous errors which our Users need certainly to face. therein case, they ought to forthwith dial the fee QuickBooks Technical Support Service.

    ReplyDelete
  35. QuickBooks Error 6000-301 takes place when accessing the company file in Quickbooks accounting software. This error may be brought on by various defect and damages to QuickBooks desktop. QuickBooks Error -6000, -301 encounters while trying to use a desktop company file.

    ReplyDelete
  36. Just make sure whenever you need to update or change any data, you should create the backup of the older data. These were the simple steps with the QuickBooks Support help of which you will be able to complete writing cheques with unscheduled payroll.

    ReplyDelete
  37. QuickBooks Pro is some type of class accounting software which has benefited its customers with various accounting services. It offers brought ease to you by enabling some extra ordinary features and also at QuickBooks Payroll Tech Support Number it is simple to seek optimal solutions if any error hinders your work. With QuickBooks Pro you can easily easily effortlessly create invoices and keep close tabs on every little thing like exacltly what the shoppers bought, just how much they paid etc. In addition it lets you have a crystal-clear insight of your business that can help someone to monitor your cash, taxes as well as sales report, everything at one place.

    ReplyDelete
  38. QuickBooks Payroll in addition has many lucrative features that set it irrespective of rest about the QuickBooks versions. It simply assists you to by enabling choosing and sending of custom invoices. It will be possible to very easily keep track of 50 employees at a time and you also can monitor the sheer number of working hours of each and every employee. It will help you in calculating the complete soon add up to be paid to a worker depending until the QuickBooks Payroll Tech Support Number of hours he/she has contributed of their work. The amazing feature for this application is direct deposit and this can be done at any time one day.

    ReplyDelete
  39. Every business wishes to obtain revenues on a regular basis. But, not every one of you will be capable. Do you realize why? It is due to lack of QuickBooks Technical Support You will be a new comer to the business enterprise and then make a lot of errors.

    ReplyDelete
  40. For such type of information, be always in contact with us through our blogs. To find the reliable supply of help to create customer checklist in QB desktop, QuickBooks online and intuit online payroll? Our QuickBooks Payroll Tech Support Phone Number might help you better.

    ReplyDelete
  41. QuickBooks Payroll Support Phone Number may be the toll-free quantity of where our skilled, experienced and responsible team are available 24*7 at your service. You will find a selection of errors that pop up in QuickBooks Payroll that are taken care of by our highly knowledgeable and dedicated customer support executives. There are numerous regularly occurring Payroll errors with this software that may be of just a little help to you.

    ReplyDelete


  42. Printers have built up their asset internationally, with full help of every single individual existing. Likewise, HP printer has additionally contributed its Hp printer support at each progression any place required. Like each framework needs an update, HP printers additionally should be update properly. Hp printer not just print the archives or non-official pages yet in addition check all the more then one page as indicated by the client's necessity. We immovably have confidence in giving prime Hp printer bolster Phone number to our clients who face trouble while printing. HP printers can likewise have some default, which aggravates the client for a considerable length of time. Any sort of inconvenience which the HP printer client faces, he is openly permitted to contact on our
    HP Printer Support Phone Number
    .

    ReplyDelete
  43. You can quickly dial the HP Printer Tech Support Number to take support for several technical issues and complications. HP Printer Problems and Troubleshooting Guide Amongst most of the products launched by h . p ., it is their printers that are regarded as the very best.

    ReplyDelete
  44. QuickBooks Payroll Customer Service
    helps one to resolve all your valuable technical and functional problems whilst looking after this well known extensive, premium and end-to-end business accounting and payroll management software. Our experts team at QuickBooks payroll support number is going to make you understand its advanced functions and assists someone to lift up your business growth.

    ReplyDelete
  45. Ensuing to accomplishing the help support, you need to pass on them clear issues that you are facing, so they help you with impeccable courses of action. In the event that you are in mid of this issue and would slant toward not to squander a particular moment by then essentially give an undertaking to the approaches referenced here for some exceptionally ordinary issues. These issues rise from time to time in the term of the printing strategy.
    HP Printer Support Phone Number

    HP Printer Support Phone Number

    HP Printer Support Number

    HP Printer Support

    HP Envy Printer Support

    HP Printer Customer Support Number

    HP Printer Customer Support Phone Number

    HP Printer Helpline Number

    ReplyDelete
  46. Your tech support team team is definitely just one tap away, dial our QuickBooks Support and experience our best hassle-free tech support team.

    ReplyDelete
  47. Our research team at Intuit QuickBooks Support is dependable for most other reasons as well. We have customer care executives which are exceptionally supportive and pay complete awareness of the demand of technical assistance made by QuickBooks users.

    ReplyDelete
  48. It's simple to get a quantity of benefits with QuickBooks Customer Support Phone Number. Proper analyses are done first. The experts find out from the nature associated with trouble. You'll get a whole knowledge as well. The support specialist will identify the problem. The deep real cause will probably be found out.

    ReplyDelete
  49. QuickBooks Support Number has a great deal to offer to its customers in order to manage every trouble that obstructs your projects. There are tons many errors in QuickBooks such as difficulty in installing this software, problem in upgrading the software in to the newer version so that you can avail the most recent QuickBooks features, trouble in generating advanced reports, difficulty with opening company file in multi-user mode and thus on and so forth. Whatever the issue is, if it bothers you and deters the performance of your respective business, you may need to not get back seat and offer up, just dial us at our toll-free number and luxuriate in incredible customer care.

    ReplyDelete

Popular Posts

Get Sponsored by Big Brands