One more question regarding Reporting action

All the reporting action samples I read from AdventureWorks, when pass link and parameter to the report in reporting service, is linked to the report as Analysis Service data source based report.

I am wondering if reporting action can pass parameter to regular relational db based reporting service report not Analysis Service data source based report.

If yes, could you share a simple sample?

I can't see why the data source of a report would matter, when using a reporting action. The Adventure Works examples may all be using an Analysis Services data source just for convenience. Have you tried editing an existing Adventure Works reporting action, to point to a relational report instead?|||

Hi Deepak, thank you so much for your reply! Yes, I did try the following using AW AS and AW sample reports RS projects:

Here is my reporting action:

Name: Sales Order Detail

Target Type: Dimension members

Target object: Internet Sales Order Detail

ReportServer:localhost

ReportPath:ReportServer?/AdventureWorks Sample Reports/Sales Order Detail

Parameters NamealesOrderNumber (Match the one at Sales Order Detail report

Parameter Value: [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Name

And I make sure Sales Order Detail Report is running correctly and parameter is matching the parameter name I defined at the cube reporting action. Then I deploy the cube, however when drop the Sale Order Number dimension and right click, there is no Reporting Action showing up.

Could you give me some pointers?

Thanks

|||

Did you configure the "Caption", under "Additional Properties" - if not, you can refer to the Adventure Works "Sales Reason Comparisons" action, whose Caption is defined as an MDX expression:

"Sales Reason Comparisons for " +

[Product].[Category].CurrentMember.Member_Caption + "..."

|||

Thanks again. Just did that and deployed.

"Sales Reason Comparisons for " + [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Member_Caption + "..."

However reporting action is still not showing. Deepak, anything else I miss?

Thanks

|||Can't think of anything else - are you able to see the standard Reporting action which comes with Adventure Works, ie. "Sales Reason Comparisons"? If so, I would suggest making a test copy of Adventure Works, and progressively modifying this action to incorporate your report in small steps, till it breaks - or maybe it doesn't break!

ole db source editor "preview" throwing an error

i have a data flow configured to use a ole db source. the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection. the data access mode of the ole db source is "sql command". below is the sql command text:

SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?

this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:

Parameter0: User::ExtractStartDate

Parameter1: User::ExtractStopDate

ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004. however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".

as far as i can tell, i have the ole db source configured correctly. thus, i can't figure out why this error is being generated. has anyone else experienced this issue? if so, were you able to resolve it? is this a bug?

thanks in advance.

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

|||

Brian Knight wrote:

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

thanks for the reply. i wonder if this is a bug or a "feature".|||

Not sure it's really either. It was the same way in DTS. Essentially the provider can't parse the literal query. Probably the ultimate answer is to perform the preview and parse like Reporting Services does where it pops open a box asking for the variable values you'd like to parse. As it stands, SSIS or DTS doesn't have enough info to perform a parse.

Brian

ole db source editor "preview" throwing an error

i have a data flow configured to use a ole db source. the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection. the data access mode of the ole db source is "sql command". below is the sql command text:

SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?

this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:

Parameter0: User::ExtractStartDate

Parameter1: User::ExtractStopDate

ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004. however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".

as far as i can tell, i have the ole db source configured correctly. thus, i can't figure out why this error is being generated. has anyone else experienced this issue? if so, were you able to resolve it? is this a bug?

thanks in advance.

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

|||

Brian Knight wrote:

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

thanks for the reply. i wonder if this is a bug or a "feature".|||

Not sure it's really either. It was the same way in DTS. Essentially the provider can't parse the literal query. Probably the ultimate answer is to perform the preview and parse like Reporting Services does where it pops open a box asking for the variable values you'd like to parse. As it stands, SSIS or DTS doesn't have enough info to perform a parse.

Brian