Parent-Child Hierarchy but reversed(!)

Hi!

I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly.

However, instead of having it look like this(simple example):

+ Profit

After the + has been clicked:

-Profit
+Sales
+Cost

I would like it to look like this:

+Sales
+Cost
-Profit

Is this possible with when using a parent-child hierarchy?

http://ssasfreak.spaces.live.com/

Have you tried using the group footer row instead of the group header row to control visibility?|||

Yes. That only works when you have the diffrent levels in different fields. Like this:

Level1 Level2 Level2

I use the parentgroup property(for the grouping) with a recordset that looks like this:

LevelID LevelName ParentLevelID

Any ideas?

Parent-Child Hierarchies & User-defined hierarchies not appearing in Report Builder

Hi "Team SSAS",

I have this weird situation where Parent-Child hierarchies, and User-defined hierarchies show up in Visual Studio, but once I'vedeployed/processed the solution, they do not appear in Report Builder.

Is this a known product bug, or has someone else encountered this and have a solution, PLEASE?

Thanks in advance and in anticipation

Gregg Withers

gwithers atsymbol jhancock dotsymbol com

Hi Greg,

Report Builder can't handle parent/child hierarchies at all; with user-defined hierarchies it just shows the underlying attributes that make up each level if they themselves are visible. I know, it's a bit rubbish...

Take a look at this white paper for more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssrs_reportmodel.mspx

HTH,

Chris

|||

Chris,

Thanks for your hyperlink to the intro to report models.

I'm having a tough time swallowing that answer - It just doesn't make sense that parent-child hierarchies are not available for ad-hoc reporting. I can't think of any decent-sized organization that does not need an parent-child type org chart. Also, if your answer also pertains to user-defined hierarchies, I have the same response.

Anyone else run into this problem, please?

TIA

Gregg

Parent-Child Dimension in a Report

Hi,
I saw some threads about using parent-child dimensions in a reporting
services report on an OLAP cube in this newsgroup. I have the same problem
that I didn't get the nesting, I want, because reporting services didn't
render it. I tried with the UniqueName and ParentUniqueName properties, but
I only get a hierarchy of Level2 and no deeper. Does anyone knows a solution
in the meantime?
Thanks
HansI have managed to get round this problem by creating a virtual
dimension of level 3 and then doing a cross join on the 2 dimensions.
This will get you your nesting

Parent Expression

I have created a Report that has many Datasets and they each link to an individual table in the Report. I need the tables to only show the Data that relates to a Parent field on the Report.

E.G.

John Smith (parent)
Tasks 3
Task1
Task2
Task3
Appointments 10
... etc

Joe Bloggs (parent field)
Tasks 2
Task1
Task2
Appointments 5
... etc

I dont know how to make all the Datasets for the individual tables look at the name field. I can only do this if I use one Dataset or use a Parameter. I have tried using Lists but they dont seem to do what I need to do.

Thanks
NJA

What you are describing is a cross-dataset join. This is not natively supported in Reporting Services. You will either need to do the join in your query or use subreports (which are less efficient).

Parent child reports problems

Hi,
I have a report which I have created 3 or 4 child reports from.
If I make a change to the format of an object within the parent report
(e.g. the colour of a text box) then the change appears on the child
reports as I would expect.
The problem is that if I make a change to the properties of the report
(e.g. the margins or the page size) then the change is NOT picked up
in the child reports.
Does anybody have any solution to this annoying problem.
Regards,
JamesOn Nov 6, 4:33 am, joli...@.googlemail.com wrote:
> Hi,
> I have a report which I have created 3 or 4 child reports from.
> If I make a change to the format of an object within the parent report
> (e.g. the colour of a text box) then the change appears on the child
> reports as I would expect.
> The problem is that if I make a change to the properties of the report
> (e.g. the margins or the page size) then the change is NOT picked up
> in the child reports.
> Does anybody have any solution to this annoying problem.
> Regards,
> James
This might not be exactly what you're looking for, but you could use a
comparison tool to compare the RDL files side-by-side and merge the
changes pretty quickly. There's a trial version of a tool I like to
use here: http://www.araxis.com/merge/
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Parse dataset in report

I am using a "jump to report" that accepts multivalue parameters for fiscal periods. It works fine, returning the correct data. However, I want to add the chosen fiscal periods to the report header in a textbox. When I use

=Join(Parameters!DATEfiscalperiod.Value, ", ")

the results in the textbox show as:

[DATE].[fiscal_period].&[5], [DATE].[fiscal_period].&

When using::

=Parameters!DATEfiscalperiod.Value

results in

[rsInvalidExpressionDataType] The Value expression used in textbox ‘textbox5’ returned a data type that is not valid.

Is there a way to parse the dataset to only return "5, 6" in a string?

TIA

Hi, Takuma,

Try using the parameter label field instead:

=Join(Parameters!DATEfiscalperiod.Label, ", ")

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you Mary,

That works.

However, I also have a single value parameter. When I try to use this:

=Parameters!SALESPERSON.Label

no data is returned.

When I use:

=Parameters!SALESPERSONBDM.Value, I get this error:

[rsInvalidExpressionDataType] The Value expression used in textbox ‘textbox9’ returned a data type that is not valid.

Thank you for your help.

|||Is it possible to do this string concatenation in the sql that defines the dataset?
select field1 + ", " + field2 as fieldname
from ...
where ...

otherwise you can do similar things in the expression editor.

Parameters!Report_Parameter_0.Value.ToString() + ", " + Parameters!Report_Parameter_1.Value.ToString()|||

Thanks for the reply killerless,

Unfortunately, when I use

Parameters!Report_Parameter_0.Value.ToString()

it results in the whole dimension hierarchy structure: [SALESMGR].[]SALESPERSON.[NORTH]

I only want to show "NORTH".

Parameters!Report_Parameter_0.Value results in the same [SALESMGR].[SALESPERSON].[NORTH]

Parameters!Report_Parameter_0.:Label returns nothing.

It's also only a single value parameter.

Maybe I need to modify my mdx somehow but as you can see I'm a newbie.

|||So you have a parameter that is

[SALESMGR].[]SALESPERSON.[NORTH]?

|||

The parameter name that is passed is "salesperson".

I pass the parameter using "Jump to report". The parameter itself works fine, the data returned is correct. The only problem is displaying the chosen parameter in a user friendly format, (not in the mdx structure format).

The multivalue fiscal periods parameter works using the join().

|||Using the left or right functions you could reasonably easily filter out the unecessary text that is coming through to that textbox|||

Thank you for the help. That works.

Paremeter question

I have a report parameter: Customers
That parameter is used in main data set to pull data for the specified customer.
In the report header Iâ'd like to show that customerâ's name.
But because parameter has a customer code and not a name I dont know
the name of the customer that a user selects.
What is a solution here?Use the cutsomer code to query the customers name from the database, and
display that name in the header.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:A4AFE9B4-8598-42C0-B60B-873A37904B5F@.microsoft.com...
> I have a report parameter: Customers
> That parameter is used in main data set to pull data for the specified
customer.
> In the report header I'd like to show that customer's name.
> But because parameter has a customer code and not a name I dont know
> the name of the customer that a user selects.
> What is a solution here?
>

Paremeter Control Greyed out

I have a balance sheet report with only one parameter, Month.
The parameter is a query and the default for this parameter is a query to
determine the current month.
We schedule this report to run every hour because managers don't like to
wait 5 minutes for a report to generate. The problem is, we can't change the
parameter because it is greyed out. Is there a setting that would allow
people to choose a new month from the drop-down control even if the report is
on a schedule?You should be able to go into Report Manager and select Prompt User
checkbox. Or you can set the PromptUser property in the RDL to true.
--
| Thread-Topic: Paremeter Control Greyed out
| thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==| X-WBNR-Posting-Host: 12.38.198.125
| From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
| Subject: Paremeter Control Greyed out
| Date: Tue, 3 May 2005 07:30:19 -0700
| Lines: 10
| Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a balance sheet report with only one parameter, Month.
|
| The parameter is a query and the default for this parameter is a query to
| determine the current month.
|
| We schedule this report to run every hour because managers don't like to
| wait 5 minutes for a report to generate. The problem is, we can't change
the
| parameter because it is greyed out. Is there a setting that would allow
| people to choose a new month from the drop-down control even if the
report is
| on a schedule?
||||You are refering to PROPERTIES>PARAMETERS for the report itself correct?
It's already checked.
""Brad Syputa - MS"" wrote:
> You should be able to go into Report Manager and select Prompt User
> checkbox. Or you can set the PromptUser property in the RDL to true.
> --
> | Thread-Topic: Paremeter Control Greyed out
> | thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==> | X-WBNR-Posting-Host: 12.38.198.125
> | From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
> | Subject: Paremeter Control Greyed out
> | Date: Tue, 3 May 2005 07:30:19 -0700
> | Lines: 10
> | Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a balance sheet report with only one parameter, Month.
> |
> | The parameter is a query and the default for this parameter is a query to
> | determine the current month.
> |
> | We schedule this report to run every hour because managers don't like to
> | wait 5 minutes for a report to generate. The problem is, we can't change
> the
> | parameter because it is greyed out. Is there a setting that would allow
> | people to choose a new month from the drop-down control even if the
> report is
> | on a schedule?
> |
>

Paramters with Multiple Values

I know I must be missing something obvious but I cannot find it in the
documentation. How do I allow the user to input multiple values for a report
parameter. For example, the main query has this for test criteria:
where CustID in(100, 200, 300)
so I changed to:
IN(@.CustId) expecting to input 100,200,300
but instead I get an error "Application uses a value of the wrong type for
the current operation"
? ThanksTo do that you have to write dynamic sql and your parameter has to be a
varchar... the reason you are getting an error is you are comparing an
INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
"100,200,300" to integers.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> I know I must be missing something obvious but I cannot find it in the
> documentation. How do I allow the user to input multiple values for a
report
> parameter. For example, the main query has this for test criteria:
> where CustID in(100, 200, 300)
> so I changed to:
> IN(@.CustId) expecting to input 100,200,300
> but instead I get an error "Application uses a value of the wrong type for
> the current operation"
> ? Thanks
>|||So does that mean if it were a varchar column it would accept the commas and
treat them as delimiters between separate values ie 100 OR 200 OR 300?
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> To do that you have to write dynamic sql and your parameter has to be a
> varchar... the reason you are getting an error is you are comparing an
> INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> "100,200,300" to integers.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
>> I know I must be missing something obvious but I cannot find it in the
>> documentation. How do I allow the user to input multiple values for a
> report
>> parameter. For example, the main query has this for test criteria:
>> where CustID in(100, 200, 300)
>> so I changed to:
>> IN(@.CustId) expecting to input 100,200,300
>> but instead I get an error "Application uses a value of the wrong type
>> for
>> the current operation"
>> ? Thanks
>>
>|||No, it would treat it as one string... the easiest way to do a query with a
parameter that contains multiple integer values is dynamic sql like...
DECLARE @.CustID VARCHAR(100)
DECLARE @.sql VARCHAR(1000)
SET @.CustID = '100,200,300'
SELECT @.sql = 'SELECT * FROM Customer WHERE CustID IN (' + @.CustID + ')'
EXEC(@.SQL)
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:O1XluJDyEHA.2212@.TK2MSFTNGP15.phx.gbl...
> So does that mean if it were a varchar column it would accept the commas
and
> treat them as delimiters between separate values ie 100 OR 200 OR 300?
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> > To do that you have to write dynamic sql and your parameter has to be a
> > varchar... the reason you are getting an error is you are comparing an
> > INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> > "100,200,300" to integers.
> >
> >
> > "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> > news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> >> I know I must be missing something obvious but I cannot find it in the
> >> documentation. How do I allow the user to input multiple values for a
> > report
> >> parameter. For example, the main query has this for test criteria:
> >> where CustID in(100, 200, 300)
> >> so I changed to:
> >> IN(@.CustId) expecting to input 100,200,300
> >> but instead I get an error "Application uses a value of the wrong type
> >> for
> >> the current operation"
> >>
> >> ? Thanks
> >>
> >>
> >
> >
>

Paramter posting back?

I have a report with four paramters.
A "Start Year" and "End Year" that are strings with values from a query, so
available valuse are 2003,2004. 2005, and 2006.
Two datetime for a "Start Transaction Date" and "End Transaction Date".
When I set the "Start Year" parameter, the report seems to post back and I
get the error:
The value provided for the report parameter 'TransStart' is not valid
for its type. (rsReportParameterTypeMismatch) Get Online Help
The paramter was empty, so of course it is not valid. How do I stop this
behavior? The paramters are not dependant on each other and do not use
dynamic SQL.
Any help would be appreciated.
Thanks Shannondid you set allow blank for the parameter that you allow it to be empty?

Paramter Passing

Is it possible to pass variables/parameters to a report other thank through
a url using ASP, as this will compromise our security ?
Thanks in advanceTwo options. 1. Use web services. 2. Pull the sensitive parameters by using
the global user!userid. This value tells you who is running the report and
you can then use that to query the database to find out the sensitive
parameters. You might need to do something like having a table with the
username, report and sensitive parameters that your write to before your
application calls the report. And, as I said, you can also use web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Con" <conh@.melbournehosting.com> wrote in message
news:u8z7eiPvEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is it possible to pass variables/parameters to a report other thank
through
> a url using ASP, as this will compromise our security ?
> Thanks in advance
>

Parametrized Report with Analysis Services didn't work

Y made 2 report with parametrized MDX queries and they only work with the default values. But when I select some values from the combos with legal values the report is empty.
The weird thing is when I test the report in visual studio it works really fine. But when I go from http://somemachine/reports it fails.
Can anybody try this?
The MDX querie is ok. I have a lot of experience with MDX in SQL 2000.
It says something like this.

WITH
MEMBER [Measures].[Caida] AS 'IIF([Measures].[Variacion]<-0.5, [Measures].[Variacion], NULL)'
SELECT
NON EMPTY { [Measures].[Caida]} ON COLUMNS,
NON EMPTY { STRTOMEMBER(@.Grupo).CHILDREN } ON ROWS
FROM Ventas
WHERE ( {STRTOMEMBER(@.Mes)} )

I made others easier queries with the same behavior of the empty report.
Can anybody help me or know what happend?
Thanks in advanceCan you post an RDL / query that runs against the AdventureWorks sample cube and exposes the same issue?
Additional questions:
* Which build of BI Development Studio are you running?
* Which build of RS 2005 is running on the report server?
* Which build of AS 2005 is running on the AS server?

-- Robert|||

I'm not using adventure works. I'm using all of my own. But if you see the query is really simple (no tricks). Can you help me?

<?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="OLAP_INSA">

<rd:DataSourceID>b60468c5-c60b-47e2-942d-eb4686e92533</rd:DataSourceID>

<DataSourceReference>OLAP_INSA</DataSourceReference>

</DataSource>

</DataSources>

<InteractiveHeight>11in</InteractiveHeight>

<ReportParameters>

<ReportParameter Name="Anio">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>[Fecha].[Standard].[Anio].&amp;[15]</Value>

</Values>

</DefaultValue>

<Prompt>A¤o</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>Anio</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaption</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

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

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

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

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

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

<ZIndex>1</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<BorderColor>

<Bottom>Black</Bottom>

</BorderColor>

<BorderWidth>

<Bottom>3pt</Bottom>

</BorderWidth>

<PaddingTop>2pt</PaddingTop>

<FontSize>18pt</FontSize>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<BackgroundColor>DeepSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Height>0.8381cm</Height>

<Value>Caida de las Ventas respecto a¤o anterior</Value>

</Textbox>

<List Name="List1">

<Width>14.07936cm</Width>

<Grouping Name="list1_Anio">

<GroupExpressions>

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

</GroupExpressions>

<PageBreakAtEnd>true</PageBreakAtEnd>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!Anio.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<DataSetName>OLAP_INSA</DataSetName>

<ReportItems>

<Textbox Name="Anio">

<Width>12.69841cm</Width>

<rd:DefaultName>Anio</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>900</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<FontSize>18pt</FontSize>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Height>0.8381cm</Height>

<Value>="A¤o: "+Fields!Anio.Value</Value>

</Textbox>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>3.5cm</Width>

</MatrixColumn>

</MatrixColumns>

<Width>7cm</Width>

<DataSetName>OLAP_INSA</DataSetName>

<RowGroupings>

<RowGrouping>

<DynamicRows>

<Grouping Name="matrix1_Grupo">

<GroupExpressions>

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

</GroupExpressions>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!Grupo.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<ReportItems>

<Textbox Name="Grupo">

<rd:DefaultName>Grupo</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</DynamicRows>

<Width>3.5cm</Width>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<Height>0.60952cm</Height>

<DynamicColumns>

<Grouping Name="matrix1_Mes">

<GroupExpressions>

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

</GroupExpressions>

</Grouping>

<ReportItems>

<Textbox Name="Mes">

<rd:DefaultName>Mes</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</DynamicColumns>

</ColumnGrouping>

<ColumnGrouping>

<Height>0.53333cm</Height>

<StaticColumns>

<StaticColumn>

<ReportItems>

<Textbox Name="textbox8">

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

<ZIndex>2</ZIndex>

<Style>

<FontWeight>600</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<PaddingTop>2pt</PaddingTop>

<FontSize>9pt</FontSize>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Right</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>Ca?da mas de 25%</Value>

</Textbox>

</ReportItems>

</StaticColumn>

</StaticColumns>

</ColumnGrouping>

</ColumnGroupings>

<MatrixRows>

<MatrixRow>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Textbox Name="Caida_mas_de_25_">

<rd:DefaultName>Caida_mas_de_25_</rd:DefaultName>

<Action>

<Drillthrough>

<ReportName>Caida Ventas por Producto</ReportName>

<Parameters>

<Parameter Name="Mes">

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

</Parameter>

<Parameter Name="Grupo">

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

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<ToolTip>Ver detalle caida de las ventas para este mes y este grupo</ToolTip>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<TextDecoration>Underline</TextDecoration>

<PaddingTop>2pt</PaddingTop>

<Format>#.00%</Format>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<Color>Blue</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

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

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

<Height>0.60952cm</Height>

</MatrixRow>

</MatrixRows>

<Top>1.26984cm</Top>

<Corner>

<ReportItems>

<Textbox Name="textbox5">

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

<ZIndex>4</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

</ReportItems>

</Corner>

<Style />

</Matrix>

</ReportItems>

<Top>0.8381cm</Top>

<Style>

<FontWeight>900</FontWeight>

<FontFamily>Times New Roman</FontFamily>

<FontSize>18pt</FontSize>

<Color>Maroon</Color>

</Style>

</List>

</ReportItems>

<Height>3.86032cm</Height>

<Style />

</Body>

<LeftMargin>2.5cm</LeftMargin>

<BottomMargin>2.5cm</BottomMargin>

<rd:ReportID>bc8342a4-de2c-4d93-ab3c-f257505a4aa9</rd:ReportID>

<PageWidth>21cm</PageWidth>

<DataSets>

<DataSet Name="OLAP_INSA">

<Fields>

<Field Name="Anio">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Anio]" /&gt;</DataField>

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

</Field>

<Field Name="Mes">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Mes]" /&gt;</DataField>

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

</Field>

<Field Name="Grupo">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Producto].[Grupo].[Grupo]" /&gt;</DataField>

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

</Field>

<Field Name="Caida_mas_de_25_">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Caida mas de 25%]" /&gt;</DataField>

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

</Field>

</Fields>

<Query>

<DataSourceName>OLAP_INSA</DataSourceName>

<CommandText>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]&lt;-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</CommandText>

<QueryParameters>

<QueryParameter Name="Anio">

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

</QueryParameter>

</QueryParameters>

<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>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Mes</LevelName><UniqueName>[Fecha].[Standard].[Mes]</UniqueName></ID><ItemCaption>Mes</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Producto</DimensionName><HierarchyName>Grupo</HierarchyName><HierarchyUniqueName>[Producto].[Grupo]</HierarchyUniqueName><LevelName>Grupo</LevelName><UniqueName>[Producto].[Grupo].[Grupo]</UniqueName></ID><ItemCaption>Grupo</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Caida mas de 25%</MeasureName><UniqueName>[Measures].[Caida mas de 25%]</UniqueName></ID><ItemCaption>Caida mas de 25%</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]&lt;-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</Statement><ParameterDefinitions><ParameterDefinition><Name>Anio</Name><DefaultValues><DefaultValue>[Fecha].[Standard].[Anio].&amp;[15]</DefaultValue></DefaultValues><Caption>Anio</Caption><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Fecha].[Standard].ALLMEMBERS ON ROWS FROM [Ventas]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

<DataSet Name="Anio">

<Fields>

<Field Name="Anio">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Anio]" /&gt;</DataField>

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

</Field>

<Field Name="ParameterCaption">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" /&gt;</DataField>

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

</Field>

<Field Name="ParameterValue">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterValue]" /&gt;</DataField>

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

</Field>

<Field Name="ParameterLevel">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterLevel]" /&gt;</DataField>

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

</Field>

</Fields>

<Query>

<DataSourceName>OLAP_INSA</DataSourceName>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</CommandText>

<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>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterCaption</MeasureName><UniqueName>[Measures].[ParameterCaption]</UniqueName></ID><ItemCaption>ParameterCaption</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterValue</MeasureName><UniqueName>[Measures].[ParameterValue]</UniqueName></ID><ItemCaption>ParameterValue</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterLevel</MeasureName><UniqueName>[Measures].[ParameterLevel]</UniqueName></ID><ItemCaption>ParameterLevel</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

</DataSets>

<RightMargin>2.5cm</RightMargin>

<Width>16cm</Width>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

|||After 2 days of research and kicking my desktop.
I found finnally that to build parameters you must not use UNIQUENAME. Instead use MEMBER [Measures].[ParameterValue] AS ' "[DimensionName].["+[DimensionName].CURRENTMEMBER.NAME+"]" '
It's works fine.
I think there is a bug using STRTOMEMBER(@.parameter) when @.parameter is in the UNIQUENAME form.
I hope it helps you too.|||I edit the previous post where i said that you can only put dimensions on columns. That's wrong, i'm using dimension in columns and rows and works really fine.

Parametric

Hello,

I'm using SQL 2005 and reporting services.

I have several parameters, the report show two parameters in the same line and I would like to present three parameters in the same line.

how can I do that ?

This is not natively supported.

The closest you can get is to write your own parameter frontend application and then e.g. use the RS report viewer controls to send the parameters to the report server and execute the report in "remote" mode. Other options include to send the parameters via URL-access from your custom frontend application.

-- Robert

Parameters: Users must choose one or both parameters

I have two date parameters in my report. The user must make a selction on at
least one of the two parameters (either one), but does not have to make a
selection on both.
How do I verify that at least one parameters has a value? I guess this must
be verified on a pre-Execute event on the View Report button.
Thanks in advance for your help.Allow Null on both dates. Then in your sproc (which is essentially what the
query turns into) check both are not null, else raise an error.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
|I have two date parameters in my report. The user must make a selction on
at
| least one of the two parameters (either one), but does not have to make a
| selection on both.
|
| How do I verify that at least one parameters has a value? I guess this
must
| be verified on a pre-Execute event on the View Report button.
|
| Thanks in advance for your help.|||Do I understand you correct when I sum up your answer like this:
In the receiving procedure on the SQL Server I will have to check if at
least one of the two parameters has a value. If not I raise an error in the
stored procedure. This error is automatically caught by Reporting Services
and displayed to the end user.
"William Stacey [MVP]" wrote:
> Allow Null on both dates. Then in your sproc (which is essentially what the
> query turns into) check both are not null, else raise an error.
> --
> William Stacey [MVP]
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
> |I have two date parameters in my report. The user must make a selction on
> at
> | least one of the two parameters (either one), but does not have to make a
> | selection on both.
> |
> | How do I verify that at least one parameters has a value? I guess this
> must
> | be verified on a pre-Execute event on the View Report button.
> |
> | Thanks in advance for your help.
>
>|||Right. And the report page will display the error text as you entered it.
A dialog box would be better (maybe), but that is what we have.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:9A2E1E77-BC99-466C-A856-F4DD48DD2B64@.microsoft.com...
| Do I understand you correct when I sum up your answer like this:
|
| In the receiving procedure on the SQL Server I will have to check if at
| least one of the two parameters has a value. If not I raise an error in
the
| stored procedure. This error is automatically caught by Reporting Services
| and displayed to the end user.
|
|
|
|
| "William Stacey [MVP]" wrote:
|
| > Allow Null on both dates. Then in your sproc (which is essentially what
the
| > query turns into) check both are not null, else raise an error.
| >
| > --
| > William Stacey [MVP]
| >
| > "Billy" <Billy@.discussions.microsoft.com> wrote in message
| > news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
| > |I have two date parameters in my report. The user must make a selction
on
| > at
| > | least one of the two parameters (either one), but does not have to
make a
| > | selection on both.
| > |
| > | How do I verify that at least one parameters has a value? I guess this
| > must
| > | be verified on a pre-Execute event on the View Report button.
| > |
| > | Thanks in advance for your help.
| >
| >
| >

Parameters...HELP!

I am using 6 parameters in a report. If I set the Where clauses to OR,
the report will return everything (understood) but it doesnt work when
I set it to AND (how it should be). By themselves, each parameter
works. But when you combine all of them or even 2, nothing returns. Am
I missing a simple step?
Also, I wanted some parameters to be "optional"-dont have to have a
value in them (is this possible with report designer?). I've tried
setting the "optional" ones to null.
I even tried to use SQL profiler to try and view the sql being executed
but to no avail.
Any suggestions would be greatly appreciated!
Thanks!gte401:
Does the query work correct in Query Designer? Typically, I like to design
my SQL code there and then paste it into the report query after it's been
debugged. If it's working correctly then you should get the same results.
As for six parameters, I have a few with up to four with no problems.
Regarding your question about leaving parameters NULL. If you do, then
you'll definitely want to make sure you've accommodated the NULL in your
WHERE statement. You could use an ISNULL function to convert it to '%' and
do a LIKE. For example:
WHERE myField LIKE '%' + RTRIM(LTRIM(ISNULL(@.TEST,''))) + '%'
Hope this helps! I'm sure someone may have an easier way to do this, but
for now it works for me.
Patrick|||Open the report up in Visual Studio.
Go to Report => parameters.
You can specify if the value of a parameter may be NULL/Empty.
hope this helps
Regards,
Stas K.|||When you select the Null Function for say Last Name, does the query
skips this parameter or does it look for Last Names with Null?
Thanks
Kevin|||Patrick,
My SQL runs. Its just when I add the Where clause, it does not work.
This is what Business Intelligence Studio generated:
WHERE
(Person.Status LIKE @.Status) AND (PersonType.PersonType_Desc =PersonType.PersonType_Desc) AND (Methodology.Methodology_Desc IN
(@.Methodology_Desc)) AND (Certification.Certification_Desc IN
(@.Certification_Desc)) AND (Person.Last_Name = @.Last_Name) AND
(Skill.Skill_Desc IN (@.Skill_Desc)) AND (Person.First_Name =@.First_Name)
When you run it, it will prompt "Parameters for Query" I set everything
to null.
I then go to the preview tab. Enter the desired values in my search
page, and nothing appears.|||This also works:
and myfield = ISNULL(@.myparameter, myfield)
... if @.myparameter isnull then statement is myfield = myfield, which is
always true
"gte401e" wrote:
> I am using 6 parameters in a report. If I set the Where clauses to OR,
> the report will return everything (understood) but it doesnt work when
> I set it to AND (how it should be). By themselves, each parameter
> works. But when you combine all of them or even 2, nothing returns. Am
> I missing a simple step?
> Also, I wanted some parameters to be "optional"-dont have to have a
> value in them (is this possible with report designer?). I've tried
> setting the "optional" ones to null.
> I even tried to use SQL profiler to try and view the sql being executed
> but to no avail.
> Any suggestions would be greatly appreciated!
> Thanks!
>|||Dterrie,
Thanks for the help!
But one more question....I have several multi value parameters
=IN(@.Example) . I know you cant set them to Null. Is there a way to
make them optional though? Or can I incorporate =IN(@.Example) into the
statement you gave me? If so, how?
Thanks in advance!
Kevin

Parameters with ODBC server

Hi all,
I have to build a report from SQL tables thru an ODBC server. When I
try to pass (nonnamed) parameters to the report, the SQL request runs
but returns nothing, as if the value was not understood. I have put
"?" in the SQL request in place of the parameter.
Do I need to do something else ? What is the format of the capture in
the run-time parameter dialog box : are quotes needed, double quotes,
or other character ?
Thanks ODBC users !
Jean-MarcI'm not sure what you mean by non-named parameters. RS uses the @. format
for parameters in queries, like so:
... WHERE FieldName = @.ParameterName
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> Hi all,
> I have to build a report from SQL tables thru an ODBC server. When I
> try to pass (nonnamed) parameters to the report, the SQL request runs
> but returns nothing, as if the value was not understood. I have put
> "?" in the SQL request in place of the parameter.
> Do I need to do something else ? What is the format of the capture in
> the run-time parameter dialog box : are quotes needed, double quotes,
> or other character ?
> Thanks ODBC users !
> Jean-Marc|||Umm, no it does not. It uses that for SQL Server but otherwise it needs
unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
It sounds to me like you are trying to put in the values in the dialog box
that pops up with you hit the exclamation point. When prompted by the dialog
box do not put in any quotes (single or double) just put the value in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you mean by non-named parameters. RS uses the @. format
> for parameters in queries, like so:
> ... WHERE FieldName = @.ParameterName
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > Hi all,
> >
> > I have to build a report from SQL tables thru an ODBC server. When I
> > try to pass (nonnamed) parameters to the report, the SQL request runs
> > but returns nothing, as if the value was not understood. I have put
> > "?" in the SQL request in place of the parameter.
> > Do I need to do something else ? What is the format of the capture in
> > the run-time parameter dialog box : are quotes needed, double quotes,
> > or other character ?
> >
> > Thanks ODBC users !
> >
> > Jean-Marc
>|||Thanks for the correction, Bruce.
Rats! Now I've got more work to do when we port to Oracle.
...sigh...
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Umm, no it does not. It uses that for SQL Server but otherwise it needs
> unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> It sounds to me like you are trying to put in the values in the dialog box
> that pops up with you hit the exclamation point. When prompted by the
> dialog
> box do not put in any quotes (single or double) just put the value in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you mean by non-named parameters. RS uses the @. format
>> for parameters in queries, like so:
>> ... WHERE FieldName = @.ParameterName
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
>> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
>> > Hi all,
>> >
>> > I have to build a report from SQL tables thru an ODBC server. When I
>> > try to pass (nonnamed) parameters to the report, the SQL request runs
>> > but returns nothing, as if the value was not understood. I have put
>> > "?" in the SQL request in place of the parameter.
>> > Do I need to do something else ? What is the format of the capture in
>> > the run-time parameter dialog box : are quotes needed, double quotes,
>> > or other character ?
>> >
>> > Thanks ODBC users !
>> >
>> > Jean-Marc
>>
>|||I'm not sure about Oracle. Oracle might be the same and it might not. The
reason is that Oracle is a special case. Here is a tidbit for you. Only
Oracle and SQL Server use the dotnet managed provider in RS. But, wait, it
is not that simple. When you use the GUI query designer it uses the OLEDB
provider because the GUI query designer knows nothing about dotnet. But, if
you go to the generic designer it uses the managed provider. At runtime it
uses the managed provider. The reason I mention this is two fold. First, so
all testing is done with the same provider as used at runtime I would
recommend always going to the generic query designer. Second, I'm not sure
whether managed provider uses named parameters or not. If it does then you
will not have to change this.
One last point, the next version should have a query designer that knows
about dotnet (V2, not SP2).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Thanks for the correction, Bruce.
> Rats! Now I've got more work to do when we port to Oracle.
> ...sigh...
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > Umm, no it does not. It uses that for SQL Server but otherwise it needs
> > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> >
> > It sounds to me like you are trying to put in the values in the dialog
box
> > that pops up with you hit the exclamation point. When prompted by the
> > dialog
> > box do not put in any quotes (single or double) just put the value in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> I'm not sure what you mean by non-named parameters. RS uses the @.
format
> >> for parameters in queries, like so:
> >>
> >> ... WHERE FieldName = @.ParameterName
> >>
> >> --
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> >> > Hi all,
> >> >
> >> > I have to build a report from SQL tables thru an ODBC server. When I
> >> > try to pass (nonnamed) parameters to the report, the SQL request runs
> >> > but returns nothing, as if the value was not understood. I have put
> >> > "?" in the SQL request in place of the parameter.
> >> > Do I need to do something else ? What is the format of the capture in
> >> > the run-time parameter dialog box : are quotes needed, double quotes,
> >> > or other character ?
> >> >
> >> > Thanks ODBC users !
> >> >
> >> > Jean-Marc
> >>
> >>
> >
> >
>|||The syntax used by the managed Oracle provider to mark named parameters is a
colon (":") instead of @.. This is nothing specific to RS, this is just how
the data provider works.
E.g., select * from emp where deptno = :Dept
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm not sure about Oracle. Oracle might be the same and it might not. The
> reason is that Oracle is a special case. Here is a tidbit for you. Only
> Oracle and SQL Server use the dotnet managed provider in RS. But, wait, it
> is not that simple. When you use the GUI query designer it uses the OLEDB
> provider because the GUI query designer knows nothing about dotnet. But,
if
> you go to the generic designer it uses the managed provider. At runtime
it
> uses the managed provider. The reason I mention this is two fold. First,
so
> all testing is done with the same provider as used at runtime I would
> recommend always going to the generic query designer. Second, I'm not sure
> whether managed provider uses named parameters or not. If it does then you
> will not have to change this.
> One last point, the next version should have a query designer that knows
> about dotnet (V2, not SP2).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > Thanks for the correction, Bruce.
> >
> > Rats! Now I've got more work to do when we port to Oracle.
> >
> > ...sigh...
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > > Umm, no it does not. It uses that for SQL Server but otherwise it
needs
> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > >
> > > It sounds to me like you are trying to put in the values in the dialog
> box
> > > that pops up with you hit the exclamation point. When prompted by the
> > > dialog
> > > box do not put in any quotes (single or double) just put the value in.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
> format
> > >> for parameters in queries, like so:
> > >>
> > >> ... WHERE FieldName = @.ParameterName
> > >>
> > >> --
> > >> '(' Jeff A. Stucker
> > >> \
> > >>
> > >> Business Intelligence
> > >> www.criadvantage.com
> > >> ---
> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > >> > Hi all,
> > >> >
> > >> > I have to build a report from SQL tables thru an ODBC server. When
I
> > >> > try to pass (nonnamed) parameters to the report, the SQL request
runs
> > >> > but returns nothing, as if the value was not understood. I have put
> > >> > "?" in the SQL request in place of the parameter.
> > >> > Do I need to do something else ? What is the format of the capture
in
> > >> > the run-time parameter dialog box : are quotes needed, double
quotes,
> > >> > or other character ?
> > >> >
> > >> > Thanks ODBC users !
> > >> >
> > >> > Jean-Marc
> > >>
> > >>
> > >
> > >
> >
> >
>|||So the big question is: What's the best way to manage reports that need to
work against both SQL Server and Oracle databases, depending where they are
deployed? (The database structure will be identical, but the platform is
customer-specific.)
Thanks,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> The syntax used by the managed Oracle provider to mark named parameters is
> a
> colon (":") instead of @.. This is nothing specific to RS, this is just how
> the data provider works.
> E.g., select * from emp where deptno = :Dept
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> I'm not sure about Oracle. Oracle might be the same and it might not. The
>> reason is that Oracle is a special case. Here is a tidbit for you. Only
>> Oracle and SQL Server use the dotnet managed provider in RS. But, wait,
>> it
>> is not that simple. When you use the GUI query designer it uses the OLEDB
>> provider because the GUI query designer knows nothing about dotnet. But,
> if
>> you go to the generic designer it uses the managed provider. At runtime
> it
>> uses the managed provider. The reason I mention this is two fold. First,
> so
>> all testing is done with the same provider as used at runtime I would
>> recommend always going to the generic query designer. Second, I'm not
>> sure
>> whether managed provider uses named parameters or not. If it does then
>> you
>> will not have to change this.
>> One last point, the next version should have a query designer that knows
>> about dotnet (V2, not SP2).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > Thanks for the correction, Bruce.
>> >
>> > Rats! Now I've got more work to do when we port to Oracle.
>> >
>> > ...sigh...
>> > --
>> > '(' Jeff A. Stucker
>> > \
>> >
>> > Business Intelligence
>> > www.criadvantage.com
>> > ---
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> needs
>> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
>> > >
>> > > It sounds to me like you are trying to put in the values in the
>> > > dialog
>> box
>> > > that pops up with you hit the exclamation point. When prompted by the
>> > > dialog
>> > > box do not put in any quotes (single or double) just put the value
>> > > in.
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
>> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
>> format
>> > >> for parameters in queries, like so:
>> > >>
>> > >> ... WHERE FieldName = @.ParameterName
>> > >>
>> > >> --
>> > >> '(' Jeff A. Stucker
>> > >> \
>> > >>
>> > >> Business Intelligence
>> > >> www.criadvantage.com
>> > >> ---
>> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
>> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
>> > >> > Hi all,
>> > >> >
>> > >> > I have to build a report from SQL tables thru an ODBC server. When
> I
>> > >> > try to pass (nonnamed) parameters to the report, the SQL request
> runs
>> > >> > but returns nothing, as if the value was not understood. I have
>> > >> > put
>> > >> > "?" in the SQL request in place of the parameter.
>> > >> > Do I need to do something else ? What is the format of the capture
> in
>> > >> > the run-time parameter dialog box : are quotes needed, double
> quotes,
>> > >> > or other character ?
>> > >> >
>> > >> > Thanks ODBC users !
>> > >> >
>> > >> > Jean-Marc
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>|||Hi Jeff,
Did you get any answers to your big question? I find myself in the exact
same situation (except its either Sql Server or Sybase)..I was just curious
what you did to solve this problem...
Thanks...
--Aparna.
"Jeff A. Stucker" wrote:
> So the big question is: What's the best way to manage reports that need to
> work against both SQL Server and Oracle databases, depending where they are
> deployed? (The database structure will be identical, but the platform is
> customer-specific.)
> Thanks,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > The syntax used by the managed Oracle provider to mark named parameters is
> > a
> > colon (":") instead of @.. This is nothing specific to RS, this is just how
> > the data provider works.
> > E.g., select * from emp where deptno = :Dept
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> >> I'm not sure about Oracle. Oracle might be the same and it might not. The
> >> reason is that Oracle is a special case. Here is a tidbit for you. Only
> >> Oracle and SQL Server use the dotnet managed provider in RS. But, wait,
> >> it
> >> is not that simple. When you use the GUI query designer it uses the OLEDB
> >> provider because the GUI query designer knows nothing about dotnet. But,
> > if
> >> you go to the generic designer it uses the managed provider. At runtime
> > it
> >> uses the managed provider. The reason I mention this is two fold. First,
> > so
> >> all testing is done with the same provider as used at runtime I would
> >> recommend always going to the generic query designer. Second, I'm not
> >> sure
> >> whether managed provider uses named parameters or not. If it does then
> >> you
> >> will not have to change this.
> >>
> >> One last point, the next version should have a query designer that knows
> >> about dotnet (V2, not SP2).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> >> > Thanks for the correction, Bruce.
> >> >
> >> > Rats! Now I've got more work to do when we port to Oracle.
> >> >
> >> > ...sigh...
> >> > --
> >> > '(' Jeff A. Stucker
> >> > \
> >> >
> >> > Business Intelligence
> >> > www.criadvantage.com
> >> > ---
> >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > needs
> >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> >> > >
> >> > > It sounds to me like you are trying to put in the values in the
> >> > > dialog
> >> box
> >> > > that pops up with you hit the exclamation point. When prompted by the
> >> > > dialog
> >> > > box do not put in any quotes (single or double) just put the value
> >> > > in.
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > >
> >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
> >> format
> >> > >> for parameters in queries, like so:
> >> > >>
> >> > >> ... WHERE FieldName = @.ParameterName
> >> > >>
> >> > >> --
> >> > >> '(' Jeff A. Stucker
> >> > >> \
> >> > >>
> >> > >> Business Intelligence
> >> > >> www.criadvantage.com
> >> > >> ---
> >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> >> > >> > Hi all,
> >> > >> >
> >> > >> > I have to build a report from SQL tables thru an ODBC server. When
> > I
> >> > >> > try to pass (nonnamed) parameters to the report, the SQL request
> > runs
> >> > >> > but returns nothing, as if the value was not understood. I have
> >> > >> > put
> >> > >> > "?" in the SQL request in place of the parameter.
> >> > >> > Do I need to do something else ? What is the format of the capture
> > in
> >> > >> > the run-time parameter dialog box : are quotes needed, double
> > quotes,
> >> > >> > or other character ?
> >> > >> >
> >> > >> > Thanks ODBC users !
> >> > >> >
> >> > >> > Jean-Marc
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>|||I have the same issue. I have a whole lot of reports that are going against
a process control database in Sybase and will be switched to go against a
datamart in SQL Server. I would love to have an easy way to switch it back
if I needed to. Putting aside having compatible SQL (Sybase does not have
Top and some other SQL) I don't see an easy way to do this. I plan on
manually changing the reports by going into the RDL and changing it (rather
than doing it from the IDE). The only way I can see this would work is if
you make your dataset sql be based on an expression. You can use the generic
query screen and do this:
= "Select * from something where somefield = " & Parameters!ParamName.value
But if you have a date or character field you have to do this:
= "Select * from something where somefield = '" & Parameters!ParamName.value
& "'" (that is a double quote, a single quote, a double quote)
So it is slow and potentially buggy. When done though if the SQL created is
compatible this will work against any databases.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C4540199-F9D9-4D37-A491-BC5EE4AEE91A@.microsoft.com...
> Hi Jeff,
> Did you get any answers to your big question? I find myself in the exact
> same situation (except its either Sql Server or Sybase)..I was just
curious
> what you did to solve this problem...
> Thanks...
> --Aparna.
> "Jeff A. Stucker" wrote:
> > So the big question is: What's the best way to manage reports that need
to
> > work against both SQL Server and Oracle databases, depending where they
are
> > deployed? (The database structure will be identical, but the platform
is
> > customer-specific.)
> >
> > Thanks,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > > The syntax used by the managed Oracle provider to mark named
parameters is
> > > a
> > > colon (":") instead of @.. This is nothing specific to RS, this is just
how
> > > the data provider works.
> > > E.g., select * from emp where deptno = :Dept
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> > >> I'm not sure about Oracle. Oracle might be the same and it might not.
The
> > >> reason is that Oracle is a special case. Here is a tidbit for you.
Only
> > >> Oracle and SQL Server use the dotnet managed provider in RS. But,
wait,
> > >> it
> > >> is not that simple. When you use the GUI query designer it uses the
OLEDB
> > >> provider because the GUI query designer knows nothing about dotnet.
But,
> > > if
> > >> you go to the generic designer it uses the managed provider. At
runtime
> > > it
> > >> uses the managed provider. The reason I mention this is two fold.
First,
> > > so
> > >> all testing is done with the same provider as used at runtime I would
> > >> recommend always going to the generic query designer. Second, I'm not
> > >> sure
> > >> whether managed provider uses named parameters or not. If it does
then
> > >> you
> > >> will not have to change this.
> > >>
> > >> One last point, the next version should have a query designer that
knows
> > >> about dotnet (V2, not SP2).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > >> > Thanks for the correction, Bruce.
> > >> >
> > >> > Rats! Now I've got more work to do when we port to Oracle.
> > >> >
> > >> > ...sigh...
> > >> > --
> > >> > '(' Jeff A. Stucker
> > >> > \
> > >> >
> > >> > Business Intelligence
> > >> > www.criadvantage.com
> > >> > ---
> > >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > > needs
> > >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > >> > >
> > >> > > It sounds to me like you are trying to put in the values in the
> > >> > > dialog
> > >> box
> > >> > > that pops up with you hit the exclamation point. When prompted by
the
> > >> > > dialog
> > >> > > box do not put in any quotes (single or double) just put the
value
> > >> > > in.
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > >
> > >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> > >> I'm not sure what you mean by non-named parameters. RS uses the
@.
> > >> format
> > >> > >> for parameters in queries, like so:
> > >> > >>
> > >> > >> ... WHERE FieldName = @.ParameterName
> > >> > >>
> > >> > >> --
> > >> > >> '(' Jeff A. Stucker
> > >> > >> \
> > >> > >>
> > >> > >> Business Intelligence
> > >> > >> www.criadvantage.com
> > >> > >> ---
> > >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > >> > >> > Hi all,
> > >> > >> >
> > >> > >> > I have to build a report from SQL tables thru an ODBC server.
When
> > > I
> > >> > >> > try to pass (nonnamed) parameters to the report, the SQL
request
> > > runs
> > >> > >> > but returns nothing, as if the value was not understood. I
have
> > >> > >> > put
> > >> > >> > "?" in the SQL request in place of the parameter.
> > >> > >> > Do I need to do something else ? What is the format of the
capture
> > > in
> > >> > >> > the run-time parameter dialog box : are quotes needed, double
> > > quotes,
> > >> > >> > or other character ?
> > >> > >> >
> > >> > >> > Thanks ODBC users !
> > >> > >> >
> > >> > >> > Jean-Marc
> > >> > >>
> > >> > >>
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Hey Bruce, Thanks so much! You know, I never even thought of using
expressions within the Query Designer... You are right that this approach is
not very elegant, but its good to know that there is a last resort...I think
I am going to investigate using a custom data extension to just read a
dataset produced by an external proram....and see if that can help in
anyway...
But once again, thanks for the tip...
"Bruce L-C [MVP]" wrote:
> I have the same issue. I have a whole lot of reports that are going against
> a process control database in Sybase and will be switched to go against a
> datamart in SQL Server. I would love to have an easy way to switch it back
> if I needed to. Putting aside having compatible SQL (Sybase does not have
> Top and some other SQL) I don't see an easy way to do this. I plan on
> manually changing the reports by going into the RDL and changing it (rather
> than doing it from the IDE). The only way I can see this would work is if
> you make your dataset sql be based on an expression. You can use the generic
> query screen and do this:
> = "Select * from something where somefield = " & Parameters!ParamName.value
> But if you have a date or character field you have to do this:
> = "Select * from something where somefield = '" & Parameters!ParamName.value
> & "'" (that is a double quote, a single quote, a double quote)
> So it is slow and potentially buggy. When done though if the SQL created is
> compatible this will work against any databases.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C4540199-F9D9-4D37-A491-BC5EE4AEE91A@.microsoft.com...
> > Hi Jeff,
> > Did you get any answers to your big question? I find myself in the exact
> > same situation (except its either Sql Server or Sybase)..I was just
> curious
> > what you did to solve this problem...
> > Thanks...
> > --Aparna.
> >
> > "Jeff A. Stucker" wrote:
> >
> > > So the big question is: What's the best way to manage reports that need
> to
> > > work against both SQL Server and Oracle databases, depending where they
> are
> > > deployed? (The database structure will be identical, but the platform
> is
> > > customer-specific.)
> > >
> > > Thanks,
> > >
> > > --
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > > news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > > > The syntax used by the managed Oracle provider to mark named
> parameters is
> > > > a
> > > > colon (":") instead of @.. This is nothing specific to RS, this is just
> how
> > > > the data provider works.
> > > > E.g., select * from emp where deptno = :Dept
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > rights.
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > >> I'm not sure about Oracle. Oracle might be the same and it might not.
> The
> > > >> reason is that Oracle is a special case. Here is a tidbit for you.
> Only
> > > >> Oracle and SQL Server use the dotnet managed provider in RS. But,
> wait,
> > > >> it
> > > >> is not that simple. When you use the GUI query designer it uses the
> OLEDB
> > > >> provider because the GUI query designer knows nothing about dotnet.
> But,
> > > > if
> > > >> you go to the generic designer it uses the managed provider. At
> runtime
> > > > it
> > > >> uses the managed provider. The reason I mention this is two fold.
> First,
> > > > so
> > > >> all testing is done with the same provider as used at runtime I would
> > > >> recommend always going to the generic query designer. Second, I'm not
> > > >> sure
> > > >> whether managed provider uses named parameters or not. If it does
> then
> > > >> you
> > > >> will not have to change this.
> > > >>
> > > >> One last point, the next version should have a query designer that
> knows
> > > >> about dotnet (V2, not SP2).
> > > >>
> > > >> --
> > > >> Bruce Loehle-Conger
> > > >> MVP SQL Server Reporting Services
> > > >>
> > > >>
> > > >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > >> > Thanks for the correction, Bruce.
> > > >> >
> > > >> > Rats! Now I've got more work to do when we port to Oracle.
> > > >> >
> > > >> > ...sigh...
> > > >> > --
> > > >> > '(' Jeff A. Stucker
> > > >> > \
> > > >> >
> > > >> > Business Intelligence
> > > >> > www.criadvantage.com
> > > >> > ---
> > > >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > > >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > > > needs
> > > >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > > >> > >
> > > >> > > It sounds to me like you are trying to put in the values in the
> > > >> > > dialog
> > > >> box
> > > >> > > that pops up with you hit the exclamation point. When prompted by
> the
> > > >> > > dialog
> > > >> > > box do not put in any quotes (single or double) just put the
> value
> > > >> > > in.
> > > >> > >
> > > >> > > --
> > > >> > > Bruce Loehle-Conger
> > > >> > > MVP SQL Server Reporting Services
> > > >> > >
> > > >> > >
> > > >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > > >> > >> I'm not sure what you mean by non-named parameters. RS uses the
> @.
> > > >> format
> > > >> > >> for parameters in queries, like so:
> > > >> > >>
> > > >> > >> ... WHERE FieldName = @.ParameterName
> > > >> > >>
> > > >> > >> --
> > > >> > >> '(' Jeff A. Stucker
> > > >> > >> \
> > > >> > >>
> > > >> > >> Business Intelligence
> > > >> > >> www.criadvantage.com
> > > >> > >> ---
> > > >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > > >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > > >> > >> > Hi all,
> > > >> > >> >
> > > >> > >> > I have to build a report from SQL tables thru an ODBC server.
> When
> > > > I
> > > >> > >> > try to pass (nonnamed) parameters to the report, the SQL
> request
> > > > runs
> > > >> > >> > but returns nothing, as if the value was not understood. I
> have
> > > >> > >> > put
> > > >> > >> > "?" in the SQL request in place of the parameter.
> > > >> > >> > Do I need to do something else ? What is the format of the
> capture
> > > > in
> > > >> > >> > the run-time parameter dialog box : are quotes needed, double
> > > > quotes,
> > > >> > >> > or other character ?
> > > >> > >> >
> > > >> > >> > Thanks ODBC users !
> > > >> > >> >
> > > >> > >> > Jean-Marc
> > > >> > >>
> > > >> > >>
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >
>
>

parameters with multi value and navigation

Hello.

I have a report with parameters include multiple value.

I configure "Jamp To Report" under navigation and I would like to forward to a new reports the same parameters user select in the source report undel multiple value parameters.

in the text box I use =join(Parameters!Ds.Label ,", ") but how can I forward the selected parameters to the new report

Thanks

Idan

Please read this related posting: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

-- Robert

parameters with multi value and navigation

Hello.
I have a report with parameters include multiple value.
I configure "Jamp To Report" under navigation and I would like to
forward to a new reports the same parameters user select in the source
report undel multiple value parameters.
in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
forward the selected parameters to the new report
Thanks
IdanHi,
The parameter used in both the query should be same.
Amarnath
"Idan" wrote:
> Hello.
> I have a report with parameters include multiple value.
> I configure "Jamp To Report" under navigation and I would like to
> forward to a new reports the same parameters user select in the source
> report undel multiple value parameters.
>
> in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
> forward the selected parameters to the new report
> Thanks
> Idan
>

Parameters with Crystal Reports.

I have set up some (three to be exact) parameters in a report that I want to
be able to print. Before I set the parameters everything worked just fine. I
set the datasource and the printout worked great. Now I add parameters to the
report and it seems that no matter what I do, I get an exception indicating
that I have not set the parameters. Here is the latest try (this is Winform
code):
Dim rpt As New QuoteDetail
Dim disVal As New CrystalDecisions.Shared.ParameterDiscreteValue()
disVal.Value = "Net 30"
rpt.Parameter_Terms.CurrentValues.Add(disVal)
disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
disVal.Value = q.FileName
rpt.Parameter_QuoteFile.CurrentValues.Add(disVal)
disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
disVal.Value = "Me"
rpt.Parameter_PreparerName.CurrentValues.Add(disVal)
rpt.SetDataSource(ds)
rpt.PrintToPrinter(1, False, 0, 0)
rpt.Dispose()
One interesting thing that I learned from several sessions in the debugger.
Before the SetDataSource there are current values for each of the parameters.
After the call to SetDataSource it seems that the current values go away.
I would like to just be able to set values for these parameters and print,
like a function call. Any suggestions on what I am doing wrong?
Thank you.
KevinThis forum is for Reporting Services, the reporting product from MS that
competes with Crystal. I know Crystal is still bundled with VS. I would
suggest a Visual Studio newsgroup. Not really sure where the best place is
for you to go for an answer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:F3EA2C93-0DB0-4CB4-9A65-815EBAD105C4@.microsoft.com...
>I have set up some (three to be exact) parameters in a report that I want
>to
> be able to print. Before I set the parameters everything worked just fine.
> I
> set the datasource and the printout worked great. Now I add parameters to
> the
> report and it seems that no matter what I do, I get an exception
> indicating
> that I have not set the parameters. Here is the latest try (this is
> Winform
> code):
> Dim rpt As New QuoteDetail
> Dim disVal As New
> CrystalDecisions.Shared.ParameterDiscreteValue()
> disVal.Value = "Net 30"
> rpt.Parameter_Terms.CurrentValues.Add(disVal)
> disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
> disVal.Value = q.FileName
> rpt.Parameter_QuoteFile.CurrentValues.Add(disVal)
> disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
> disVal.Value = "Me"
> rpt.Parameter_PreparerName.CurrentValues.Add(disVal)
> rpt.SetDataSource(ds)
> rpt.PrintToPrinter(1, False, 0, 0)
> rpt.Dispose()
> One interesting thing that I learned from several sessions in the
> debugger.
> Before the SetDataSource there are current values for each of the
> parameters.
> After the call to SetDataSource it seems that the current values go away.
> I would like to just be able to set values for these parameters and print,
> like a function call. Any suggestions on what I am doing wrong?
> Thank you.
>
> Kevin