Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

In one SSRS report, can we sync data from both table and SSAS cube? Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 11:46 AM
Points: 11, Visits: 46
1. can I take 1 source from table and other source from SSAS cube and do the "Report Server Project"
2. can I take 1 source from table and other source from SSAS cube and do the "Report Model Project" (for users to create Ad hoc Reports)
Post #1370073
Posted Monday, October 8, 2012 5:52 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 1,219, Visits: 1,263
Have you tried it? What did you find? Go to your test environment and try some test examples in SSRS.

Rob

One thought is that you could do an OPENROWSET to get the cube data and union with your relational data.
Post #1370118
Posted Tuesday, October 9, 2012 4:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
kothakeerthi (10/8/2012)
1. can I take 1 source from table and other source from SSAS cube and do the "Report Server Project"
2. can I take 1 source from table and other source from SSAS cube and do the "Report Model Project" (for users to create Ad hoc Reports)


In the report you can create two different datasets one pointing to relational and other to OLAP. And then you can use Lookup/LookupSet function to join data from those two datasets.

OR

In the stored procedure, you can write an MDX query in a variable (say @mdx) to get the OLAP data and save it to a temp table. For that you need to create a Linked server (to OLAP DB). And use this @mdx variable in an OPENQUERY command (alongwith insert and select statements to fill the temp table). Then you can join the temp table along with relation table to get the appropriate data.


BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Post #1370283
Posted Tuesday, October 9, 2012 4:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 11:26 PM
Points: 116, Visits: 397
Hi,
yes you can use two datasource SSAS and TSQL see these two links

http://www.bi-rootdata.com/2012/09/execute-mdx-query-with-tsql.html
http://www.bi-rootdata.com/2012/10/passing-multi-value-tsql-parameter-to.html
Post #1370284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse