Edit and Update exsting report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Edit and Update exsting report

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-16 : 23:10:57

I have the reporting Services related report and each year will update by means of new Build and this build is applied onto the front end and the report will be updated.

The New Build has got 'new Column specifications' which is identified by report on updating, this new specifications would be Added on invoking the existing report. (On the other hand the Front end has updated its screens).

It seems very simple but I could not EDIT the existing report, and this is identified by new specifications through new Starting and End positions for the report, which should be updated.

As I am new to this reporting services could anyone help me in updating the report by making use of this new build parameters how to update them to project the data to be updated for this new Parameters of new specifications.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 23:37:01

sorry not quite clear on what exactly you're looking at. can you elaborate on sample scenario?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-16 : 23:52:55

I have to update the report such that my new build which comes along with new Start and End position parameters / fields would be accomodated through the report. This report is functioning based on last year specifications and this year there are new Fields which has accomodated in the Front end Screen but in order to populate these NEW PARAMETERS by means of new positions should be incorporated and when the report executed should accompany and come along with the new values for the parameters.

There are couple of stored procedures which activate the front end screens but the report builder 'perhaps' should be UPDATED accordingly.

In other words how can I update my existing report from the reporting server such that I can increment these new Specifications which are come through the new build.

many thanks for coming forward to help me Vishak16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 23:57:59

you dont have straightforward way of programmatically changing report definition like adding new parameters, adding new dataset fields, new tables etc. You've to do it by manually opening and modifying report in business intelligence development studio.
Otherways you've to write .NET code to get report definition using webservices and modify it which is not straightforward especially if you dont have much exposure in .NET

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-17 : 00:03:56

manually opening and modifying report in business intelligence development studio

'I am new to this.....'

I have reportserver and when I open I see the Reports and right click on the particular report which I am supposed to work on have EDIT REPORT, VIEW REPORT, REPLACE REPORT AND NEW LINKED REPORT, MOVE, SCRIPT ... PROPERTIES etc.

By using EDIT and SCRIPT I could not see how to add them as to how I can UPDATE them.

How can I EDIT on VIEW the exist report?

I have googled for this but not getting there.... Sorry.

Any further clues Please

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-17 : 00:15:23

I managed to open the reports with extension of .RDL and i dont know should I open in BIDS and make changes to them?

Please advise as I dont have support in my organisation and need to do on my own for good

if not done

. Funny isn't it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 00:17:41

you can import report as .rdl file from the reportserver then save it locally. Then open it in a report server project in Business Intelligence Studio (access it from start->programs->sql server) and you can do the modifications

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-17 : 00:22:09

When I open the .rdl it is showing in the form like HTML xml language and should I increment the calues here and still execute it on the main environment?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 23:28:32

quote:
Originally posted by pdset

When I open the .rdl it is showing in the form like HTML xml language and should I increment the calues here and still execute it on the main environment?

you should be opening it under report server project in business intelligence development studio/visual studio and you'll get visual layout of report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-18 : 00:24:16

Thanks Vishak16.

Its almost the same which I have done. But there is no evidence that I can still 'Make Changes' to the existing report.

When I choose to EDIT the Report then I could able to create .RDL extension.

On opening .RDL file: following is the CODE:

<?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="dsIPCDatabase">
<rd:DataSourceID>caae8fe4-6299-4afe-8268-501a9c0f0c92</rd:DataSourceID>
<DataSourceReference>dsIPCDatabase</DataSourceReference>
</DataSource>
</DataSources>
<InteractiveHeight>29.7cm</InteractiveHeight>
<ReportParameters>
<ReportParameter Name="DefYear">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>dsDefaultYear</DataSetName>
<ValueField>DefYear</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Enter the Year : </Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>2007</Value>
</ParameterValue>
<ParameterValue>
<Value>2008</Value>
</ParameterValue>
<ParameterValue>
<Value>2009</Value>
</ParameterValue>
<ParameterValue>
<Value>2010</Value>
</ParameterValue>
<ParameterValue>
<Value>2011</Value>
</ParameterValue>
<ParameterValue>
<Value>2012</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="DefMonth">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>dsDefaultMonth</DataSetName>
<ValueField>DefMonth</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Enter the month : </Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>1</Value>
<Label>January</Label>
</ParameterValue>
<ParameterValue>
<Value>2</Value>
<Label>February</Label>
</ParameterValue>
<ParameterValue>
<Value>3</Value>
<Label>March</Label>
</ParameterValue>
<ParameterValue>
<Value>4</Value>
<Label>April</Label>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
<Label>May</Label>
</ParameterValue>
<ParameterValue>
<Value>6</Value>
<Label>June</Label>
</ParameterValue>
<ParameterValue>
<Value>7</Value>
<Label>July</Label>
</ParameterValue>
<ParameterValue>
<Value>8</Value>
<Label>August</Label>
</ParameterValue>
<ParameterValue>
<Value>9</Value>
<Label>September</Label>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
<Label>October</Label>
</ParameterValue>
<ParameterValue>
<Value>11</Value>
<Label>November</Label>
</ParameterValue>
<ParameterValue>
<Value>12</Value>
<Label>December</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="RefreshReportData">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Refresh export data with new values?</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>1</Value>
<Label>Yes</Label>
</ParameterValue>
<ParameterValue>
<Value>0</Value>
<Label>No</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>2.5cm</RightMargin>
<LeftMargin>2.5cm</LeftMargin>
<BottomMargin>2.5cm</BottomMargin>
<rd:ReportID>165343cc-91bd-4658-b71e-98d11ddb31f3</rd:ReportID>
<PageWidth>21cm</PageWidth>
<DataSets>
<DataSet Name="dsExportTable">
<Fields>
<Field Name="line">
<DataField>line</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>dsIPCDatabase</DataSourceName>
<CommandText>IF @RefreshReportData=1
Begin
-- Sets the Year and Month in the lkpDefault table to the Setting Selected
Update lkpDefault Set DefMonth = @DefMonth, DefYear = @DefYear

-- Executes the Stored Procedure
Exec SP_rpl_Export

End

Select * from Export</CommandText>
<QueryParameters>
<QueryParameter Name="@RefreshReportData">
<Value>=Parameters!RefreshReportData.Value</Value>
</QueryParameter>
<QueryParameter Name="@DefMonth">
<Value>=Parameters!DefMonth.Value</Value>
</QueryParameter>
<QueryParameter Name="@DefYear">
<Value>=Parameters!DefYear.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="dsDefaultYear">
<Fields>
<Field Name="DefYear">
<DataField>DefYear</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>dsIPCDatabase</DataSourceName>
<CommandText>Select Top 1 DefYear
From lkpDefault</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="dsDefaultMonth">
<Fields>
<Field Name="DefMonth">
<DataField>DefMonth</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>dsIPCDatabase</DataSourceName>
<CommandText>SELECT TOP (1) DefMonth
FROM lkpDefault</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Width>20.75cm</Width>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<DataSetName>dsExportTable</DataSetName>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="line">
<rd:DefaultName>line</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<TableColumn>
<Width>20.75cm</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Height>0.63492cm</Height>
</Body>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report

When I RUN the report it give me around 200 ROWS.

Each ROW is prefixed with the 'Data Length' and any extra Data is TRUNCATED (As Expalined in my earlier posts)

Now I need to INCREASE this ROW LENGTH for data to incorporate the 'additional data'.

How Can I use this .RDL file to make changes (above) and whether can I use it existing report to OVERWRITE or make a Duplicate report with newer EXTRA LENGTH?

Please Suggest the best method.

Many Thanks for your intervention.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 00:45:49

where are you opening this? dont use open as option.

Create a new project in BIDS as suggested then browse and add your rdl using add new item option and you should be able to see report in the design format.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-20 : 22:20:30

when I ope RDL file couldn't get the ADD NEW ITEM to see in the Desgin Layout.

Perhaps Missing something while opening, how to be invoked please.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-20 : 22:22:55

I tried using RDL file to get into but while opening using BIDS couldn't find ADD NEW ITEM, perhaps missing something may be.....

Any clue please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 23:20:21

quote:
Originally posted by pdset

I tried using RDL file to get into but while opening using BIDS couldn't find ADD NEW ITEM, perhaps missing something may be.....

Any clue please.

have you installed report server project templates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-21 : 10:24:16

quote:
Originally posted by pdset

I tried using RDL file to get into but while opening using BIDS couldn't find ADD NEW ITEM, perhaps missing something may be.....

Any clue please.

If you have not already done so, download the RDL file from the Report Server site. See here for step by step instructions: http://sql-articles.com/blogs/download-export-rdl-files-from-report-server/

After you have downloaded the RDL file, use the instructions here to open it in BIDS: http://msdn.microsoft.com/en-us/library/gg309379.aspx

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-21 : 19:39:44

Thanks Very much is like peel off Banana and make to eat :)

Your effort was Adorable.

Could able to open the RDL;

Could able to open the existing Data source (RDS);

But unable to match with the data set to RDL error:

Data source was not found.also specifiesto add data source to data set before proceeding or choose another data source.

Then I have created the Data source to match with RDL then complains about SQL Authentication Login failed.

Can you further educate me and many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-21 : 20:51:00

quote:
Originally posted by pdset

Thanks Very much is like peel off Banana and make to eat :)

Your effort was Adorable.

Could able to open the RDL;

Could able to open the existing Data source (RDS);

But unable to match with the data set to RDL error:

Data source was not found.also specifiesto add data source to data set before proceeding or choose another data source.

Then I have created the Data source to match with RDL then complains about SQL Authentication Login failed.

Can you further educate me and many thanks

are you using new datasource? if yes, have you deployed it to server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-21 : 21:35:53

Thaks Vishak16 and also Sunitabeck here for my rescue.

we don't have testing reporting server so I was just taking the existing copies RDL and RDS with different names and trying to invoke LAYOUT which got it.

I did with RDL same RDS but complaining as 'Data source was not found ...' and created New Data Source with different name connected to Server and also Database ... Test Connection Successful.

But when toggling between Data ~Layout ~ Preview still the same error.

Under Data: error

'The query could not be loaded. Verify your Connection String and query String'

Under Preview: Error

'An Error occurred during local report processing. The item '/dsIRSData' cannot be found.

NOTE: dsIRSData is the existing data source to which this is pointed out - BUT, I have created new Data source 'IRSExportData', is not showing up under the PREVIEW tab error.

Going nowhere and struck, Any further evaluation here. Many Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-22 : 07:14:58

If you are using a shared dataset, it may be that you need to download the dataset definition also from the report site.

Conceptually, I think of it in the following hierarchy:

1. A Data Source - this may be a shared data source, or it may not be. This specifies the connection to the database server.

2. A Data Set - this uses the data source and defines the query that is used to get the data. The data set also may be a shared data set.

3. Report definition - The report definition uses the data set to populate the various fields.

You have to make sure that all the three components above are available and refer to the same entities. So if the dataset or data source is named dsIRSData in the production version, in the report definition, it would be looking for a data set or datasource with that name. If your new data source is named IRSExportData, unless you change the data set to point to that data source, it wouldn't be able to recognize or retrieve the data.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2012-10-22 : 23:40:07

Thank you both on wonderful support in uderstanding the depth of problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources