database collation: parameters

Showing posts with label parameters. Show all posts

Tuesday, March 27, 2012

Changing the report parameters or data sources to the values you specified is not allowed

I have a report I want to modify and when I go to deploy it I get:

Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report

Now I understand the error, it is because on the report server's web management interface I entered credentials for the report to run that point to a custom datasource and supplied the appropriate credentials to run the report.

However, I don't want to have to reconfigure that just to deploy a slightly modified version of the report.

How can I get around this? Or what is the proper work around? I just want to change the background color from dark gray to light gray on a header column....

Thanks!

Hello,

It seems that other modifications might have been made to the report. Try to get the latest version from the report server, modify the header column's color, and try to re-deploy.

Go to your report in Report Manager and click on the Properties tab. In the General section, click on the Edit link, it will ask you to save the RDL file. This is the report file as it is on the RS server. Navigate to where your RDL files are stored on your development box and replace the report with the one you got from the RS server. Open your VS project and make your change.

Hope this helps.

Jarret

|||

I'll have to try that thanks.

Question for you as well, when I do that will it keep all the settings that report has like execution schedules, history, etc..?

Or does all that get lost when I re-deploy the report to the server?

Also what about security settings for the report?

If I set the report to be viewable by HR Management only, will I have to redo that once I overwrite the report by re-deploying that or is that all preserved?

(If it IS preserved is it preserved in the report itself or just on the report server where those properties point to the report by the name of the report?)

Thanks!

|||

Another option is to create a specific HR-only shared data source, specify the credentials -there-, and build reports off of it, then you don't need to supply credentials for each report.

Then limit access to that datasource to only HR personnel.

Then in your SSRS Project Properties, change 'OverwriteDataSources' to False. Then you can specify credentials on the server once, and each time you deploy your project, they don't get erased by the deployment process.

The execution schedules, permissions, and history settings remain the same after deployment. The only thing that will get wiped out is the credentials.

Hope this helps,

Geof

|||

That does make sense, I'll have to do that.

Thanks for the info.

Monday, March 19, 2012

Changing SQL in runtime

It's possible to change the SQL in runtime, according to selected parameters, using some scripting language ?

Thank you

Hi,

I would rather use a stored procedure for that. THe logic in the stored procedure can decide which Query to execute.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
-

Wednesday, March 7, 2012

Changing Parameters Dynamically

Hello All,
How do I change paramters dynamically based on user selection. For
example if I let the user select country in a drop down, and based on
the country, I need to load the states parameter, and based on the
states, the counties parameter etc...Is such a dynamic layout possible
using reporting services and VS.NET? If so how? Any links or code
samples would be really helpful.
Thanks,
ChrisYes you can do this. Reference the data set in the report parameter. In the
code snippet below, I have 2 parameters. The first report Parameter is
passed into the query parameter. The resulting data set is then use in the
second parameter and the report filters the data set based on that
parameter. Play with the .rdl a bit and you will get it to work.
<ReportParameters>
<ReportParameter Name="FirstName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Brad</Value>
</Values>
</DefaultValue>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Brad</Value>
</ParameterValue>
<ParameterValue>
<Value>Jon</Value>
</ParameterValue>
<ParameterValue>
<Value>Steve</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<Prompt>FirstName</Prompt>
</ReportParameter>
<ReportParameter Name="LastName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Syputa</Value>
</Values>
</DefaultValue>
<Prompt>LastName</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Northwind</DataSetName>
<ValueField>EmployeeLastName</ValueField> --Field(column) in the
dataset.
<LabelField>EmployeeLastName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
--
| From: chrispragash@.hotmail.com (CPragash)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Changing Parameters Dynamically
| Date: 14 Sep 2004 10:57:39 -0700
| Organization: http://groups.google.com
| Lines: 11
| Message-ID: <79c9faae.0409140957.43a028f6@.posting.google.com>
| NNTP-Posting-Host: 167.7.17.3
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1095184659 14011 127.0.0.1 (14 Sep 2004
17:57:39 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 14 Sep 2004 17:57:39 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews2.google.com!no
t-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29153
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello All,
|
| How do I change paramters dynamically based on user selection. For
| example if I let the user select country in a drop down, and based on
| the country, I need to load the states parameter, and based on the
| states, the counties parameter etc...Is such a dynamic layout possible
| using reporting services and VS.NET? If so how? Any links or code
| samples would be really helpful.
|
| Thanks,
| Chris
|

Changing parameters causes refresh...

Hey Guys,

I have two parameters on a report, which are independent of one another, yet when I change one, the current report refreshes, and I'm left with a blank screen. What is causing the refresh behavior on this report? It appears as though it is treating them as cascading parameters, but I don’t use one to filter the other. Since the report has default values, when first navigating to the report, it displays just fine. I would like the user to be able to change the first parameter without the report refreshing to a blank screen. Once it refreshes, they can click on the “View Report” button and see the report, but I would like for the current report to stay there, which is the case on my other reports, until they click the button. Any ideas?

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

Data is retrieved from OLAP cube, some parameters are also retrieved from olap cube and I have also some non-queried parameters and all parameters have non- queried defaults with expressions and custom codes.

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

My Main Data Set :

"WITH SET [Period1] AS '{"+ Parameters!px_Time_Hier1.Value +"}' MEMBER [Measures].[SalesP1] AS 'SUM([Period1] ,[Measures].Sales)' MEMBER [Measures].[SalesP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].Sales)' MEMBER [Measures].[SalesP3] AS 'SUM({ParallelPeriod(Year)} ,[Measures].Sales)' MEMBER [Measures].[SalesP4] AS 'SUM({PeriodsToDate("+Parameters!CompareBy.Label+",ParallelPeriod(Year))} ,[Measures].Sales)' MEMBER [Measures].[CIPP1] AS 'SUM([Period1] ,[Measures].CIP)' MEMBER [Measures].[CIPP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].CIP)' MEMBER [Measures].[Open Orders] AS 'SUM({[Time].[All Time]},[Measures].[Open Order])' MEMBER [Sales Category].[SalesCat] AS 'Aggregate({"+ Parameters!SalesCat.Value +"})' SELECT NONEMPTYCROSSJOIN({" + Parameters!px_Subsidiary_Hier.Value +"},{[Country].[Purc Country].Members} ,{Descendants([Item].[All Item],[Item].[Commodity],LEAVES)} ) ON ROWS, {[Measures].[SalesP1], [Measures].[CIPP1],[Measures].[SalesP2], [Measures].[CIPP2],[Measures].[SalesP3],[Measures].[SalesP4],[Measures].[Open Orders]} ON COLUMNS , [Period1] ON PAGES FROM Cost WHERE ([Sales Category].[SalesCat], " + Parameters!px_Currency_Hier1.Value + ")"

|||

It sounds like the dataset queries used as valid value / default value of the second parameter use complex expressions (e.g. with string concatenation) and could potentially call custom assemblies etc. which could indirectly reference the value of the first report parameter - this is why the parameters are treated as cascading parameters.

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

|||Actually, there were no complex expressions involved. The datasets only took one parameter, which was a hard-coded number. I tried switching the the order of the parameters, and it didn't help. What ended up helping was to create a report parameter specifically for the dataset to pass as its parameter, instead of using the hard-coded parameter directly in the dataset. Not sure why that helped, but it did.|||It is related with default values, expression type defaults cause this
eventhough expression does not refer to another parameter. I have changed my
default values to queried defaults and the problem has gone.

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

There is no referenses between my parameters and non of them has 'hardcoded' default-values (except for some that defaults to null). However, some datasets uses the parameters as input to stored procedures.

When the exact same report is viewed from a reportViewer in a winform the report is left intact after the parameters are changed and the report only updates if the 'view report' or 'refresh' button is pressed. So, here I'm not left with a blanc (white) report.

Can I solve this problem in some way?

Regards Andreas

Changing parameters causes refresh...

Hey Guys,

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

My Main Data Set :

|||

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

Can I solve this problem in some way?

Regards Andreas

Changing parameters causes refresh...

Hey Guys,

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

My Main Data Set :

|||

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

Can I solve this problem in some way?

Regards Andreas

Changing Parameter Dropdown Size

I have parameters that are too long to fit into the dropdown box which then
requires horizontal scrolling. How can i change the width of the dropdown
controls?
TIA
DeanOn Jun 25, 9:35 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> I have parameters that are too long to fit into the dropdown box which then
> requires horizontal scrolling. How can i change the width of the dropdown
> controls?
> TIA
> Dean
Currently, there is no way to control the width of the drop-down
boxes. Possibly, the next version of SSRS (V2008) will have this
functionality, since this topic has come up a few times in this group.
Sorry that I can not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Friday, February 24, 2012

Changing join order based on parameters

Hi,
I keep coming across a performance problem in SQL that I feel must have
an elegant solution, I just can't find it.
The problem is quite obvious:
I have a query that performs a search based on some search parameters
which are given default values in the query of null.
I have a heirachical structure of tables which might be used in the
filtering
e.g.
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd
I filter the result by adding to these join filters something like
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
= @.filterCol1 )
etc...
The problem comes when there are a few hundred thousand rows in each
table, and my search wants to filter on a parameter which affects
TableD. It appears from the execution plan (and indeed only seems
logical) that SQL has to join the entirity of tables A,B and C on their
foreign keys, before joining to D in order to filter. This of course
makes the query take ages.
I've got ideas of how I could get around this, but they are at best
"clunky".
for example:
1) I could have several different "selects" each joining in a
different order based on the parameters that are set.
2) I could build up a "filter" temporary table based on the parameters
passed, then only do the select once, starting with this filter table
first
3) I could come up with a nice elegant solution which currently eludes
me.
Any suggestions?
WillWill

> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb OR TableB.col1 =
COALESCE(@.filterCol1,TableB.col1 )
WHERE @.filterCol1 IS NULL
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143715517.502232.295550@.z34g2000cwc.googlegroups.com...
> Hi,
> I keep coming across a performance problem in SQL that I feel must have
> an elegant solution, I just can't find it.
> The problem is quite obvious:
> I have a query that performs a search based on some search parameters
> which are given default values in the query of null.
> I have a heirachical structure of tables which might be used in the
> filtering
> e.g.
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd
> I filter the result by adding to these join filters something like
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
> etc...
> The problem comes when there are a few hundred thousand rows in each
> table, and my search wants to filter on a parameter which affects
> TableD. It appears from the execution plan (and indeed only seems
> logical) that SQL has to join the entirity of tables A,B and C on their
> foreign keys, before joining to D in order to filter. This of course
> makes the query take ages.
> I've got ideas of how I could get around this, but they are at best
> "clunky".
> for example:
> 1) I could have several different "selects" each joining in a
> different order based on the parameters that are set.
> 2) I could build up a "filter" temporary table based on the parameters
> passed, then only do the select once, starting with this filter table
> first
> 3) I could come up with a nice elegant solution which currently eludes
> me.
> Any suggestions?
> Will
>|||Uri,
Thanks for replying.
I must admit I'm having trouble fully understanding what the aim of the
change is. As I see it if I pass something not null as @.filterCol1 then
I won't get any results back. This isn't what I wanted. Essentially the
point of the @.filtercol1 was to indicate that I'm using optional
parameters to filter it, so sometimes I want to filter on @.filterCol1,
sometimes the user might not select any filter for that, in which case
I'll want to filter on another variable @.filterCol2 (sorry I should
have put a fuller example in, I was abreviating it and only using one
variable). This behaviour in itself isn't causing me any problems. My
issue is that if the user only selects a parameter which filters on
tableD, I need this to be used to optimise my query. e.g.:
let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
= TableB.Col1)
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
= TableD.Col2)
I need my sql query to realise that TableD is going to be the bounding
result set, evaluate this one first, then use that to restrict the
scope of the joins on the other 3 tables. However if the user were to
put a value into @.filterCol1 and null for @.filterCol2 then I would need
it to evaluate tableB first as this will be the smallest result set.
I don't think there's going to be a perfect answer, but this problem
must be encountered a lot.
Will|||Will
Well if I understood you cannot dictate to optimizer what order of joins to
be chosen. Yes, there are some JOIN hints but not in this case I think. Have
you considered ( I don't know your business requirements) to separe this
SELECT statement i mean JOIN two tables/three tables based on
parameteters that you've got?
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143720216.439397.65770@.t31g2000cwb.googlegroups.com...
> Uri,
> Thanks for replying.
> I must admit I'm having trouble fully understanding what the aim of the
> change is. As I see it if I pass something not null as @.filterCol1 then
> I won't get any results back. This isn't what I wanted. Essentially the
> point of the @.filtercol1 was to indicate that I'm using optional
> parameters to filter it, so sometimes I want to filter on @.filterCol1,
> sometimes the user might not select any filter for that, in which case
> I'll want to filter on another variable @.filterCol2 (sorry I should
> have put a fuller example in, I was abreviating it and only using one
> variable). This behaviour in itself isn't causing me any problems. My
> issue is that if the user only selects a parameter which filters on
> tableD, I need this to be used to optimise my query. e.g.:
> let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
> = TableB.Col1)
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
> = TableD.Col2)
> I need my sql query to realise that TableD is going to be the bounding
> result set, evaluate this one first, then use that to restrict the
> scope of the joins on the other 3 tables. However if the user were to
> put a value into @.filterCol1 and null for @.filterCol2 then I would need
> it to evaluate tableB first as this will be the smallest result set.
> I don't think there's going to be a perfect answer, but this problem
> must be encountered a lot.
> Will
>|||Uri,
Yes, splitting the select out into different ones dependent on
parameters seems to be the only way to go (but unfortunately not an
option as I won't be able to justify a potentially breaking change such
as that for performance increases). I was hoping that there would be
some nice sql trick that allows you to always hit the smaller tables
first.
Thanks for your help
Will|||If not specified otherwise (through the use of join hints) the Query
Optimizer will always choose the best order according to indexes, statistics
,
etc.
ML
http://milambda.blogspot.com/|||I know that the optimiser will try, and that I can override with join
hints, but in this case the optimiser can't help because the logic of
the join forces it to be evaluated in that order. What I was hoping for
was some kind of new way of laying out the query such that I could
"enter" my data structure from different directions depending on which
one has parameters defined to filter it. I suspect it's not possible,
but doesn't anyone else find this a problem? is there some better way
to either lay out the tables or to have filter procs with multiple
optional parameters?
Cheers
Will|||One way to filter the rows before the join is to use inline table functions,
that accept filtering values as parameters, I'm not sure, however, if this
will actually improve the performance. It's an option to consider and test.
ML
http://milambda.blogspot.com/|||"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143723585.520821.232940@.e56g2000cwe.googlegroups.com...
>I know that the optimiser will try, and that I can override with join
> hints, but in this case the optimiser can't help because the logic of
> the join forces it to be evaluated in that order. What I was hoping for
> was some kind of new way of laying out the query such that I could
> "enter" my data structure from different directions depending on which
> one has parameters defined to filter it. I suspect it's not possible,
> but doesn't anyone else find this a problem? is there some better way
> to either lay out the tables or to have filter procs with multiple
> optional parameters?
>
The problem is that SQL Server will pick one query plan and reuse it for all
parameter sets. If this is in a stored procedure, you could try to mark it
WITH RECOMPILE, or use dynamic SQL.
David|||You have hit one of the problems with SQL Server. It was built for a
"departmetn level" database on a small machine. It keeps one exectuion
plan for each stored procedure and uses that plan when the proc is
invoked. They added parameter sniffing, but that can actually hurt.
Better products, meant for enterprise level applications and VLDB hold
multiple plans (Is DB2 keeping 16 or 32 now? I do not remember). Thus
they know that when the query looks like this (I will use the @. for
parameters even tho that is not Standard SQL):
SELECT x.y.x
FROM Foobar AS F1
WHERE F1.a = COALESCE (@.p1, a)
AND F1.b = COALESCE (@.p2, b)
AND WHERE F1.c = COALESCE (@.p3, c)
SQL needs to generate 8 execution plans, one for each combination of
NULLs. The best one will be picked at run time when we know (@.p1, @.p2,
@.p3) and twher the NULLs are that will turn a search condition into a
constant TRUE, FALSE or UNKNOWN.
This is your solution #1 -- fake a good optimizer and RDBMS engine in
T-SQL by hand. You can do it with IF-THEN-ELSE control flow in T-SQL.
It is a XXXXX to maintain, but easy to write with a cut and paste in a
text editor. It can run much faster than what you have now, however.

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Hi guys,

just a simple question here: i have some input parameters on my report that are datetimes (i.e. the user gets the date picker to select the date), if i want to use that parameter in a MDX statement what will it look like? IOW would today's date look like the string "13/02/07", or would i be expecting a string like this: "2007/02/13 00:00"?

I am looking to convert some dropdowns that contain dates extracted from a cube heirarchy with the datepickers so i need to know what i have to change in the MDX to accomodate this.

I also filter the dates that appear in the current dropdowns, is there a way to do this with the datepickers (maybe by pointing them to a dataset of dates extracted from the cube)?

Thanks!

sluggy

This report sample may help.

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>aad8c909-02c6-4fb6-841c-0557ea327ec4</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Product].[Product Categories].[Category].&[1]</Value>

</Values>

</DefaultValue>

<Prompt>Product Categories</Prompt>

<DataSetName>ProductProductCategories</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaptionIndented</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<DataType>DateTime</DataType>

</Values>

</DefaultValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox16</rd:DefaultName>

<Style>

</Style>

<Value>Category Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox17</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox18</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>Category</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Category.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox13</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox14</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox15</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Category.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

<rd:DefaultName>textbox26</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox27</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox28</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Aggregate(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox21</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Subcategory</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Subcategory.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox23</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox24</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox25</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Subcategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox22</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Product</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Product.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!InternetSalesAmount.FormattedValue</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!ResellerSalesAmount.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<Value>Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

</Style>

<Value>Product</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Internet Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Reseller Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>03e1fd95-077c-4176-a8c7-4d09893efe4e</rd:ReportID>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<Value>=Parameters!ProductProductCategories.Value</Value>

</QueryParameter>

<Value>="[Date].[Date].[" & CDate(Parameters!DateDate.Value).ToString("MMMM d, yyyy") & "]"</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>AdventureWorksAS</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>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Internet Sales Amount</MeasureName><UniqueName>[Measures].[Internet Sales Amount]</UniqueName></ID><ItemCaption>Internet Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Reseller Sales Amount</MeasureName><UniqueName>[Measures].[Reseller Sales Amount]</UniqueName></ID><ItemCaption>Reseller Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Total Sales</MeasureName><UniqueName>[Measures].[Total Sales]</UniqueName></ID><ItemCaption>Total Sales</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions><ParameterDefinition><Name>ProductProductCategories</Name><DefaultValues><DefaultValue>[Product].[Product Categories].[Category].&[1]</DefaultValue></DefaultValues><Caption>Product Categories</Caption><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Product Categories].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition><ParameterDefinition><Name>DateDate</Name><DefaultValues><DefaultValue>[Date].[Date].[All Periods]</DefaultValue></DefaultValues><Caption>Date.Date</Caption><HierarchyUniqueName>[Date].[Date]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

</DataSet>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Value>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value</Value>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

|||

Thanks Teo, I'll pull that apart and have a look at it.

Cheers

sluggy

|||

Note that whatever you do with the date parameter, you need to comply with the AS member format, e.g. [Date][Day].[Day].&[20070213]. For the sample report, I think I pulled a little trick where I changed the Value property of the dimension key in the AW Date dimension to load the DateTime value. Also you can try:

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

Thursday, February 16, 2012

changing filename of PDF-Report

Hello,
we use the Report Services to generate bills from our orders. Therefor a
url with all the report parameters is created and send to the internet
explorer which displays the report with the pdf plugin (standard).
Our problem is that all reports have the same name (for example bill.pdf).
Is it possible to send a paramter (maybe a system one) which the report
server is using and named the file like it?
For example we give in the url &reportname=bill001 and then the file
generated names bill001.pdf?
Thanks for all answers.Hi Thomas,
We are using RS to generate pdf files, but we are just streaming directly
the resulting pdf to the client browser.
So, the result of the Render method of the ReportingServices object (which
is of type "array of bytes" ) gets passed to the browser using
Response.BinaryWrite(result).
I think you could use something to write the array of bytes to a pdf file -
and at this step you can choose the name you want for this pdf file.
Example:
Dim fs As New System.IO.FileStream("c:\customers\BILL001.pdf",
System.IO.FileMode.CreateNew)
' Create the writer for data.
Dim w As New System.io.BinaryWriter(fs)
' Write data to Test.data.
Dim b() As Byte
b = rs.Render(...parameters for report rendering)
w.Write(b)
w.Close()
fs.Close()
Hope this helps,
Andrei.
"Thomas Weiler" <Thomas.Weiler@.bigfoot.de> wrote in message
news:ec8HJOGPFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hello,
> we use the Report Services to generate bills from our orders. Therefor a
> url with all the report parameters is created and send to the internet
> explorer which displays the report with the pdf plugin (standard).
> Our problem is that all reports have the same name (for example bill.pdf).
> Is it possible to send a paramter (maybe a system one) which the report
> server is using and named the file like it?
> For example we give in the url &reportname=bill001 and then the file
> generated names bill001.pdf?
> Thanks for all answers.

Tuesday, February 14, 2012

Changing default parameters cause postback\refresh

I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.

Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?

Anyone know of a solution to this?|||

Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:

Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

Hi John,

I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.

So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?

Thanks!,

|||

John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.

Changing default parameters cause postback\refresh

Anyone know of a solution to this?|||

Hi John,

Thanks!,

|||

Changing default parameters cause postback\refresh

Anyone know of a solution to this?|||

Hi John,

Thanks!,

|||John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.

Changing default parameters cause postback\refresh

Anyone know of a solution to this?|||

Hi John,

Thanks!,

|||

Tuesday, March 27, 2012

Changing the report parameters or data sources to the values you specified is not allowed

Monday, March 19, 2012

Changing SQL in runtime

Wednesday, March 7, 2012

Changing Parameters Dynamically

Changing parameters causes refresh...

Changing parameters causes refresh...

Changing parameters causes refresh...

Changing Parameter Dropdown Size

Friday, February 24, 2012

Changing join order based on parameters

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Thursday, February 16, 2012

changing filename of PDF-Report

Tuesday, February 14, 2012

Changing default parameters cause postback\refresh

Changing default parameters cause postback\refresh

Changing default parameters cause postback\refresh

Changing default parameters cause postback\refresh

database collation

Blog Archive

About Me