Clustered DB Server: reports

Showing posts with label reports. Show all posts

Monday, March 19, 2012

Collapsible reports

Hi,

I have a Dataset with repetitive data and i want to group all data based on a particular field and have it disaplayed as collapsible rows on a report.

BTW i am generating reports dynamically in my APP.Any ideas on how to proceed?

If I understand correctly, you want to group your data and have the details toggled by the group. To do that, you just need to add a table group on the field, set the visibility properties on the detail row of the table - 1. it's toggled by a textbox in the group header 2. the initial visibility to be hidden if you want to start as collapsed.|||

Hi,

Thanks 4 ur post wang...

You understood my question exactly...

Do u want me to add one more table to the Report?

could you please be more elaborate as to what needs to be done?

|||

No, you don't need another table. Here is a sample report to show how to do this:

<?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="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>30d8ee62-a72d-48fa-ad77-66fdebc3f620</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<DataSetName>DataSet1</DataSetName>
<Top>0.625in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<rd:DefaultName>ProductName</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<rd:DefaultName>UnitPrice</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>textbox6</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Category ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Unit Price</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>3.25in</Height>
</Body>
<rd:ReportID>87afdd90-7b33-49bc-8880-0df212c6637a</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT TOP 10 CategoryID, ProductName, UnitPrice
FROM Products</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.50001in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<PageFooter>
<Height>0.375in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>
<TopMargin>1in</TopMargin>
</Report>

collapse button in SQL reports

I am working with SQL reporting services, I am getting the data dispalyed...

the only problem is that I am not able to collapse, ie (+) button next to the row header and the cloum header.

Can any one let me know, how to get that....

Thnks in adv.

Hi,

i don′t know how familiar you are with Reporting Services, but this is the function accessible through the Hidden property. Select e.g. a group in your table (clicking on the row left next to the table) and choose the property Hidden = True, then specify a toggle item (up the hierarchy of the group could be a textbox in the table) which should display the +/- signs:

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-2/

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Sunday, March 11, 2012

Cognos IWR Server

Our disk drive died, and we were able to backup most of the files (all
the reports, the shuttled, pending, etc)

But when we open the administrator, we get a blank. none of our
catalogues are listed in there.

I don't want to recreate all the catalogues and reports- does anyone
know how we can "import" them in there? The database that contains all
the "data" was not backed up, sicne it was in use during backup (bad
Network admin)

If anyone can help, PLEASE email. trying to get an answer from cognos,
but who knows?!!!Bliss <eternalbliss@.techie.com> wrote in message news:<400f1786$1@.obsidian.gov.bc.ca>...
> Our disk drive died, and we were able to backup most of the files (all
> the reports, the shuttled, pending, etc)
> But when we open the administrator, we get a blank. none of our
> catalogues are listed in there.
> I don't want to recreate all the catalogues and reports- does anyone
> know how we can "import" them in there? The database that contains all
> the "data" was not backed up, sicne it was in use during backup (bad
> Network admin)
> If anyone can help, PLEASE email. trying to get an answer from cognos,
> but who knows?!!!

I have no idea about the Cognos side of things, but if still have the
..mdf and .ldf files for your MSSQL databases, you can try using
sp_attach_db or sp_attach_single_file_db to attach them again. If this
doesn't work, and you have no backups, then all you can really do is
contact Microsoft PSS. They may be able to help recover data from the
files.

By the way, MSSQL databases can be backed up while in use - this is
usually how backups are done.

Simon|||Thanks! But Cognos, apparently (to the dismay of the network admin) was
skipping open files.

And we have all the reports/databases. We were more interested in the
cogno's side of things. We can't import into the admin program.

But thanks for the info!

Simon Hayes wrote:
> Bliss <eternalbliss@.techie.com> wrote in message news:<400f1786$1@.obsidian.gov.bc.ca>...
>>Our disk drive died, and we were able to backup most of the files (all
>>the reports, the shuttled, pending, etc)
>>
>>But when we open the administrator, we get a blank. none of our
>>catalogues are listed in there.
>>
>>I don't want to recreate all the catalogues and reports- does anyone
>>know how we can "import" them in there? The database that contains all
>>the "data" was not backed up, sicne it was in use during backup (bad
>>Network admin)
>>
>>If anyone can help, PLEASE email. trying to get an answer from cognos,
>>but who knows?!!!
>
> I have no idea about the Cognos side of things, but if still have the
> .mdf and .ldf files for your MSSQL databases, you can try using
> sp_attach_db or sp_attach_single_file_db to attach them again. If this
> doesn't work, and you have no backups, then all you can really do is
> contact Microsoft PSS. They may be able to help recover data from the
> files.
> By the way, MSSQL databases can be backed up while in use - this is
> usually how backups are done.
> Simon

Code Samples for external linking to Reports

Hello All,
Are there any code samples which would let me post a URL
link from a classic ASP app to specified Reports on SRS
via URL querystring parameters?
Any thoughts/insight would be greatly appreciated...
TIA,
--Karthiki found ample examples of this in the samples that was an option on installtion
"Karthik" wrote:
> Hello All,
> Are there any code samples which would let me post a URL
> link from a classic ASP app to specified Reports on SRS
> via URL querystring parameters?
> Any thoughts/insight would be greatly appreciated...
> TIA,
> --Karthik
>

Friday, February 24, 2012

Clustering SQL and service packs

I have a SQL 2000 cluster and have applied SP3 to it.
However, when running on one side of the cluster SQL reports a different
version to when it is running on the other side. Is this normal / expected
or has it not applied SP3 to the other node correctly?
e.g
Running on node 1
select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
SELECT SERVERPROPERTY('ProductLevel')
SP3
Running on node 2
select @.@.version
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
SELECT SERVERPROPERTY('ProductLevel')
RTM
If this is not normal / exptected, can I just apply SP3 to node 2 on its
own?
Thanks.
Hi
SQL 2000 sp3 automatically installs itself on all nodes of a cluster. You
have a problem.
Try running it on the 2nd node and see if it will install. Was B down when
sp3 was installed?
Regards
Mike
"Stephen Aldous" wrote:

> I have a SQL 2000 cluster and have applied SP3 to it.
> However, when running on one side of the cluster SQL reports a different
> version to when it is running on the other side. Is this normal / expected
> or has it not applied SP3 to the other node correctly?
> e.g
> Running on node 1
> select @.@.version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> --
> SELECT SERVERPROPERTY('ProductLevel')
> SP3
> --
> Running on node 2
> select @.@.version
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> SELECT SERVERPROPERTY('ProductLevel')
> RTM
>
> If this is not normal / exptected, can I just apply SP3 to node 2 on its
> own?
>
> Thanks.
>
>
|||I've tried to install SP3 on the virtual cluster name again and it crashes
to desktop with an error with _INS576._mp error.
Can I just fail it over to the second node and install SP3 on the local
machine?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:76ECCB2C-267F-406E-B6EE-0CA05F435A2B@.microsoft.com...[vbcol=seagreen]
> Hi
> SQL 2000 sp3 automatically installs itself on all nodes of a cluster. You
> have a problem.
> Try running it on the 2nd node and see if it will install. Was B down when
> sp3 was installed?
> Regards
> Mike
> "Stephen Aldous" wrote:
expected[vbcol=seagreen]
|||Hi
Raise a case with Microsoft PSS as you need their help. Safest.
Regards
Mike
"Stephen Aldous" wrote:

> I've tried to install SP3 on the virtual cluster name again and it crashes
> to desktop with an error with _INS576._mp error.
> Can I just fail it over to the second node and install SP3 on the local
> machine?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:76ECCB2C-267F-406E-B6EE-0CA05F435A2B@.microsoft.com...
> expected
>
>
|||Hi Steve,
This is not normal. It appears that SP3 got installed only on node 1 and not node 2. Currently your cluster nodes has mis-matched binaries. Infact, SQL Server MAY not even start on node 2 or you may get
unexpected results if you run SQL on node 2.
While SQL is running on node 1, try running SP3 setup on node 2 and see if it succeeds. If it fails then post the exact steps that you took to run the setup, exact error message and the sqlsp.log file.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx