tag:blogger.com,1999:blog-50711748201279052682024-03-22T07:16:49.845+05:30Experiencing Microsoft BIмαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-5071174820127905268.post-13417999239260281602011-09-14T22:49:00.003+05:302011-09-16T11:47:48.530+05:30DrillDown Charts<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: black; font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"> Drilling down</span> usually refers to navigating a hierarchy of information</span></span>. <span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">It helps in encapsulating or wrapping the data details into levels.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">User can expand or collapse the data present in the form of hierarchy according to their usage.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">We have seen this thing in our reports in the form of Tabulated data.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"> </span></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHzouICRmH7E7eQaAZSE9_ArT5ZU96YbQehMBDGgXOTASVZQ7W-diw1_FLqN7QyaLlL7ilMZIw_bwOTldcSPxCej-5JJ1nTcvab7KbdSAJadRcBAGz0G2MjQ5nUCfIN1ejIXMIekrXSxQ/s1600/drill1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="31" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHzouICRmH7E7eQaAZSE9_ArT5ZU96YbQehMBDGgXOTASVZQ7W-diw1_FLqN7QyaLlL7ilMZIw_bwOTldcSPxCej-5JJ1nTcvab7KbdSAJadRcBAGz0G2MjQ5nUCfIN1ejIXMIekrXSxQ/s400/drill1.JPG" width="400" /></a></div>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"> </span></span><br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYXDBJFaSw5jBiADLbfR19pnm-BkIFHb-ZDQ7MIq_25Cf-SyDdRm0uv07hOcbzTbyW4mxXF6IpWAypyCbTJS3nbejBDA9Jx3WjUXH3qkNZMknkjFy6ucxFyyuow1p4Ys70hQI_iZV7Qbg/s1600/drill2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="56" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYXDBJFaSw5jBiADLbfR19pnm-BkIFHb-ZDQ7MIq_25Cf-SyDdRm0uv07hOcbzTbyW4mxXF6IpWAypyCbTJS3nbejBDA9Jx3WjUXH3qkNZMknkjFy6ucxFyyuow1p4Ys70hQI_iZV7Qbg/s400/drill2.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">SSRS provides us the feature in the form of <span style="color: #cc0000;">Toggle </span></span><span style="font-family: Verdana, sans-serif;">that can be used in our tablix to have this drill-down hierarchy.</span></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbq-L-jUUF4HdaAysTp_T-_rsColA9hFOS-U-514kO_-5wPjN__bVlmcbZ4g4LutzH6TT-xb_74tYKMjHqKj4nSwQAORKxbJ-VgpJ1FGzIfsS9P4kZ4xg5q6iKk0fPeW93Uh1IcJ2_6FE/s1600/drill3.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="325" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbq-L-jUUF4HdaAysTp_T-_rsColA9hFOS-U-514kO_-5wPjN__bVlmcbZ4g4LutzH6TT-xb_74tYKMjHqKj4nSwQAORKxbJ-VgpJ1FGzIfsS9P4kZ4xg5q6iKk0fPeW93Uh1IcJ2_6FE/s400/drill3.JPG" width="400" /></a></div>
<br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">That was easy, i mean we have the control available to toggle the visibility of table data.</span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"> </span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">How about doing the same to charts?</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br />Can we also do the same in our charts in the way. I am sorry, we don't have any Toggle kind of thing available for charts.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">So, what's the catch! If you can play with expressions and parameters then you can achieve that.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">I tried on achieving the same and came out with a workaround (I will say it a workaround J ) </span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Lets start then,</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><br /></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Categories used in my example: Year, Month, Date</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Values used in my example: Amount</span></span><br />
<ul>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Create a parameter <span style="color: #cc0000;">Level </span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Set the visibility to Hide.</span></span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Assign Default value to 0</span></span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Assign Available values to 0, 1, 2</span></span></span></span></li>
</ul>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"> Note: <span style="color: black;">Parameter Level will be used for conditionally checking the hierarchy shown in the chart.</span></span></span></span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"> Create a chart, add a category (I used Year for instance) and a Value (used Amount)</span></span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">I want to show <span style="color: #cc0000;">Year --> Month --> Date</span> drill-down structure in my chart so I have used the fields accordingly.</span></span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">In the Category group, I have used the expression, both on the <b>Group On</b> and <b>Label, </b>as</span></span></span></span></li>
</ul>
<div style="color: #cc0000;">
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">= Switch ( Parameters!Level.Value = 0, Fields!Year.Value,</span></span></div>
<div style="color: #cc0000;">
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Parameters!Level.Value = 1, Fields!Month.Value,</span></span></div>
<div style="color: #cc0000;">
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Parameters!Level.Value = 2, Fields!Date.Value</span></span></div>
<span style="color: #cc0000; font-size: small;"><span style="font-family: Verdana, sans-serif;">) </span></span><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"> </span></span></span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"><br /></span></span></span></span><br />
<ul style="text-align: left;">
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Then, another important aspect that needs to be added is action on the Bars, so as to drill-down furthur.</span></span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Add an Action to the Bars, using <span style="color: #cc0000;">Go To Report</span> feature.</span></span></span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Select the same report, add a parameter Level with value</span></span></span></span></li>
</ul>
<div>
<br />
<div style="color: #cc0000;">
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">= Switch ( Parameters!Level.Value = 0, 1,</span></span></div>
<div style="color: #cc0000;">
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Parameters!Level.Value = 1,2,</span></span></div>
<div style="color: #cc0000;">
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;">Parameters!Level.Value = 2,0</span></span></div>
<span style="color: #cc0000; font-size: small;"><span style="font-family: Verdana, sans-serif;">) </span></span><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"></span></span></span><br />
<ul style="text-align: left;">
<li><span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">Now, the basic structure is done, you can add images and manipulate visual labels etc. based on the requirement.</span></span></span></span></li>
</ul>
</div>
<div>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;">So, here's the report:</span></span></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrVit-9k-KkljdZwS6U1fvMAXfe-imGsZm5dUzz6KVd6k9OfdxcK37dbcZpGDrc99oXfJlo7u4-pmOVJ7Tav1cU6eA67ADauMhUEpNZRCgshyphenhyphen9VK6man_Cb1hFFzZ-t66_UQpwckhMMNw/s1600/drillchart1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="337" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrVit-9k-KkljdZwS6U1fvMAXfe-imGsZm5dUzz6KVd6k9OfdxcK37dbcZpGDrc99oXfJlo7u4-pmOVJ7Tav1cU6eA67ADauMhUEpNZRCgshyphenhyphen9VK6man_Cb1hFFzZ-t66_UQpwckhMMNw/s400/drillchart1.JPG" width="400" /></a></div>
<div>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"> </span></span></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_RF_XQ7eaAAtgFWdkW7HbsuIdwlXj6g4jeZ2IFxHHNVLq3CEbYYEkIN_yQzHAsMNws8HSbL5dqXAmApOk3s8MEI-EBeD_J0yLqxCdRLGPn6jhkNCmkyuttjQ0Q5LBb79-7sE9W9FZZmM/s1600/drillchart2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="312" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_RF_XQ7eaAAtgFWdkW7HbsuIdwlXj6g4jeZ2IFxHHNVLq3CEbYYEkIN_yQzHAsMNws8HSbL5dqXAmApOk3s8MEI-EBeD_J0yLqxCdRLGPn6jhkNCmkyuttjQ0Q5LBb79-7sE9W9FZZmM/s400/drillchart2.JPG" width="400" /></a></div>
<div>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"> </span></span></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWWHIARKWjQjFMnYeIvvvBaAvdZGM9A3iM1TTShVNB8X4q0bZnh1s_gjMW7WbeerQApTZFrC9UP_UhoOKFCbVaPILUDqDhZc0TC0XN9wZHgLHT3COm1csb6S8IJh_Gv2T-3GK095uzD78/s1600/drilldown2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="307" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWWHIARKWjQjFMnYeIvvvBaAvdZGM9A3iM1TTShVNB8X4q0bZnh1s_gjMW7WbeerQApTZFrC9UP_UhoOKFCbVaPILUDqDhZc0TC0XN9wZHgLHT3COm1csb6S8IJh_Gv2T-3GK095uzD78/s400/drilldown2.JPG" width="400" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWWHIARKWjQjFMnYeIvvvBaAvdZGM9A3iM1TTShVNB8X4q0bZnh1s_gjMW7WbeerQApTZFrC9UP_UhoOKFCbVaPILUDqDhZc0TC0XN9wZHgLHT3COm1csb6S8IJh_Gv2T-3GK095uzD78/s1600/drilldown2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWWHIARKWjQjFMnYeIvvvBaAvdZGM9A3iM1TTShVNB8X4q0bZnh1s_gjMW7WbeerQApTZFrC9UP_UhoOKFCbVaPILUDqDhZc0TC0XN9wZHgLHT3COm1csb6S8IJh_Gv2T-3GK095uzD78/s1600/drilldown2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a></div>
<div>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"> </span></span></span></span></div>
<div>
<span style="font-size: small;"><span style="font-family: Verdana, sans-serif;"><span style="color: #cc0000;"><span style="color: black;"> </span></span></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRR95Cnze1VW86edMOZ1y_N2mOF6uRRqoP25mPG50kZAEz0AkvCawEVhWydNaD6rv-lZrXH4JFyFBo-NWcjiJ-KuhJt9moliI7t_Mqnu6LIQtFZtNPWFGg-PNYlCGZSrNcDaFT7rewfak/s1600/drillpie1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRR95Cnze1VW86edMOZ1y_N2mOF6uRRqoP25mPG50kZAEz0AkvCawEVhWydNaD6rv-lZrXH4JFyFBo-NWcjiJ-KuhJt9moliI7t_Mqnu6LIQtFZtNPWFGg-PNYlCGZSrNcDaFT7rewfak/s1600/drillpie1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRR95Cnze1VW86edMOZ1y_N2mOF6uRRqoP25mPG50kZAEz0AkvCawEVhWydNaD6rv-lZrXH4JFyFBo-NWcjiJ-KuhJt9moliI7t_Mqnu6LIQtFZtNPWFGg-PNYlCGZSrNcDaFT7rewfak/s1600/drillpie1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="257" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRR95Cnze1VW86edMOZ1y_N2mOF6uRRqoP25mPG50kZAEz0AkvCawEVhWydNaD6rv-lZrXH4JFyFBo-NWcjiJ-KuhJt9moliI7t_Mqnu6LIQtFZtNPWFGg-PNYlCGZSrNcDaFT7rewfak/s400/drillpie1.JPG" width="400" /></a></div>
<div>
<div>
<span class="Apple-style-span" style="font-family: Verdana, sans-serif; font-size: 16px;">Same can be done to other chart types as well, like the Pie chart.</span><br />
<br />
<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioD_x4dxMAF3v5rCev3IypxqCTn1GwkJpPvlNBG6WYY1c0D8fMSmYUdVn5ZcISRm6vrCNSWo3OySh0NTIsrEYO6jT9b6cNHalAeXMv6cMPgQkcJFlpKPL6JzhtwKMnXg8BGwDtjdZ1QA0/s1600/drillpie2.JPG" imageanchor="1" style="clear: left; display: inline ! important; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioD_x4dxMAF3v5rCev3IypxqCTn1GwkJpPvlNBG6WYY1c0D8fMSmYUdVn5ZcISRm6vrCNSWo3OySh0NTIsrEYO6jT9b6cNHalAeXMv6cMPgQkcJFlpKPL6JzhtwKMnXg8BGwDtjdZ1QA0/s400/drillpie2.JPG" width="400" /></a><br />
<br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt;"></span></div>
</div>
</div>
</div>
мαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.com10tag:blogger.com,1999:blog-5071174820127905268.post-309022428365215792011-09-13T12:38:00.002+05:302011-09-13T12:41:22.658+05:30SSRS Calendar and Date Restriction<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Interactive reports are always in demand, the ease it gives our users for usage. One of the important component to any report will be the Date Parameter/Filter, to get insight of historical data as well as current data.</span><br />
<div>
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Adding a calendar will solve the purpose.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJPLNck3MAsVqpo_3nWUs0MSEXMC-yF4EuQE8YkmswFCf-MlgOy0Uwzaz8ow809v2CY4lCVSlA_mEvcq57SdL2faL_e63iW-Mx7yJWTkWbCQxV3Gkn6EFUbgSpe9Ud21Z6CFJPZ29LL1s/s1600/date1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJPLNck3MAsVqpo_3nWUs0MSEXMC-yF4EuQE8YkmswFCf-MlgOy0Uwzaz8ow809v2CY4lCVSlA_mEvcq57SdL2faL_e63iW-Mx7yJWTkWbCQxV3Gkn6EFUbgSpe9Ud21Z6CFJPZ29LL1s/s1600/date1.JPG" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJPLNck3MAsVqpo_3nWUs0MSEXMC-yF4EuQE8YkmswFCf-MlgOy0Uwzaz8ow809v2CY4lCVSlA_mEvcq57SdL2faL_e63iW-Mx7yJWTkWbCQxV3Gkn6EFUbgSpe9Ud21Z6CFJPZ29LL1s/s1600/date1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">SSRS provides a data type known as <span class="Apple-style-span" style="color: #cc0000;">Date/Time</span> for this purpose.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHfGO53r0AU9tBjCa6Po3c7Busapu0bn2wqMv3h_y86QzX53XIW0Q1FUM_W6cp7aFc5XMbcJEEnxwltVgB5ZGoLTRGK5zTcVqu8kc4ZmaY5iiEOg0yXs32AvEhfIlvb7BJjUUl_uWzEdo/s1600/date.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHfGO53r0AU9tBjCa6Po3c7Busapu0bn2wqMv3h_y86QzX53XIW0Q1FUM_W6cp7aFc5XMbcJEEnxwltVgB5ZGoLTRGK5zTcVqu8kc4ZmaY5iiEOg0yXs32AvEhfIlvb7BJjUUl_uWzEdo/s1600/date.JPG" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Now, that's simple we just use the Date/Time and got the Calendar. So, life's simple :) not for developers you know.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">One of the requirements we usually get is to restrict the calendar, what we mean by this - user should be allowed to select only selected dates for which we got the data.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">How to achieve that? Till now I haven't came across perfect solution for that.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">Note: </span>Though you can assign a default value to a Date parameter, you can't do that for available values.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Available Values section should be assigned - "No Default value", otherwise you will lose the Calendar.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Default values can be anything like</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">= Today = Now =DateAdd("d",-1,Today)</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">So, how to achieve the restriction of Dates?</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">We can use Custom code for that. Custom code will be a function that will validate the dates selected by the user.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlzoU6xkO1o_QNxVSSlMuIhpYVXXaoycvKKxqXFOUXqsAeP81me7tTu1orkAWfHlKsICYtOPrPjj_dqpdANFlMCMnNEthElLuk7GIxDUHZNQ2yezEMwAClitF1XxyPoP9TundjiHTsss4/s1600/date2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlzoU6xkO1o_QNxVSSlMuIhpYVXXaoycvKKxqXFOUXqsAeP81me7tTu1orkAWfHlKsICYtOPrPjj_dqpdANFlMCMnNEthElLuk7GIxDUHZNQ2yezEMwAClitF1XxyPoP9TundjiHTsss4/s1600/date2.JPG" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<u><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Code</span></u></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">Function CheckDateParameters(d1 as Date, d2 as Date)</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">Dim msg as String</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">msg = ""</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">If (d1 <DateAdd("d",-200,Today ) ) Then </span><span class="Apple-style-span" style="color: #38761d;">'If From Date is less than 200 days</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">msg="Please select 'From Date' again, it should not be before than " </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">& DateAdd("d", -200, Today ) & "." & vbCrLf & msg</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">End If</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">If (d2 >DateAdd("d",0,Today ) ) Then </span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #38761d;">'If To Date is greater than Current date</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">msg="Please select 'To Date' again, it should not be later than " </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">& DateAdd("d", 0, Today ) & "." & vbCrLf & msg</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">End If</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">If (d1 > d2 ) Then </span><span class="Apple-style-span" style="color: #38761d;">'If From Date selected is greater than To Date</span></span></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">msg="From Date should not be greater than To Date" & vbCrLf & msg</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">End If</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">Return msg</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">End Function</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Create a textbox in your report and use the expression:</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="color: #cc0000; font-family: Verdana, sans-serif;">=Code.CheckDateParameters(Parameters!FromDate.Value,Parameters!ToDate.Value)</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">So, whenever the conditions mentioned in your Code breached by the user a message with show up in your report.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2qJvq-i3I4Oendpm3jwdVbKYXqoa0sO9qWQU5mMylhGsNKzobPGP18bOu4ZxotcltuqpVlztuo4cSfs4dyKKIoZkW8q_jEk8qvbrj9r2HPwNjbqIfaeTzi6mw287OuiEAmFQDv7TMpRA/s1600/date4.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="52" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2qJvq-i3I4Oendpm3jwdVbKYXqoa0sO9qWQU5mMylhGsNKzobPGP18bOu4ZxotcltuqpVlztuo4cSfs4dyKKIoZkW8q_jEk8qvbrj9r2HPwNjbqIfaeTzi6mw287OuiEAmFQDv7TMpRA/s400/date4.JPG" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
</div>
мαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.com7tag:blogger.com,1999:blog-5071174820127905268.post-70985449700598856202011-09-06T21:13:00.000+05:302011-09-11T18:07:01.627+05:30Ranking our Reports<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: Verdana,sans-serif; text-align: justify;">
One of the interesting requirement both as a user and developer, is to have rankings worked out in your report.Everyone either in school exams, sports etc. get really excited to know how they fare and what's their rank.</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif; text-align: justify;">
Likewise, as we are human to have that nature instilled as an employee too, we want to know how we perform in our organization.</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
In this post, we will see how you can create a report ranking for your employees too.</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif; text-align: justify;">
Though, we got a function in MDX called Rank(), its not that easy to get it done.</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<pre style="font-family: Verdana,sans-serif;">General syntax for Rank,</pre>
<pre style="font-family: Verdana,sans-serif;"> </pre>
<pre style="color: #cc0000; font-family: Verdana,sans-serif;"><span style="font-size: small;">Rank(Tuple_Expression, Set_Expression [ ,Numeric Expression ] )</span></pre>
<pre style="font-family: Verdana,sans-serif;"> </pre>
<pre style="font-family: Verdana,sans-serif;">Some theory of this function,</pre>
<div style="text-align: justify;">
<div style="text-align: justify;">
<pre style="font-family: Verdana,sans-serif;"><span class="input">- <span class="Apple-style-span" style="color: #cc0000;">Rank</span></span><span class="Apple-style-span" style="color: #cc0000;"> function</span> determines the one-based rank for the specified tuple</pre>
<pre style="font-family: Verdana,sans-serif;">by evaluating the specified numeric expression against the tuple. If a </pre>
<pre style="font-family: Verdana,sans-serif;">numeric expression is specified, the <span class="input">Rank</span> function assigns the same </pre>
<pre style="font-family: Verdana,sans-serif;">rank to tuples with duplicate values in the set. This assignment of the </pre>
<pre style="font-family: Verdana,sans-serif;">same rank to duplicate values affects the ranks of subsequent tuples </pre>
<pre style="font-family: Verdana,sans-serif;">in the set. </pre>
<pre style="font-family: Verdana,sans-serif;"> </pre>
<pre style="font-family: Verdana,sans-serif;"> </pre>
</div>
</div>
<pre style="font-family: Verdana,sans-serif;"><span style="color: #cc0000;">Note:</span> I will take an example from Adventure Works, to illustrate the </pre>
<pre style="font-family: Verdana,sans-serif;">same. </pre>
<div style="text-align: justify;">
<pre style="font-family: Verdana,sans-serif;">We have a Customer dimension containing all the customers and </pre>
<pre style="font-family: Verdana,sans-serif;">Internet Order Count values.</pre>
<pre style="font-family: Verdana,sans-serif;">Now, how can we assign a rank based on the Internet Order count </pre>
<pre style="font-family: Verdana,sans-serif;">values to each of our customers. We will see that in a moment,</pre>
</div>
<pre style="font-family: Verdana,sans-serif;"> </pre>
<pre style="font-family: Verdana,sans-serif;"></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGG95A3MxX0VOuB53ibdksHtgiAhZcBYxFK94ysMmJUhXo-O3a8D6_VjAdb8Mj84a9hG_glGIhnyKWqhIf8eZec4Cvy6Ja7qh1w3N-aM1osdo_J7qX3spj4e0JS2q4-G1dmBBDM9WjeFs/s1600/rank1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGG95A3MxX0VOuB53ibdksHtgiAhZcBYxFK94ysMmJUhXo-O3a8D6_VjAdb8Mj84a9hG_glGIhnyKWqhIf8eZec4Cvy6Ja7qh1w3N-aM1osdo_J7qX3spj4e0JS2q4-G1dmBBDM9WjeFs/s1600/rank1.JPG" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<pre style="font-family: Verdana,sans-serif;"></pre>
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
Steps:</div>
<div style="font-family: Verdana,sans-serif;">
</div>
<ul style="text-align: left;">
<li style="text-align: justify;"> <span style="font-family: Verdana, sans-serif;">First of all, we will create an Ordered Set, that will order the </span><span style="font-family: Verdana, sans-serif;">customers based on the measure, in this case, Internet Order Count. Though, </span><span style="font-family: Verdana, sans-serif;">an ordered set</span><span style="font-family: Verdana, sans-serif;"> is not required and a simple set will work too.</span></li>
</ul>
<ul style="text-align: left;">
<li style="text-align: justify;"><span style="font-family: Verdana, sans-serif;">Then, we will use our rank function on this set.</span></li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3pEvANe17RbYXkgA_1pYbYonKRx_CXSTeCGzQABmAV38q6iQrH0N-XAjcWWn-E_Am-aOYSKX2teymRL1mnAh-XpbeLE5NgCS4AlraQugnG9HEhkWc8r8Di98OIymHXRIkHrxj23wgQRA/s1600/rank3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3pEvANe17RbYXkgA_1pYbYonKRx_CXSTeCGzQABmAV38q6iQrH0N-XAjcWWn-E_Am-aOYSKX2teymRL1mnAh-XpbeLE5NgCS4AlraQugnG9HEhkWc8r8Di98OIymHXRIkHrxj23wgQRA/s1600/rank3.JPG" /></a></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4Kjv7e0R25pvPDNtNYL7s1O5-Gyj7kq5na3UXdgJ83mfOY4v5jA2oEsMn6SzOqo5bxjkAKGpICe2VQIgvoQHW5Mf2VK10BjX0o02Ypzuvc5_wvQoPZTXtF0noAQJWCl9qxWCMUCTXmEM/s1600/rank2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><br />
<br />
<div style="font-family: Verdana,sans-serif;">
<br />
Here it is, we have ranked our customers based on the order count.</div>
<div style="font-family: Verdana,sans-serif;">
<br />
<br /></div>
</div>
мαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.com0tag:blogger.com,1999:blog-5071174820127905268.post-44113080666937652912011-09-02T14:05:00.001+05:302011-09-03T17:04:47.006+05:30Multiple Cube vs Single Cube Approach<div dir="ltr" style="text-align: left;" trbidi="on"><div class="separator" style="clear: both; text-align: justify;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Shifting my focus to SSAS, so here is the post on whether to go for Multiple cube or Single cube.Well, if you ask me what's my opinion then I will say wait for the post to complete. </span></div><div style="text-align: justify;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">First of all, let me put all the relevant information and then we will be in a good position to have a call on this.</span></div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Let me start briefing about the general terms that I will be using in this post like Database, Cube, Measures, Dimensions etc.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">Database</span> is the collection of data, in SSAS it can be collection of cube data.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">Cube</span> also termed as <span class="Apple-style-span" style="color: #cc0000;">OLAP cube</span>, is a data structure that helps you analyze & visualize your data in multiple dimensional way. It is set of related measures (or facts) and dimensions.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">Measures</span> are the numeric facts (or data).</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><span class="Apple-style-span" style="color: #cc0000;">Dimensions</span> are the attributes, for which Measures are categorized.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">So, what exactly is Single/Multiple cubes?</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">If your database contain only one cube, then you have a single cube wherein all the users refer to the same cube.</span><br />
<div class="separator" style="clear: both; text-align: center;"></div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">And, if your database contains more than one cube, each cube for specific users then its a part of multiple cube.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPQDkUhWJ-al6W3kw2jwN7RfMZg1_Brt1teiXo0nvgtN11Wpus1_OttONcUKAJVtK2wa8oqcqXyEmuk8X5EJKtdsjeyE2QcZzjAbVcs7wYJ_kMzNbYqTrFnrLyUqk6N_cdIQ0aEYZPJg0/s1600/Microsoft+analysis+services+cube1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPQDkUhWJ-al6W3kw2jwN7RfMZg1_Brt1teiXo0nvgtN11Wpus1_OttONcUKAJVtK2wa8oqcqXyEmuk8X5EJKtdsjeyE2QcZzjAbVcs7wYJ_kMzNbYqTrFnrLyUqk6N_cdIQ0aEYZPJg0/s1600/Microsoft+analysis+services+cube1.JPG" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiz0PTWETUIdZR_sYU1VlICq1rB5GAItKa_Zv-05I1qxJrhu_rewj_Z-SgO1Nrga7vgS5CqKBgIht63DTboZXybc-Rjkfn10CNxYOO9BqSx52sffeEl7rsG74qUelx3xPTnLlWMLkh8s74/s1600/multiple+cube.JPG" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiz0PTWETUIdZR_sYU1VlICq1rB5GAItKa_Zv-05I1qxJrhu_rewj_Z-SgO1Nrga7vgS5CqKBgIht63DTboZXybc-Rjkfn10CNxYOO9BqSx52sffeEl7rsG74qUelx3xPTnLlWMLkh8s74/s1600/multiple+cube.JPG" /></a><br />
<br />
<br />
<span class="Apple-style-span" style="color: #444444; font-family: Verdana, sans-serif;"><u>Single Cube Approach - Pros & Cons</u></span><br />
<ul style="text-align: left;"><li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Users can easily fetch data from multiple measure groups even if they not related. Performance won't be degraded in cross quering measure groups.</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Security Management is tough here, for example, we want certain users to have the access to certain measure groups and not all. So, restrictions won't be easy.</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Less user-friendly, as every measure is present in a single location so navigating through long list of measures is a tough ask. </span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Complexity is bound to increase with everything loaded into one.</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Change Management, any change made, how small it may be will result in whole cube structure change, that may effect users who are not associated with that change.</span></li>
</ul><span class="Apple-style-span" style="color: #444444;"><u><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span></u></span><br />
<span class="Apple-style-span" style="color: #444444;"><u><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Multiple Cube Approach - Pros & Cons</span></u></span><br />
<br />
<ul style="text-align: left;"><li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Performance can be a worry if user performs cross-querying based on multiple cubes.</span></li>
</ul><div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Just to elaborate more on that, MDX provides a function to cross-querying 2 cubes using <b>LookUpCube()</b> function.</span></div><div><ul style="text-align: left;"><li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Security Management is relatively easy, as you have cube for each specific usage (only certain users will be there). So, we can apply security restrictions on each cube.</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">User-friendly, limited number of measures groups and that too related to each other.</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Less complex structure.</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Change Management is relatively simpler, changes will be applied to specific cube so others users won't be affected by that.</span></li>
</ul><div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span></div></div><div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">These were some of the differences that can help you plan your approach according to your requirement.</span></div><div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Now, my call on this will be:</span></div><div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Initially, go for the One cube approach,add unrelated dimensions and measures to a certain extent.</span></div><div><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">But, once you get the feeling that beyond this point, adding more things can create confusion and complexity, immediately move to Multiple cube approach.</span></div><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiz0PTWETUIdZR_sYU1VlICq1rB5GAItKa_Zv-05I1qxJrhu_rewj_Z-SgO1Nrga7vgS5CqKBgIht63DTboZXybc-Rjkfn10CNxYOO9BqSx52sffeEl7rsG74qUelx3xPTnLlWMLkh8s74/s1600/multiple+cube.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br />
</a></div>мαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.com1tag:blogger.com,1999:blog-5071174820127905268.post-1826110128999043042011-09-01T12:45:00.000+05:302011-09-01T15:23:25.669+05:30Common functions used in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">While working on a Technology/Product, there always are some common functions/methods that we come across. Though, we don't understand the importance of those small piece of codes but it can help a lot of other guys working on.<br />
I have also seen a lot of forum queries with such type questions, so I will post some of those functions here.<br />
<br />
<br />
<div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Data of Multiple-select parameters in a text-box.</u></span></li>
</ul>We all make use of multi-value parameters in our report but how to show all the selected values in a textbox.<br />
<div style="text-align: left;">Let us assume, we have a Country parameter named as Parameters!Country.value. Parameters!Country.value(0) gives you first selected value, but to show all use Join()</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=Join(Parameters!Country.value, "," )</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYe-byqpo235DtSoxjrQL04txwDVxgJDv5ndBaBcz6kgTN31VX40Skjrzty5J2cfa9jh-A3HEjJd7xL9p6A4fDjDRSZ-xCxPwUlA_kUNh94-l58Y7EkOcTH2ifeZ_QNjCfNDAz-dAyIMo/s1600/ssrs.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYe-byqpo235DtSoxjrQL04txwDVxgJDv5ndBaBcz6kgTN31VX40Skjrzty5J2cfa9jh-A3HEjJd7xL9p6A4fDjDRSZ-xCxPwUlA_kUNh94-l58Y7EkOcTH2ifeZ_QNjCfNDAz-dAyIMo/s1600/ssrs.JPG" /></a></div><br />
<div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"> </span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;"><br />
</span><br />
<span class="Apple-style-span" style="color: #444444;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="font-family: Verdana,sans-serif;"><u><span class="Apple-style-span" style="color: #444444;">Splitting a string and picking specific </span><span class="Apple-style-span" style="color: #444444;">sub-string</span></u></span></li>
</ul><span class="Apple-style-span" style="color: #444444;">Let us assume we have a directory path of a file present in C:\SSRS folder named "File1.txt". We want to retrieve only the filename, in this case we can use Choose() with Split() .</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="color: #444444;">Here, Fields!dirpath.value contains the value "</span>C:\SSRS\File1.txt"</div><div style="text-align: left;"><br />
</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">= Choose( 3, Split( Fields!dirpath.value ,"\"))</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Newline inside a text-box</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">We have a requirement to show following in a single </span>text-box<span class="Apple-style-span" style="font-family: inherit;">,</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">Total</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">-------</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">1000</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">Don't worry, use special characters "</span>chr(10)" or "vbcrlf" for that.</div><div style="text-align: left;"><br />
</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">= "Total" & chr(10) & "---------" & chr(10) & "1000"</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Date-time Manipulation</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">If I ask you, please give me a date that is10days from Today either ways.</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">What kind of question is this, go search the calendar... :)</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">Well, these kind of requirements constitutes your daily reports. So, SSRS gives us a function for that </span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">DateAdd() and DateDiff()</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">I generally use DateAdd() only for both adding and subtracting.</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">= DateAdd(DateInterval.Day , -2, Today) </span><br />
<span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">= DateAdd(DateInterval.Day , 2, Today)</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444; font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">Here,</span><br />
<span class="Apple-style-span" style="color: #cc0000; font-family: inherit;"><b>DateInterval.Day</b></span><span class="Apple-style-span" style="color: #444444; font-family: inherit;"> - Manipulation for days</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: inherit;"><b>-2</b> </span><span class="Apple-style-span" style="color: #444444; font-family: inherit;">- Subtract 2 days (depends on the interval selected. Here, Day is selected) </span><br />
<span class="Apple-style-span" style="color: #cc0000; font-family: inherit;"><b>2</b></span><span class="Apple-style-span" style="color: #444444; font-family: inherit;"><b> </b>- </span>Adding 2 days <span class="Apple-style-span" style="color: #444444; font-family: inherit;">(depends on the interval selected) </span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: inherit;"><b>Today</b></span><span class="Apple-style-span" style="color: #444444; font-family: inherit;"> - Base value for adding or subtracting</span><br />
<span class="Apple-style-span" style="color: #444444; font-family: inherit;"><br />
</span></div><div style="text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg04u1Ebj_j4yAkB1uGoYEWXylPy_nrE-x-uK4z_ttMciaPB6WyijJ3Trk5UbxWid3tivpHeu0bji3WFKN-_5eRaqL9URfp0QVJptcXQjA5W2vJwSYVWilhiwmJkNppwg7RXjkYf9rVftg/s1600/ssrs1.JPG" imageanchor="1" style="clear: left; display: inline ! important; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg04u1Ebj_j4yAkB1uGoYEWXylPy_nrE-x-uK4z_ttMciaPB6WyijJ3Trk5UbxWid3tivpHeu0bji3WFKN-_5eRaqL9URfp0QVJptcXQjA5W2vJwSYVWilhiwmJkNppwg7RXjkYf9rVftg/s1600/ssrs1.JPG" /></a></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444; font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444; font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #444444; font-family: inherit;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Lookup()</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">I must say, Lookup() is one of the great addition to SSRS 2008 R2 kitty.</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">It lets you retrieve a field value using a join on two datasets. So, this way you are performing joins not in the query but inside your report.</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">Assume, you have 2 datasets: DataSet1 and DataSet2</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">DataSet1 got EmpId, Name, DOB, Dept and comes from Employee table</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">DataSet2 got EmpId, Balances, Salary and comes from HR table</span></div><div style="text-align: left;"><br />
</div><div style="text-align: left;">Now, you require to show Balances for each employee in a single tablix along with Employee details and you know both of them are in different datasets.Use lookup,</div><div style="text-align: left;"><br />
</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=Lookup(Fields!EmpId.Value, Fields!EmpId.Value, Fields!Balances.Value, "DataSet2")</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Searching within a string</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">To check whether your string contains any specific lettters or words, InStr() comes handy</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=InStr(1, Fields!Stringval.value, "me")</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">returns a non null value if search string is present and basically returns the position of that.</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Formatting integers, date-time</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">SSRS gives a function, Format(), to format the values according to your need.</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=Format(Fields!Calls.value , "formatstring")</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: inherit;">formatstring : </span><span class="Apple-style-span" style="font-family: inherit;">Can be "#,##0", "$#,##0.00", "MM/dd/yyyy", "yyy-MM-dd HH:mm:ss" etc.</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Getting the row number</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">To find out the row number of any record inside a Tablix, Matrix control, use RowNumber().</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;">It can be very helpful in assign alternate row background color to any tablix.</div><div style="text-align: left;"><br />
</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=IIF( RowNumber(Nothing) mod 2 = 0 , "Lightgrey" ,"white" )</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Month name, Week Name in different format</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">You want month name in full or abbreviated from. Use,</span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=MonthName(1)</span><span class="Apple-style-span" style="font-family: inherit;"> will give January.</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=MonthName(1,true)</span> will give Jan.</div><div style="text-align: left;"><br />
</div><div style="text-align: left;">Similarly, for week name</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=WeekDayName(1) </span>will give Sunday.</div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=WeekDayName(1,true)</span> will give Sun.</div><div style="text-align: left;">Also, Sunday got value 1 and Saturday as 7 by default</div><div style="text-align: left;"><br />
</div><div style="text-align: left;"><br />
</div><div style="text-align: left;"></div><ul style="text-align: left;"><li><span class="Apple-style-span" style="color: #444444; font-family: Verdana,sans-serif;"><u>Replace-with in SSRS</u></span></li>
</ul><span class="Apple-style-span" style="font-family: inherit;">You can replace specific occurrence of string with another one. </span><br />
<div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">And the function is Replace(string, find, replacewith)</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;">=Replace( Fields!Categ.value , "/", ",") </span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="color: #cc0000; font-family: Arial,Helvetica,sans-serif;"><br />
</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">These were some of the functions that I feel comes handy in our day-to-day reporting activities.</span></div><div style="text-align: left;"><span class="Apple-style-span" style="font-family: inherit;">Though, the list is not over but needs to be ended here.</span></div></div>мαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.com0tag:blogger.com,1999:blog-5071174820127905268.post-78605730267621301912011-08-30T19:45:00.000+05:302011-09-13T12:50:57.344+05:30Using Javascript in SSRS<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">I will start my first post with javascript injection (there is a personal reason for that).</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Well, I always wanted to use scripting in SSRS reports to add the so-called "web flavour" in my reports.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Though, SSRS don't gives you a developer-friendly interface but still its managaeble.And, also let me allow to clear the air that javascript you will use in your reports, works only while you navigate from one report to another.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">So, some restrictions are there but I hope future versions will give you more programming edge.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Till that time lets focus on what we have.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">In this post, you will be able to see creating:</span><br />
<ul style="text-align: left;">
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Alerts</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">User Prompt</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Confirmation Box</span></li>
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">And widely used, Opening new window</span></li>
</ul>
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Lets start the basic steps you need to follow,</span><br />
<ol style="text-align: left;">
<li><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Create a textbox where you want to inject the javascript code.</span></li>
</ol>
<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZK_WablU-WZibY50eKvvQwaK1LbgpdLZEYxJQzI45eYEG1V6GxAVS2hXZ-pSZrwG-VYLoJsC6ApI4DKGj43_Yg8OZuRu_WdNnwi5t50hS1nHV8CFEPNX8mED-3qLOq3Qg8prx0GT8uGs/s1600/js1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="391" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZK_WablU-WZibY50eKvvQwaK1LbgpdLZEYxJQzI45eYEG1V6GxAVS2hXZ-pSZrwG-VYLoJsC6ApI4DKGj43_Yg8OZuRu_WdNnwi5t50hS1nHV8CFEPNX8mED-3qLOq3Qg8prx0GT8uGs/s400/js1.JPG" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"> 2. Add the javascript code in the Go To URL section.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"> 3. Deploy your report to a web interface (Report Manager, SharePoint etc.)</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"> 4. Test your link, you will like it.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Here, are the javascript codes that can be used:</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<div style="color: black;">
<span style="font-family: Verdana, sans-serif; font-size: small;"><u>Alert</u></span></div>
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span><br />
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif; line-height: 115%;">=<span style="color: #a31515;">"javascript:( alert('Hi Guys') )"</span></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="color: black;">
<span style="font-family: Verdana, sans-serif; font-size: small; line-height: 115%;"><u>User Prompt</u></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<ul style="text-align: left;">
<li><span style="line-height: 115%;"><span style="color: #a31515; font-family: Verdana, sans-serif;"><span style="color: black;">Based, on the </span><span style="color: black;">user input you can open a new web page.</span><u style="font-size: 10pt;"><span style="color: black;"><br />
</span></u></span></span></li>
</ul>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif; line-height: 115%;">=<span style="color: #a31515;">"javascript:var name=prompt('Which page do you want to navigate?'); if(name=='google') {void window.open('http://www.google.com',800,800,'_blank')} else {void window.open('http://www.msn.com',800,800,'_blank')}"</span></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="color: black;">
<span style="font-family: Verdana, sans-serif; font-size: small; line-height: 115%;"><u>Confirmation Box</u></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif; line-height: 115%;">=<span style="color: #a31515;">"javascript:var check=confirm('Do you want to navigate to New report?'); if (check == true) {window.open('http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fReport1','_self')}"</span></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="color: black;">
<span style="font-family: Verdana, sans-serif; font-size: small;"><u>Opening new window</u></span></div>
<div class="MsoNormal">
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br /></span></div>
<ul style="text-align: left;">
<li><span style="line-height: 115%;"><span style="color: #a31515; font-family: Verdana, sans-serif;"><span style="color: black;">When you want to open SSRS report in new window with set of parameters to be passed, you can this piece of code</span><u style="font-size: 10pt;"><span style="color: black;"></span></u></span></span></li>
</ul>
<div class="MsoNormal" style="color: #990000;">
<span style="font-family: Verdana, sans-serif; font-size: small;">="javascript:void (window.open('http://localhost/ReportServer/Pages/ReportViewer.aspx?/Folder/Reportname&rs:Command=Render&Param1="& Parameters!Param1.Value & "&Param2=" & Parameters!Param2.Value & "','_blank'))"</span><br />
<div style="font-family: 'Courier New', Courier, monospace;">
<br />
<br /></div>
</div>
<div class="MsoNormal">
<span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"><span style="color: #a31515;"><u><span style="color: black; font-family: Arial, Helvetica, sans-serif;"> </span></u></span></span></div>
</div>
<div>
</div>
<div style="color: black;">
<span style="font-family: Verdana, sans-serif; line-height: 115%;">Well, these were some basic javascript codes that are used widely.</span></div>
<div style="color: black;">
<span style="font-family: Verdana, sans-serif; line-height: 115%;">I will add more to it in my future posts. </span></div>
<div style="color: black;">
</div>
<div style="color: black;">
<span style="font-family: Verdana, sans-serif; line-height: 115%;">Till then, enjoy blogging!</span> </div>
</div>
мαησנ_α∂нιкαяιhttp://www.blogger.com/profile/09506808804338755544noreply@blogger.com1