September 5, 2008
Subscription is Disabled When User!UserID Global Variable is Used in SSRS Report
I recently had a problem where I was unable to create a data-driven subscription for a SQL Server Reporting Services report. I opened SQL Server Management Studio, connected to Reporting Services, navigated to a report, then right clicked on it's Subscriptions node and both the New Subscription and New Data-Driven Subscription menu options were grayed out.
The report in question had five parameters that the users could enter at runtime. I wanted to implement the data-driven subscription to execute the report with all possible combinations of the parameters in order to get every possible version of the report to be cached. Since the data for the report was only refreshed once a week, this seemed like a good idea.
I eventually found the answer in Books On Line. Subscriptions are disabled when you use the User!UserID global variable. The User!UserID global variable contains the domain\username of the user running the report. In my case I used the User!UserID global variable to filter the various dropdown lists for parameters so users could only run the report for specific regions and/or offices that they are allowed to see.
Share:
Posted by Steve Mann at 7:39 PM | Comments (0)
Subscription is Disabled When User!UserID Global Variable is Used in SSRS Report
I recently had a problem where I was unable to create a data-driven subscription for a SQL Server Reporting Services report. I opened SQL Server Management Studio, connected to Reporting Services, navigated to a report, then right clicked on it's Subscriptions node and both the New Subscription and New Data-Driven Subscription menu options were grayed out.
The report in question had five parameters that the users could enter at runtime. I wanted to implement the data-driven subscription to execute the report with all possible combinations of the parameters in order to get every possible version of the report to be cached. Since the data for the report was only refreshed once a week, this seemed like a good idea.
I eventually found the answer in Books On Line. Subscriptions are disabled when you use the User!UserID global variable. The User!UserID global variable contains the domain\username of the user running the report. In my case I used the User!UserID global variable to filter the various dropdown lists for parameters so users could only run the report for specific regions and/or offices that they are allowed to see.
Share:
Posted by Steve Mann at 7:39 PM | Comments (0)
I recently had a problem where I was unable to create a data-driven subscription for a SQL Server Reporting Services report. I opened SQL Server Management Studio, connected to Reporting Services, navigated to a report, then right clicked on it's Subscriptions node and both the New Subscription and New Data-Driven Subscription menu options were grayed out.
The report in question had five parameters that the users could enter at runtime. I wanted to implement the data-driven subscription to execute the report with all possible combinations of the parameters in order to get every possible version of the report to be cached. Since the data for the report was only refreshed once a week, this seemed like a good idea.
I eventually found the answer in Books On Line. Subscriptions are disabled when you use the User!UserID global variable. The User!UserID global variable contains the domain\username of the user running the report. In my case I used the User!UserID global variable to filter the various dropdown lists for parameters so users could only run the report for specific regions and/or offices that they are allowed to see.
Share:
Posted by Steve Mann at 7:39 PM | Comments (0)
Using data label expressions to create SSRS 2005 Pie Charts with intuitive and helpful pie slice
by Joe Toscano, Senior Software Engineer, RDA Corporation
If you#8217;ve created PIE charts in SQL Server Reporting Services you#8217;ve noticed that you are able to drag and drop category, series and data fields. The setup is pretty straightforward, but by working a bit with the data label values you are able to display more than a single column value in each slice of the pie. This may be helpful in allowing your reports more contain more useful information.
In our example we are looking at the total sales for bike products from the AdventureWorks database. We are looking at 3 categories: Touring Bikes, Road Bikes and Mountain Bikes. Each bike category has both a Total Sales and a Percentage of Total Sales that is part of the reports data set. Most pie chart reports display a single value present in each slice of the pie; however, in our report we are displaying both values. Below is a snapshot of this sample report:
How did we improve our pie slice labels?
This blog entry focuses on how we are able to display both the total sales and the percentage of total in each pie slice. The answer lies in modifying the data label for the Total Sales column that we dropped to the Data Field area while in Report Layout / Design Mode.
The screen snapshot below was taken from the Report Design / Report Layout mode. Notice that we drug the LineItemTotal column to the Data Field area. Once there, we right-clicked on the data label and chose properties.
Let#8217;s focus on the Point Labels tab. Notice the Data Label dropdown and the little #8220;Fx#8221; to its right. This tells us that this value can be much more than a simple column in your dataset. The Data Label value can actually be the results of an expression! In our case, we used the several built-in functions to convert data types of more than a single column, strip out trailing spaces and concatenate formatted strings. Below is the expression that was used in our Data label:
cstr(left(Fields!LineItemTotal.Value / 1000000, 5)) amp; vbcrlf amp; ( amp; cstr(left(Fields!PercentageOfTotal.Value,5)) amp; %)
In summary, by working with the Data Label expressions, your pie slices can contain much more information that a single column value. The only limit is really the size of each pie slice, but as was done with our report you are able to specify that the slice values be placed on the outside of the pie.
Share:
Posted by Steve Mann at 3:35 PM | Comments (0)
Using data label expressions to create SSRS 2005 Pie Charts with intuitive and helpful pie slice
by Joe Toscano, Senior Software Engineer, RDA Corporation
If you#8217;ve created PIE charts in SQL Server Reporting Services you#8217;ve noticed that you are able to drag and drop category, series and data fields. The setup is pretty straightforward, but by working a bit with the data label values you are able to display more than a single column value in each slice of the pie. This may be helpful in allowing your reports more contain more useful information.
In our example we are looking at the total sales for bike products from the AdventureWorks database. We are looking at 3 categories: Touring Bikes, Road Bikes and Mountain Bikes. Each bike category has both a Total Sales and a Percentage of Total Sales that is part of the reports data set. Most pie chart reports display a single value present in each slice of the pie; however, in our report we are displaying both values. Below is a snapshot of this sample report:
How did we improve our pie slice labels?
This blog entry focuses on how we are able to display both the total sales and the percentage of total in each pie slice. The answer lies in modifying the data label for the Total Sales column that we dropped to the Data Field area while in Report Layout / Design Mode.
The screen snapshot below was taken from the Report Design / Report Layout mode. Notice that we drug the LineItemTotal column to the Data Field area. Once there, we right-clicked on the data label and chose properties.
Let#8217;s focus on the Point Labels tab. Notice the Data Label dropdown and the little #8220;Fx#8221; to its right. This tells us that this value can be much more than a simple column in your dataset. The Data Label value can actually be the results of an expression! In our case, we used the several built-in functions to convert data types of more than a single column, strip out trailing spaces and concatenate formatted strings. Below is the expression that was used in our Data label:
cstr(left(Fields!LineItemTotal.Value / 1000000, 5)) amp; vbcrlf amp; ( amp; cstr(left(Fields!PercentageOfTotal.Value,5)) amp; %)
In summary, by working with the Data Label expressions, your pie slices can contain much more information that a single column value. The only limit is really the size of each pie slice, but as was done with our report you are able to specify that the slice values be placed on the outside of the pie.
Share:
Posted by Steve Mann at 3:35 PM | Comments (0)
quot
by Joe Toscano, Senior Software Engineer, RDA Corporation
If you#8217;ve created PIE charts in SQL Server Reporting Services you#8217;ve noticed that you are able to drag and drop category, series and data fields. The setup is pretty straightforward, but by working a bit with the data label values you are able to display more than a single column value in each slice of the pie. This may be helpful in allowing your reports more contain more useful information.
In our example we are looking at the total sales for bike products from the AdventureWorks database. We are looking at 3 categories: Touring Bikes, Road Bikes and Mountain Bikes. Each bike category has both a Total Sales and a Percentage of Total Sales that is part of the reports data set. Most pie chart reports display a single value present in each slice of the pie; however, in our report we are displaying both values. Below is a snapshot of this sample report:
How did we improve our pie slice labels?
This blog entry focuses on how we are able to display both the total sales and the percentage of total in each pie slice. The answer lies in modifying the data label for the Total Sales column that we dropped to the Data Field area while in Report Layout / Design Mode.
The screen snapshot below was taken from the Report Design / Report Layout mode. Notice that we drug the LineItemTotal column to the Data Field area. Once there, we right-clicked on the data label and chose properties.
Let#8217;s focus on the Point Labels tab. Notice the Data Label dropdown and the little #8220;Fx#8221; to its right. This tells us that this value can be much more than a simple column in your dataset. The Data Label value can actually be the results of an expression! In our case, we used the several built-in functions to convert data types of more than a single column, strip out trailing spaces and concatenate formatted strings. Below is the expression that was used in our Data label:
cstr(left(Fields!LineItemTotal.Value / 1000000, 5)) amp; vbcrlf amp; ( amp; cstr(left(Fields!PercentageOfTotal.Value,5)) amp; %)
In summary, by working with the Data Label expressions, your pie slices can contain much more information that a single column value. The only limit is really the size of each pie slice, but as was done with our report you are able to specify that the slice values be placed on the outside of the pie.
Share:
Posted by Steve Mann at 3:35 PM | Comments (0)
Tips to get optimum performance from large dataset manipulation
Here is a non exhaustive list of ways to get the best performance from large dataset DML.Depending on system configuration and design, the effect of these recommendations may vary.
1-Do not create more than 16 exact match joins.
2- make sure indexes are created on exact match columns only if necessary for reporting
3- Avoid join on large views use temporary tables instead; Proceed by loading data from the view into a temp table then use the temp table in the join instead.
4- Avoid as much as possible creating indexes on temporary tables as it will impact insert performance
5- Avoid grouping as much as possible, if necessary make sure that the non- aggregated columns actually regroup data.
Most of the times as developement advances more columns are added onto the query. At some point the columns in the 'group by' clause may become candidate key to the dataset causing grouping to be ineffective; on a large query the effect on performance can be very important.
--The classics
6- Avoid table scans
7- Avoid nested Sql queries; join are more effective.
8- Avoid cursors. An effective use of Case statement can replace the use of cursors and deliver much better performance.
This sample query is for illustration only:
SELECT VENDORID, PRODUCTID,
SUM(CASE WHEN MONTH(SALESDATE)=1
THEN SUM(ISNULL(SALESAMOUNT,0))
ELSE 0 END) AS JAN_SALES,
SUM(CASE WHEN MONTH(SALESDATE)=2
THEN SUM(ISNULL(SALESAMOUNT,0))
ELSE 0 END) AS FEB_SALES,
SUM(CASE WHEN MONTH(SALESDATE)=3
THEN SUM(ISNULL(SALESAMOUNT,0))
ELSE 0 END) AS MAR_SALES
--...
FROM SALES S (NOLOCK)
JOIN PRODUCT P (NOLOCK) ON P.PRODUCTID=S.PRODUCTID
JOIN VENDOR V (NOLOCK) ON V.VENDORID=P.VENDORID
WHERE SALESDATE BETWEEN @PERIODSTART AND @PERIODEND
GROUP BY V.VENDORID,P.PRODUCTID
Share:
Posted by Steve Mann at 12:49 PM | Comments (0)
Tips to get optimum performance from large dataset manipulation
Here is a non exhaustive list of ways to get the best performance from large dataset DML.Depending on system configuration and design, the effect of these recommendations may vary.
1-Do not create more than 16 exact match joins.
2- make sure indexes are created on exact match columns only if necessary for reporting
3- Avoid join on large views use temporary tables instead; Proceed by loading data from the view into a temp table then use the temp table in the join instead.
4- Avoid as much as possible creating indexes on temporary tables as it will impact insert performance
5- Avoid grouping as much as possible, if necessary make sure that the non- aggregated columns actually regroup data.
Most of the times as developement advances more columns are added onto the query. At some point the columns in the 'group by' clause may become candidate key to the dataset causing grouping to be ineffective; on a large query the effect on performance can be very important.
--The classics
6- Avoid table scans
7- Avoid nested Sql queries; join are more effective.
8- Avoid cursors. An effective use of Case statement can replace the use of cursors and deliver much better performance.
This sample query is for illustration only:
SELECT VENDORID, PRODUCTID,
SUM(CASE WHEN MONTH(SALESDATE)=1
THEN SUM(ISNULL(SALESAMOUNT,0))
ELSE 0 END) AS JAN_SALES,
SUM(CASE WHEN MONTH(SALESDATE)=2
THEN SUM(ISNULL(SALESAMOUNT,0))
ELSE 0 END) AS FEB_SALES,
SUM(CASE WHEN MONTH(SALESDATE)=3
THEN SUM(ISNULL(SALESAMOUNT,0))
ELSE 0 END) AS MAR_SALES
--...
FROM SALES S (NOLOCK)
JOIN PRODUCT P (NOLOCK) ON P.PRODUCTID=S.PRODUCTID
JOIN VENDOR V (NOLOCK) ON V.VENDORID=P.VENDORID
WHERE SALESDATE BETWEEN @PERIODSTART AND @PERIODEND
GROUP BY V.VENDORID,P.PRODUCTID
Share:
Posted by Steve Mann at 12:49 PM | Comments (0)
