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

 All Forums
 Development Tools
 Reporting Services Development
 Show SQL Query with dynamic columns to RS

Author  Topic 

A-bsal
Starting Member

9 Posts

Posted - 2012-12-20 : 09:24:53
Hi all

I have the SQL query results with Pivot Table in the first column I have the name of a product, and the following columns, filtering dates for the product, which the filter days (start date - end date) I have more columns or less columns of the query result, each product showing me, how many have been sold.

For example:

name 1-12 2-12 3-12 ...

prod1 5 10 15 ...



As I can show this table as it is in a table or matrix within the report?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 11:24:25
you've to use matrix with name field as column group

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 02:38:34
but the problem is that not as the columns are called
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 02:40:58
sorry didnt get that. can you explain wit an example?

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 02:59:18
for example, has made ??a query where the filter is from December 1 to December 3 and the result of the query is:
columnName: name - 1Dec - 2Dec - 3Dec
resultQuery: porod1 - 2 - 3 - 5

and another query where the filter is the 5th of December to 7 December and the result of the query is:
columnName: name - 5Dec - 6Dec - 7Dec
resultQuery: porod1 - 4 - 8 - 9
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 03:45:22
ok..so whats the issue? you just need to add dates field as column group in matrix

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 03:53:45
That I have done, but as I have to configure Reporting Services to display the results correctly?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 04:10:26
can you be more specific. what configuration are you taking about? if possible, post a screenshot and explain your issue. cant make out much from your explanation.

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 04:40:01
this is result my query:

and this I would like the result to look like this, but with the values ??of the query:
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 04:47:06
oh...you already pivoted it? if you want dynamic columns to appear as values in header easiest thing is to bring them as rows and then do the pivotting in report using matrix.

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 04:54:58
and how I can make the pivot on the same report? Because the dates if I can get.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 04:58:48
your query/dataset will bring one row per date as per parameters passed and in report matrix will pivot them onto columns

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 05:10:56
and how to do the pivot in the matrix?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 05:15:43
you dont have to do any pivot. Just include field containing dates inside column group for matrix and apply some aggregates over data field (min,max,sum,avg etc) and you'll get data pivoted automatically

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 06:29:03
first of all I have to say thanks for all the help you are giving me qye.

Do not quite understand, as shown in the image, which Deveria putting data in cell Data "Datos" for the result then show me results that are in the query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 07:55:10
whats the field you're trying to show as data? that what you need to place under datos

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

Go to Top of Page

A-bsal
Starting Member

9 Posts

Posted - 2012-12-21 : 08:07:10
the numbers that appear in the SQL query
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:23:26
yep

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

Go to Top of Page
   

- Advertisement -