× Data Warehousing and Data integration

Pivot in a report with dynamic columns

  • Posts: 40
  • Thank you received: 3

Pivot in a report with dynamic columns was created by Vassalli

I am in need to create a report with dynamic columns.
The columns are weeks number, starting from actual week (12-2022), going back for 52 weeks (13-2021) in a rolling year way.
The report will be executed once per week, so next week I'll have 13-2022 up to 14-2021 and so on.
Data comes from some columns:
External consultant in charge of a customer, name of the customer, name of the person of the customer who provided an order, weeks number, year number, email address, day of the order (for this field duplicates are admitted).
I am in need to have the number of the orders done each week, using a normal sql count.
I can eventually create a pivot with rolling week numbers, with some code like that:
select * into tab from cust_table
declare @sql nvarchar(max), @cols varchar(max)
select @cols = coalesce(@cols + ',', '') + ''
In this case I should create a variable and use those variables (52, like the number of the weeks in a year) and define them.
Is it there any more simple solution?
Thank you! :)
4 months 3 weeks ago #21690

Please Log in or Create an account to join the conversation.

  • Posts: 8507
  • Karma: 33
  • Thank you received: 570

Replied by admin on topic Pivot in a report with dynamic columns

When you say report do you mean this kind of report?
www.etl-tools.com/wiki/aetle/reports?s[]=report
Mike
ETL Architect
4 months 3 weeks ago #21691

Please Log in or Create an account to join the conversation.

  • Posts: 40
  • Thank you received: 3

Replied by Vassalli on topic Pivot in a report with dynamic columns

Yes Mike,
unfortunately I am in need to create a pdf report for each of my colleague and send it via email. :(
4 months 3 weeks ago #21692

Please Log in or Create an account to join the conversation.

  • Posts: 8507
  • Karma: 33
  • Thank you received: 570

Replied by admin on topic Pivot in a report with dynamic columns

Looks like an interesting challenge ;-)

Can you prove us with some examples, please

Here how my source data looks like (excel file)
And here is what I am trying to create (excel file)
Mike
ETL Architect
4 months 3 weeks ago #21693

Please Log in or Create an account to join the conversation.

  • Posts: 40
  • Thank you received: 3

Replied by Vassalli on topic Pivot in a report with dynamic columns

Perfect, I'll try to create a test environment. And I'll give you some excel files. :)
The following user(s) said Thank You: admin
4 months 3 weeks ago #21694

Please Log in or Create an account to join the conversation.

  • Posts: 40
  • Thank you received: 3

Replied by Vassalli on topic Pivot in a report with dynamic columns

Hi Mike,
here it is: it's an excel file with a pivot inside.

db is the database, while sheet 1 is the graphical representation of the database that I would like to get.

Each "Representative" should receive it's own subset of data in a pdf, once per week, and the row containing week numbers should be desc (current week is the first, last week is current week+1 of the last year).

Thank you! :)
4 months 3 weeks ago #21696
Attachments:

Please Log in or Create an account to join the conversation.

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies