If you could please help, I would like to know if we can add a vertical or horizontal scroll bar to the reportviewer?
Your Report should have that automatically one the screen fills up your scrollbars should appear
sql
Showing posts with label bar. Show all posts
Showing posts with label bar. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
ReportViewer and Browser scrolling
Does anyone know how to get the web browser scroll bar to synch with
the ReportViewer scroll bar?What are you trying to do? Do you have a need to display the web
browser scroll bar at all? Otherwise, I would just size the
ReportViewer to just take up the whole page and only allow the
ReportViewer scroll bar to display.
On Apr 3, 4:03 pm, "Christopher M Siwy" <top...@.gmail.com> wrote:
> Does anyone know how to get the web browser scroll bar to synch with
> the ReportViewer scroll bar?|||I would resize the ReportViewer to fit in the browser window, but this
webpage has two panes. One which acts as a sort of "Report Explorer"
where reports are organized in a tree, and the other pane is for
rendered reports. We draw the ReportViewer larger than the Render
Pane because the user can hide/unhide the explorer pane to increase
the view of the report.
Nonetheless, we need a way were the browser scroll is locked with the
ReportViewer scroll.
Anyone have a solution for this?
Much thanks in advance!
On Apr 3, 11:08 pm, "jeremy.lattim...@.gmail.com"
<jeremy.lattim...@.gmail.com> wrote:
> What are you trying to do? Do you have a need to display the web
> browser scroll bar at all? Otherwise, I would just size the
> ReportViewer to just take up the whole page and only allow the
> ReportViewer scroll bar to display.
> On Apr 3, 4:03 pm, "Christopher M Siwy" <top...@.gmail.com> wrote:
>
> > Does anyone know how to get the web browser scroll bar to synch with
> > the ReportViewer scroll bar... Hide quoted text -
> - Show quoted text -sql
the ReportViewer scroll bar?What are you trying to do? Do you have a need to display the web
browser scroll bar at all? Otherwise, I would just size the
ReportViewer to just take up the whole page and only allow the
ReportViewer scroll bar to display.
On Apr 3, 4:03 pm, "Christopher M Siwy" <top...@.gmail.com> wrote:
> Does anyone know how to get the web browser scroll bar to synch with
> the ReportViewer scroll bar?|||I would resize the ReportViewer to fit in the browser window, but this
webpage has two panes. One which acts as a sort of "Report Explorer"
where reports are organized in a tree, and the other pane is for
rendered reports. We draw the ReportViewer larger than the Render
Pane because the user can hide/unhide the explorer pane to increase
the view of the report.
Nonetheless, we need a way were the browser scroll is locked with the
ReportViewer scroll.
Anyone have a solution for this?
Much thanks in advance!
On Apr 3, 11:08 pm, "jeremy.lattim...@.gmail.com"
<jeremy.lattim...@.gmail.com> wrote:
> What are you trying to do? Do you have a need to display the web
> browser scroll bar at all? Otherwise, I would just size the
> ReportViewer to just take up the whole page and only allow the
> ReportViewer scroll bar to display.
> On Apr 3, 4:03 pm, "Christopher M Siwy" <top...@.gmail.com> wrote:
>
> > Does anyone know how to get the web browser scroll bar to synch with
> > the ReportViewer scroll bar... Hide quoted text -
> - Show quoted text -sql
Monday, March 12, 2012
Reports cannot be exported to csv format
Hi,
We have developed few reports through SQL Server Reporting Service 2005.
All those reports are columnar graphs (BAR charts).
One of the requirement of the project is to have the ability to export the report in different formats.
Since our reports are columnar graphs we are unable to export it to CSV or MHTML format.
Is there are setting in the report, which allows me to display the values, when exported to csv format?
also is there any setting for MHTML format. Some machine open the report in MHTML format and some machine does not.
Any help would be appreciated.
Thanks in advance!
No replies yet:(
The only solution we can think about is to develop those many reports in tabular format. (only for CSV format)
Any other solution available?
Thanks!
|||CSV output should include Chart Data. http://msdn2.microsoft.com/en-us/library/aa178937(SQL.80).aspx
If you are not getting data, make sure to set DataElementOutput to true. Here is a link to how it works for XML.
http://msdn2.microsoft.com/en-us/library/aa179497(SQL.80).aspx
|||
Even I read the first MSDN link where it mentions charts can be rendered to CSV. Thats why I am a bit sure that there may be some setting in the properties, which will show the data values in CSV.
I also tried the setting mentioned in the CSV Device Information Settings and mentioned Encoding value for CSV as ASCII. Restarted SQL Server.
I also set the DataElementOutput property to "Output" but still I am not able to see the expected result.
Please do let me know if there is any solution available.
Thanks!
|||Chart Data is supposed to be exported to CSV.
Can you attach the report definition so I can take a look and diagnose the problem?
Thanks!
|||Hi Denis,
Following is the report definition. Hope to find some solution to my problem.
Thanks in advance!
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ML_OLAP_DS">
<DataSourceReference>ML_OLAP_DS</DataSourceReference>
<rd:DataSourceID>2e071b5e-2d8d-4aab-94ff-edf8900886a2</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="startDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="endDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
<ReportParameter Name="users">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[USERS].[WINDOWS LOGON NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Users</Prompt>
</ReportParameter>
<ReportParameter Name="appName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[APPLICATIONS].[APPLICATION NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Applications</Prompt>
</ReportParameter>
<ReportParameter Name="chartTitle">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Ten Most Used Applications</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Chart Title</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="MostUsedAppChart">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title>
<Caption>=RTRIM( Parameters!chartTitle.Value)</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<NoRows>The current report criteria does not satisfy any records.</NoRows>
<Height>6.125in</Height>
<CategoryAxis>
<Axis>
<Title>
<Caption>Applications</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>MostUsedAppDS</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Caption>Occurences</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Column</Type>
<Width>4.5375in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!APPLICATION_NAME.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!APPLICATION_INSTANCES_VIEW_Count.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<FontSize>8pt</FontSize>
</Style>
<Value>=Fields!APPLICATION_INSTANCES_VIEW_Count.Value</Value>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>7.875in</Height>
</Body>
<rd:ReportID>a7bd15d4-cace-4fe8-b2d3-62bc6fc786a8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="MostUsedAppDS">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] >= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</CommandText>
<QueryParameters>
<QueryParameter Name="startDate">
<Value>=Parameters!startDate.Value</Value>
</QueryParameter>
<QueryParameter Name="endDate">
<Value>=Parameters!endDate.Value</Value>
</QueryParameter>
<QueryParameter Name="users">
<Value>=Parameters!users.Value</Value>
</QueryParameter>
<QueryParameter Name="appName">
<Value>=Parameters!appName.Value</Value>
</QueryParameter>
<QueryParameter Name="chartTitle">
<Value>=Parameters!chartTitle.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>ML_OLAP_DS</DataSourceName>
<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>APPLICATIONS</DimensionName><HierarchyName>APPLICATION NAME</HierarchyName><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><LevelName>APPLICATION NAME</LevelName><UniqueName>[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]</UniqueName></ID><ItemCaption>APPLICATION NAME</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>APPLICATION INSTANCES VIEW Count</MeasureName><UniqueName>[Measures].[APPLICATION INSTANCES VIEW Count]</UniqueName></ID><ItemCaption>APPLICATION INSTANCES VIEW Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>ApplicationUsage</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] >= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</Statement><ParameterDefinitions><ParameterDefinition><Name>startDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>startDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>endDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>endDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>users</Name><DefaultValues><DefaultValue>[USERS].[WINDOWS LOGON NAME].[All]</DefaultValue></DefaultValues><Caption>users</Caption><HierarchyUniqueName>[USERS].[WINDOWS LOGON NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [USERS].[WINDOWS LOGON NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>appName</Name><DefaultValues><DefaultValue>[APPLICATIONS].[APPLICATION NAME].[All]</DefaultValue></DefaultValues><Caption>appName</Caption><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [APPLICATIONS].[APPLICATION NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>chartTitle</Name><DefaultValues><DefaultValue>Ten Most Used Applications</DefaultValue></DefaultValues><Caption>chartTitle</Caption><ParameterValuesQuery><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="APPLICATION_NAME">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]" /></DataField>
</Field>
<Field Name="APPLICATION_INSTANCES_VIEW_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[APPLICATION INSTANCES VIEW Count]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
It appears to be an error in Books Online: CSV renderer does not render chart data, while XML renderer does. Sorry about confusion.
One way you can export chart data is by applying XSL transformation to the XML data output of the report:
http://msdn2.microsoft.com/en-us/library/aa178953(SQL.80).aspx
Reports cannot be exported to csv format
Hi,
We have developed few reports through SQL Server Reporting Service 2005.
All those reports are columnar graphs (BAR charts).
One of the requirement of the project is to have the ability to export the report in different formats.
Since our reports are columnar graphs we are unable to export it to CSV or MHTML format.
Is there are setting in the report, which allows me to display the values, when exported to csv format?
also is there any setting for MHTML format. Some machine open the report in MHTML format and some machine does not.
Any help would be appreciated.
Thanks in advance!
No replies yet:(
The only solution we can think about is to develop those many reports in tabular format. (only for CSV format)
Any other solution available?
Thanks!
|||CSV output should include Chart Data. http://msdn2.microsoft.com/en-us/library/aa178937(SQL.80).aspx
If you are not getting data, make sure to set DataElementOutput to true. Here is a link to how it works for XML.
http://msdn2.microsoft.com/en-us/library/aa179497(SQL.80).aspx
|||
Even I read the first MSDN link where it mentions charts can be rendered to CSV. Thats why I am a bit sure that there may be some setting in the properties, which will show the data values in CSV.
I also tried the setting mentioned in the CSV Device Information Settings and mentioned Encoding value for CSV as ASCII. Restarted SQL Server.
I also set the DataElementOutput property to "Output" but still I am not able to see the expected result.
Please do let me know if there is any solution available.
Thanks!
|||Chart Data is supposed to be exported to CSV.
Can you attach the report definition so I can take a look and diagnose the problem?
Thanks!
|||Hi Denis,
Following is the report definition. Hope to find some solution to my problem.
Thanks in advance!
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ML_OLAP_DS">
<DataSourceReference>ML_OLAP_DS</DataSourceReference>
<rd:DataSourceID>2e071b5e-2d8d-4aab-94ff-edf8900886a2</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="startDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="endDate">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[DATE].[Startdate].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
<ReportParameter Name="users">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[USERS].[WINDOWS LOGON NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Users</Prompt>
</ReportParameter>
<ReportParameter Name="appName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[APPLICATIONS].[APPLICATION NAME].[All]</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Applications</Prompt>
</ReportParameter>
<ReportParameter Name="chartTitle">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Ten Most Used Applications</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Chart Title</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="MostUsedAppChart">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title>
<Caption>=RTRIM( Parameters!chartTitle.Value)</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<NoRows>The current report criteria does not satisfy any records.</NoRows>
<Height>6.125in</Height>
<CategoryAxis>
<Axis>
<Title>
<Caption>Applications</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>MostUsedAppDS</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Caption>Occurences</Caption>
<Style>
<FontFamily>Arial Black</FontFamily>
</Style>
</Title>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Column</Type>
<Width>4.5375in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!APPLICATION_NAME.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!APPLICATION_INSTANCES_VIEW_Count.Value)</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<FontSize>8pt</FontSize>
</Style>
<Value>=Fields!APPLICATION_INSTANCES_VIEW_Count.Value</Value>
<Visible>true</Visible>
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>7.875in</Height>
</Body>
<rd:ReportID>a7bd15d4-cace-4fe8-b2d3-62bc6fc786a8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="MostUsedAppDS">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] >= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</CommandText>
<QueryParameters>
<QueryParameter Name="startDate">
<Value>=Parameters!startDate.Value</Value>
</QueryParameter>
<QueryParameter Name="endDate">
<Value>=Parameters!endDate.Value</Value>
</QueryParameter>
<QueryParameter Name="users">
<Value>=Parameters!users.Value</Value>
</QueryParameter>
<QueryParameter Name="appName">
<Value>=Parameters!appName.Value</Value>
</QueryParameter>
<QueryParameter Name="chartTitle">
<Value>=Parameters!chartTitle.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>ML_OLAP_DS</DataSourceName>
<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>APPLICATIONS</DimensionName><HierarchyName>APPLICATION NAME</HierarchyName><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><LevelName>APPLICATION NAME</LevelName><UniqueName>[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]</UniqueName></ID><ItemCaption>APPLICATION NAME</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>APPLICATION INSTANCES VIEW Count</MeasureName><UniqueName>[Measures].[APPLICATION INSTANCES VIEW Count]</UniqueName></ID><ItemCaption>APPLICATION INSTANCES VIEW Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>ApplicationUsage</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
MEMBER [DATE].[StartDate].[DateRange] AS
AGGREGATE
( STRTOMEMBER(@.startDate) : STRTOMEMBER(@.endDate), [Measures].CURRENTMEMBER
)
MEMBER [USERS].[WINDOWS LOGON NAME].[UserName] AS
AGGREGATE
( {STRTOSET(@.users)} )
SELECT
NON EMPTY { [Measures].[APPLICATION INSTANCES VIEW Count] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]}, [Measures].[APPLICATION INSTANCES VIEW Count] >= 0 ) , 10, [Measures].[APPLICATION INSTANCES VIEW Count] ) ON ROWS
FROM
(select STRTOSET(@.appName) on 0 from [ApplicationUsage] )
WHERE
(([USERS].[WINDOWS LOGON NAME].[UserName]),[DATE].[StartDate].[DateRange])</Statement><ParameterDefinitions><ParameterDefinition><Name>startDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>startDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>endDate</Name><DefaultValues><DefaultValue>[DATE].[Startdate].[All]</DefaultValue></DefaultValues><Caption>endDate</Caption><HierarchyUniqueName>[DATE].[Startdate]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[DATE].[Startdate].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DATE].[Startdate].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DATE].[Startdate].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [DATE].[Startdate].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>users</Name><DefaultValues><DefaultValue>[USERS].[WINDOWS LOGON NAME].[All]</DefaultValue></DefaultValues><Caption>users</Caption><HierarchyUniqueName>[USERS].[WINDOWS LOGON NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[USERS].[WINDOWS LOGON NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [USERS].[WINDOWS LOGON NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>appName</Name><DefaultValues><DefaultValue>[APPLICATIONS].[APPLICATION NAME].[All]</DefaultValue></DefaultValues><Caption>appName</Caption><HierarchyUniqueName>[APPLICATIONS].[APPLICATION NAME]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[APPLICATIONS].[APPLICATION NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [APPLICATIONS].[APPLICATION NAME].ALLMEMBERS ON ROWS FROM [ApplicationUsage]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>chartTitle</Name><DefaultValues><DefaultValue>Ten Most Used Applications</DefaultValue></DefaultValues><Caption>chartTitle</Caption><ParameterValuesQuery><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="APPLICATION_NAME">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[APPLICATIONS].[APPLICATION NAME].[APPLICATION NAME]" /></DataField>
</Field>
<Field Name="APPLICATION_INSTANCES_VIEW_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[APPLICATION INSTANCES VIEW Count]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
It appears to be an error in Books Online: CSV renderer does not render chart data, while XML renderer does. Sorry about confusion.
One way you can export chart data is by applying XSL transformation to the XML data output of the report:
http://msdn2.microsoft.com/en-us/library/aa178953(SQL.80).aspx
Monday, February 20, 2012
Reporting Services, pivot function or union on MDX
I have to build a rather simple report with only one chart. The chart has to show a 100% stacked bar chart, with the VBS members as series.
I have 2 measures, subscriber count and total revenue. Now I want 2 bars: a first one showing the vbs stacks on subscriber count and a second one showing the vbs stacks on total revenue.
Because subscriber count and total revenue are measures, I cannot add them to the categorie fields in reporting services.
This means that I have to somehow perform a sort of pivot function on my mdx query, or a kind of union to create a record per measure.
This is the basic mdx query I have now:
SELECT NON EMPTY { [Measures].[Total Net Rev], [Measures].[Subscriber Count] } ON COLUMNS, NON EMPTY { ([Dim Vbs].[Vbs].[Vbs].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DimMcoMco, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimTimeTimeHierarchy, CONSTRAINED) ) ON COLUMNS FROM [RaceSegments])) WHERE ( IIF( STRTOSET(@.DimTimeTimeHierarchy, CONSTRAINED).Count = 1, STRTOSET(@.DimTimeTimeHierarchy, CONSTRAINED), [Dim Time].[Time Hierarchy].currentmember ), IIF( STRTOSET(@.DimMcoMco, CONSTRAINED).Count = 1, STRTOSET(@.DimMcoMco, CONSTRAINED), [Dim Mco].[Mco].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Thanks
Joos
Anybody?
I need a 100% stacked chart with 2 bars. One for subscriber count and one for Total Net Revenue. I would like to visualize the distribution of the VBS dimension members within each bar.
To make it easier...this is the mdx query without any parameters:
SELECT NON EMPTY { [Measures].[Total Net Rev], [Measures].[Subscriber Count] } ON COLUMNS, NON EMPTY { ([Dim Vbs].[Vbs].[Vbs].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [RaceSegments] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Subscribe to:
Posts (Atom)